SQL 日付をカウントする形での集計方法

Question

Google Cloud PlatformのBig QueryでSQLの作業をしています。

ユーザーIDとログイン日時のデータ(同じユーザーが同じ日に何度もログインすることもあり、 2021-11-12 12:31:52 UTCのように時刻も含むデータ)がありますが、

このデータテーブルから、ID, 日付(重複無し、2021-11-12のように時刻含まないデータに変換), その日におけるログイン回数[出現する同じ日付の個数をID毎にカウントする] をSQLで抽出するにはどうすれば良いでしょうか。

集計結果のイメージは、下記のような形です。
ID①　2021-11-12　4回
ID①　2021-11-15　11回
ID①　2021-11-19　3回
ID②　2021-11-10　2回
ID③　2021-11-12　1回
ID③　2021-11-19　4回
ID④　2021-11-28　20回

よろしくお願いします。

Comments

[退会済みユーザー]

自分ではどこまでできたのか書きましょう。

[chanoma]

現状、別のデータ(ログイン回数ではなくクリック回数のデータ)で試してみたのですが、

SELECT
*,
DATE(activity_date) AS u_date,
ROW_NUMBER() OVER(PARTITION BY jsid,DATE(activity_date) ORDER BY activity_date asc) AS click_number
FROM mail_action_log
GROUP BY 1,2,3
ORDER BY 1,2,3

その後、click_numberの最大値をIDと日付ごとに取得できればと思うのですが、方法が分かっておりません。よろしくお願いします。

Answer 1

Big Queryを試せる環境がないので、ウラを取れませんが、↓でいけませんか。

SELECT DATE(activity_date) AS u_date, jsid, COUNT(*)
FROM mail_action_log
GROUP BY 1, 2
ORDER BY 1, 2

Comments

[chanoma]

出来ました。ありがとうございます。