DATE_TRUNC を使用したデータ集計

SaraDev · 2023 年 9 月 6 日午後 11:24

SQL における date_trunc 関数は非常に強力なツールです。指定された日付部分を基準に TIMESTAMP または INTERVAL 値を切り捨てることができ、特定の期間に基づいてデータを集計またはグループ化したい場合に不可欠な関数となります。

構文

date_trunc 関数の構文は以下の通りです。

date_trunc('date_part', field)

date_part: 切り捨て対象の日付またはタイムスタンプの部分を指定する文字列です。以下の値のいずれかになります。
- millennium
- century
- decade
- year
- quarter
- month
- week
- day
- hour
- minute
- second
- milliseconds
- microseconds
field: 切り捨て対象となるタイムスタンプまたは INTERVAL です。

DE クエリでの使用例

date_trunc を利用したいくつかのクエリ例を見てみましょう。

月ごとの新規トピック数

難易度: 初級

この SQL クエリは、Discourse データベースで各月に作成されたトピックの数をカウントするために使用されます。

SELECT 
    date_trunc('month', created_at)::DATE AS month,
    count(id)
FROM topics
GROUP BY month
ORDER BY month DESC

このクエリでは、date_trunc('month', created_at)::DATE によって created_at タイムスタンプを月単位に切り捨て、その後 DATE 型にキャストして日付として表示しています。これにより、トピックが作成された月ごとにグループ化されます。

その後、count(id) 関数によって各月に作成されたトピックの数がカウントされます。結果は月ごとに降順にソートされるため、最も新しい月が先頭に表示されます。

結果例:

month	count
2023-09-01	1
2023-08-01	6
2023-07-01	10

インラインコメント付きの詳細な説明

-- トピックが作成された月とトピックのカウントを選択
SELECT 
    -- 'created_at' タイムスタンプを月単位に切り捨て、日付型にキャスト
    -- これによりトピックが作成された月ごとにグループ化されます
    date_trunc('month', created_at)::DATE AS month,
    -- 各月に作成されたトピックのカウント
    count(id)
-- 'topics' テーブルから
FROM topics
-- 結果を月ごとにグループ化
GROUP BY month
-- 結果を月ごとに降順にソート
-- これにより最も新しい月が先頭に表示されます
ORDER BY month DESC

ユーザー累積合計

難易度: 中級

このクエリは、Discourse フォーラムでのユーザー登録の週次レポートと、ユーザーの累積合計を提供します。一時的な結果セット（daily_signups）を作成するために WITH 句を使用し、その後その結果セットから選択を行います。

-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

WITH daily_signups AS(
SELECT
    to_char(date_trunc('week', u.created_at)::date,'YYYY-MM-DD') AS Date,
    Count (id) as Signups
FROM users u
WHERE
    u.created_at::date BETWEEN :start_date::date AND :end_date::date
GROUP BY Date
)

SELECT
    Date, Signups, SUM(Signups) OVER (ORDER BY Date) as total_users
FROM daily_signups
ORDER BY Date Asc

このクエリの動作を以下に解説します。

WITH 句は daily_signups という名前の一時結果セットを作成します。この結果セットには、パラメータ :start_date と :end_date で指定された開始日と終了日の間の各週のユーザー登録数が含まれます。
daily_signups 結果セット内で、date_trunc('week', u.created_at)::date によって created_at タイムスタンプを週単位に切り捨て、その後日付型にキャストしています。これにより、ユーザーが登録した週ごとにグループ化されます。
Count(id) によって、各週に登録したユーザーの数がカウントされます。
主な SELECT 文では、SUM(Signups) OVER (ORDER BY Date) によってユーザーの累積合計が計算されます。OVER (ORDER BY Date) 句は、日付順に並べ替えられた行に対して合計を計算することを指定しており、各日付までの累積登録数を返します。
結果は日付順に昇順でソートされます。

結果例:

date	signups	total_users
2013-01-28	20	20.0
2013-02-04	2136	2156.0
2013-02-11	442	2598.0

インラインコメント付きの詳細な説明

-- 開始日と終了日のパラメータを定義
-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

-- 週ごとのユーザー登録数をカウントするための共通テーブル式（CTE）を作成
WITH daily_signups AS(
SELECT
    -- 'created_at' タイムスタンプを週単位に切り捨て、日付文字列としてフォーマット
    to_char(date_trunc('week', u.created_at)::date,'YYYY-MM-DD') AS Date,
    -- 登録したユーザーのカウント
    Count (id) as Signups
FROM users u
WHERE
    -- 開始日と終了日の間に登録したユーザーのみを含める
    u.created_at::date BETWEEN :start_date::date AND :end_date::date
GROUP BY Date
)

