Simple minds think alike

より多くの可能性を

トップ > sql > 【SQL】limitを使わずグループ毎に先頭N件を取得する

【SQL】limitを使わずグループ毎に先頭N件を取得する

sql MySQL

例えば、データベースに以下のような書籍テーブル、書籍注文数テーブルがあって、売れている本をカテゴリ毎に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