teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップに関する質問【レシピ】PostgreSQLのSELECT句：時間配列の作り方

編集履歴

質問編集履歴

6

内容を修正

2019/10/11 09:24

投稿

tama_yn0815
tama_yn0815

スコア143

title CHANGED
File without changes
body CHANGED
@@ -74,10 +74,23 @@
74
74
  -- ここを作成中
75
75
  -- これは、あまり参考にならない。。。作りかけ箇所
76
76
  ,ARRAY[
77
+ ARRAY[
77
- '00:00:00'
78
+ '00:00:00'
78
- ,SUM(CASE WHEN td.time= '00:00:00' THEN td.value END)::TEXT
79
+ ,SUM(CASE WHEN td.time= '00:00:00' THEN td.value END)::TEXT
79
- ,COUNT(CASE WHEN td.time= '00:00:00' THEN 1 ELSE 0 END)::TEXT
80
+ ,COUNT(CASE WHEN td.time= '00:00:00' THEN 1 ELSE 0 END)::TEXT
81
+ ],
82
+ ARRAY[
83
+ '01:00:00'
84
+ ,SUM(CASE WHEN td.time= '01:00:00' THEN td.value END)::TEXT
85
+ ,COUNT(CASE WHEN td.time= '01:00:00' THEN 1 ELSE 0 END)::TEXT
86
+ ],
87
+ -- ...続く
88
+ ARRAY[
89
+ '23:00:00'
90
+ ,SUM(CASE WHEN td.time= '23:00:00' THEN td.value END)::TEXT
91
+ ,COUNT(CASE WHEN td.time= '23:00:00' THEN 1 ELSE 0 END)::TEXT
92
+ ]
80
- ] AS values_00
93
+ ] AS values
81
94
  -- ...
82
95
  FROM (
83
96
  SELECT

5

内容を変更

2019/10/11 09:24

投稿

tama_yn0815
tama_yn0815

スコア143

title CHANGED
File without changes
body CHANGED
@@ -1,5 +1,34 @@
1
1
  ・**時間配列を作る方法を教えて欲しいです。**
2
2
 
3
+ 元の表から、特定のデータをグループ化し、複数行を一行にまとめ、
4
+ 属性ごと（今回は、時間単位）に列へ入れるのが目的
5
+
6
+ グループ化するカラム：datetime(date部分), user_id, datetime(trunc_time部分), item_id
7
+ 配列化したいカラム：trunc_timeの値、valueの合計、対象のCOUNT(item_idの数)
8
+
9
+ ARRAY[
10
+ ARRAY['00:00:00',SUM(value:数値),COUNT(item_id)]
11
+ ,ARRAY['01:00:00',SUM(value:数値),COUNT(item_id)]
12
+ ...
13
+ ,ARRAY['23:00:00',SUM(value:数値),COUNT(item_id)]
14
+ ]
15
+ これを作る最もEXPLAIN的にコストの低いSQLがあれば、教えて欲しいです。
16
+ ※Planning time, Execution timeどちらも重視します。
17
+ 　と言うのも、実際は、数件ではなく、一日分で、十数万件分のレコードが格納されております。
18
+ 　そちらをPDFへ出力する際に、データの丸め込みを行って、数人～三十人分を一ヵ月分等で出力するため、
19
+ 　SQLのメインレコード的には、900件ですが、item_id毎に特定の加工を施して、LEFT OUTER JOINします。
20
+ 　...出来る事なら、JOINは一回(*1)で済ませたいので、各item_id毎JOIN（*2）よりもコストの低い実現が望ましいです。
21
+
22
+ *1:実行結果（EXPLAIN ANALYZE）
23
+ 計画行：65行
24
+ Planning time: 2.051ms
25
+ Execution time: 89.219 ms
26
+
27
+ *2:実行結果（EXPLAIN ANALYZE）
28
+ 計画行：334行
29
+ Planning time: 177.550ms
30
+ Execution time: 116.606 ms
31
+
3
32
  ** これが元データ **
4
33
  |id|user_id|datetime|item_id|value|
5
34
  |:--|:--|:--|:--|:--|
@@ -12,9 +41,8 @@
12
41
  |7|101|2019/09/01 11:02:49|3|500|
13
42
  |8|101|2019/09/01 08:35:12|1|500|
14
43
 
15
-
16
44
  ** これが作りたい **
17
- |user_id|date|item_id|values{'time','sum(value)','count(id)'}|
45
+ |user_id|date|item_id|values{'time','sum(value)','count(item_id)'}|
18
46
  |:--|:--|:--|:--|
19
47
  |101|2019/08/31|1|{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}|
20
48
  |101|2019/08/31|2|{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}|
@@ -30,13 +58,13 @@
30
58
  FROM (
31
59
  VALUES
32
60
  (1,101,'2019/08/31 22:35:51'::TIMESTAMP,1,500)
33
- ,(2,102,'2019/08/31 01:54:32'::TIMESTAMP,2,500)
61
+ ,(2,101,'2019/08/31 01:54:32'::TIMESTAMP,2,500)
34
62
  ,(3,101,'2019/08/31 15:57:20'::TIMESTAMP,3,500)
35
63
  ,(4,101,'2019/08/31 19:18:45'::TIMESTAMP,2,500)
36
- ,(5,103,'2019/09/01 10:09:58'::TIMESTAMP,1,500)
64
+ ,(5,101,'2019/09/01 10:09:58'::TIMESTAMP,1,500)
37
- ,(6,102,'2019/09/01 20:21:30'::TIMESTAMP,2,500)
65
+ ,(6,101,'2019/09/01 20:21:30'::TIMESTAMP,2,500)
38
66
  ,(7,101,'2019/09/01 11:02:49'::TIMESTAMP,3,500)
39
- ,(8,103,'2019/09/01 08:35:12'::TIMESTAMP,1,500)
67
+ ,(8,101,'2019/09/01 08:35:12'::TIMESTAMP,1,500)
40
68
  ) AS temp (id, user_id, datetime, item_id, value)
41
69
  )
