【レシピ】PostgreSQLのSELECT句：時間配列の作り方

Question

・時間配列を作る方法を教えて欲しいです。

元の表から、特定のデータをグループ化し、複数行を一行にまとめ、
属性ごと（今回は、時間単位）に列へ入れるのが目的

グループ化するカラム：datetime(date部分), user_id, datetime(trunc_time部分), item_id
配列化したいカラム：trunc_timeの値、valueの合計、対象のCOUNT(item_idの数)

ARRAY[
ARRAY['00:00:00',SUM(value:数値),COUNT(item_id)]
,ARRAY['01:00:00',SUM(value:数値),COUNT(item_id)]
...
,ARRAY['23:00:00',SUM(value:数値),COUNT(item_id)]
]
これを作る最もEXPLAIN的にコストの低いSQLがあれば、教えて欲しいです。
※Planning time, Execution timeどちらも重視します。
と言うのも、実際は、数件ではなく、一日分で、十数万件分のレコードが格納されております。
そちらをPDFへ出力する際に、データの丸め込みを行って、数人～三十人分を一ヵ月分等で出力するため、
SQLのメインレコード的には、900件ですが、item_id毎に特定の加工を施して、LEFT OUTER JOINします。
...出来る事なら、JOINは一回(*1)で済ませたいので、各item_id毎JOIN（*2）よりもコストの低い実現が望ましいです。

*1:実行結果（EXPLAIN ANALYZE）
計画行：65行
Planning time: 2.051ms
Execution time: 89.219 ms

*2:実行結果（EXPLAIN ANALYZE）
計画行：334行
Planning time: 177.550ms
Execution time: 116.606 ms

** これが元データ **

id	user_id	datetime	item_id	value
1	101	2019/08/31 22:35:51	1	500
2	101	2019/08/31 01:54:32	2	500
3	101	2019/08/31 15:57:20	3	500
4	101	2019/08/31 19:18:45	2	500
5	101	2019/09/01 10:09:58	1	500
6	101	2019/09/01 20:21:30	2	500
7	101	2019/09/01 11:02:49	3	500
8	101	2019/09/01 08:35:12	1	500

** これが作りたい **

user_id	date	item_id	values{'time','sum(value)','count(item_id)'}
101	2019/08/31	1	{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}
101	2019/08/31	2	{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}
101	2019/08/31	3	{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}
101	2019/09/01	1	{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}
101	2019/09/01	2	{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}
101	2019/09/01	3	{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}

該当のソースコード

WITH temp_datas AS (
  SELECT *
  FROM (
    VALUES
      (1,101,'2019/08/31 22:35:51'::TIMESTAMP,1,500)
      ,(2,101,'2019/08/31 01:54:32'::TIMESTAMP,2,500)
      ,(3,101,'2019/08/31 15:57:20'::TIMESTAMP,3,500)
      ,(4,101,'2019/08/31 19:18:45'::TIMESTAMP,2,500)
      ,(5,101,'2019/09/01 10:09:58'::TIMESTAMP,1,500)
      ,(6,101,'2019/09/01 20:21:30'::TIMESTAMP,2,500)
      ,(7,101,'2019/09/01 11:02:49'::TIMESTAMP,3,500)
      ,(8,101,'2019/09/01 08:35:12'::TIMESTAMP,1,500)
  ) AS temp (id, user_id, datetime, item_id, value)
)
SELECT
  td.user_id
  ,td.item_id
  ,td.date
  -- ここを作成中
  -- これは、あまり参考にならない。。。作りかけ箇所
  ,ARRAY[
    ARRAY[
      '00:00:00'
      ,SUM(CASE WHEN td.time= '00:00:00' THEN td.value END)::TEXT
      ,COUNT(CASE WHEN td.time= '00:00:00' THEN 1 ELSE 0 END)::TEXT
    ],
    ARRAY[
      '01:00:00'
      ,SUM(CASE WHEN td.time= '01:00:00' THEN td.value END)::TEXT
      ,COUNT(CASE WHEN td.time= '01:00:00' THEN 1 ELSE 0 END)::TEXT
    ],
    -- ...続く
    ARRAY[
      '23:00:00'
      ,SUM(CASE WHEN td.time= '23:00:00' THEN td.value END)::TEXT
      ,COUNT(CASE WHEN td.time= '23:00:00' THEN 1 ELSE 0 END)::TEXT
    ]
  ] AS values
  -- ...
FROM (
    SELECT
      *
      ,temp_datas.datetime::DATE AS date
      ,TO_CHAR(DATE_TRUNC('hour', temp_datas.datetime::TIMESTAMP), 'HH24:MI:SS') AS time
    FROM
      temp_datas
  ) AS td
