日次サマリデータから週次・月次グラフを出力する

WEBサービスのページごとのアクセスや、ユーザーの行動の数を日毎にまとめたサマリテーブルから、グラフを出力することになりました。 グラフを出力するにあたり、ロジックではなくSQLを工夫することで、週次・月次のデータを作ってみました。

週次グラフ

今回のサマリテーブルは、アクセスや行動がない場合レコードが作成されません。このため、歯抜けになった日付の扱いに工夫が必要です。 これには、PostgreSQLのGENERATE_SERIES関数を利用して、指定された範囲の日数分の集合から、日付を作り出しました。

週次グラフは、指定された範囲を開始日１週間毎に合計した数をプロットします。これは、週ごとにGROUP BYしSUMで合計を求めます。 日付の週を求めるため、日付を作るために利用したGENERATE_SERIES関数の結果を7で割ることで、週番号を作り出しました。

これらを実装すると、以下のようなSQLになりました。週の最初の日付とPV数とアクション数が出力されます。

SELECT
    TO_CHAR(TO_DATE(MIN(w.date),'YYYYMMDD'),'MM/DD') AS date,
    CASE WHEN SUM(w.pv) IS NULL THEN 0 ELSE SUM(w.pv) END AS pv,
    CASE WHEN SUM(w.action) IS NULL THEN 0 ELSE SUM(w.action) END AS action
FROM (
    SELECT
        m.date AS date,
        (m.i / 7) AS week_number,
        CASE WHEN SUM(l.pv) IS NULL THEN 0 ELSE SUM(l.pv) END AS pv,
        CASE WHEN SUM(l.action) IS NULL THEN 0 ELSE SUM(l.action) END AS action
    FROM
    (
        SELECT
            TO_CHAR((/*from*/::DATE) + arr.i,'YYYYMMDD') AS date,
            i
        FROM
            GENERATE_SERIES(0, EXTRACT(DAY FROM ((/*to*/::TIMESTAMP) - (/*from*/::TIMESTAMP)))::INTEGER) AS arr(i)
    ) m
    LEFT OUTER JOIN s_access_report l
    ON l.ymd = m.date
    GROUP BY m.date, m.i
) AS w
GROUP BY w.week_number
ORDER BY MIN(w.date)

月次グラフ

月次グラフも、週次グラフと同様に１週間毎に合計した数をプロットします。週次グラフとの違いは、月ごとにGROUP BYしSUMで合計を求めることです。 日付の月を求めるため、TO_CHAR関数を利用して日付からYYYYMM形式の文字列を作り出しました。

SELECT
    TO_CHAR(TO_DATE(MIN(w.date),'YYYYMMDD'),'MM/DD') AS date,
    CASE WHEN SUM(w.pv) IS NULL THEN 0 ELSE SUM(w.pv) END AS pv,
    CASE WHEN SUM(w.action) IS NULL THEN 0 ELSE SUM(w.action) END AS action
FROM (
    SELECT
        m.date AS date,
        (m.yyyymm) AS yyyymm,
        CASE WHEN SUM(l.pv) IS NULL THEN 0 ELSE SUM(l.pv) END AS pv,
        CASE WHEN SUM(l.action) IS NULL THEN 0 ELSE SUM(l.action) END AS action
    FROM
    (
        SELECT
            TO_CHAR((/*from*/::DATE) + arr.i,'YYYYMMDD') AS date,
            TO_CHAR((/*from*/::DATE) + arr.i,'YYYYMM') AS yyyymm
        FROM
            GENERATE_SERIES(0, EXTRACT(DAY FROM ((/*to*/::TIMESTAMP) - (/*from*/::TIMESTAMP)))::INTEGER) AS arr(i)
    ) M
    LEFT OUTER JOIN s_access_report l
    ON l.ymd = m.date
    GROUP BY m.date, m.yyyymm
) AS w
GROUP BY w.yyyymm
ORDER BY MIN(w.date)

まとめ

以上のSQLをもとにデータを作成し、JavaScriptのライブラリを利用して、グラフを描画することが出来ました。