DB上でソートして上位n件のデータを取得してみた｜ITエンジニアとして経験・学習したこと

前回、Oracle上でソートして上位n件のデータを取得するSQLを作成したが、今回は他のDB上で同様のSQLを作成してみたので、共有する。

前提条件

下記記事に記載されている、MySQLとPostgreSQLでのユーザー作成とUSER_DATAテーブル作成が完了していること。

MySQLでユーザーを作成しテーブルを追加してみたMySQLの場合は、Workbenchを利用すると、スキーマ(データベース)やユーザーの作成をGUIベースで実施できる。今回は、Work...

Postgresqlでユーザーを作成しテーブルを追加してみたPostgresqlの場合は、pgAdminを利用すると、ユーザーやデータベースの作成をGUIベースで実施できる。今回は、pgAdmin...

また、下記記事の「前提条件」の内容が完了していること。

Spring BootでSQL Serverに接続しMyBatisを利用してみた今回は、Spring Bootアプリケーションで接続するデータベースをSQL Serverに変更してみたので、そのサンプルプログラムを共...

さらに、各DB上のUSER_DATAテーブルに、以下のデータが追加されていること。（下記はOracleの例）

SELECT * FROM USER_DATA

1	SELECT * FROM USER_DATA

Oracleの場合の実行結果

Oracleの場合の実行結果は、下記記事を参照のこと。

Oracle上でソートして上位n件のデータを取得してみたデータベース上のデータを抽出する際、ある一定の規則でソートして上位n件のデータを取得したい、という場合がある。今回は、Oracle上で、...

MySQLの場合の実行結果

MySQLで、ROW_NUMBER分析関数を利用し、USER_DATAテーブルのデータを、生年月日と性別を加工し、IDの昇順にソートした後で、上位5件のみを取得するSQLの実行結果は、以下の通り。ROW_NUMBER分析関数の記載方法は、Oracleの場合と同じになる。

SELECT usr.* 
FROM(
    SELECT 
        ID, 
        NAME, 
        STR_TO_DATE(
            CONCAT(LPAD(BIRTH_YEAR, 4, '0')
                 , LPAD(BIRTH_MONTH, 2, '0')
                 , LPAD(BIRTH_DAY, 2, '0')) 
            , '%Y%m%d')
        AS BIRTHDAY,
        CASE SEX 
            WHEN '1' THEN '男'
            WHEN '2' THEN '女'
            ELSE ''
        END AS SEX_STR,
        MEMO,
        ROW_NUMBER() OVER (ORDER BY ID ASC) RN
    FROM USER_DATA
) usr
WHERE usr.RN <= 5

SELECT usr.*

FROM(

SELECT

ID,

NAME,

STR_TO_DATE(

CONCAT(LPAD(BIRTH_YEAR, 4, '0')

, LPAD(BIRTH_MONTH, 2, '0')

, LPAD(BIRTH_DAY, 2, '0'))

, '%Y%m%d')

AS BIRTHDAY,

CASE SEX

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE ''

END AS SEX_STR,

MEMO,

ROW_NUMBER() OVER (ORDER BY ID ASC) RN

FROM USER_DATA

) usr

WHERE usr.RN <= 5

また、先ほどのSQLと同様のことを、ROW_NUMBER 分析関数を利用しないで実行した結果は、以下の通り。ソート処理は副問い合わせの外で行い、LIMIT句で上位5件の取得を行っている。

SELECT usr.* 
FROM(
    SELECT 
        ID, 
        NAME, 
        STR_TO_DATE(
            CONCAT(LPAD(BIRTH_YEAR, 4, '0')
                 , LPAD(BIRTH_MONTH, 2, '0')
                 , LPAD(BIRTH_DAY, 2, '0')) 
           , '%Y%m%d')
        AS BIRTHDAY,
        CASE SEX 
            WHEN '1' THEN '男'
            WHEN '2' THEN '女'
            ELSE ''
        END AS SEX_STR,
        MEMO
    FROM USER_DATA
) usr
ORDER BY usr.ID
LIMIT 5

SELECT usr.*

FROM(

SELECT

ID,

NAME,

STR_TO_DATE(

CONCAT(LPAD(BIRTH_YEAR, 4, '0')

, LPAD(BIRTH_MONTH, 2, '0')

, LPAD(BIRTH_DAY, 2, '0'))

, '%Y%m%d')

AS BIRTHDAY,

CASE SEX

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE ''

END AS SEX_STR,

MEMO

FROM USER_DATA

) usr

ORDER BY usr.ID

LIMIT 5

PostgreSQLの場合の実行結果

PostgreSQLで、ROW_NUMBER分析関数を利用し、USER_DATAテーブルのデータを、生年月日と性別を加工し、IDの昇順にソートした後で、上位5件のみを取得するSQLの実行結果は、以下の通り。ROW_NUMBER分析関数の記載方法は、Oracleの場合と同じになる。

SELECT usr.* 
FROM(
    SELECT 
        ID, 
        NAME, 
       TO_DATE(
             CONCAT(TO_CHAR(BIRTH_YEAR, '0000')
                  , TO_CHAR(BIRTH_MONTH, '00')
                  , TO_CHAR(BIRTH_DAY, '00'))
            , 'YYYYMMDD')
        AS BIRTHDAY,
        CASE SEX 
            WHEN '1' THEN '男'
            WHEN '2' THEN '女'
            ELSE ''
        END AS SEX_STR,
        MEMO,
        ROW_NUMBER() OVER (ORDER BY ID ASC) RN
    FROM USER_DATA
) usr
WHERE usr.RN <= 5

