回答編集履歴
6
修正
test
CHANGED
|
@@ -48,6 +48,8 @@
|
|
|
48
48
|
|
|
49
49
|
)
|
|
50
50
|
|
|
51
|
+
select
|
|
52
|
+
|
|
51
53
|
d.dt::date
|
|
52
54
|
|
|
53
55
|
, COALESCE(t.購入金額, 0) AS 購入金額
|
5
修正
test
CHANGED
|
@@ -30,7 +30,7 @@
|
|
|
30
30
|
|
|
31
31
|
何らかの基準が無いと。
|
|
32
32
|
|
|
33
|
-
redshift版
|
|
33
|
+
redshift版（但し環境がなく未確認）
|
|
34
34
|
|
|
35
35
|
--
|
|
36
36
|
|
4
追記
test
CHANGED
|
@@ -1,4 +1,8 @@
|
|
|
1
1
|
[genarate_serise](https://www.postgresql.jp/document/9.6/html/functions-srf.html)が使えます。
|
|
2
|
+
|
|
3
|
+
postgres版
|
|
4
|
+
|
|
5
|
+
--
|
|
2
6
|
|
|
3
7
|
```SQL
|
|
4
8
|
|
|
@@ -8,7 +12,7 @@
|
|
|
8
12
|
|
|
9
13
|
, COALESCE(t.購入金額, 0) AS 購入金額
|
|
10
14
|
|
|
11
|
-
, coalesce((select 累積購入金額 from tableA where dt<=
|
|
15
|
+
, coalesce((select 累積購入金額 from tableA where dt<=d.dt order by dt desc limit 1), 0) as 累積購入金額
|
|
12
16
|
|
|
13
17
|
, '岩手' as 地域
|
|
14
18
|
|
|
@@ -16,7 +20,7 @@
|
|
|
16
20
|
|
|
17
21
|
generate_series('2018/01/01'::date,'2018/12/31'::date, '1 days') as d(dt)
|
|
18
22
|
|
|
19
|
-
left JOIN tableA t ON d.dt=
|
|
23
|
+
left JOIN tableA t ON d.dt=t.dt and t.地域='岩手'
|
|
20
24
|
|
|
21
25
|
order by d.dt
|
|
22
26
|
|
|
@@ -25,3 +29,33 @@
|
|
|
25
29
|
データが無い日から開始する場合、累積は0とすれば取れますけど、地域は無理ですね。
|
|
26
30
|
|
|
27
31
|
何らかの基準が無いと。
|
|
32
|
+
|
|
33
|
+
redshift版
|
|
34
|
+
|
|
35
|
+
--
|
|
36
|
+
|
|
37
|
+
```SQL
|
|
38
|
+
|
|
39
|
+
with
|
|
40
|
+
|
|
41
|
+
dt_table AS (
|
|
42
|
+
|
|
43
|
+
SELECT ('2018-01-01'::date + (ROW_NUMBER() OVER()) - 1)::date AS dt
|
|
44
|
+
|
|
45
|
+
FROM table -- 適当なテーブルを指定
|
|
46
|
+
|
|
47
|
+
limit 366
|
|
48
|
+
|
|
49
|
+
)
|
|
50
|
+
|
|
51
|
+
d.dt::date
|
|
52
|
+
|
|
53
|
+
, COALESCE(t.購入金額, 0) AS 購入金額
|
|
54
|
+
|
|
55
|
+
, coalesce((select 累積購入金額 from tableA where dt<=d.dt order by dt desc limit 1), 0) as 累積購入金額
|
|
56
|
+
|
|
57
|
+
, '岩手' as 地域
|
|
58
|
+
|
|
59
|
+
FROM dt_table d LEFT JOIN tableA t ON d.dt=t.dt and t.地域='岩手'
|
|
60
|
+
|
|
61
|
+
```
|
3
修正
test
CHANGED
|
@@ -2,13 +2,23 @@
|
|
|
2
2
|
|
|
3
3
|
```SQL
|
|
4
4
|
|
|
5
|
-
SELECT
|
|
5
|
+
SELECT
|
|
6
6
|
|
|
7
|
-
|
|
7
|
+
d.dt::date
|
|
8
8
|
|
|
9
|
-
|
|
9
|
+
, COALESCE(t.購入金額, 0) AS 購入金額
|
|
10
10
|
|
|
11
|
+
, coalesce((select 累積購入金額 from tableA where dt<= d.dt order by dt desc limit 1), 0) as 累積購入金額
|
|
12
|
+
|
|
13
|
+
, '岩手' as 地域
|
|
14
|
+
|
|
15
|
+
FROM
|
|
16
|
+
|
|
17
|
+
generate_series('2018/01/01'::date,'2018/12/31'::date, '1 days') as d(dt)
|
|
18
|
+
|
|
11
|
-
|
|
19
|
+
left JOIN tableA t ON d.dt= t.dt and t.地域='岩手'
|
|
20
|
+
|
|
21
|
+
order by d.dt
|
|
12
22
|
|
|
13
23
|
```
|
|
14
24
|
|
2
修正
test
CHANGED
|
@@ -4,7 +4,7 @@
|
|
|
4
4
|
|
|
5
5
|
SELECT d.dt::date, COALESCE(t.購入金額, 0) AS 購入金額
|
|
6
6
|
|
|
7
|
-
|
|
7
|
+
, coalesce((select 累積購入金額 from tableA where dt<= d.dt and 地域=t.地域 order by dt desc limit 1), 0) as 累積購入金額
|
|
8
8
|
|
|
9
9
|
FROM generate_series('2018/01/01'::date,'2018/12/31'::date, '1 days') as d(dt)
|
|
10
10
|
|
1
追記
test
CHANGED
|
@@ -4,8 +4,14 @@
|
|
|
4
4
|
|
|
5
5
|
SELECT d.dt::date, COALESCE(t.購入金額, 0) AS 購入金額
|
|
6
6
|
|
|
7
|
+
, coalesce((select 累積購入金額 from tableA where dt<= d.dt order by dt desc limit 1), 0) as 累積購入金額
|
|
8
|
+
|
|
7
9
|
FROM generate_series('2018/01/01'::date,'2018/12/31'::date, '1 days') as d(dt)
|
|
8
10
|
|
|
9
11
|
left JOIN tableA t ON d.dt= t.dt
|
|
10
12
|
|
|
11
13
|
```
|
|
14
|
+
|
|
15
|
+
データが無い日から開始する場合、累積は0とすれば取れますけど、地域は無理ですね。
|
|
16
|
+
|
|
17
|
+
何らかの基準が無いと。
|