【SQL】limitを使わずグループ毎に先頭N件を取得する
例えば、データベースに以下のような書籍テーブル、書籍注文数テーブルがあって、売れている本をカテゴリ毎に1件ずつ取得したい、というようなことがあります。
【書籍テーブル(books)】
id | 書籍名(name) | カテゴリ(category) |
---|---|---|
1 | でこぼこホットケーキ 世界文化社のワンダー絵本 | 絵本 |
2 | 1日10分でちずをおぼえる絵本 | 絵本 |
3 | FACTFULNESS | ビジネス書 |
4 | 1分で話せ | ビジネス書 |
5 | アフターコロナ 見えてきた7つのメガトレンド | ビジネス書 |
6 | HARD THINGS | IT |
【書籍注文数テーブル(books_orders)】
id | 書籍id(book_id) | 注文数(quantity) |
---|---|---|
1 | 1 | 2 |
2 | 1 | 3 |
3 | 2 | 7 |
4 | 3 | 1 |
5 | 3 | 8 |
6 | 3 | 4 |
7 | 4 | 3 |
8 | 4 | 5 |
9 | 5 | 9 |
10 | 5 | 3 |
11 | 6 | 4 |
12 | 6 | 1 |
【欲しい結果】
書籍id | 書籍名 | カテゴリ |
---|---|---|
2 | 1日10分でちずをおぼえる絵本 | 絵本 |
3 | FACTFULNESS | ビジネス書 |
6 | HARD THINGS | IT |
欲しい結果を取得するSQLクエリにWindow関数(後述)が使えると楽なのですが、古いのバージョンのMySQLやAmazon Aurora(MySQL 5.7まで互換)だとWindow関数を使えないので、他の方法にする必要があります。最近のMySQLやOracle, PostgreSQLデータベースだとWindow関数を使えます。
GROUP_CONCAT, FIND_IN_SETを使った書き方
GROUP_CONCATと, FIND_IN_SETを使った書き方です。( MySQL 5.7
で確認しました。 )
まずは、書籍毎の注文数合計が分からないと並び替えられないので、以下のデータを取得するSQLクエリを作ります。
書籍id(book_id) | 注文数合計(total_quantity) |
---|---|
1 | 5 |
2 | 7 |
3 | 13 |
4 | 8 |
5 | 12 |
6 | 5 |
書籍注文数テーブル(books_orders)を、書籍id(book_id)毎に集計すれば良いのでこんなSQLクエリになります。
select book_id, sum(quantity) as total_quantity from book_orders group by book_id
次にカテゴリ別に順位が並び変っている以下のデータを取得するSQLクエリを作ります。
カテゴリ(category) | 書籍idリスト(book_id_list) |
---|---|
絵本 | 2,1 |
ビジネス書 | 3,5,4 |
IT | 6 |
先程取得したデータに書籍テーブルを書籍idで結合して、カテゴリでgroup byしたうえで、GROUP_CONCATに book_id order by total_quantity desc
を入れると上の表のデータが取得できます。
SQLクエリはこんな感じになります。
select category, group_concat(book_orders_group_by_id.book_id order by total_quantity desc) as book_id_list from books, ( select book_id, sum(quantity) as total_quantity from book_orders group by book_id ) book_orders_group_by_id where books.id = book_orders_group_by_id.book_id group by books.category
次は、カテゴリ毎の順番を書籍テーブルにくっつけたデータを取得してみます。
書籍id | 書籍名 | カテゴリ | カテゴリ内の順番(rank) |
---|---|---|---|
1 | でこぼこホットケーキ 世界文化社のワンダー絵本 | 絵本 | 2 |
2 | 1日10分でちずをおぼえる絵本 | 絵本 | 1 |
3 | FACTFULNESS | ビジネス書 | 1 |
4 | 1分で話せ | ビジネス書 | 3 |
5 | アフターコロナ 見えてきた7つのメガトレンド | ビジネス書 | 2 |
6 | HARD THINGS | IT | 1 |
カテゴリ内での書籍の順位は分かっているので、FIND_IN_SETを使って該当書籍が何番目にあるのかを探すSQLクエリを書きます。
取得データに書籍テーブルをカテゴリで結合して、 FIND_IN_SET( books.id, book_id_list ) as rank
のように
- 第一引数に探したいもの(書籍id)
- 第二引数に探す対象のリスト(上で取得したカテゴリ毎の順位のリスト)
を指定します。
SELECT books.id, books.name, books.category, FIND_IN_SET( books.id, book_id_list ) as rank FROM books, ( SELECT category, GROUP_CONCAT( book_orders_group_by_id.book_id order by total_quantity desc ) as book_id_list from books, ( select book_id, sum(quantity) as total_quantity from book_orders group by book_id ) book_orders_group_by_id where books.id = book_orders_group_by_id.book_id group by books.category ) book_ids_ranked_by_category where books.category = book_ids_ranked_by_category.category
最後に順位をカテゴリ内の順番(rank)が1のものに絞れば、欲しい結果が取得できます。
書籍id | 書籍名 | カテゴリ |
---|---|---|
2 | 1日10分でちずをおぼえる絵本 | 絵本 |
3 | FACTFULNESS | ビジネス書 |
6 | HARD THINGS | IT |
select ranked_books.id, ranked_books.name, ranked_books.category from ( SELECT books.id, books.name, books.category, FIND_IN_SET( books.id, book_id_list ) as rank FROM books, ( SELECT category, GROUP_CONCAT( book_orders_group_by_id.book_id order by total_quantity desc ) as book_id_list from books, ( select book_id, sum(quantity) as total_quantity from book_orders group by book_id ) book_orders_group_by_id where books.id = book_orders_group_by_id.book_id group by books.category ) book_ids_ranked_by_category WHERE books.category = book_ids_ranked_by_category.category ) ranked_books where rank = 1
GROUP_CONCATを使った書き方のデメリットとしては、GROUP_CONCAT関数の結果が切れる事象が発生することがあります。これを防ぐには、例えば、MySQLだと group_concat_max_len
という設定値を変更すれば大丈夫です。
Window関数を使った書き方
Window関数を使った書き方にするとだいぶシンプルです。( PostgreSQL 10.12
で確認しました。 )
select * from ( select books.name, books.category row_number() over (partition by books.category order by book_orders_group_by_id.total_quantity desc) as rank from books, ( select book_id, sum( quantity ) as total_quantity from book_orders group by book_id ) book_orders_group_by_id where books.id = book_orders_group_by_id.book_id ) ranked_book_orders where rank = 1