MySQL查询每个店铺销售额前三的商品 |
您所在的位置:网站首页 › 排名前三的购物平台 › MySQL查询每个店铺销售额前三的商品 |
1.前言 最近面试数据分析岗,两家公司都问到了这个题目,一个是用SQL查询每家店铺销售额前三的商品,一个是用Python统计每家店铺销售额前三的商品;而且在leetcode的数据库题库中,“部门工资前三高的所有员工”属于同样的类型,在所有题目中出现频率排名第一,今天先进行SQL解题方法的复盘总结。
2. 题目 sales表包含所有的订单信息,每个订单有对应的订单id orderid,店铺id shopid,商品id goodid,销售数量 salenum,销售单价 price,下单日期 orderdate; shop表包含店铺信息,店铺id shopid,店铺名称 shopname;
goods表包含商品信息,商品id goodsid,商品名称 goodsname; 1)基础版题目:编写一个SQL查询,找出每个店铺在2020年Q1销售额(销售数据*销售数量)排名前三的商品。例如,根据上述给定的表,查询结果应返回: 2)进阶版题目:编写一个SQL查询,找出每个店铺在距今三个月内销售额(销售数据*销售数量)排名前三的商品,分列展示。例如,根据上述给定的表,查询结果应返回:
附建表语句和插入数据语句: # 创建表sales并插入数据 CREATE TABLE `sales` ( `orderid` int NOT NULL AUTO_INCREMENT, `shopid` int NOT NULL, `goodsid` int NOT NULL, `salenum` int NOT NULL, `price` int NOT NULL, `orderdate` date NOT NULL, PRIMARY KEY(`orderid`) ); INSERT INTO `sales` (`shopid`, `goodsid`, `salenum`, `price`, `orderdate`) VALUES (1, 10001, 1, 90, '2020-01-15'), (1, 10002, 1, 50, '2020-02-23'), (2, 10004, 2, 120, '2020-01-18'), (1, 10003, 3, 60, '2020-01-19'), (2, 10002, 1, 50, '2020-02-23'), (1, 10002, 1, 40, '2020-03-01'), (1, 10004, 3, 20, '2020-02-14'), (1, 10003, 1, 10, '2020-03-01'), (2, 10002, 1, 50, '2020-02-02'), (2, 10001, 1, 40, '2020-02-09'); # 创建表shop并插入数据 CREATE TABLE `shop` ( `shopid` int NOT NULL, `shopname` varchar(10) NOT NULL ); INSERT INTO `shop` VALUES (1, 'SexyBaby'), (2, 'AngelCity'); # 创建表goods并插入数据 CREATE TABLE `goods` ( `goodsid` int NOT NULL, `goodsname` varchar(10) NOT NULL ); INSERT INTO `goods` VALUES (10001, 'dress'), (10002, 'shirt'), (10003, 'coat'), (10004, 'blouse');
3.使用窗口函数解题 注:MySQL从8.0版本开始支持窗口函数。 既然要分组统计每个店铺、每个商品的数据,先回忆一下具有分组统计功能的group by 和 partition by的区别:group by具有汇总的功能,只保留参与分组的字段和聚合函数的结果; 而partition by 能够保留全部数据,只对其中某些字段做分组统计,常与排序函数连用(注意将聚合函数用在partition后的结果集上时,聚合函数是逐条累积计算值的,具体可参考博客:https://www.cnblogs.com/hello-yz/p/9962356.html)。 基础版题目解题思路: 1.使用where筛选2020年 Q1的订单数据; 2.因为一个店铺中的同一个商品可能会存在多条订单记录,所以使用groupby聚合得到每个店铺中每个商品的销售额sumprice; 3.通过使用row_number() over (partition by ……),对每个店铺内的商品销售额进行降序排序,得到每个店铺内商品的销售额排名sumprice_rank; 4.将查询的结果与shop表和goods表join,得到shopname和goodsname,再在外层使用where sumprice_rank '2020-01-01' AND orderdate '2020-01-01' and orderdate |
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |
CopyRight 2018-2019 实验室设备网 版权所有 win10的实时保护怎么永久关闭 |