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

編集履歴

質問編集履歴

6

内容を修正

2019/10/11 09:24

投稿

tama_yn0815
tama_yn0815

スコア143

test CHANGED
File without changes
test CHANGED
@@ -150,13 +150,39 @@
150
150
 
151
151
  ,ARRAY[
152
152
 
153
+ ARRAY[
154
+
153
- '00:00:00'
155
+ '00:00:00'
154
-
156
+
155
- ,SUM(CASE WHEN td.time= '00:00:00' THEN td.value END)::TEXT
157
+ ,SUM(CASE WHEN td.time= '00:00:00' THEN td.value END)::TEXT
156
-
158
+
157
- ,COUNT(CASE WHEN td.time= '00:00:00' THEN 1 ELSE 0 END)::TEXT
159
+ ,COUNT(CASE WHEN td.time= '00:00:00' THEN 1 ELSE 0 END)::TEXT
160
+
158
-
161
+ ],
162
+
163
+ ARRAY[
164
+
165
+ '01:00:00'
166
+
167
+ ,SUM(CASE WHEN td.time= '01:00:00' THEN td.value END)::TEXT
168
+
169
+ ,COUNT(CASE WHEN td.time= '01:00:00' THEN 1 ELSE 0 END)::TEXT
170
+
171
+ ],
172
+
173
+ -- ...続く
174
+
175
+ ARRAY[
176
+
177
+ '23:00:00'
178
+
179
+ ,SUM(CASE WHEN td.time= '23:00:00' THEN td.value END)::TEXT
180
+
181
+ ,COUNT(CASE WHEN td.time= '23:00:00' THEN 1 ELSE 0 END)::TEXT
182
+
183
+ ]
184
+
159
- ] AS values_00
185
+ ] AS values
160
186
 
161
187
  -- ...
162
188
 

5

内容を変更

2019/10/11 09:24

投稿

tama_yn0815
tama_yn0815

スコア143

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

4

値の編集

2019/10/11 09:06

投稿

tama_yn0815
tama_yn0815

スコア143

test CHANGED
File without changes
test CHANGED
@@ -88,7 +88,7 @@
88
88
 
89
89
  ,td.date
90
90
 
91
- // ここを作成中
91
+ -- ここを作成中
92
92
 
93
93
  ,ARRAY[
94
94
 
@@ -100,7 +100,7 @@
100
100
 
101
101
  ] AS values_00
102
102
 
103
- // ...
103
+ -- ...
104
104
 
105
105
  FROM (
106
106
 

3

内容を変更

2019/10/11 07:58

投稿

tama_yn0815
tama_yn0815

スコア143

test CHANGED
File without changes
test CHANGED
@@ -76,7 +76,7 @@
76
76
 
77
77
  ,(8,103,'2019/09/01 08:35:12'::TIMESTAMP,1,500)
78
78
 
79
- ) AS temp (id, user_id, datetime, tranc_time, item_id, value)
79
+ ) AS temp (id, user_id, datetime, item_id, value)
80
80
 
81
81
  )
82
82
 
@@ -94,9 +94,9 @@
94
94
 
95
95
  '00:00:00'
96
96
 
97
- ,SUM(CASE WHEN td.tranc_time = '00:00:00' THEN td.value END)::TEXT
97
+ ,SUM(CASE WHEN td.time= '00:00:00' THEN td.value END)::TEXT
98
98
 
99
- ,COUNT(CASE WHEN td.tranc_time = '00:00:00' THEN 1 ELSE 0 END)::TEXT
99
+ ,COUNT(CASE WHEN td.time= '00:00:00' THEN 1 ELSE 0 END)::TEXT
100
100
 
101
101
  ] AS values_00
102
102
 
@@ -109,6 +109,8 @@
109
109
  *
110
110
 
111
111
  ,temp_datas.datetime::DATE AS date
112
+
113
+ ,TO_CHAR(DATE_TRUNC('hour', temp_datas.datetime::TIMESTAMP), 'HH24:MI:SS') AS time
112
114
 
113
115
  FROM
114
116
 

2

値の編集

2019/10/11 07:57

投稿

tama_yn0815
tama_yn0815

スコア143

test CHANGED
File without changes
test CHANGED
@@ -10,19 +10,19 @@
10
10
 
11
11
  |1|101|2019/08/31 22:35:51|1|500|
12
12
 
13
- |2|102|2019/08/31 01:54:32|2|500|
13
+ |2|101|2019/08/31 01:54:32|2|500|
14
14
 
15
15
  |3|101|2019/08/31 15:57:20|3|500|
16
16
 