-- 日付、登録数、登録数の累積合計を選択
SELECT
    Date, 
    Signups, 
    -- 登録数の累積合計を計算
    SUM(Signups) OVER (ORDER BY Date) as total_users
FROM daily_signups
-- 結果を日付順に昇順でソート
ORDER BY Date Asc

月ごとの解決済みおよび未解決の質問数

難易度: 中級 / Discourse Solved プラグインが必要

このクエリは、Discourse フォーラム上の解決済みおよび未解決の質問数の月次レポートを提供します。このクエリは、サイトのすべてのトピックが解決可能であると仮定しています。

-- [params]
-- date :start_date
-- date :end_date

WITH monthly_questions AS (
    SELECT
        date_trunc('month', created_at)::DATE AS month,
        COUNT(id) as total_questions
    FROM topics
    WHERE
        created_at::date BETWEEN :start_date::date AND :end_date::date
    GROUP BY month
),
solved_questions AS (
    SELECT
        date_trunc('month', created_at)::DATE AS month,
        COUNT(id) as solved
    FROM user_actions
    WHERE
        created_at::date BETWEEN :start_date::date AND :end_date::date
    AND action_type = 15
    GROUP BY month
)

SELECT
    mq.month, 
    mq.total_questions, 
    COALESCE(sq.solved, 0) as solved,
    mq.total_questions - COALESCE(sq.solved, 0) as unsolved
FROM monthly_questions mq
LEFT JOIN solved_questions sq ON mq.month = sq.month
ORDER BY mq.month ASC

このクエリでは、monthly_questions CTE によって各月に作成された質問（トピック）の総数がカウントされます。solved_questions CTE によって、user_actions テーブルから action type = 15 の id をカウントすることで、各月に解決済みとしてマークされた質問の数がカウントされます。

主な SELECT 文では、総質問数から解決済み質問数を引くことで未解決質問数が計算されます。結果は月ごとに昇順でソートされるため、最も古い月が先頭に表示されます。

結果例:

month	total_questions	solved	unsolved
2023-07-01	10	3	7
2023-08-01	6	0	6
2023-09-01	1	1	0

インラインコメント付きの詳細な説明

-- [params]
-- date :start_date = 2000-01-01
-- date :end_date

-- 各月に作成された質問（トピック）の総数をカウントする CTE を作成
WITH monthly_questions AS (
    SELECT
        -- 'created_at' タイムスタンプを月単位に切り捨て
        date_trunc('month', created_at)::DATE AS month,
        -- 各月に作成されたトピックのカウント
        COUNT(id) as total_questions
    FROM topics
    WHERE
        -- 開始日と終了日の間に作成されたトピックのみを含める
        created_at::date BETWEEN :start_date::date AND :end_date::date
    GROUP BY month
),
-- 各月に解決済みとしてマークされた質問数をカウントする CTE を作成
solved_questions AS (
    SELECT
        -- 'created_at' タイムスタンプを月単位に切り捨て
        date_trunc('month', created_at)::DATE AS month,
        -- 各月の解決済み質問のカウント
        COUNT(id) as solved
    FROM user_actions
    WHERE
        -- 開始日と終了日の間に行われたアクションのみを含める
        created_at::date BETWEEN :start_date::date AND :end_date::date
        -- アクションタイプが 15（解決済み質問を示す）のアクションのみを考慮
        AND action_type = 15
    GROUP BY month
)

-- 月、総質問数、解決済み質問数、未解決質問数を選択
SELECT
    mq.month, 
    mq.total_questions, 
    -- 月に解決済み質問がない場合は 0 を表示
    COALESCE(sq.solved, 0) as solved,
    -- 総質問数から解決済み質問数を引いて未解決質問数を取得
    mq.total_questions - COALESCE(sq.solved, 0) as unsolved
FROM monthly_questions mq
-- 'monthly_questions' と 'solved_questions' の CTE を月を基準に結合
LEFT JOIN solved_questions sq ON mq.month = sq.month
-- 結果を月ごとに昇順でソート
ORDER BY mq.month ASC

トピック返信統計

難易度: 上級

