🎲

SQLで第二ソートキーを考慮して並べ替えたい

2023/01/20に公開

SQL

tech

やりたいこと

SQLで第二ソートkeyも考慮した最上位のデータを表示したいという要件があり、その解決時に少し苦労したのでメモ程度に書いておきたいと思います。

要件は以下です。

productsテーブルのカラムは以下の画像のような構造になっている。
catalog_idが同一のもので集計を行い、第一ソートkeyはprice第二ソートkeyはcreated_atいずれも降順としてソートする。
ソートした中で一番順位が高いもののみを表示する

TypeScriptでソートした場合は以下のような実装になるイメージです。

type Product = {
id: number
price: number
createdAt: string
catalogID: number
}

const products: Product[] = [
  {
    id: 1,
    price: 5000,
    createdAt: "2023-01-01T09:00:00.000000+00:00",
    catalogID: 99
  },
  {
    id: 2,
    price: 5000,
    createdAt: "2023-01-02T09:00:00.000000+00:00",
    catalogID: 99
  },
]

const filterResults = products.sort((a: Product, b: Product) => {
	if (a.price > b.price) return -1;
	if (a.price < b.price) return 1;
	// 得点は降順
	if (a.createdAt > b.createdAt) return -1;
	if (a.createdAt < b.createdAt) return 1;
	return 0;
})

consol.log(filterResults)


type Product = {
  id: number
  price: number
  createdAt: string
  }
  
  const products: Product[] = [
    {
      id: 1,
      price: 5000,
      createdAt: "2023-01-01T09:00:00.000000+00:00",
      catalogID: 99
    },
    {
      id: 2,
      price: 5000,
      createdAt: "2023-01-02T09:00:00.000000+00:00",
      catalogID: 99
    },
  ]
  
  const filterResults = products.sort((a: Product, b: Product) => {
    if (a.price > b.price) return -1;
    if (a.price < b.price) return 1;
    // 得点は降順
    if (a.createdAt > b.createdAt) return -1;
    if (a.createdAt < b.createdAt) return 1;
    return 0;
  })
  
  console.log(filterResults[0])

  // [LOG]: {
  //   "id": 2,
  //   "price": 5000,
  //   "createdAt": "2023-01-02T09:00:00.000000+00:00",
  //   "catalogID": 99
  // }

実現方法

上記で定義した仕様を満たすために
ROW()とROW_NUMBER()という関数を使用しました。
MySQLやPostgreSQLなどよく使われているRDBMSでは標準装備されています。

この両者の違いは以下です。

RANK()	ROW_NUMBER()
同値の場合順位は同じになり、その次は順位を飛ばす。（1位, １位, 3位）	同値の場合でも順位は飛ばさずにカウントする。（1位, 2位, 3位）

クエリは以下のようになります。

WITH highest_price_prodcuts AS(
  SELECT
      *
  FROM
      (
          SELECT
              *,
              RANK() over(partition by catalog_id order by price desc) rownum
          FROM
            products
      ) with_rownum
  where
      rownum = 1
)
  SELECT
      *
  FROM
      (
          SELECT
              *,
              ROW_NUMBER() over(partition by catalog_id order by created_at desc) AS update_at_row_number
          FROM
              highest_price_prodcuts
      ) with_rownum
  where
      update_at_row_number = 1

感想

実装してみたところ、サブクエリに分けてクエリを書いてみると、段階を踏んでソートしている様子がイメージしやすいので見通しの良いクエリが作れたなと思っています。

やりたいこと

実現方法

感想

Discussion