求占据前90%销售额的商品类型

1
-- 求占据前90%销售额的商品类型
2
-- step0. 准备数据
3
create table cm.tb_sale_amount(
4
    good_category int comment '商品类型ID',
5
    sale_date date comment '销售日期',
6
    amount int comment '销售额',
7
    primary key(good_category, sale_date)
8
);
9

10

11
truncate cm.tb_sale_amount;
12

13
insert into cm.tb_sale_amount
14
(good_category, sale_date, amount) values
15
(1003, '2022-01-10', 264),
16
(1001, '2022-06-01', 21),
17
(1005, '2022-06-01', 73),
18
(1002, '2022-06-27', 44),
19
(1006, '2022-06-27', 405),
20
(1003, '2022-09-10', 16),
21
(1005, '2022-09-13', 72),
22
(1004, '2022-10-01', 29),
23
(1005, '2022-10-03', 332),
24
(1001, '2022-10-29', 10),
25
(1006, '2022-10-29', 137),
26
(1002, '2022-12-02', 23),
27
(1007, '2022-12-02', 19),
28
(1003, '2022-12-02', 30),
29
(1008, '2022-12-03', 3),
30
(1009, '2022-12-04', 1),
31
(1010, '2022-12-05', 9),
32
(1003, '2022-12-30', 121);
33

34
-- step1. 计算每种商品的总销售额，并降序排序
35
select
36
    good_category,
37
    sum(amount) as good_amount
38
from cm.tb_sale_amount
39
group by good_category
40
order by good_amount desc;
41

42
-- step2. 求全部商品的总销售额，为了step3求各种商品的占比，需要先求和。注意：求总和时，窗口值既不排序也不进行分组
43
select
44
    *,
45
    sum(good_amount) over () as all_amount
46
from
47
    (select
48
        good_category,
49
        sum(amount) as good_amount
50
    from cm.tb_sale_amount
51
    group by good_category
52
    order by good_amount desc
53
    ) t1;
54

55
-- step3. 求占比
56
select
57
    *,
58
    good_amount * 1.0 / all_amount as ratio
59
from (select
60
          *,
61
          sum(good_amount) over () as all_amount
62
      from (select
63
                good_category,
64
                sum(amount) as good_amount
65
            from cm.tb_sale_amount
66
            group by good_category
67
            order by good_amount desc
68
            ) as t1
69
      ) t2;
70

71
-- step4. 求累计占比，注意：求累计值时，一定要进行排序
72
select
73
    *,
74
    sum(ratio) over (order by ratio desc) as acc_ratio
75
from(
76
    select
77
        *,
78
        good_amount * 1.0 / all_amount as ratio
79
    from (select
80
              *,
81
              sum(good_amount) over () as all_amount
82
            from (select
83
                    good_category,
84
                    sum(amount) as good_amount
85
                from cm.tb_sale_amount
86
                group by good_category
87
                order by good_amount desc
88
            ) as t1
89
        ) t2
90
    ) t3;
91

92
-- step5. 求前一行的累计占比
93
select
94
    *,
95
    lag(acc_ratio) over() as pre_acc_ratio
96
from(select
97
         *,
98
       sum(ratio) over (order by ratio desc) as acc_ratio
99
    from(
100
        select
101
            *,
102
            good_amount * 1.0 / all_amount as ratio
103
        from (select
104
                  *,
105
                  sum(good_amount) over () as all_amount
106
              from (select
107
                        good_category,
108
                        sum(amount) as good_amount
109
                    from cm.tb_sale_amount
110
                    group by good_category
111
                    order by good_amount desc
112
                ) t1
113
            ) t2
114
        ) t3
115
    ) t4;
116

117
-- step6. 过滤
118
select *
119
from (
120
    select
121
    *,
122
    lag(acc_ratio) over() as pre_acc_ratio
123
from(select
124
         *,
125
       sum(ratio) over (order by ratio desc) as acc_ratio
126
     from(
127
        select
128
            *,
129
            good_amount * 1.0 / all_amount as ratio
130
        from (select
131
                  *,
132
                  sum(good_amount) over () as all_amount
133
              from (select
134
                        good_category,
135
                        sum(amount) as good_amount
136
                    from cm.tb_sale_amount
137
                    group by good_category
138
                    order by good_amount desc
139
                )  t1
140
              ) t2
141
        ) t3
142
    ) t4
143
) t5
144
where pre_acc_ratio IS NULL or pre_acc_ratio < 0.90;