teratail
回答率
85.35%
teratail header banner
teratail header banner
質問するログイン新規登録
トップPostgreSQLに関する質問[PostgreSQL] 特殊なケースにおける配列抽出の実現方法

編集履歴

回答編集履歴

4

追記したSQL文を添削

2021/10/14 15:22

投稿

mayu-
mayu-

スコア335

test CHANGED
@@ -94,17 +94,31 @@
94
94
 
95
95
  　　
96
96
 
97
- **コメントいただいたロジックのSQLを追記:**
97
+ **以下はコメントいただいたロジックや回答とは別の記述方法:**
98
+
99
+
100
+
98
-
101
+ - 配列をSELECT句で展開
102
+
99
- ```SQL
103
+ ```SQL
100
-
104
+
101
- SELECT a _group
105
+ SELECT a _group
102
-
106
+
103
- , b _aggregate
107
+ , b _aggregate
108
+
104
-
109
+ , (
110
+
111
+ SELECT coalesce(
112
+
105
- , coalesce( min( e ) filter( where d - c != 1 )
113
+ min( coalesce( b[ d - 1 ], 0 ) ) filter( where d - c != 1 )
106
-
114
+
107
- , max( c )
115
+ , max( c )
116
+
117
+ )
118
+
119
+ FROM unnest( b )
120
+
121
+ with ordinality t ( c, d )
108
122
 
109
123
  ) _result
110
124
 
@@ -112,43 +126,123 @@
112
126
 
113
127
  (
114
128
 
115
- SELECT a
116
-
117
- , b
118
-
119
- , unnest( b ) c
120
-
121
- , rank() over( partition by a order by unnest( b ) ) d
122
-
123
- , coalesce(
124
-
125
- b[ rank() over( partition by a order by unnest( b ) ) - 1 ]
126
-
127
- , 0
128
-
129
- ) e
130
-
131
- FROM
132
-
133
- (
134
-
135
- VALUES
136
-
137
- ( 1, array[0,1,2,4,5]::int[] )
138
-
139
- , ( 2, array[0,1,2,3]::int[] )
140
-
141
- , ( 3, array[0,1,5]::int[] )
142
-
143
- , ( 4, array[0,1]::int[] )
144
-
145
- , ( 5, array[0,1,2,5]::int[] )
146
-
147
- , ( 6, array[1,2,3]::int[] )
148
-
149
- ) t1 ( a, b )
150
-
151
- ) q
129
+ VALUES
130
+
131
+ ( 1, array[0,1,2,4,5]::int[] )
132
+
133
+ , ( 2, array[0,1,2,3]::int[] )
134
+
135
+ , ( 3, array[0,1,5]::int[] )
136
+
137
+ , ( 4, array[0,1]::int[] )
138
+
139
+ , ( 5, array[0,1,2,5]::int[] )
140
+
141
+ , ( 6, array[1,2,3]::int[] )
142
+
143
+ ) q ( a, b )
144
+
145
+ ORDER BY 1
146
+
147
+ ;
148
+
149
+ ```
150
+
151
+
152
+
153
+ 　
154
+
155
+ - 配列同士の差分比較
156
+
157
+ ```SQL
158
+
159
+ SELECT a _group
160
+
161
+ , b _aggregate
162
+
163
+ , (
164
+
165
+ SELECT coalesce( min(i) - 1 + ( min(i) = 0 )::int
166
+
167
+ , b[ array_upper( b, 1 ) ]
168
+
169
+ )
170
+
171
+ FROM generate_series( 0, b[ array_length( b, 1 ) ] )
172
+
173
+ as nums(i)
174
+
175
+ WHERE NOT array[i] <@ b
176
+
177
+ ) _result
178
+
179
+ FROM
180
+
181
+ (
182
+
183
+ VALUES
184
+
185
+ ( 1, array[0,1,2,4,5]::int[] )
186
+
187
+ , ( 2, array[0,1,2,3]::int[] )
188
+
189
+ , ( 3, array[0,1,5]::int[] )
190
+
191
+ , ( 4, array[0,1]::int[] )
192
+
193
+ , ( 5, array[0,1,2,5]::int[] )
194
+
195
+ , ( 6, array[1,2,3]::int[] )
196
+
197
+ ) q ( a, b )
198
+
199
+ ORDER BY 1
200
+
201
+ ;
202
+
203
+ ```
204
+
205
+
206
+
207
+ 　　
208
+
209
+ - 配列をFROM句で展開してビューのネストを無くす
210
+
211
+ ```SQL
212
+
213
+ SELECT a _group
214
+
215
+ , b _aggregate
216
+
217
+ , coalesce(
218
+
219
+ min( coalesce( b[ d - 1 ], 0 ) ) filter( where d - c != 1 )
220
+
221
+ , max( c )
222
+
223
+ ) _result
224
+
225
+ FROM
226
+
227
+ (
228
+
229
+ VALUES
230
+
231
+ ( 1, array[0,1,2,4,5]::int[] )
232
+
233
+ , ( 2, array[0,1,2,3]::int[] )
234
+
235
+ , ( 3, array[0,1,5]::int[] )
236
+
237
+ , ( 4, array[0,1]::int[] )
238
+
239
+ , ( 5, array[0,1,2,5]::int[] )
240
+
241
+ , ( 6, array[1,2,3]::int[] )
242
+
243
+ ) t1 ( a, b )
244
+
245
+ , LATERAL unnest( b ) with ordinality t2 ( c, d )
152
246
 
