以梧桐数据库为例分析分组排序并取每组第二大数值对应的用户的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.valueLAG(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_idFROM
(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.valueLAG(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: 按照城市编码以及网格编码排序。