-
一道SQL题的多种解法
今天我们来看一道SQL题目：
店铺 销售日期 销售额
A 2017-10-11 300
A 2017-10-12 200
B 2017-10-11 400
B 2017-10-12 200
A 2017-10-13 100
A 2017-10-15 100
C 2017-10-11 350
C 2017-10-15 400
C 2017-10-16 200
D 2017-10-13 500
E 2017-10-14 600
E 2017-10-15 500
D 2017-10-14 600
B 2017-10-13 300
C 2017-10-17 100
需求：求出连续三天有销售记录的店铺
思路一：
自然的想法，寻找每个店铺是否连续三天都有销售额。利用现有的表，构造一个中间表，中间表既有当前日期的销售额，又有当前日期后两天的销售额，然后筛选销售额大于0的店铺名称即可。这种思路可以有(至少)两种实现方式。
一是通过自连接来实现，join两次。连接的条件是店铺名称相同并且天数相差1天。这种方式无论是在MySQL中还是Hive中都适用。我们以mysql为例说明，写法如下：
注：左右滑动查看全部代码
mysql> create table sales (
name char(1),
day char(10),
amount int
);
mysql> insert into sales values('A', '2017-10-11', 300);
mysql> insert into sales values('A', '2017-10-12', 200);
mysql> insert into sales values('B', '2017-10-11', 400);
mysql> insert into sales values('B', '2017-10-12', 200);
mysql> insert into sales values('A', '2017-10-13', 100);
mysql> insert into sales values('A', '2017-10-15', 100);
mysql> insert into sales values('C', '2017-10-11', 350);
mysql> insert into sales values('C', '2017-10-15', 400);
mysql> insert into sales values('C', '2017-10-16', 200);
mysql> insert into sales values('D', '2017-10-13', 500);
mysql> insert into sales values('E', '2017-10-14', 600);
mysql> insert into sales values('E', '2017-10-15', 500);
mysql> insert into sales values('D', '2017-10-14', 600);
mysql> insert into sales values('B', '2017-10-13', 300);
mysql> insert into sales values('C', '2017-10-17', 100);
我们看一下我们想要构造的中间表，大概是长这个样子：
mysql> select *
-> from sales a
-> left join sales b
-> on a.name = b.name and
-> datediff(str_to_date(b.day, '%Y-%m-%d'), str_to_date(a.day, '%Y-%m-%d')) = 1
-> left join sales c
-> on b.name = c.name and
-> datediff(str_to_date(c.day, '%Y-%m-%d'), str_to_date(b.day, '%Y-%m-%d')) = 1;
+------+------------+--------+------+------------+--------+------+------------+--------+
| name | day | amount | name | day | amount | name | day | amount |
+------+------------+--------+------+------------+--------+------+------------+--------+
| A | 2017-10-11 | 300 | A | 2017-10-12 | 200 | A | 2017-10-13 | 100 |
| B | 2017-10-11 | 400 | B | 2017-10-12 | 200 | B | 2017-10-13 | 300 |
| C | 2017-10-15 | 400 | C | 2017-10-16 | 200 | C | 2017-10-17 | 100 |
| A | 2017-10-12 | 200 | A | 2017-10-13 | 100 | NULL | NULL | NULL |
| E | 2017-10-14 | 600 | E | 2017-10-15 | 500 | NULL | NULL | NULL |
| D | 2017-10-13 | 500 | D | 2017-10-14 | 600 | NULL | NULL | NULL |
| B | 2017-10-12 | 200 | B | 2017-10-13 | 300 | NULL | NULL | NULL |
| C | 2017-10-16 | 200 | C | 2017-10-17 | 100 | NULL | NULL | NULL |
| A | 2017-10-13 | 100 | NULL | NULL | NULL | NULL | NULL | NULL |
| A | 2017-10-15 | 100 | NULL | NULL | NULL | NULL | NULL | NULL |
| C | 2017-10-11 | 350 | NULL | NULL | NULL | NULL | NULL | NULL |
| E | 2017-10-15 | 500 | NULL | NULL | NULL | NULL | NULL | NULL |
| D | 2017-10-14 | 600 | NULL | NULL | NULL | NULL | NULL | NULL |
| B | 2017-10-13 | 300 | NULL | NULL | NULL | NULL | NULL | NULL |
| C | 2017-10-17 | 100 | NULL | NULL | NULL | NULL | NULL | NULL |
+------+------------+--------+------+------------+--------+------+------------+--------+
15 rows in set (0.00 sec)
可以看到需要借助str_to_date和datediff函数处理日期的差值，每一条记录相邻两个日期在天数上依次加一，不满足这样条件的为NULL值。我们在此基础上增加where条件过滤amount>0，并筛选出店铺名称即可，如下所示：
mysql> select a.name
-> from sales a
-> left join sales b
-> on a.name = b.name and
-> datediff(str_to_date(b.day, '%Y-%m-%d'), str_to_date(a.day, '%Y-%m-%d')) = 1
-> left join sales c
-> on b.name = c.name and
-> datediff(str_to_date(c.day, '%Y-%m-%d'), str_to_date(b.day, '%Y-%m-%d')) = 1
-> where a.amount > 0 and b.amount > 0 and c.amount > 0;
+------+
| name |
+------+
| A |
| B |
| C |
+------+
3 rows in set (0.01 sec)
接下来我们思考，同样的思路放在Hive中能不能实现呢？有没有什么差别呢？通过join的方式当然没有问题。但能够联想到，Hive中提供了窗口函数，其中有一个lead函数可以获得当前记录的下一条记录，我们如果按照日期升序排列，借用lead函数是不是也可以得到同样结构的中间表了呢？我们看下代码：
hive> create external table sales(name string, day string, amount int) row format delimited fields terminated by '\t';
OK
Time taken: 0.043 seconds
hive> load data local inpath 'sales.txt' into table sales;
Loading data to table learn.sales
Table learn.sales stats: [numFiles=1, totalSize=255]
OK
Time taken: 0.152 seconds
hive> select * from sales;
OK
A 2017-10-11 300
A 2017-10-12 200
B 2017-10-11 400
B 2017-10-12 200
A 2017-10-13 100
A 2017-10-15 100
C 2017-10-11 350
C 2017-10-15 400
C 2017-10-16 200
D 2017-10-13 500
E 2017-10-14 600
E 2017-10-15 500
D 2017-10-14 600
B 2017-10-13 300
C 2017-10-17 100
Time taken: 0.042 seconds, Fetched: 15 row(s)
使用lead函数构造中间表的代码如下：
hive> select *
> from
> (
> select a.name, a.day day, a.amount,
> lead(a.name, 1, null) over (partition by a.name order by day) as name2,
> lead(a.day, 1, null) over (partition by a.name order by day) as day2,
> lead(a.amount, 1, null) over (partition by a.name order by day) as amount2,
> lead(a.name, 2, null) over (partition by a.name order by day) as name3,
> lead(a.day, 2, null) over (partition by a.name order by day) as day3,
> lead(a.amount, 2, null) over (partition by a.name order by day) as amount3
> from
> (
> select * from sales order by name, day
> ) a
> ) b ;
OK
A 2017-10-11 300 A 2017-10-12 200 A 2017-10-13 100
A 2017-10-12 200 A 2017-10-13 100 A 2017-10-15 100
A 2017-10-13 100 A 2017-10-15 100 NULL NULL NULL
A 2017-10-15 100 NULL NULL NULL NULL NULL NULL
B 2017-10-11 400 B 2017-10-12 200 B 2017-10-13 300
B 2017-10-12 200 B 2017-10-13 300 NULL NULL NULL
B 2017-10-13 300 NULL NULL NULL NULL NULL NULL
C 2017-10-11 350 C 2017-10-15 400 C 2017-10-16 200
C 2017-10-15 400 C 2017-10-16 200 C 2017-10-17 100
C 2017-10-16 200 C 2017-10-17 100 NULL NULL NULL
C 2017-10-17 100 NULL NULL NULL NULL NULL NULL
D 2017-10-13 500 D 2017-10-14 600 NULL NULL NULL
D 2017-10-14 600 NULL NULL NULL NULL NULL NULL
E 2017-10-14 600 E 2017-10-15 500 NULL NULL NULL
E 2017-10-15 500 NULL NULL NULL NULL NULL NULL
Time taken: 18.652 seconds, Fetched: 15 row(s)
这样的结果和刚刚类似，但按照店铺名称排了序。同样我们需要处理日期的差值，然后使用where条件过滤amount>0的记录，并筛选出店铺名称即可，这里使用了datediff函数和to_date函数。
hive> select b.name
> from
> (
> select a.name, a.day day, a.amount,
> lead(a.name, 1, null) over (partition by a.name order by day) as name2,
> lead(a.day, 1, null) over (partition by a.name order by day) as day2,
> lead(a.amount, 1, null) over (partition by a.name order by day) as amount2,
> lead(a.name, 2, null) over (partition by a.name order by day) as name3,
> lead(a.day, 2, null) over (partition by a.name order by day) as day3,
> lead(a.amount, 2, null) over (partition by a.name order by day) as amount3
> from
> (
> select * from sales order by name, day
> ) a
> ) b
> where b.amount > 0 and b.amount2 > 0 and b.amount3 > 0
> and datediff(to_date(b.day3), to_date(b.day2)) = 1 and datediff(to_date(b.day2), to_date(b.day)) = 1;
OK
A
B
C
Time taken: 17.858 seconds, Fetched: 3 row(s)
思路二：
上面的思路虽然比较自然，但稍微多想一下，如果连续的日期不是3天，是7天，15天呢，是不是就要多写好多join，多写好多lead了呢，一方面join的效率是个问题，而且代码上会比较繁琐。所以有没有更好的思路呢？答案是肯定的，这种思路有点寻找规律的意思，要对每个店铺的销售记录按天进行组内排序，并求序号和销售“日”的和，和的值是有规律的，但不需要用到join。请看在hive中执行的代码(省略了MapReduce的日志)：
hive> select *, substr(day, 9, 2), row_number() over (partition by name order by day desc),
> cast(substr(day, 9, 2) as int) + row_number() over (partition by name order by day desc) as plus
> from sales;
OK
A 2017-10-15 100 15 1 16
A 2017-10-13 100 13 2 15
A 2017-10-12 200 12 3 15
A 2017-10-11 300 11 4 15
B 2017-10-13 300 13 1 14
B 2017-10-12 200 12 2 14
B 2017-10-11 400 11 3 14
C 2017-10-17 100 17 1 18
C 2017-10-16 200 16 2 18
C 2017-10-15 400 15 3 18
C 2017-10-11 350 11 4 15
D 2017-10-14 600 14 1 15
D 2017-10-13 500 13 2 15
E 2017-10-15 500 15 1 16
E 2017-10-14 600 14 2 16
上面的结果中，倒数第三列是“年月日”的“日”，倒数第二列是对于每一个店铺内部，按照日期降序排列的序号，最后一列是二者的和。可以观察到，店铺有连续销售日期的记录，这个“和”列是一致的，且有连续几天，同样的和就会出现几次。销售日期如果不连续，则和的值也不一样。这样如果是连续3天，我们只需要筛选出这样的“和”出现3次的，同时选出店铺名称即可，7天就是7次，以此类推。代码如下(省略了MapReduce的日志)：
hive> select b.name
> from
> (
> select a.name, plus, count(*)
> from
> (
> select *, substr(day, 9, 2), row_number() over (partition by name order by day desc),
> cast(substr(day, 9, 2) as int) + row_number() over (partition by name order by day desc) as plus
> from sales
> ) a
> group by
> a.name, plus
> having count(*) >=3
> ) b;
OK
A
B
C
可以看到，也实现了我们想要的结果。
总结
我们用两种思路，三种方法，实现了“求连续三天有销售记录的店铺”，其中前两种方法容易理解，但第三种方法可能不太容易想到，但容易扩展，希望对大家有所启示。需要注意的是，我们只是使用了自己构造的数据，没有在专业的OJ上测，所以可能也并不是最优的解法。如果你有更好的解决思路，欢迎关注我的微信公众号一起交流~
最新更新
nodejs爬虫
Python正则表达式完全指南
爬取豆瓣Top250图书数据
shp 地图文件批量添加字段
爬虫小试牛刀（爬取学校通知公告）
【python基础】函数-初识函数
【python基础】函数-返回值
HTTP请求：requests模块基础使用必知必会
Python初学者友好丨详解参数传递类型
如何有效管理爬虫流量？
SQL SERVER中递归
2个场景实例讲解GaussDB(DWS)基表统计信息估
常用的 SQL Server 关键字及其含义
动手分析SQL Server中的事务中使用的锁
openGauss内核分析：SQL by pass & 经典执行
一招教你如何高效批量导入与更新数据
天天写SQL，这些神奇的特性你知道吗？
openGauss内核分析：执行计划生成
[IM002]Navicat ODBC驱动器管理器 未发现数据
初入Sql Server 之 存储过程的简单使用
这是目前我见过最好的跨域解决方案！
减少回流与重绘
减少回流与重绘
如何使用KrpanoToolJS在浏览器切图
performance.now() 与 Date.now() 对比
一款纯 JS 实现的轻量化图片编辑器
关于开发 VS Code 插件遇到的 workbench.scm.
前端设计模式——观察者模式
前端设计模式——中介者模式
创建型-原型模式