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

一、配景说明

在运营贸易务中，常常有各种各样的业务分类统计，出各范例使命的业务报表数据，比如，“统计下9月份各地市在各网格上任我选产品订购数量的分组排序状态”。
二、问题描述

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

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

1. create table order_record (
2.     user_id int,
3.     city_code varchar(32),
4.     wg_code varchar(32),
5.     order_date date,
6.     product varchar(32),
7.     primary key (user_id)
8. );

复制代码

四、表数据插入

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

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

复制代码

五、sql代码解释

1. SELECT subq.group_id, subq.second_max_value_user_id
2. FROM (
3.     SELECT
4.         a.city_code,
5.         a.wg_code,
6.         a.value
7.         LAG(value, 1, 0) OVER (PARTITION BY city_code,wg_code ORDER BY value DESC) AS second_max_value,
8.         FIRST_VALUE(wg_code) OVER (PARTITION BY city_code,wg_code ORDER BY value DESC) AS second_max_value_user_id
9.     FROM
10. (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
11. )a
12. ) subq
13. WHERE subq.value = subq.second_max_value;

复制代码

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

SELECT 子句:

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

复制代码

FROM 子句:

1. 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 子句:

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

复制代码

ORDER BY 子句:

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

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。