42
70
  SELECT
@@ -44,6 +72,7 @@
44
72
  ,td.item_id
45
73
  ,td.date
46
74
  -- ここを作成中
75
+ -- これは、あまり参考にならない。。。作りかけ箇所
47
76
  ,ARRAY[
48
77
  '00:00:00'
49
78
  ,SUM(CASE WHEN td.time= '00:00:00' THEN td.value END)::TEXT

4

値の編集

2019/10/11 09:06

投稿

tama_yn0815
tama_yn0815

スコア143

title CHANGED
File without changes
body CHANGED
@@ -43,13 +43,13 @@
43
43
  td.user_id
44
44
  ,td.item_id
45
45
  ,td.date
46
- // ここを作成中
46
+ -- ここを作成中
47
47
  ,ARRAY[
48
48
  '00:00:00'
49
49
  ,SUM(CASE WHEN td.time= '00:00:00' THEN td.value END)::TEXT
50
50
  ,COUNT(CASE WHEN td.time= '00:00:00' THEN 1 ELSE 0 END)::TEXT
51
51
  ] AS values_00
52
- // ...
52
+ -- ...
53
53
  FROM (
54
54
  SELECT
55
55
  *

3

内容を変更

2019/10/11 07:58

投稿

tama_yn0815
tama_yn0815

スコア143

title CHANGED
File without changes
body CHANGED
@@ -37,7 +37,7 @@
37
37
  ,(6,102,'2019/09/01 20:21:30'::TIMESTAMP,2,500)
38
38
  ,(7,101,'2019/09/01 11:02:49'::TIMESTAMP,3,500)
39
39
  ,(8,103,'2019/09/01 08:35:12'::TIMESTAMP,1,500)
40
- ) AS temp (id, user_id, datetime, tranc_time, item_id, value)
40
+ ) AS temp (id, user_id, datetime, item_id, value)
41
41
  )
42
42
  SELECT
43
43
  td.user_id
@@ -46,14 +46,15 @@
46
46
  // ここを作成中
47
47
  ,ARRAY[
48
48
  '00:00:00'
49
- ,SUM(CASE WHEN td.tranc_time = '00:00:00' THEN td.value END)::TEXT
49
+ ,SUM(CASE WHEN td.time= '00:00:00' THEN td.value END)::TEXT
50
- ,COUNT(CASE WHEN td.tranc_time = '00:00:00' THEN 1 ELSE 0 END)::TEXT
50
+ ,COUNT(CASE WHEN td.time= '00:00:00' THEN 1 ELSE 0 END)::TEXT
51
51
  ] AS values_00
52
52
  // ...
53
53
  FROM (
54
54
  SELECT
55
55
  *
56
56
  ,temp_datas.datetime::DATE AS date
57
+ ,TO_CHAR(DATE_TRUNC('hour', temp_datas.datetime::TIMESTAMP), 'HH24:MI:SS') AS time
57
58
  FROM
58
59
  temp_datas
59
60
  ) AS td

