以梧桐数据库为例分析分组排序并取每组第二大数值对应的用户的SQL实现

一、背景说明

在运营商业务中，经常有各种各样的业务分类统计，出各类型任务的业务报表数据，比如，“统计下9月份各地市在各网格上任我选产品订购数量的分组排序状况”。

二、问题描述

现在有一个业务场景，要求计算8月份各地市在各网格上任我选产品订购数量的分组排序状况，并输出排第二位的各地市网格名称。 本次以梧桐数据库为例进行SQL实现及思路讲解。

三、表结构说明

梧桐数据库产品订购记录表建表语句

create table order_record (
    user_id int,
    city_code varchar(32),
    wg_code varchar(32),
    order_date date,
    product varchar(32),
    primary key (user_id)
);

四、表数据插入

通过insert语句向梧桐数据库插入样例数据

insert into order_record values(1, '1134', '113403', '2024-09-01','1134023');
insert into order_record values(2, '2102', '210205', '2024-09-01','1134033');
insert into order_record values(3, '1134', '113403', '2024-09-01','1134043');
insert into order_record values(4, '1135', '113504', '2024-09-01','1134023');
insert into order_record values(5, '1130', '113001', '2024-09-01','1134023');
insert into order_record values(6, '2314', '231402', '2024-01-01','1134043');
insert into order_record values(7, '2208', '220801', '2024-09-02','1134043');
insert into order_record values(8, '2102', '210202', '2023-01-02','1134023');
insert into order_record values(9, '2102', '210203', '2023-01-02','1134043');
insert into order_record values(10, '1130', '113001', '2023-01-02','1134043');
insert into order_record values(11, '1130', '113001', '2024-01-02','1134033');
insert into order_record values(12, '1135', '113504', '2023-01-02','1134023');
insert into order_record values(13, '2208', '220802', '2023-01-03','1134023');

五、sql代码解释

SELECT subq.group_id, subq.second_max_value_user_id
FROM (
    SELECT 
        a.city_code, 
        a.wg_code,
        a.value
        LAG(value, 1, 0) OVER (PARTITION BY city_code,wg_code ORDER BY value DESC) AS second_max_value,
        FIRST_VALUE(wg_code) OVER (PARTITION BY city_code,wg_code ORDER BY value DESC) AS second_max_value_user_id
    FROM 
(select city_code,wg_code,count(distinct user_id) as value from order_record where order_date >='20240901' and order_date <='20240930' and product='1134043' group by city_code,wg_code
)a
) subq
WHERE subq.value = subq.second_max_value;

六、解释sql每个部分的功能

SELECT 子句:

 a.city_code, 
        a.wg_code,
        a.value
        LAG(value, 1, 0) OVER (PARTITION BY city_code,wg_code ORDER BY value DESC) AS second_max_value,
        FIRST_VALUE(wg_code) OVER (PARTITION BY city_code,wg_code ORDER BY value DESC) AS second_max_value_user_id 

FROM 子句:

select city_code,wg_code,count(distinct user_id) as value from order_record where order_date >='20240901' and order_date <='20240930' and product='1134043' group by city_code,wg_code

PARTITION BY 子句:

city_code,wg_code: 按照城市编码以及网格编码分组。

ORDER BY 子句:

city_code,wg_code: 按照城市编码以及网格编码排序。