153
247
  GROUP BY a, b
154
248
 
@@ -157,107 +251,3 @@
157
251
  ;
158
252
 
159
253
  ```
160
-
161
-
162
-
163
- 　
164
-
165
- **コメントの内容を読み違えていましたので、別のアプローチでSQLを再々追記:**
166
-
167
- ```SQL
168
-
169
- SELECT a _group
170
-
171
- , b _aggregate
172
-
173
- , (
174
-
175
- SELECT coalesce( min(i) - 1 + ( min(i) = 0 )::int
176
-
177
- , b[ array_upper( b, 1 ) ]
178
-
179
- )
180
-
181
- FROM generate_series( 0, b[ array_length( b, 1 ) ] )
182
-
183
- as nums(i)
184
-
185
- WHERE NOT array[i] <@ b
186
-
187
- ) _result
188
-
189
- FROM
190
-
191
- (
192
-
193
- VALUES
194
-
195
- ( 1, array[0,1,2,4,5]::int[] )
196
-
197
- , ( 2, array[0,1,2,3]::int[] )
198
-
199
- , ( 3, array[0,1,5]::int[] )
200
-
201
- , ( 4, array[0,1]::int[] )
202
-
203
- , ( 5, array[0,1,2,5]::int[] )
204
-
205
- , ( 6, array[1,2,3]::int[] )
206
-
207
- ) q ( a, b )
208
-
209
- ORDER BY 1
210
-
211
- ;
212
-
213
- ```
214
-
215
-
216
-
217
- 　　
218
-
219
- **コメントいただいたロジックに近いと思われるSQLを追記:**
220
-
221
- ```SQL
222
-
223
- SELECT a _group
224
-
225
- , b _aggregate
226
-
227
- , coalesce(
228
-
229
- min( coalesce( b[ d - 1 ], 0 ) ) filter( where d - c != 1 )
230
-
231
- , max( c )
232
-
233
- ) _result
234
-
235
- FROM
236
-
237
- (
238
-
239
- VALUES
240
-
241
- ( 1, array[0,1,2,4,5]::int[] )
242
-
243
- , ( 2, array[0,1,2,3]::int[] )
244
-
245
- , ( 3, array[0,1,5]::int[] )
246
-
247
- , ( 4, array[0,1]::int[] )
248
-
249
- , ( 5, array[0,1,2,5]::int[] )
250
-
251
- , ( 6, array[1,2,3]::int[] )
252
-
253
- ) t1 ( a, b )
254
-
255
- , LATERAL unnest( b ) with ordinality t2 ( c, d )
256
-
257
- GROUP BY a, b
258
-
259
- ORDER BY 1
260
-
261
- ;
262
-
263
- ```

3

別ロジックのSQLを追記

2021/10/14 15:21

投稿

mayu-
mayu-

スコア335

test CHANGED
@@ -211,3 +211,53 @@
211
211
  ;
212
212
 
213
213
  ```
