回答編集履歴
4
追記したSQL文を添削
answer
CHANGED
|
@@ -46,41 +46,36 @@
|
|
|
46
46
|
| 6 | {1,2,3} | 0 |
|
|
47
47
|
|
|
48
48
|
|
|
49
|
-
**コメントいただいたロジックの
|
|
49
|
+
**以下はコメントいただいたロジックや回答とは別の記述方法:**
|
|
50
|
+
|
|
51
|
+
- 配列をSELECT句で展開
|
|
50
52
|
```SQL
|
|
51
53
|
SELECT a _group
|
|
52
54
|
, b _aggregate
|
|
55
|
+
, (
|
|
56
|
+
SELECT coalesce(
|
|
53
|
-
|
|
57
|
+
min( coalesce( b[ d - 1 ], 0 ) ) filter( where d - c != 1 )
|
|
54
|
-
|
|
58
|
+
, max( c )
|
|
59
|
+
)
|
|
60
|
+
FROM unnest( b )
|
|
61
|
+
with ordinality t ( c, d )
|
|
55
62
|
) _result
|
|
56
63
|
FROM
|
|
57
64
|
(
|
|
58
|
-
SELECT a
|
|
59
|
-
, b
|
|
60
|
-
, unnest( b ) c
|
|
61
|
-
, rank() over( partition by a order by unnest( b ) ) d
|
|
62
|
-
, coalesce(
|
|
63
|
-
b[ rank() over( partition by a order by unnest( b ) ) - 1 ]
|
|
64
|
-
, 0
|
|
65
|
-
) e
|
|
66
|
-
FROM
|
|
67
|
-
(
|
|
68
|
-
|
|
65
|
+
VALUES
|
|
69
|
-
|
|
66
|
+
( 1, array[0,1,2,4,5]::int[] )
|
|
70
|
-
|
|
67
|
+
, ( 2, array[0,1,2,3]::int[] )
|
|
71
|
-
|
|
68
|
+
, ( 3, array[0,1,5]::int[] )
|
|
72
|
-
|
|
69
|
+
, ( 4, array[0,1]::int[] )
|
|
73
|
-
|
|
70
|
+
, ( 5, array[0,1,2,5]::int[] )
|
|
74
|
-
|
|
71
|
+
, ( 6, array[1,2,3]::int[] )
|
|
75
|
-
|
|
72
|
+
) q ( a, b )
|
|
76
|
-
) q
|
|
77
|
-
GROUP BY a, b
|
|
78
73
|
ORDER BY 1
|
|
79
74
|
;
|
|
80
75
|
```
|
|
81
76
|
|
|
82
77
|
|
|
83
|
-
|
|
78
|
+
- 配列同士の差分比較
|
|
84
79
|
```SQL
|
|
85
80
|
SELECT a _group
|
|
86
81
|
, b _aggregate
|
|
@@ -107,7 +102,7 @@
|
|
|
107
102
|
```
|
|
108
103
|
|
|
109
104
|
|
|
110
|
-
|
|
105
|
+
- 配列をFROM句で展開してビューのネストを無くす
|
|
111
106
|
```SQL
|
|
112
107
|
SELECT a _group
|
|
113
108
|
, b _aggregate
|
3
別ロジックのSQLを追記
answer
CHANGED
|
@@ -104,4 +104,29 @@
|
|
|
104
104
|
) q ( a, b )
|
|
105
105
|
ORDER BY 1
|
|
106
106
|
;
|
|
107
|
+
```
|
|
108
|
+
|
|
109
|
+
|
|
110
|
+
**コメントいただいたロジックに近いと思われるSQLを追記:**
|
|
111
|
+
```SQL
|
|
112
|
+
SELECT a _group
|
|
113
|
+
, b _aggregate
|
|
114
|
+
, coalesce(
|
|
115
|
+
min( coalesce( b[ d - 1 ], 0 ) ) filter( where d - c != 1 )
|
|
116
|
+
, max( c )
|
|
117
|
+
) _result
|
|
118
|
+
FROM
|
|
119
|
+
(
|
|
120
|
+
VALUES
|
|
121
|
+
( 1, array[0,1,2,4,5]::int[] )
|
|
122
|
+
, ( 2, array[0,1,2,3]::int[] )
|
|
123
|
+
, ( 3, array[0,1,5]::int[] )
|
|
124
|
+
, ( 4, array[0,1]::int[] )
|
|
125
|
+
, ( 5, array[0,1,2,5]::int[] )
|
|
126
|
+
, ( 6, array[1,2,3]::int[] )
|
|
127
|
+
) t1 ( a, b )
|
|
128
|
+
, LATERAL unnest( b ) with ordinality t2 ( c, d )
|
|
129
|
+
GROUP BY a, b
|
|
130
|
+
ORDER BY 1
|
|
131
|
+
;
|
|
107
132
|
```
|
2
別アプローチでSQLを追記
answer
CHANGED
|
@@ -77,4 +77,31 @@
|
|
|
77
77
|
GROUP BY a, b
|
|
78
78
|
ORDER BY 1
|
|
79
79
|
;
|
|
80
|
+
```
|
|
81
|
+
|
|
82
|
+
|
|
83
|
+
**コメントの内容を読み違えていましたので、別のアプローチでSQLを再々追記:**
|
|
84
|
+
```SQL
|
|
85
|
+
SELECT a _group
|
|
86
|
+
, b _aggregate
|
|
87
|
+
, (
|
|
88
|
+
SELECT coalesce( min(i) - 1 + ( min(i) = 0 )::int
|
|
89
|
+
, b[ array_upper( b, 1 ) ]
|
|
90
|
+
)
|
|
91
|
+
FROM generate_series( 0, b[ array_length( b, 1 ) ] )
|
|
92
|
+
as nums(i)
|
|
93
|
+
WHERE NOT array[i] <@ b
|
|
94
|
+
) _result
|
|
95
|
+
FROM
|
|
96
|
+
(
|
|
97
|
+
VALUES
|
|
98
|
+
( 1, array[0,1,2,4,5]::int[] )
|
|
99
|
+
, ( 2, array[0,1,2,3]::int[] )
|
|
100
|
+
, ( 3, array[0,1,5]::int[] )
|
|
101
|
+
, ( 4, array[0,1]::int[] )
|
|
102
|
+
, ( 5, array[0,1,2,5]::int[] )
|
|
103
|
+
, ( 6, array[1,2,3]::int[] )
|
|
104
|
+
) q ( a, b )
|
|
105
|
+
ORDER BY 1
|
|
106
|
+
;
|
|
80
107
|
```
|
1
別ロジックのSQLを追記
answer
CHANGED
|
@@ -43,4 +43,38 @@
|
|
|
43
43
|
| 3 | {0,1,5} | 1 |
|
|
44
44
|
| 4 | {0,1} | 1 |
|
|
45
45
|
| 5 | {0,1,2,5} | 2 |
|
|
46
|
-
| 6 | {1,2,3} | 0 |
|
|
46
|
+
| 6 | {1,2,3} | 0 |
|
|
47
|
+
|
|
48
|
+
|
|
49
|
+
**コメントいただいたロジックのSQLを追記:**
|
|
50
|
+
```SQL
|
|
51
|
+
SELECT a _group
|
|
52
|
+
, b _aggregate
|
|
53
|
+
, coalesce( min( e ) filter( where d - c != 1 )
|
|
54
|
+
, max( c )
|
|
55
|
+
) _result
|
|
56
|
+
FROM
|
|
57
|
+
(
|
|
58
|
+
SELECT a
|
|
59
|
+
, b
|
|
60
|
+
, unnest( b ) c
|
|
61
|
+
, rank() over( partition by a order by unnest( b ) ) d
|
|
62
|
+
, coalesce(
|
|
63
|
+
b[ rank() over( partition by a order by unnest( b ) ) - 1 ]
|
|
64
|
+
, 0
|
|
65
|
+
) e
|
|
66
|
+
FROM
|
|
67
|
+
(
|
|
68
|
+
VALUES
|
|
69
|
+
( 1, array[0,1,2,4,5]::int[] )
|
|
70
|
+
, ( 2, array[0,1,2,3]::int[] )
|
|
71
|
+
, ( 3, array[0,1,5]::int[] )
|
|
72
|
+
, ( 4, array[0,1]::int[] )
|
|
73
|
+
, ( 5, array[0,1,2,5]::int[] )
|
|
74
|
+
, ( 6, array[1,2,3]::int[] )
|
|
75
|
+
) t1 ( a, b )
|
|
76
|
+
) q
|
|
77
|
+
GROUP BY a, b
|
|
78
|
+
ORDER BY 1
|
|
79
|
+
;
|
|
80
|
+
```
|