1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
|
create table cm.tb_sale_amount(
good_category int comment '商品类型ID',
sale_date date comment '销售日期',
amount int comment '销售额',
primary key(good_category, sale_date)
);
truncate cm.tb_sale_amount;
insert into cm.tb_sale_amount
(good_category, sale_date, amount) values
(1003, '2022-01-10', 264),
(1001, '2022-06-01', 21),
(1005, '2022-06-01', 73),
(1002, '2022-06-27', 44),
(1006, '2022-06-27', 405),
(1003, '2022-09-10', 16),
(1005, '2022-09-13', 72),
(1004, '2022-10-01', 29),
(1005, '2022-10-03', 332),
(1001, '2022-10-29', 10),
(1006, '2022-10-29', 137),
(1002, '2022-12-02', 23),
(1007, '2022-12-02', 19),
(1003, '2022-12-02', 30),
(1008, '2022-12-03', 3),
(1009, '2022-12-04', 1),
(1010, '2022-12-05', 9),
(1003, '2022-12-30', 121);
select
good_category,
sum(amount) as good_amount
from cm.tb_sale_amount
group by good_category
order by good_amount desc;
select
*,
sum(good_amount) over () as all_amount
from
(select
good_category,
sum(amount) as good_amount
from cm.tb_sale_amount
group by good_category
order by good_amount desc
) t1;
select
*,
good_amount * 1.0 / all_amount as ratio
from (select
*,
sum(good_amount) over () as all_amount
from (select
good_category,
sum(amount) as good_amount
from cm.tb_sale_amount
group by good_category
order by good_amount desc
) as t1
) t2;
select
*,
sum(ratio) over (order by ratio desc) as acc_ratio
from(
select
*,
good_amount * 1.0 / all_amount as ratio
from (select
*,
sum(good_amount) over () as all_amount
from (select
good_category,
sum(amount) as good_amount
from cm.tb_sale_amount
group by good_category
order by good_amount desc
) as t1
) t2
) t3;
select
*,
lag(acc_ratio) over() as pre_acc_ratio
from(select
*,
sum(ratio) over (order by ratio desc) as acc_ratio
from(
select
*,
good_amount * 1.0 / all_amount as ratio
from (select
*,
sum(good_amount) over () as all_amount
from (select
good_category,
sum(amount) as good_amount
from cm.tb_sale_amount
group by good_category
order by good_amount desc
) t1
) t2
) t3
) t4;
select *
from (
select
*,
lag(acc_ratio) over() as pre_acc_ratio
from(select
*,
sum(ratio) over (order by ratio desc) as acc_ratio
from(
select
*,
good_amount * 1.0 / all_amount as ratio
from (select
*,
sum(good_amount) over () as all_amount
from (select
good_category,
sum(amount) as good_amount
from cm.tb_sale_amount
group by good_category
order by good_amount desc
) t1
) t2
) t3
) t4
) t5
where pre_acc_ratio IS NULL or pre_acc_ratio < 0.90;
|