17
17
  |4|101|2019/08/31 19:18:45|2|500|
18
18
 
19
- |5|103|2019/09/01 10:09:58|1|500|
19
+ |5|101|2019/09/01 10:09:58|1|500|
20
20
 
21
- |6|102|2019/09/01 20:21:30|2|500|
21
+ |6|101|2019/09/01 20:21:30|2|500|
22
22
 
23
23
  |7|101|2019/09/01 11:02:49|3|500|
24
24
 
25
- |8|103|2019/09/01 08:35:12|1|500|
25
+ |8|101|2019/09/01 08:35:12|1|500|
26
26
 
27
27
 
28
28
 

1

内容を変更

2019/10/11 07:53

投稿

tama_yn0815
tama_yn0815

スコア143

test CHANGED
@@ -1 +1 @@
1
- 【回答不要！！。。。ちょっと、整理します】PostgreSQLのSELECT句：時間配列の作り方
1
+ 【レシピ】PostgreSQLのSELECT句：時間配列の作り方
test CHANGED
@@ -1,110 +1,56 @@
1
- ### 前提・実現したいことn
2
-
3
- postgresqlのSELECT句で、時間毎の配列を作る
1
+ ・**時間配列を作る方法を教えて欲しいです。**
4
2
 
5
3
 
6
4
 
7
- **JSONライク形式でのイメージを記述します**
5
+ ** これが元データ **
8
6
 
9
- ...あまり、参考にならないかも？です。。。すみません
7
+ |id|user_id|datetime|item_id|value|
10
8
 
11
- 伝える力が弱くて！
9
+ |:--|:--|:--|:--|:--|
12
10
 
11
+ |1|101|2019/08/31 22:35:51|1|500|
13
12
 
13
+ |2|102|2019/08/31 01:54:32|2|500|
14
14
 
15
- こんな感じの二次元配列を作りたい。
15
+ |3|101|2019/08/31 15:57:20|3|500|
16
16
 
17
- ```JSON
17
+ |4|101|2019/08/31 19:18:45|2|500|
18
18
 
19
- TimeLikeArray(column): {
19
+ |5|103|2019/09/01 10:09:58|1|500|
20
20
 
21
- //[index]: {[0]: time, [1]: value(sum), [2]: count}
21
+ |6|102|2019/09/01 20:21:30|2|500|
22
22
 
23
- [0]: {[0]: '00:00:00', [1]: 'null', [2]: '0'},
23
+ |7|101|2019/09/01 11:02:49|3|500|
24
24
 
25
- [1]: {[0]: '01:00:00', [1]: '1500', [2]: '3'},
26
-
27
- [2]: {[0]: '02:00:00', [1]: 'null', [2]: '0'},
28
-
29
- [3]: {[0]: '03:00:00', [1]: 'null', [2]: '0'},
30
-
31
- [4]: {[0]: '04:00:00', [1]: '2000', [2]: '4'},
32
-
33
- [5]: {[0]: '05:00:00', [1]: 'null', [2]: '0'},
34
-
35
- ...
36
-
37
- [23]: {[0]: '23:00:00', [1]: '1000', [2]: '3'}
38
-
39
- }
40
-
41
- ```
42
-
43
-
44
-
45
- ```SQL
46
-
47
- -- 元データ
48
-
49
- WITH temp_datas AS (
50
-
51
- SELECT *
52
-
53
- FROM (VALUES
54
-
55
- (1,101,'2019/09/01 10:10:10'::TIMESTAMP,'10:00:00',1,400)
25
+ |8|103|2019/09/01 08:35:12|1|500|
56
-
57
- ,(2,101,'2019/09/01 10:20:10'::TIMESTAMP,'10:00:00',1,400)
58
-
59
- ,(3,101,'2019/09/01 12:30:10'::TIMESTAMP,'12:00:00',1,400)
60
-
61
- ,(4,101,'2019/09/01 13:40:10'::TIMESTAMP,'13:00:00',1,400)
62
-
63
- ,(5,101,'2019/09/01 13:50:10'::TIMESTAMP,'13:00:00',1,400)
64
-
65
- ,(6,101,'2019/09/01 13:00:10'::TIMESTAMP,'13:00:00',1,400)
66
-
67
- ,(7,101,'2019/09/01 15:10:10'::TIMESTAMP,'15:00:00',1,400)
68
-
69
- ,(8,101,'2019/09/01 16:10:10'::TIMESTAMP,'16:00:00',1,400)
70
-
71
- ,(9,101,'2019/09/01 16:20:10'::TIMESTAMP,'16:00:00',1,400)
72
-
73
- ) AS item(id, user_id, datetime, tranc_time, item_id, value)
74
-
75
- )
76
-
77
- SELECT *
78
-
79
- FROM temp_datas
80
-
81
- ```
82
-
83
-
84
-
85
- **目指している形**
86
-
87
- |user_id|item_id|date|values_00|...|values_15|values_16|...|
88
-
89
- |:--|:--|:--|:--|:--:|:--|:--|:--:|
90
-
91
- |101|1|2019/09/01|{'00:00:00',null,0}|...|{'15:00:00',400,1}|{'16:00:00',800,2}|...|
92
-
93
- |102|1|2019/09/01|{'00:00:00',null,0}|...|{'15:00:00',200,1}|{'16:00:00',1600,4}|...|
94
-
95
- |103|1|2019/09/01|{'00:00:00',null,0}|...|{'15:00:00',150,1}|{'16:00:00',900,3}|...|
96
26
 
