回答編集履歴
3
調整
test
CHANGED
|
@@ -30,11 +30,22 @@
|
|
|
30
30
|
sum(price) over(order by processing_date) AS subprice
|
|
31
31
|
FROM d_test
|
|
32
32
|
```
|
|
33
|
-
月ごとに集計
|
|
33
|
+
月ごとに集計もろもろ
|
|
34
|
+
```sql
|
|
35
|
+
INSERT INTO d_test (processing_date, price) VALUES
|
|
36
|
+
('2025-01-01', 100),
|
|
37
|
+
('2025-01-03', 300),
|
|
38
|
+
('2025-01-05', 300),
|
|
39
|
+
('2025-01-09', 450),
|
|
40
|
+
('2025-02-01', 400),
|
|
41
|
+
('2025-02-03', 200);
|
|
42
|
+
```
|
|
43
|
+
として
|
|
34
44
|
```sql
|
|
35
45
|
SELECT processing_date,
|
|
36
46
|
price,
|
|
37
|
-
sum(price) over(partition by LAST_DAY(processing_date) order by processing_date) AS subprice
|
|
47
|
+
sum(price) over(partition by LAST_DAY(processing_date) order by processing_date) AS sub_price,
|
|
48
|
+
sum(price) over(partition by LAST_DAY(processing_date)) AS month_total_price,
|
|
49
|
+
sum(price) over() AS total_price
|
|
38
50
|
FROM d_test
|
|
39
51
|
```
|
|
40
|
-
|
2
調整
test
CHANGED
|
@@ -30,4 +30,11 @@
|
|
|
30
30
|
sum(price) over(order by processing_date) AS subprice
|
|
31
31
|
FROM d_test
|
|
32
32
|
```
|
|
33
|
+
月ごとに集計
|
|
34
|
+
```sql
|
|
35
|
+
SELECT processing_date,
|
|
36
|
+
price,
|
|
37
|
+
sum(price) over(partition by LAST_DAY(processing_date) order by processing_date) AS subprice
|
|
38
|
+
FROM d_test
|
|
39
|
+
```
|
|
33
40
|
|
1
調整
test
CHANGED
|
@@ -21,3 +21,13 @@
|
|
|
21
21
|
order by processing_date
|
|
22
22
|
```
|
|
23
23
|
ただし同じ日付があるときはどうしたいかによります
|
|
24
|
+
|
|
25
|
+
# window関数
|
|
26
|
+
window関数がつかえるならもっと楽です
|
|
27
|
+
```sql
|
|
28
|
+
SELECT processing_date,
|
|
29
|
+
price,
|
|
30
|
+
sum(price) over(order by processing_date) AS subprice
|
|
31
|
+
FROM d_test
|
|
32
|
+
```
|
|
33
|
+
|