これは、ユーザープロフィール表示に関するダッシュボードレポートのSQLバージョンです。
このダッシュボードレポートは、指定された期間内の1日あたりのユーザープロフィール表示の総数を生成します。
-- [params]
-- date :start_date = 2024-01-05
-- date :end_date = 2024-02-06
WITH profile_views_per_day AS (
SELECT
date_trunc('day', viewed_at) AS day,
COUNT(*) AS views
FROM user_profile_views
WHERE viewed_at >= :start_date AND viewed_at <= :end_date
GROUP BY day
)
SELECT
day::date,
views
FROM profile_views_per_day
ORDER BY day
SQLクエリの説明
クエリの仕組みを以下に示します。
- パラメータ: クエリは2つのパラメータ、
:start_dateと:end_dateを受け入れます。これにより、ユーザーはレポートの日付範囲を指定できます。両方のdateパラメータは、YYYY-MM-DDの日付形式を受け入れます。 - 共通テーブル式 (CTE): クエリは
profile_views_per_dayという名前のCTEから始まります。これは、メインクエリで使用する一時的な結果セットです。 - 日付の切り捨て: CTE内では、
date_trunc関数を使用してviewed_atタイムスタンプを日に切り捨てます。これは、プロフィールが表示された時刻に関係なく、日付のみに基づいてカウントされることを意味します。 - 表示回数のカウント:
COUNT(*)関数は、日ごとのプロフィール表示回数をカウントします。 - 日付範囲によるフィルタリング:
WHERE句は、クエリの上部で設定されている:start_dateと:end_dateパラメータの間で発生した表示のみをカウントするように制限します。 - 結果のグループ化:
GROUP BY句は、結果を切り捨てられた日にグループ化するため、表示回数が日ごとに集計されます。 - 結果の選択: メインの
SELECTステートメントは、CTEから結果を取得します。切り捨てられた日（日付型にキャスト）と、その日の表示回数を選択します。 - 結果の並べ替え: 最後に、
ORDER BY句により、結果が日付の昇順で表示されることが保証されます。
結果例
| day | views |
|---|---|
| 2024-01-05 | 263 |
| 2024-01-06 | 374 |
| 2024-01-07 | 272 |
| 2024-01-08 | 409 |
| 2024-01-09 | 606 |