SELECT usr.*

FROM(

SELECT

ID,

NAME,

TO_DATE(

CONCAT(TO_CHAR(BIRTH_YEAR, '0000')

, TO_CHAR(BIRTH_MONTH, '00')

, TO_CHAR(BIRTH_DAY, '00'))

, 'YYYYMMDD')

AS BIRTHDAY,

CASE SEX

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE ''

END AS SEX_STR,

MEMO,

ROW_NUMBER() OVER (ORDER BY ID ASC) RN

FROM USER_DATA

) usr

WHERE usr.RN <= 5

また、先ほどのSQLと同様のことを、ROW_NUMBER 分析関数を利用しないで実行した結果は、以下の通り。MySQLの場合と同様に、ソート処理は副問い合わせの外で行い、LIMIT句で上位5件の取得を行っている。

SELECT usr.* 
FROM(
    SELECT 
        ID, 
        NAME, 
       TO_DATE(
             CONCAT(TO_CHAR(BIRTH_YEAR, '0000')
                  , TO_CHAR(BIRTH_MONTH, '00')
                  , TO_CHAR(BIRTH_DAY, '00'))
            , 'YYYYMMDD')
        AS BIRTHDAY,
        CASE SEX 
            WHEN '1' THEN '男'
            WHEN '2' THEN '女'
            ELSE ''
        END AS SEX_STR,
        MEMO
    FROM USER_DATA
) usr
ORDER BY usr.ID ASC
LIMIT 5

SELECT usr.*

FROM(

SELECT

ID,

NAME,

TO_DATE(

CONCAT(TO_CHAR(BIRTH_YEAR, '0000')

, TO_CHAR(BIRTH_MONTH, '00')

, TO_CHAR(BIRTH_DAY, '00'))

, 'YYYYMMDD')

AS BIRTHDAY,

CASE SEX

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE ''

END AS SEX_STR,

MEMO

FROM USER_DATA

) usr

ORDER BY usr.ID ASC

LIMIT 5

SQL Serverの場合の実行結果

SQL Serverで、ROW_NUMBER分析関数を利用し、USER_DATAテーブルのデータを、生年月日と性別を加工し、IDの昇順にソートした後で、上位5件のみを取得するSQLの実行結果は、以下の通り。ROW_NUMBER分析関数の記載方法は、Oracleの場合と同じになる。

SELECT usr.* 
FROM(
    SELECT 
        ID, 
        NAME, 
        CONVERT(DATETIME
            , (FORMAT(BIRTH_YEAR,'0000') 
             + FORMAT(BIRTH_MONTH,'00') 
             + FORMAT(BIRTH_DAY,'00')))
        AS BIRTHDAY,
        CASE SEX 
            WHEN '1' THEN '男'
            WHEN '2' THEN '女'
            ELSE ''
        END AS SEX_STR,
        MEMO,
        ROW_NUMBER() OVER (ORDER BY ID ASC) RN
    FROM USER_DATA
) usr
WHERE usr.RN <= 5

SELECT usr.*

FROM(

SELECT

ID,

NAME,

CONVERT(DATETIME

, (FORMAT(BIRTH_YEAR,'0000')

+ FORMAT(BIRTH_MONTH,'00')

+ FORMAT(BIRTH_DAY,'00')))

AS BIRTHDAY,

CASE SEX

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE ''

END AS SEX_STR,

MEMO,

ROW_NUMBER() OVER (ORDER BY ID ASC) RN

FROM USER_DATA

) usr

WHERE usr.RN <= 5

また、先ほどのSQLと同様のことを、ROW_NUMBER 分析関数を利用しないで実行した結果は、以下の通り。ソート処理は副問い合わせの外で行い、先頭のSELECTに付与しているTOP句で上位5件の取得を行っている。

SELECT TOP 5 usr.* 
FROM(
    SELECT 
        ID, 
        NAME, 
        CONVERT(DATETIME
            , (FORMAT(BIRTH_YEAR,'0000') 
             + FORMAT(BIRTH_MONTH,'00') 
             + FORMAT(BIRTH_DAY,'00')))
        AS BIRTHDAY,
        CASE SEX 
            WHEN '1' THEN '男'
            WHEN '2' THEN '女'
            ELSE ''
        END AS SEX_STR,
        MEMO
    FROM USER_DATA
) usr
ORDER BY usr.ID ASC

SELECT TOP 5 usr.*

FROM(

SELECT

ID,

NAME,

CONVERT(DATETIME

, (FORMAT(BIRTH_YEAR,'0000')

+ FORMAT(BIRTH_MONTH,'00')

+ FORMAT(BIRTH_DAY,'00')))

AS BIRTHDAY,

CASE SEX

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE ''

END AS SEX_STR,

MEMO

FROM USER_DATA

) usr

ORDER BY usr.ID ASC

要点まとめ

各DB上でソートして上位n件のデータを取得するには、Oracleの場合と同様にROW_NUMBER 分析関数を利用するか、SQLの副問い合わせ外でソートした後で、LIMIT句やTOP句で上位n件を取得すればよい。

前提条件

やってみたこと

Oracleの場合の実行結果

MySQLの場合の実行結果

PostgreSQLの場合の実行結果

SQL Serverの場合の実行結果

要点まとめ

A5M2でトランザクション管理を有効にしてみた

各DB上で表のカラムを変更してみた

SQLServerのUPDATE文でJOIN句とCASE式を使ってみた

各DB上で表からカラムを削除してみた

各DB上でgroup by文を試してみた(データ準備編)

各DB上でgroup by文を試してみた(実践編)