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