2

値の編集

2019/10/11 07:57

投稿

tama_yn0815
tama_yn0815

スコア143

title CHANGED
File without changes
body CHANGED
@@ -4,13 +4,13 @@
4
4
  |id|user_id|datetime|item_id|value|
5
5
  |:--|:--|:--|:--|:--|
6
6
  |1|101|2019/08/31 22:35:51|1|500|
7
- |2|102|2019/08/31 01:54:32|2|500|
7
+ |2|101|2019/08/31 01:54:32|2|500|
8
8
  |3|101|2019/08/31 15:57:20|3|500|
9
9
  |4|101|2019/08/31 19:18:45|2|500|
10
- |5|103|2019/09/01 10:09:58|1|500|
10
+ |5|101|2019/09/01 10:09:58|1|500|
11
- |6|102|2019/09/01 20:21:30|2|500|
11
+ |6|101|2019/09/01 20:21:30|2|500|
12
12
  |7|101|2019/09/01 11:02:49|3|500|
13
- |8|103|2019/09/01 08:35:12|1|500|
13
+ |8|101|2019/09/01 08:35:12|1|500|
14
14
 
15
15
 
16
16
  ** これが作りたい **

1

内容を変更

2019/10/11 07:53

投稿

tama_yn0815
tama_yn0815

スコア143

title CHANGED
@@ -1,1 +1,1 @@
1
- 【回答不要！！。。。ちょっと、整理します】PostgreSQLのSELECT句：時間配列の作り方
1
+ 【レシピ】PostgreSQLのSELECT句：時間配列の作り方
body CHANGED
@@ -1,78 +1,49 @@
1
- ### 前提・実現したいことn
2
- postgresqlのSELECT句で、時間毎の配列を作る
1
+ ・**時間配列を作る方法を教えて欲しいです。**
3
2
 
4
- **JSONライク形式でのイメージを記述します**
3
+ ** これが元データ **
4
+ |id|user_id|datetime|item_id|value|
5
- ...あまり、参考にならないかも？です。。。すみません
5
+ |:--|:--|:--|:--|:--|
6
- 伝える力が弱くて！
6
+ |1|101|2019/08/31 22:35:51|1|500|
7
+ |2|102|2019/08/31 01:54:32|2|500|
8
+ |3|101|2019/08/31 15:57:20|3|500|
9
+ |4|101|2019/08/31 19:18:45|2|500|
10
+ |5|103|2019/09/01 10:09:58|1|500|
11
+ |6|102|2019/09/01 20:21:30|2|500|
12
+ |7|101|2019/09/01 11:02:49|3|500|
13
+ |8|103|2019/09/01 08:35:12|1|500|
7
14
 
8
- こんな感じの二次元配列を作りたい。
9
- ```JSON
10
- TimeLikeArray(column): {
11
- //[index]: {[0]: time, [1]: value(sum), [2]: count}
12
- [0]: {[0]: '00:00:00', [1]: 'null', [2]: '0'},
13
- [1]: {[0]: '01:00:00', [1]: '1500', [2]: '3'},
14
- [2]: {[0]: '02:00:00', [1]: 'null', [2]: '0'},
15
- [3]: {[0]: '03:00:00', [1]: 'null', [2]: '0'},
16
- [4]: {[0]: '04:00:00', [1]: '2000', [2]: '4'},
17
- [5]: {[0]: '05:00:00', [1]: 'null', [2]: '0'},
18
- ...
19
- [23]: {[0]: '23:00:00', [1]: '1000', [2]: '3'}
20
- }
21
- ```
22
15
 