GROUP BY
  td.user_id
  ,td.item_id
  ,td.date

補足情報（FW/ツールのバージョンなど）

PostgreSQL_version 9.5

Comments

[m.ts10806]

備忘録的な使い方したいのでしたらQiitaへ。
自己解決で書きたいのでしたら、全て整理して一気に解決まで書いてください。投稿前に幾らでも整理できるはずです。

[sazi]

配列は２４時間分固定にしたいのでしょうか？
時間の書式を入れているところを見ると可変ですか？
それだと配列内を検索しない駄目なので、やっぱり固定？
固定だとすると配列の添え字で時間帯が決まるので、key部分は無駄なのでは？
配列に何を格納したいのか文章での説明が無いと分かりませんよ。
結果データがあっているならまだしも。

[tama_yn0815]

配列は、24時間で固定したいです（データ無い場合は、データが無い状態の配列を明確に入れる）
各行は必ず、00時～23時までの配列を作って、valuesカラムに持たせる認識です！
時間の書式を明示的に入れているのは、出力時点で最初にデータの値がある時刻を出力するため、参照するだけで、値（時刻）を取れるようにするためです。

Answer 1

２４時間固定部分は、generate_seriesを用いて生成します。
まあ、24個程度であれば、そんなに手間でもないですからvaluesでも良いですけど。
それを集計データと直積(cross join）して、配列に畳む際にcaseにて有効値を優先します。

WITH temp_datas AS (
  SELECT *
  FROM (
    VALUES
      (1,101,'2019/08/31 22:35:51'::TIMESTAMP,1,500)
      ,(2,102,'2019/08/31 01:54:32'::TIMESTAMP,2,500)
      ,(3,101,'2019/08/31 15:57:20'::TIMESTAMP,3,500)
      ,(4,101,'2019/08/31 19:18:45'::TIMESTAMP,2,500)
      ,(5,103,'2019/09/01 10:09:58'::TIMESTAMP,1,500)
      ,(6,102,'2019/09/01 20:21:30'::TIMESTAMP,2,500)
      ,(7,101,'2019/09/01 11:02:49'::TIMESTAMP,3,500)
      ,(8,103,'2019/09/01 08:35:12'::TIMESTAMP,1,500)
  ) AS temp (id, user_id, datetime, item_id, value)
)
select  user_id, item_id, date
      , array_agg(
          array[
            title_time
          , case when title_time=time then sum_value end
          , case when title_time=time then id_cnt end
          ]::text[] 
          order by title_time
        ) array_value
from (
    SELECT  user_id, item_id, datetime::date as date
          , date_trunc('hour', datetime::time) as time
          , SUM(value) sum_value, count(*) id_cnt
    FROM  temp_datas
    GROUP BY  user_id, item_id, date, time
  ) step1 cross join (
    select '00:00:00'::time + (GENERATE_SERIES(0,23) || ' hours')::interval as title_time
  ) title_time
group by user_id, item_id, date

実環境があるわけではないですし、コストまで求められても困りますけどね。
速さの調整は上記SQLではstep1内が如何に早くなるかです。
(user_id, item_id, datetime, value)のインデックスがあればインデックスonlyで済むかもしれません。

それから、項目名はデータ型と被らないように命名した方が良いですね。
メンテナンス時に検索などで困りますし。

Comments

[tama_yn0815]

回答ありがとうございます。
Indexは張られていないので、、、将来的に早くなることを考えて張るように提案します。
SQLはcross join箇所のGENERATE_SERIES関数とvaluesのより実行計画の早い方を利用したく！
二度続けて、回答をいただきましてありがとうございます。

[sazi]

CROSS JOIN の部分は内部的な展開なので、殆ど差はないと思われますので、可読性や保守性で決定すれば良いかと思います。
基本的に、GROUP BYを使用すると内部的な展開になるので、件数が多くなるとメモリーの割り当てのチューニングが必要になりますが、数千件程度であれば不要だと思います。
解決積みという事は実行計画的に、許容範囲だったという事でしょうか？

[tama_yn0815]

はい！許容範囲内でした！！
合計で、以下の実行計画でした。

計画行：72行
Planning time: 2.223ms
Execution time: 91.795 ms

運用ですか。。。将来的な保守改修が発生しそうではあるのですが。。。
例えば、開所時間から24時間分みたいな、開始が一日のスタートが固定0時から変則するとかですかね
そんな時に対応できる、GENERATE_SERIESの方が、開始時刻をパラメータ化するだけで済むので、
今回は、GENERATE_SERIESを利用する事に致しました。
また、動く実例があれば、エンジニアに知識をつけていただく時にも助けになると思いました。

[sazi]

工夫というか気付き次第で、驚くほど記述が簡潔になり、コストも低くなりますので頑張って下さい。