回答編集履歴
2
訂正
answer
CHANGED
|
@@ -20,6 +20,6 @@
|
|
|
20
20
|
from test
|
|
21
21
|
)
|
|
22
22
|
select test.No, range.開始日, test.値
|
|
23
|
-
from range
|
|
23
|
+
from range left join test
|
|
24
24
|
on range.開始日 between test.開始日 and test.終了日
|
|
25
25
|
```
|
1
追記
answer
CHANGED
|
@@ -1,5 +1,14 @@
|
|
|
1
|
-
|
|
1
|
+
求める結果にはなっていますよ？
|
|
2
2
|
```SQL
|
|
3
|
+
select No, GENERATE_SERIES(開始日, 終了日,'1 day'::interval)::date 開始日, 値
|
|
4
|
+
from (values
|
|
5
|
+
(1,'2019/12/01'::date, '2019/12/05'::date, 12345)
|
|
6
|
+
, (2,'2019/12/06'::date, '2019/12/07'::date, 99999)
|
|
7
|
+
) as w(No, 開始日, 終了日, 値)
|
|
8
|
+
```
|
|
9
|
+
|
|
10
|
+
データの存在しない日付も出力したいのなら、**GENERATE_SERIES()**に与える範囲をデータの最小と最大にします。
|
|
11
|
+
```SQL
|
|
3
12
|
with test as (
|
|
4
13
|
select *
|
|
5
14
|
from (values
|
|
@@ -7,10 +16,10 @@
|
|
|
7
16
|
, (2,'2019/12/06'::date, '2019/12/07'::date, 99999)
|
|
8
17
|
) as w(No, 開始日, 終了日, 値)
|
|
9
18
|
), range as (
|
|
10
|
-
select GENERATE_SERIES(min(開始日), max(終了日),'1 day'::interval)::date
|
|
19
|
+
select GENERATE_SERIES(min(開始日), max(終了日),'1 day'::interval)::date 開始日
|
|
11
20
|
from test
|
|
12
21
|
)
|
|
13
|
-
select range.
|
|
22
|
+
select test.No, range.開始日, test.値
|
|
14
23
|
from range inner join test
|
|
15
|
-
on range.
|
|
24
|
+
on range.開始日 between test.開始日 and test.終了日
|
|
16
25
|
```
|