214
+
215
+
216
+
217
+ 　　
218
+
219
+ **コメントいただいたロジックに近いと思われるSQLを追記:**
220
+
221
+ ```SQL
222
+
223
+ SELECT a _group
224
+
225
+ , b _aggregate
226
+
227
+ , coalesce(
228
+
229
+ min( coalesce( b[ d - 1 ], 0 ) ) filter( where d - c != 1 )
230
+
231
+ , max( c )
232
+
233
+ ) _result
234
+
235
+ FROM
236
+
237
+ (
238
+
239
+ VALUES
240
+
241
+ ( 1, array[0,1,2,4,5]::int[] )
242
+
243
+ , ( 2, array[0,1,2,3]::int[] )
244
+
245
+ , ( 3, array[0,1,5]::int[] )
246
+
247
+ , ( 4, array[0,1]::int[] )
248
+
249
+ , ( 5, array[0,1,2,5]::int[] )
250
+
251
+ , ( 6, array[1,2,3]::int[] )
252
+
253
+ ) t1 ( a, b )
254
+
255
+ , LATERAL unnest( b ) with ordinality t2 ( c, d )
256
+
257
+ GROUP BY a, b
258
+
259
+ ORDER BY 1
260
+
261
+ ;
262
+
263
+ ```

2

別アプローチでSQLを追記

2021/10/12 22:54

投稿

mayu-
mayu-

スコア335

test CHANGED
@@ -157,3 +157,57 @@
157
157
  ;
158
158
 
159
159
  ```
160
+
161
+
162
+
163
+ 　
164
+
165
+ **コメントの内容を読み違えていましたので、別のアプローチでSQLを再々追記:**
166
+
167
+ ```SQL
168
+
169
+ SELECT a _group
170
+
171
+ , b _aggregate
172
+
173
+ , (
174
+
175
+ SELECT coalesce( min(i) - 1 + ( min(i) = 0 )::int
176
+
177
+ , b[ array_upper( b, 1 ) ]
178
+
179
+ )
180
+
181
+ FROM generate_series( 0, b[ array_length( b, 1 ) ] )
182
+
183
+ as nums(i)
184
+
185
+ WHERE NOT array[i] <@ b
186
+
187
+ ) _result
188
+
189
+ FROM
190
+
191
+ (
192
+
193
+ VALUES
194
+
195
+ ( 1, array[0,1,2,4,5]::int[] )
196
+
197
+ , ( 2, array[0,1,2,3]::int[] )
198
+
199
+ , ( 3, array[0,1,5]::int[] )
200
+
201
+ , ( 4, array[0,1]::int[] )
202
+
203
+ , ( 5, array[0,1,2,5]::int[] )
204
+
205
+ , ( 6, array[1,2,3]::int[] )
206
+
207
+ ) q ( a, b )
208
+
209
+ ORDER BY 1
210
+
211
+ ;
212
+
213
+ ```

1

別ロジックのSQLを追記

2021/10/12 07:41

投稿

mayu-
mayu-

スコア335

test CHANGED
@@ -89,3 +89,71 @@
89
89
  | 5 | {0,1,2,5} | 2 |
90
90
 
91
91
  | 6 | {1,2,3} | 0 |
92
+
93
+
94
+
95
+ 　　
96
+
97
+ **コメントいただいたロジックのSQLを追記:**
98
+
99
+ ```SQL
100
+
101
+ SELECT a _group
102
+
103
+ , b _aggregate
104
+
105
+ , coalesce( min( e ) filter( where d - c != 1 )
106
+
107
+ , max( c )
108
+
109
+ ) _result
110
+
111
+ FROM
112
+
113
+ (
114
+
115
+ SELECT a
116
+
117
+ , b
118
+
119
+ , unnest( b ) c
120
+
121
+ , rank() over( partition by a order by unnest( b ) ) d
122
+
123
+ , coalesce(
124
+
125
+ b[ rank() over( partition by a order by unnest( b ) ) - 1 ]
126
+
127
+ , 0
128
+
129
+ ) e
130
+
131
+ FROM
132
+
133
+ (
134
+
135
+ VALUES
136
+
137
+ ( 1, array[0,1,2,4,5]::int[] )
138
+
139
+ , ( 2, array[0,1,2,3]::int[] )
140
+
141
+ , ( 3, array[0,1,5]::int[] )
142
+
143
+ , ( 4, array[0,1]::int[] )
144
+
145
+ , ( 5, array[0,1,2,5]::int[] )
146
+
147
+ , ( 6, array[1,2,3]::int[] )
148
+
149
+ ) t1 ( a, b )
150
+
151
+ ) q
152
+
153
+ GROUP BY a, b
154
+
155
+ ORDER BY 1
156
+
157
+ ;
158
+
159
+ ```