この複雑な SQL クエリは、Discourse フォーラムのトピックアクティビティに関する週次レポートを提供します。トピックデータをいくつかの主要指標に分解します。少なくとも 1 つの返信があるトピック数、返信がないトピック数、返信がない状態で経過した最大日数、および最初の返信までの平均時間です。

WITH posts_list AS (
    SELECT 
        t.id topic_id,
        p.post_number,
        p.created_at,
        ROW_NUMBER () OVER (
                    PARTITION BY p.topic_id 
                    ORDER BY p.post_number)  AS post_order
    FROM posts p
    INNER JOIN topics t ON t.id = p.topic_id AND archetype = 'regular' AND t.deleted_at ISNULL
    WHERE p.post_type = 1
        AND p.deleted_at ISNULL
        AND t.deleted_at ISNULL
        AND t.archetype = 'regular'
    ORDER BY p.topic_id, p.post_number
),
atleast_1_response AS (
    SELECT 
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics with atleast one response"
    FROM topics t
    WHERE t.archetype = 'regular'
        AND t.deleted_at ISNULL
        AND t.posts_count >= 2
    GROUP BY "week"
),
no_response AS(
    SELECT 
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics without response"
    FROM topics t
    WHERE t.archetype = 'regular'
        AND t.deleted_at ISNULL
        AND t.posts_count = 1
    GROUP BY "week"
),
max_days_without_response AS(
    SELECT 
        date_trunc('week', t.created_at::date)::date AS "week",
        CURRENT_DATE::date-MIN(created_at)::date AS "max days without response"
    FROM topics t
    WHERE t.archetype = 'regular'
        AND t.deleted_at ISNULL
        AND t.posts_count = 1
    GROUP BY "week"
),
avg_time_first_response AS (
    SELECT 
        date_trunc('week', pl.created_at::date)::date AS "week",
        EXTRACT(epoch FROM AVG(pl2.created_at-pl.created_at))/3600 AS "avg time first response (h)"
    FROM posts_list pl
    INNER JOIN posts_list pl2 ON pl.topic_id = pl2.topic_id AND pl2.post_order = 2
    WHERE  pl.post_order = 1
    GROUP BY "week" 
)

SELECT ar.week, 
    "topics without response", 
    "max days without response", 
    "topics with atleast one response",
    "avg time first response (h)"
FROM atleast_1_response ar
INNER JOIN no_response nr ON nr.week = ar.week
INNER JOIN max_days_without_response mdwr ON mdwr.week = ar.week
INNER JOIN avg_time_first_response atfr ON atfr.week = ar.week
ORDER BY "week" DESC

このクエリの動作を以下に要約します。

posts_list 共通テーブル式（CTE）は、posts および topics テーブルからすべての通常の投稿のリストを選択し、topic_id と post_number でソートします。また、各トピック内の各投稿に行番号（post_order）を割り当てます。
atleast_1_response CTE は、各週における少なくとも 1 つの返信がある通常のトピックの数（つまり、posts_count が 2 以上）をカウントします。
no_response CTE は、各週における返信がない通常のトピックの数（つまり、posts_count が 1）をカウントします。
max_days_without_response CTE は、各週において返信がないトピックが放置された最大日数を計算します。
avg_time_first_response CTE は、各週における各トピックの最初の返信までの平均時間（時間単位）を計算します。
主な SELECT 文は、これらの CTE を週を基準に結合し、関連する列を選択します。結果は週ごとに降順でソートされます。

week	topics without response	max days without response	topics with atleast one response	avg time first response (h)
2023-09-04	15	2	47	2.6778684519444444
2023-08-28	30	9	138	8.7899938238888889
2023-08-21	22	16	130	9.3280889688888889

インラインコメント付きの詳細な説明

