SQLでユーザ名を指定してselectを行いたい、この時、日別のランキングを列として同時に得たい。

Question

前提・実現したいこと

下図右側のようなTableがあるとき、ユーザ名を指定してselectを行い、左側のようなTableを得たいです。
その時、次の仕様を満たしたいです。

• 当日4時からその次の日3時59分59秒を同日として扱う。

例えば2020-05-26 04:00:00 ～ 2020-05-27 03:59:59 を同日として扱う。

• 同じ日付なら、ユーザ名の重複を除外する。このとき、得点が最も高い行のみを残し、他を除外する。
• 同じ得点・同じユーザ名・同日の列が複数ある場合は、そのうち一つだけを残す。
• 日別のランキングを列として得る。

どのようなSQL文を書けばよいですか？ご教授ください。

上記はサンプルですが、実際の列の件数は10万件程度、ユーザ数は5000程度、1日当たりの列数は1000件程度を想定しています。
実行時間は1s以下に抑えたいです。

補足情報（FW/ツールのバージョンなど）

SQLite3を利用しています。

サンプル作成に使ったSQL文は以下の通りです。

create table Scoreboard (id INTEGER PRIMARY KEY AUTOINCREMENT,user_name TEXT NOT NULL, score INTEGER NOT NULL ,time_stamp TEXT NOT NULL);
insert into Scoreboard(user_name, score,time_stamp) values ("A",10,"2020-05-26 19:00:00");
insert into Scoreboard(user_name, score,time_stamp) values ("B",5,"2020-05-26 21:00:00");
insert into Scoreboard(user_name, score,time_stamp) values ("C",20,"2020-05-27 03:05:00");
insert into Scoreboard(user_name, score,time_stamp) values ("A",15,"2020-05-27 03:00:00");
insert into Scoreboard(user_name, score,time_stamp) values ("A",20,"2020-05-27 04:00:00");
insert into Scoreboard(user_name, score,time_stamp) values ("A",5,"2020-05-27 03:59:59");
insert into Scoreboard(user_name, score,time_stamp) values ("B",20,"2020-05-28 01:00:00");
insert into Scoreboard(user_name, score,time_stamp) values ("B",20,"2020-05-28 12:00:00");
insert into Scoreboard(user_name, score,time_stamp) values ("C",20,"2020-05-28 18:00:00");
insert into Scoreboard(user_name, score,time_stamp) values ("A",1,"2020-06-23 04:00:00");

ユーザ名のTableはありません。

Comments

[m.ts10806]

まず、自身が思った通りにやっていみてください。
質問ではなく作業依頼になっています

Answer 1

まず前提知識としてウィンドウ関数に関する基礎知識を3話全部読んで内容を理解してください。
また、WITH句に対する知識も必須です。

解決する問題を分解し、1つずつ考えていきましょう。

当日4時からその次の日3時59分59秒を同日として扱う。

これを言い換えると日時から4時間引くと同日として日付がそろうことになります。
SQLiteの日付時刻関数によるとDatetime関数で日付の演算ができます。日時から4時間マイナスし、同日0時0分に時刻をそろえることでウィンドウ関数に適用する元日付を作ります。

		,datetime(日時,'-4 hours', 'start of day') as 基準日

同じ日付なら、ユーザ名の重複を除外する。このとき、得点が最も高い行のみを残し、他を除外する

まず、ユーザ名ごと、基準日ごとの最大スコアを算出する関数を考えます。
最大値を得る関数はmaxです。また、ユーザ名と基準日ごとに集計範囲を区切るので、partition byにはこの2つを指定します。

,max(score) over(PARTITION by ユーザー名, 基準日) as 日別最大スコア

同じ得点・同じユーザ名・同日の列が複数ある場合は、そのうち一つだけを残す。

単に日別最大スコア列を追加すると、元のレコードにそのままmaxが追加されるだけなので同日内1レコードに絞る方法を考えます。いろいろな方法がありますが、今回は最も単純な方法としてrow_number関数で順序を付与する方法を考えます。

まず、スコアが日別最大スコアと一致する行だけをwhereで選択します。
次に、row_number関数にて、集計範囲をユーザー名、基準日にして実行すると同日最大スコアのレコードに上から順番に順番が振られます。

,row_number() over(PARTITION by ユーザー名,基準日) order_of_day as 順序

順序は1から振られます。順序=1のレコードを抽出すればレコードが1つだけ残ります。

日別のランキングを列として得る

ランキングを得るにはrank関数を使用します。今回は全ユーザー、日別のランキングなので集計範囲は基準日のみになります。また、序列を作る元になるのは日別最大スコアです。大きい方から順位が上がるので降順ソートします。

,rank() over (PARTITION by 基準日 order by 日別最大スコア desc) 日別ランキング

なお、rank関数は同じランクのレコードが存在する場合は順番を飛ばします。
(1位→2位→3位タイが3人→6位　のように）
順番を飛ばしたくない場合はdense_rank関数を使います。

以上の処理を順番に適用していくと目的の抽出結果を作ることができます。
WITH句を使って順番にSQLを書いていきましょう。
一度のSQLで最終結果が作成できないのは、集計結果を次の集計で使用するからです。

基礎知識のリンクの内容をよく理解し、問題を1つずつ順番に解決していけばそれほど難しくはありません。手を動かしながら考えれば必ず解決できると思います。頑張ってください。

Comments

[nemumi-nomaki]

必要な知識をご教授くださり、ありがとうございます。勉強不足でしたので、教えていただいたサイトを読んで、内容を理解したいと思います。