【SQL】「日付順だけど、user_idごと」のソート

Question

SQLで「日付順だけど、user_idごと」という条件でソートしたいです。

こんなテーブルなのですが、普通に取得すると
「1,2,2,3」となりますが、
「1,2,3,2」のように取得したいのです。

MariaDB [social]> SELECT * FROM histories ;                                    
+----+---------------------+---------+
| id | insert              | user_id |
+----+---------------------+---------+
|  1 | 2020-02-08 01:00:00 |       1 |
|  2 | 2020-02-08 02:00:00 |       2 |
|  3 | 2020-02-08 03:00:00 |       2 |
|  4 | 2030-02-08 04:00:00 |       3 |
+----+---------------------+---------+

↓「日付順だけど、user_idごと」という条件でソートしたい

+----+---------------------+---------+
| id | insert              | user_id |
+----+---------------------+---------+
|  1 | 2020-02-08 01:00:00 |       1 |
|  2 | 2020-02-08 02:00:00 |       2 |
|  4 | 2030-02-08 04:00:00 |       3 |
|  3 | 2020-02-08 03:00:00 |       2 |
+----+---------------------+---------+

まずは同じuser_idをグループ化すれば「1,2,2,3」でなく「1,2,3」と取得できると考えて下記書いたのですが、もうこの取得の時点で躓いていて、

SELECT *, count(user_id) as user_id 
FROM histories
GROUP BY user_id
ORDER BY insert DESC;

そしてこの取得が「1,2,3」と取得できたとしても、「じゃ次の2はどう取得するのか？」がわからなくなってしまって、もしよろしければどなたか教えて頂きたいのです。
不躾な質問で恐縮ですが、お願いできませんでしょうか。

Answer 1

なにか色々と混乱しているようですが……

まず、「insert」というSQLでの予約語をカラム名にそのまま使うのをやめてください。
躓く石を一つ増やしてます。

SELECT min(`insert`), user_id FROM histories GROUP BY user_id ORDER BY min(`insert`);

ですかね。

─── 2/9 22:40追記

SELECT * FROM (
SELECT id, user_id, `insert`, (SELECT count(*) FROM histories H2 WHERE H2.user_id = H1.user_id AND H2.`insert` <= H1.`insert` ) AS cur FROM histories AS H1
) AS Y ORDER BY cur, `insert`;

実行サンプル

Comments

[mokemokemoke]

ありがとうございます。計4つ表示したいのに、2が1つ足りず、計3つとなりました。もしかしたら実行サンプルというのはこちらの環境にもよるのでしょうか。

[takepan1973]

ブラウザベースなんで関係ないと思いますけどね

っていうか、３つで問題あるんですか？
"「1,2,3」と取得できたとしても"って書いてあるので、そうしたいんだと思ったんですけど。

要件が伝わってこないですね。

[mokemokemoke]

なるほど、伝え方が下手でした。「1,2,3」と取得できたとしても、「じゃ次の2はどう取得するのか？」というのはつまり「1,2,3,2」という風に取得したいということです。

質問の２つめのテーブルのように取得したいんです。

[takepan1973]

うーん、なんか凄いですね。（そもそもなんでIDで表現しないのか謎ですが）
日付順ならIDで言えば1,2,3,4の順のはずで、なんで1,2,4,3になるんですか？

"日付順「だけど」user_idごと"ってどういうことですか？
なんで3より4が上位に来るか、その理屈が知りたいです。
user_id=2としては2周目だから、ということですか？

どんなときに必要になるソートなんでしょうね。

[mokemokemoke]

>そもそもなんでIDで表現しないのか謎ですが
すみません、どういうことですか？

>なんで1,2,4,3になるんですか？
>user_id=2としては2周目だから、ということですか？
そうです！仰る通りです。

>どんなときに必要になるソートなんでしょうね。
ユーザーごとの最新投稿を取得したいときです。たとえばlimit 10でまぜこぜだと、Aさんが10件投稿したとき、Aさんの10件が取得されますよね。これだとBさんやCさんの投稿が見えない。なので一人ずつ（user_idごとに）取得したいという感じです。伝わりづらいですし、ネットにサンプルもないですし、あまりやらないソートだったんですね。厄介な話を持ち込んでしまいすみません。

[mokemokemoke]

2/9 22:40の追記拝見しました。完璧でした！ありがとうございます！
すみませんが、一番奥のSELECTが何をしているのか教えて頂けませんでしょうか？H1とH2を作りinsertを比較していますが、これは一体…
あとAS YのYはなんの役割を担っているのでしょうか？

[sazi]

@takepan1973 さん
日付毎の連番にしているんだから、（連番、日付）の順じゃなく（連番、user_id）の順じゃないと駄目ですよ。

[takepan1973]

> SELECT count(*) FROM histories H2 WHERE H2.user_id = H1.user_id AND H2.`insert` <= H1.`insert`
これは、同じuser_idで当該insert以下のものをカウントしています。
| 2 | 2020-02-08 02:00:00 | 2 |
| 3 | 2020-02-08 03:00:00 | 2 |
id=2は2020-02-08 02:00:00以下をカウントしているので1、
id=3は2020-02-08 03:00:00以下をカウントしているので2です。

Yは、外して実行してみれば分かります。
Every derived table must have its own alias エラーになります。
「派生テーブルは全てエイリアス(別名)を持たなければいけません」
ということで、名前を付けることが必須になっています。
別のところで呼ばれるときは、Y.(カラム名)という形で参照することになります。
名前は別に何でも良いです。本当は意味するところが分かる名前にしたいところですが、今回は便宜的に1文字にしました。

[sazi]

順序に関しては、データのUSER_IDの1と２の日付を入れ替えて試してみてね

[退会済みユーザー]

まあ、今さらだが order by でカラム番号いけるのでは

[sazi]

＠asahina1979さん
MySQLはエイリアスでも大丈夫です。最近のpostgresも同様

Answer 2

ユーザーごとの日付の順番を振って、順番毎のユーザー順にします。
MariaDB10.2以降ならrow_number()が使えます

select row_number() over(partition by user_id order by insert) as seq
     , *
from histories
order by seq, user_id

※未検証です。

Comments

[mokemokemoke]

ありがとうございます。select にseqという名前で条件を付けてから、*で全て取得しているということですか？あと、row_number()というのを使わないバージョンというのも見てみたいのですが、検証まではいいのでお教えいただけませんでしょうか。

[sazi]

「select にseqという名前で条件を付けてから」
なぜそんな解釈に？
試す方が理解が早いと思います。

[sazi]

ポイントは連番を使うという考え方です。
row_number()を使用しない連番の作成は以下などを参考にして下さい。
【MySQL】グループ毎に連番を振る
https://qiita.com/toyottoyo/items/813338f4756dee41e49b

※「MySQL　グループ　連番」で検索すれば他にも沢山サンプルがあります。

[mokemokemoke]

ありがとうございます。連番というキーワードは良さそうですね。

[sazi]

row_number()が使えないバージョンという事ですか？
使えないバージョンなら、速度的にはサブクエリーより変数を使用した連番が高速ですよ。