ToB企服应用市场:ToB评测及商务社交产业平台

标题: 以梧桐数据库为例分析分组排序并取每组第二大数值对应的用户的SQL实现 [打印本页]
作者: 忿忿的泥巴坨    时间: 2024-11-8 06:54
标题: 以梧桐数据库为例分析分组排序并取每组第二大数值对应的用户的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企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4