23
- ```SQL
24
- -- 元データ
25
- WITH temp_datas AS (
26
- SELECT *
27
- FROM (VALUES
16
+ ** これが作りたい **
17
+ |user_id|date|item_id|values{'time','sum(value)','count(id)'}|
18
+ |:--|:--|:--|:--|
19
+ |101|2019/08/31|1|{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}|
20
+ |101|2019/08/31|2|{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}|
21
+ |101|2019/08/31|3|{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}|
28
- (1,101,'2019/09/01 10:10:10'::TIMESTAMP,'10:00:00',1,400)
22
+ |101|2019/09/01|1|{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}|
29
- ,(2,101,'2019/09/01 10:20:10'::TIMESTAMP,'10:00:00',1,400)
23
+ |101|2019/09/01|2|{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}|
30
- ,(3,101,'2019/09/01 12:30:10'::TIMESTAMP,'12:00:00',1,400)
24
+ |101|2019/09/01|3|{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}|
31
- ,(4,101,'2019/09/01 13:40:10'::TIMESTAMP,'13:00:00',1,400)
32
- ,(5,101,'2019/09/01 13:50:10'::TIMESTAMP,'13:00:00',1,400)
33
- ,(6,101,'2019/09/01 13:00:10'::TIMESTAMP,'13:00:00',1,400)
34
- ,(7,101,'2019/09/01 15:10:10'::TIMESTAMP,'15:00:00',1,400)
35
- ,(8,101,'2019/09/01 16:10:10'::TIMESTAMP,'16:00:00',1,400)
36
- ,(9,101,'2019/09/01 16:20:10'::TIMESTAMP,'16:00:00',1,400)
37
- ) AS item(id, user_id, datetime, tranc_time, item_id, value)
38
- )
39
- SELECT *
40
- FROM temp_datas
41
- ```
42
25
 
43
- **目指している形**
44
- |user_id|item_id|date|values_00|...|values_15|values_16|...|
45
- |:--|:--|:--|:--|:--:|:--|:--|:--:|
46
- |101|1|2019/09/01|{'00:00:00',null,0}|...|{'15:00:00',400,1}|{'16:00:00',800,2}|...|
47
- |102|1|2019/09/01|{'00:00:00',null,0}|...|{'15:00:00',200,1}|{'16:00:00',1600,4}|...|
48
- |103|1|2019/09/01|{'00:00:00',null,0}|...|{'15:00:00',150,1}|{'16:00:00',900,3}|...|
49
-
50
-
51
26
  ### 該当のソースコード
52
-
53
27
  ```SQL
54
- -- 元データ
55
28
  WITH temp_datas AS (
56
29
  SELECT *
57
30
  FROM (
58
31
  VALUES
32
+ (1,101,'2019/08/31 22:35:51'::TIMESTAMP,1,500)
33
+ ,(2,102,'2019/08/31 01:54:32'::TIMESTAMP,2,500)
34
+ ,(3,101,'2019/08/31 15:57:20'::TIMESTAMP,3,500)
35
+ ,(4,101,'2019/08/31 19:18:45'::TIMESTAMP,2,500)
59
- (1,101,'2019/09/01 10:10:10'::TIMESTAMP,'10:00:00',1,400)
36
+ ,(5,103,'2019/09/01 10:09:58'::TIMESTAMP,1,500)
60
- ,(2,101,'2019/09/01 10:20:10'::TIMESTAMP,'10:00:00',1,400)
37
+ ,(6,102,'2019/09/01 20:21:30'::TIMESTAMP,2,500)
61
- ,(3,101,'2019/09/01 12:30:10'::TIMESTAMP,'12:00:00',1,400)
62
- ,(4,101,'2019/09/01 13:40:10'::TIMESTAMP,'13:00:00',1,400)
63
- ,(5,101,'2019/09/01 13:50:10'::TIMESTAMP,'13:00:00',1,400)
64
- ,(6,101,'2019/09/01 13:00:10'::TIMESTAMP,'13:00:00',1,400)
38
+ ,(7,101,'2019/09/01 11:02:49'::TIMESTAMP,3,500)
65
- ,(7,101,'2019/09/01 15:10:10'::TIMESTAMP,'15:00:00',1,400)
66
- ,(8,101,'2019/09/01 16:10:10'::TIMESTAMP,'16:00:00',1,400)
39
+ ,(8,103,'2019/09/01 08:35:12'::TIMESTAMP,1,500)
67
- ,(9,101,'2019/09/01 16:20:10'::TIMESTAMP,'16:00:00',1,400)
68
40
  ) AS temp (id, user_id, datetime, tranc_time, item_id, value)
69
41
  )
70
- -- 詰め替え
71
42
  SELECT
72
43
  td.user_id
73
44
  ,td.item_id
74
45
  ,td.date
75
- -- ここにARRAY_AGGしたい・・・難しいのかな..
46
+ // ここを作成中
76
47
  ,ARRAY[
77
48
  '00:00:00'
78
49
  ,SUM(CASE WHEN td.tranc_time = '00:00:00' THEN td.value END)::TEXT
@@ -94,5 +65,4 @@
94
65
 
95
66
  ### 補足情報（FW/ツールのバージョンなど）
96
67
 
97
- PostgreSQL_version 9.5
68
+ PostgreSQL_version 9.5
98
- [関連質問](https://teratail.com/questions/216456)