グループごとに最大取得件数を指定して、レコードを取得したい

Question

環境：MYSQL5.7
accountsテーブル(id, name)とclientsテーブル(id, name)、それらをcross join したrecommendテーブル(id, account_id, client_id, recommend_date, open_date)があり、
recommendテーブルからaccountごとに最大10件取得したいです。idを指定した場合だと以下の様になると思うのですが、いちいちidを指定せずに、全accountのrecommendテーブルのデータを最大10件ずつ取得する方法はないでしょうか？
※recommend.recommend_dateは作成時はNULL, SELECTされたタイミングで日時が入ります。
open_dateに日付が入っていない場合は、再度SELECTの対象となります。

select *
from recommend
left join accounts on accounts.id = recommend.account_id
left join clients on clients.id = recommend.client_id
where recommend.account_id = 8206 and open_date is null
order by recommend_date, recommend.client_id desc
limit 10
;

Comments

[yambejp]

サンプルデータをcreate table,insert into でつけて、具体的にほしい結果もあわせて例示してください

[退会済みユーザー]

SQL の質問をするなら、SQL Server なのか、Oracle なのか、MySQL なのか、Access なのか、その他なのか、そのぐらいは書きましょうよ。

[退会済みユーザー]

質問内容を踏まえた件名にしてほしいものです。

[m.ts10806]

SQLに関する質問であることはタグでわかりますのでタイトルに含む必要はありません。もっと具体的に要件にそったタイトルにしてください。

[Orlofsky]

質問のタイトルはどんなことを質問したいのか内容を想像できるものでありたいもの。質問のタイトルを決めるのもネーミングのセンスを磨くチャンスです。

[yambejp]

MySQLですね？ではカテゴリを追加しておいてください。それとサンプルの件は検討していただきましたか？

[Orlofsky]

[MySQL]タグを追加されては？

Answer 1

accountsテーブルとclientsテーブル、それらをcross join したrecommendテーブル

だったら、recommend使用せずに、clientsを10件になるようにして、accountsとcross joinすればいいんじゃないかと。

Comments

[syunmei]

ありがとうございます。予めcross joinでテーブルを作った理由は、再度SELECT対象になったレコードを含めるために作ろうと思いました。

Answer 2

※追記あり。

limitついてるし、PostgreSQLですよね？

指針としては、

• row_number() で account_id ごとの連番を作成する(recommend_order)
• このクエリをサブクエリにして、 recommend_order が10以下の行を抽出

with query1 as (
  select *  -- ← *は止めて必要な列を列挙すること
    , row_number() over (pertition by recommend.account_id order by recommend_date, recommend.client_id desc) recommend_order
  from recommend
    left join accounts on accounts.id = recommend.account_id
    left join clients on clients.id = recommend.client_id
  order by recommend_date, recommend.client_id desc
)
select 
  * -- <- こちらも同じく*はやめること。
from 
  query1
where recommend_order <= 10 ;

環境が用意できなかったので実際には動かしていません。質問者で文法チェック等して動かしてみてください。

---

ここから追記。
ほう、MySQL5.7とな。
他の方が大勢指摘している通り、SQLはRDBSにより使える構文や関数がかなり異なりますので製品名を明記しないとこのように頓珍漢な回答が返ってくることになります。以後気を付けるようにね。

で、ウィンドウ関数が使えないMySQL8.0未満は、質問のようなグループごとに集計をかける処理がとっても苦手です。
それでも無理やり書いてみたらこんな感じになるでしょう。
（account_idごと10件まで取得できることは検証しました。
また問題の本質とは関係ないのでaccoutsとclientsのjoinは省略しました）

select
  query.account_id
  ,query.client_id
  ,query.recommend_date
  ,query.row_number
from(
  select 
    main_r.account_id
  ,main_r.client_id
  ,main_r.recommend_date
  ,(select 
      count(*) 
    from 
      recommend sub_r1 
      where sub_r1.account_id = main_r.account_id and (
        (sub_r1.recommend_date < main_r.recommend_date) 
    or (sub_r1.recommend_date = main_r.recommend_date and sub_r1.client_id > main_r.client_id)) 
     ) +1 row_number
  from
    recommend main_r
  order by row_number
) query
where query.row_number <= 10
order by account_id,row_number
;

count(*)があるサブクエリでrow_number相当の処理をやっています。比較条件を1つずつバラして書くのが大変です。
今回の件ではまだ条件が単純なのでcountとwhere句でやっつけられていますが、もっと複雑な集計条件になると黒魔術なSQLが爆誕することでしょう。

グループから上位N件を取得するクエリの作成は、皆さん本当に苦労されていて、
teratailの過去質問でも例えばこれとか、はたまたこんなのとかいろいろあるのですが、どの質問も「クエリがエレガントじゃねぇなあ」とぼやきながら回答されてます。
本件も私の正直なところ、クエリをフェッチした先で１行ずつ番号付けながら読み込み、同じaccount_id の 11件目以降は読み飛ばすみたいな処理をしたほうが精神衛生上遥かにマシだと考えています。(多分PHPとかrailsとか使っていますよね）
今回の問題は上にかいたもので解決できますが、実際に作るクエリはこれ１つではないと思いますし、取得先で集計作業したほうがきっと楽ですよ、とお伝えしておきます。

以下検証用。

CREATE TABLE `recommend` (
  `account_id` int(11) DEFAULT NULL,
  `client_id` int(11) DEFAULT NULL,
  `recommend_date` varchar(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into db.recommend values (1,1,'20180101');
insert into db.recommend values (1,1,'20180201');
insert into db.recommend values (1,1,'20180301');
insert into db.recommend values (1,2,'20180101');
insert into db.recommend values (1,2,'20180201');
insert into db.recommend values (1,2,'20180301');
insert into db.recommend values (1,3,'20180101');
insert into db.recommend values (1,3,'20180201');
insert into db.recommend values (1,3,'20180301');
insert into db.recommend values (2,1,'20180102');
insert into db.recommend values (2,1,'20180202');
insert into db.recommend values (2,1,'20180302');
insert into db.recommend values (2,2,'20180102');
insert into db.recommend values (2,2,'20180202');
insert into db.recommend values (2,2,'20180302');
insert into db.recommend values (2,3,'20180102');
insert into db.recommend values (2,3,'20180202');
insert into db.recommend values (2,3,'20180302');
insert into db.recommend values (1,11,'20180101');
insert into db.recommend values (1,11,'20180201');
insert into db.recommend values (1,11,'20180301');
insert into db.recommend values (1,12,'20180101');
insert into db.recommend values (1,12,'20180201');
insert into db.recommend values (1,12,'20180301');
insert into db.recommend values (1,13,'20180101');
insert into db.recommend values (1,13,'20180201');
insert into db.recommend values (1,13,'20180301');
insert into db.recommend values (2,11,'20180102');
insert into db.recommend values (2,11,'20180202');
insert into db.recommend values (2,11,'20180302');
insert into db.recommend values (2,12,'20180102');
insert into db.recommend values (2,12,'20180202');
insert into db.recommend values (2,12,'20180302');
insert into db.recommend values (2,13,'20180102');
insert into db.recommend values (2,13,'20180202');
insert into db.recommend values (2,13,'20180302');

Comments

[m.ts10806]

limitだけならMySQLの可能性もあります。質問者さんにはそこも含めて明示していただきたいですね（既に指摘入ってますが）

[syunmei]

すいません。環境を書いていませんでした。

[hope_mucci]

回答に追記しました。今度からは必ず環境を書いてね。

[syunmei]

細かい説明までありがとうございます！勉強になりました。