【SQL,PostgreSQL】ある日の5分おきの集計を取りたい

Question

いつもお世話になってます。
PostgreSQL-9.6.11-1、Windows10です。

以下のような「直近３日の1時間おきの集計」を取得するSQLがありました。

CREATE TABLE public.t_history
(
  id integer, 
  regist_timestamp timestamp without time zone NOT NULL,
  regist_user character varying(50) NOT NULL, 
  CONSTRAINT t_history_pkey PRIMARY KEY (id)
)

SELECT
    datetable.datetime,
    COALESCE(total.cnt, 0) as count 
FROM
(
    
    SELECT TO_CHAR(generate_series, 'YYYY/MM/DD HH24') as datetime 
    FROM
        generate_series(
            CAST(TO_CHAR(NOW() + INTERVAL '-3 day',  'YYYY/MM/DD HH24:00:00') AS timestamp),
            CAST(TO_CHAR(NOW(),                      'YYYY/MM/DD HH24:00:00') AS timestamp),
            '1 hour'
        )
) AS datetable


LEFT JOIN
(
    
    SELECT
        TO_CHAR(end_timestamp, 'YYYY/MM/DD HH24') as datetime,
        count(*) AS cnt
    FROM
        (
            
            SELECT * FROM t_history
            WHERE           
            
            end_timestamp >= CAST(TO_CHAR(NOW() + INTERVAL '-3 day', 'YYYY/MM/DD HH24:00:00') AS timestamp)
        ) as source
   
    GROUP BY datetime
) AS total
ON datetable.datetime = total.datetime

ORDER BY datetable.datetime DESC

こちらを流用して、
・直近３日→特定の１日
・1時間おき→5分おき
に変更したいです。

下記のように日付指定を追加しましたが、絞り込み出来ませんでした。
※コメント部分

SELECT
    datetable.datetime,
    COALESCE(total.cnt, 0) as count 
FROM
(
    
    SELECT TO_CHAR(generate_series, 'YYYY/MM/DD HH24') as datetime 
    FROM
        generate_series(
            CAST(TO_CHAR(NOW() + INTERVAL '-3 day',  'YYYY/MM/DD HH24:00:00') AS timestamp),
            CAST(TO_CHAR(NOW(),                      'YYYY/MM/DD HH24:00:00') AS timestamp),
            '1 hour'
        )
) AS datetable


LEFT JOIN
(
   
    SELECT
        TO_CHAR(end_timestamp, 'YYYY/MM/DD HH24') as datetime,
        count(*) AS cnt
    FROM
        (
            
            SELECT * FROM t_history
            WHERE           
            --日付指定追加
            regist_timestamp = '2018/12/12' 
            --end_timestamp >= CAST(TO_CHAR(NOW() + INTERVAL '-3 day', 'YYYY/MM/DD HH24:00:00') AS timestamp)
        ) as source
   
    GROUP BY datetime
) AS total
ON datetable.datetime = total.datetime

ORDER BY datetable.datetime DESC

分かる方教えていただけないでしょうか？

どうぞ宜しくお願い致します。

Comments

[退会済みユーザー]

generate_seriesを使って日付時刻を生成している箇所をどうアレンジするか曖昧なので、仕様を明確に示してください。例えば、特定の1日の0時から23時とする、とか。

Answer 1

指定した日付の5分間隔のタイムテーブルを基準に、t_historyのregist_timestampと結合してカウントします。

select dt.datetime
     , count(his.id)
from generate_series('2018/12/12'::timestamp, '2018/12/12'::timestamp + INTERVAL '1 day', '5 minute') as dt(datetime)
     left join t_history as his
     on his.regist_timestamp >= dt.datetime and his.regist_timestamp < (datetime + INTERVAL '5 minute')
group by dt.datetime

因みに、参考にしているSQLですが、NOW()を正時に変更していますが、Date型だとそれは不要です。
記述を簡潔にすると以下の様になります。
実行時の時間を軸にしているようなので、取り下げ
ついでに、元のSQLを簡潔にすると

select dt.datetime
     , count(his.id)
from generate_series(
            TO_CHAR(NOW(), 'YYYY/MM/DD HH24:00:00'):: timestamp + INTERVAL '-3 day',
            TO_CHAR(NOW(), 'YYYY/MM/DD HH24:00:00'):: timestamp,
            '1 hour'
     ) as dt(datetime)
     left join t_history as his
     on his.end_timestamp>= dt.datetime and his.end_timestamp< (dt.datetime + INTERVAL '1 hour')
group by dt.datetime

Comments

[pecchan]

有難う御座います。

Answer 2

まず、regist_timestampはtimestamp型なのに、日付と比較しているので、'2018/12/12 00:00:00.000'のデータとしか一致しません。
regist_timestampをdate型にキャストして比較しましょう。

次に5分おきの判断ですが、時間と違い範囲(0～5、6～10、11～15、...)で検索する必要があります。

Comments

[pecchan]

型の違い、気付きませんでした。有難う御座います。

Answer 3

パット見でわかる文法エラー。
regist_timestamp = '2018/12/12'
じゃなくて
regist_timestamp = '2018-12-12'::timestamp
かなと。

それと、

    SELECT TO_CHAR(generate_series, 'YYYY/MM/DD HH24:MI:00') as datetime 
    FROM
        generate_series(
            '2018-12-12'::timestamp,
            '2018-12-12'::timestamp + '1 day'::interval,
            '5 minutes'
        )

とか。

Comments

[pecchan]

regist_timestamp = '2018-12-12'::timestamp
上記の方法も初めて知りました。
有難う御座います。