SQL笛卡尔积结合前后行数据的统计案例

SQL前后3天统计分析

问题描述

为什么这段mysql语句能够实现如下功能？

 某市建了一个新的体育馆，每日人流量信息被记录在​​gymnasium​​​表中：序号 (​​id​​​)、日期 (​​date​​​)、 人流量 (​​visitors_flow​​)。

请编写一个查询语句，找出人流量处于高峰的记录 id、日期 date 和人流量 visitors_flow，其中高峰定义为前后连续三天人流量均不少于 100。

​​gymnasium​​表结构数据如下：

 提示：每天只有一行记录，日期随着 ​​id​​ 的增加而增加。

如上文所示，我的疑问在于既然a,b,c都是一个表的同一个别名，那么where a.visitors_flow>=100 and b.visitors_flow>=100 and c.visitors_flow>=100不就重复了吗？同时，a.id与b.id与c.id不是都相同吗？刚入门，感谢赐教。

 解决方法

--#1 需求描述：前后连续三天人流量均不少于 100。
/*
SQL转换： 找出表里流量大于100的记录且每条(天)的前后都有数据，即都能关联上。
思路演化： 需要通过笛卡尔积生成3张“表”(每张表的visitors_flow>=100),前后连续3天通过id（id是连续的）关联。
*/
--#2 表与数据
CREATE TABLE gymnasium
(
id int,
date DATE,
visitors_flow SMALLINT
)

TRUNCATE TABLE gymnasium;
INSERT INTO gymnasium VALUES(1,'2019-01-01',58);
INSERT INTO gymnasium VALUES(2,'2019-01-02',110);
INSERT INTO gymnasium VALUES(3,'2019-01-03',123);
INSERT INTO gymnasium VALUES(4,'2019-01-04',67);
INSERT INTO gymnasium VALUES(5,'2019-01-05',168);
INSERT INTO gymnasium VALUES(6,'2019-01-06',1352);
INSERT INTO gymnasium VALUES(7,'2019-01-07',382);
INSERT INTO gymnasium VALUES(8,'2019-01-08',326);
INSERT INTO gymnasium VALUES(9,'2019-01-09',99);
INSERT INTO gymnasium VALUES(10,'2019-01-10',73);
INSERT INTO gymnasium VALUES(11,'2019-01-11',65);
INSERT INTO gymnasium VALUES(12,'2019-01-12',123);
INSERT INTO gymnasium VALUES(13,'2019-01-13',28);
--#3 当前SQL解释：
/*
从同一个表“visitors_flow>=100”里取得结果集确实是相同的，加上条件“a.id = b.id-1 and b.id= c.id -1”时筛选了前后连续3天。
注：a.visitors_flow>=100需要在筛选连续3天时加上，而不是在外部。外部的要求更严格，内部的仅要求局部连续3天。
外部的要求更严格(适合连续前后3天访问量大于100)，内部的仅要求局部连续3天。
*/

--#4 正确写法：
# Way1
select  DISTINCT a.*
from gymnasium a,gymnasium as b,gymnasium as c
where 
-- (a.visitors_flow>=100 AND b.visitors_flow >=100 AND c.visitors_flow >=100) 
-- and ( 
(a.id = b.id-1 and b.id= c.id -1 AND a.visitors_flow>=100) or 
(a.id = b.id-1 and a.id=c.id -1 AND a.visitors_flow >=100 ) or 
(a.id = b.id+1 and b.id= c.id AND a.visitors_flow >=100) 
  order by a.id; 
-- 注：该结果出来的日期间隔可能超过3。

 --#Way2 通过相邻函数lag(前N行)，MYSQL8、SQL Server、Oracle都支持。
 SELECT pre_id1,pre_date1,pre_1 as visitors_flow FROM
(
SELECT *,
lag(visitors_flow,1)over(order by id) pre_1,
lag(date,1)over(order by id) pre_date1,
lag(id,1)over(order by id) pre_id1,
lag(visitors_flow,2)over(order by id) pre_2
FROM gymnasium
)A
WHERE pre_1>=100

-- 注：这里取的是有前和后的，第一行和最后一行未包含。
--#5 结果

执行结果