-- topic_id と post_number でソートされたすべての通常の投稿の一時テーブル（CTE）を作成
WITH posts_list AS (
    SELECT 
        t.id topic_id,  -- トピック ID
        p.post_number,  -- 投稿番号
        p.created_at,   -- 投稿作成日
        -- 各トピック内の各投稿に行番号を割り当てる
        ROW_NUMBER () OVER (
                    PARTITION BY p.topic_id 
                    ORDER BY p.post_number)  AS post_order
    FROM posts p
    -- topics テーブルと結合。削除されていない通常のトピックのみを考慮
    INNER JOIN topics t ON t.id = p.topic_id AND archetype = 'regular' AND t.deleted_at ISNULL
    WHERE p.post_type = 1
        AND p.deleted_at ISNULL  -- 削除された投稿を除外
        AND t.deleted_at ISNULL  -- 削除されたトピックを除外
        AND t.archetype = 'regular'  -- 通常のトピックのみを考慮
    ORDER BY p.topic_id, p.post_number
),
-- 各週における少なくとも 1 つの返信がある通常のトピックの数をカウントする CTE を作成
atleast_1_response AS (
    SELECT 
        -- created_at タイムスタンプを週単位に切り捨て
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics with atleast one response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- 通常のトピックのみを考慮
        AND t.deleted_at ISNULL  -- 削除されたトピックを除外
        AND t.posts_count >= 2  -- 少なくとも 1 つの返信があるトピックのみを考慮
    GROUP BY "week"
),
-- 各週における返信がない通常のトピックの数をカウントする CTE を作成
no_response AS(
    SELECT 
        -- created_at タイムスタンプを週単位に切り捨て
        date_trunc('week', t.created_at::date)::date AS "week",
        COUNT(*) AS "topics without response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- 通常のトピックのみを考慮
        AND t.deleted_at ISNULL  -- 削除されたトピックを除外
        AND t.posts_count = 1  -- 返信がないトピックのみを考慮
    GROUP BY "week"
),
-- 各週において返信がないトピックが放置された最大日数を計算する CTE を作成
max_days_without_response AS(
    SELECT 
        -- created_at タイムスタンプを週単位に切り捨て
        date_trunc('week', t.created_at::date)::date AS "week",
        -- トピック作成日から現在の日付までの日数を計算
        CURRENT_DATE::date-MIN(created_at)::date AS "max days without response"
    FROM topics t
    WHERE t.archetype = 'regular'  -- 通常のトピックのみを考慮
        AND t.deleted_at ISNULL  -- 削除されたトピックを除外
        AND t.posts_count = 1  -- 返信がないトピックのみを考慮
    GROUP BY "week"
),
-- 各週における各トピックの最初の返信までの平均時間（時間単位）を計算する CTE を作成
avg_time_first_response AS (
    SELECT 
        -- created_at タイムスタンプを週単位に切り捨て
        date_trunc('week', pl.created_at::date)::date AS "week",
        -- 最初の返信までの平均時間を時間単位で計算
        EXTRACT(epoch FROM AVG(pl2.created_at-pl.created_at))/3600 AS "avg time first response (h)"
    FROM posts_list pl
    -- posts_list CTE と結合。各トピックの 2 番目の投稿のみを考慮
    INNER JOIN posts_list pl2 ON pl.topic_id = pl2.topic_id AND pl2.post_order = 2
    WHERE  pl.post_order = 1  -- 各トピックの最初の投稿のみを考慮
    GROUP BY "week" 
)

-- 週、返信がないトピック数、返信がない最大日数、少なくとも 1 つの返信があるトピック数、最初の返信までの平均時間を選択
SELECT ar.week, 
    "topics without response", 
    "max days without response", 
    "topics with atleast one response",
    "avg time first response (h)"
FROM atleast_1_response ar
-- CTE を週を基準に結合
INNER JOIN no_response nr ON nr.week = ar.week
INNER JOIN max_days_without_response mdwr ON mdwr.week = ar.week
INNER JOIN avg_time_first_response atfr ON atfr.week = ar.week
-- 週ごとに降順でソート
ORDER BY "week" DESC

これらは、Data Explorer クエリで date_trunc を使用する方法のほんのいくつかの例です。これらのクエリをあなたのサイトでも自由に使用してください。質問がある場合は、下記でご質問ください。

トピック		返信	表示
Selecting the Quarter results in custom way Support	5	713	2022 年 10 月 1 日
Aggregate dashboard report data by time interval Data & reporting sql-query	4	1171	2022 年 9 月 24 日
Calculate total participation moments by month using Data Explorer Data & reporting sql-query	13	1377	2021 年 3 月 27 日
How to group by date only in data explorer Data & reporting sql-query	5	960	2018 年 8 月 21 日
Find posts "solved" in specific month Data & reporting solved , sql-query	4	1575	2019 年 3 月 29 日

DATE_TRUNC を使用したデータ集計

構文

DE クエリでの使用例

月ごとの新規トピック数

ユーザー累積合計

月ごとの解決済みおよび未解決の質問数

トピック返信統計

関連トピック