質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.77%

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

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 593

tama_yn0815

score 139

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

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

グループ化するカラム: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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • m.ts10806

    2019/10/11 16:17

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

    キャンセル

  • 退会済みユーザー

    2019/10/11 17:21

    複数のユーザーから「問題・課題が含まれていない質問」という意見がありました
    teratailでは、漠然とした興味から票を募るような質問や、意見の主張をすることを目的とした投稿は推奨していません。
    「編集」ボタンから編集を行い、質問の意図や解決したい課題を明確に記述していただくと回答が得られやすくなります。

  • sazi

    2019/10/11 17:39 編集

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

    キャンセル

  • tama_yn0815

    2019/10/11 18:09 編集

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

    キャンセル

回答 1

checkベストアンサー

+2

24時間固定部分は、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で済むかもしれません。

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/10/15 10:24

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

    キャンセル

  • 2019/10/15 10:40

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

    キャンセル

  • 2019/10/15 11:49

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

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

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

    キャンセル

  • 2019/10/15 12:06

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.77%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る