回答編集履歴
2
調整
test
CHANGED
|
@@ -48,7 +48,7 @@
|
|
|
48
48
|
sum(sum(price*(type=1))) over w1 as p2,
|
|
49
49
|
sum(sum(price*(type=2))) over w1 as p3,
|
|
50
50
|
sum(sum(honsu)) over w1 as h,
|
|
51
|
-
sum(
|
|
51
|
+
sum((sum(honsu)>=2)*2000) over w1 as h2
|
|
52
52
|
from orders
|
|
53
53
|
where dat between '2023-02-1' and '2023-02-28'
|
|
54
54
|
group by dat,user_no
|
1
調整
test
CHANGED
|
@@ -40,3 +40,19 @@
|
|
|
40
40
|
using(user_no)
|
|
41
41
|
```
|
|
42
42
|
|
|
43
|
+
# 調整
|
|
44
|
+
集計を２段階にすればいけました
|
|
45
|
+
```SQL
|
|
46
|
+
select distinct user_no,
|
|
47
|
+
sum(sum(price)) over w1 as p1,
|
|
48
|
+
sum(sum(price*(type=1))) over w1 as p2,
|
|
49
|
+
sum(sum(price*(type=2))) over w1 as p3,
|
|
50
|
+
sum(sum(honsu)) over w1 as h,
|
|
51
|
+
sum(case when sum(honsu)>=2 then 2000 else 0 end ) over w1 as h2
|
|
52
|
+
from orders
|
|
53
|
+
where dat between '2023-02-1' and '2023-02-28'
|
|
54
|
+
group by dat,user_no
|
|
55
|
+
window w1 as (partition by user_no)
|
|
56
|
+
order by user_no
|
|
57
|
+
```
|
|
58
|
+
|