97
27
 
98
28
 
99
29
 
100
30
 
101
- ### 該当のソースコード
31
+ ** これが作りたい **
32
+
33
+ |user_id|date|item_id|values{'time','sum(value)','count(id)'}|
34
+
35
+ |:--|:--|:--|:--|
36
+
37
+ |101|2019/08/31|1|{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}|
38
+
39
+ |101|2019/08/31|2|{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}|
40
+
41
+ |101|2019/08/31|3|{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}|
42
+
43
+ |101|2019/09/01|1|{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}|
44
+
45
+ |101|2019/09/01|2|{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}|
46
+
47
+ |101|2019/09/01|3|{{'00:00:00',400,2},{'01:00:00',NULL,0},{'02:00:00',800,5},...}|
102
48
 
103
49
 
104
50
 
51
+ ### 該当のソースコード
52
+
105
53
  ```SQL
106
-
107
- -- 元データ
108
54
 
109
55
  WITH temp_datas AS (
110
56
 
@@ -114,29 +60,25 @@
114
60
 
115
61
  VALUES
116
62
 
117
- (1,101,'2019/09/01 10:10:10'::TIMESTAMP,'10:00:00',1,400)
63
+ (1,101,'2019/08/31 22:35:51'::TIMESTAMP,1,500)
118
64
 
119
- ,(2,101,'2019/09/01 10:20:10'::TIMESTAMP,'10:00:00',1,400)
65
+ ,(2,102,'2019/08/31 01:54:32'::TIMESTAMP,2,500)
120
66
 
121
- ,(3,101,'2019/09/01 12:30:10'::TIMESTAMP,'12:00:00',1,400)
67
+ ,(3,101,'2019/08/31 15:57:20'::TIMESTAMP,3,500)
122
68
 
123
- ,(4,101,'2019/09/01 13:40:10'::TIMESTAMP,'13:00:00',1,400)
69
+ ,(4,101,'2019/08/31 19:18:45'::TIMESTAMP,2,500)
124
70
 
125
- ,(5,101,'2019/09/01 13:50:10'::TIMESTAMP,'13:00:00',1,400)
71
+ ,(5,103,'2019/09/01 10:09:58'::TIMESTAMP,1,500)
126
72
 
127
- ,(6,101,'2019/09/01 13:00:10'::TIMESTAMP,'13:00:00',1,400)
73
+ ,(6,102,'2019/09/01 20:21:30'::TIMESTAMP,2,500)
128
74
 
129
- ,(7,101,'2019/09/01 15:10:10'::TIMESTAMP,'15:00:00',1,400)
75
+ ,(7,101,'2019/09/01 11:02:49'::TIMESTAMP,3,500)
130
76
 
131
- ,(8,101,'2019/09/01 16:10:10'::TIMESTAMP,'16:00:00',1,400)
77
+ ,(8,103,'2019/09/01 08:35:12'::TIMESTAMP,1,500)
132
-
133
- ,(9,101,'2019/09/01 16:20:10'::TIMESTAMP,'16:00:00',1,400)
134
78
 
135
79
  ) AS temp (id, user_id, datetime, tranc_time, item_id, value)
136
80
 
137
81
  )
138
-
139
- -- 詰め替え
140
82
 
141
83
  SELECT
142
84
 
@@ -146,7 +88,7 @@
146
88
 
147
89
  ,td.date
148
90
 
149
- -- ここにARRAY_AGGしたい・・・難しいのかな..
91
+ // ここを作成中
150
92
 
151
93
  ,ARRAY[
152
94
 
@@ -191,5 +133,3 @@
191
133
 
192
134
 
193
135
  PostgreSQL_version 9.5
194
-
195
- [関連質問](https://teratail.com/questions/216456)