Mysql 取得する値での重複は3つまで取得できるSELECT文を作りたい

Question

SELECT文を使って重複３つまで取得し4つ以上は取得しないSQLを作りたいです。
具体的に、
users（ユーザー）テーブル/goods（商品）テーブル/orders(注文履歴)テーブル、goodsテーブルがあり
これらを結合して
ユーザー名、商品名、購入時間を取得してきているのですが、
30件取得した中で、ユーザーが同じ商品名を3つ以上購入していた場合3つまでは取得するようにし、4つ以上からは取得しないようなSQLを作りたいです。

現在のSQLです。

SELECT u.user_name, g.goods_name,o.date_time
FROM users as u
JOIN order as o
ON u.user_id = o.user_id
JOIN goods as g
ON o.order_id = g.id
ORDER BY o.order_id DESC
LIMIT 30

以上宜しくお願いします。

Answer 1

素直なSQLでは、おそらく記述できないので、本来であればホスト言語側で処理するのがよいと思われますが、いくつかの前提を置けば、以下のように書いてもよいかと思います。

SELECT
u.user_name,
g.goods_name,
LEFT(GROUP_CONCAT(DISTINCT o.date_time ORDER BY o.date_time DESC SEPARATOR ','), 19 * 3 + 2)
FROM users AS u
LEFT JOIN order AS o
ON o.user_id = u.user_id
INNER JOIN goods AS g
ON g.id = o.order_id
GROUP BY u.user_name, g.goods_name;

まず、
・order テーブルの date_time がDATETIME型であること
・取得結果は、ユーザー名、商品名の組み合わせを一意とすること
・直上の要件により、購入時刻が直近から降順で3件以内で取得できること
という形に要件を変形させます。

いくつか元のSQLで理解できなかったのが、
・goods.id が order.order_id に結合可能だとすると購入IDがPKにならないのではないか
・発注時刻でORDER BYするのあれば、直近から降順になるべきではないのか
といったあたりですが、そこは適宜検討していただくとして。

キモは、
LEFT(GROUP_CONCAT(DISTINCT o.date_time ORDER BY o.date_time DESC SEPARATOR ','), 19 * 3 + 2)
の部分です。MySQLやSQLiteに実装されている集約関数なのですが（DB2などその他のDBではLIST関数として実装されていることがある）、これは、セパレータで連結された文字列として集約された指定フィールドの値群を得る関数です。LEFTは指定したインデクスより右にある文字列をトリミングする関数で、
19 * 3 + 2
については、
DATETIME型の文字数 * 取得したい上限件数 + 含まれるセパレータの文字数
という意味合いになります。

細かくは調整していただければ（NULL値についてはCOALESCE関数で空文字列に丸めるなど）、そこそこ実用になるのではないでしょうか。