SQL的窗口函数

排序类窗口函数

select *,
   rank() over (partition by 班级
                 order by 成绩 desc) as ranking
from 班级表

select *,
   rank() over (order by 成绩 desc) as ranking,# 12225
   dense_rank() over (order by 成绩 desc) as dese_rank,# 12223
   row_number() over (order by 成绩 desc) as row_num # 12345
from 班级表

聚合类窗口函数

SUM、AVG、COUNT、MAX、MIN等

select sid,sum(score) over(partition by sid  order by score) current_sum
from sc

# 加order by会进行累行计算

偏移类窗口函数

偏移函数
lag(字段名，行数) 取出当前行前面第n行数据
lead (字段名，行数) 取出当前行后面第n行数据

# 创建一张销售记录表
CREATE TABLE sales_record  (
  shopid varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  dt date NULL DEFAULT NULL,
  sale int(0) NULL DEFAULT NULL
);

# 插入数据
INSERT INTO sales_record VALUES ('A', '2017-10-11', 300);
INSERT INTO sales_record VALUES ('A', '2017-10-12', 200);
INSERT INTO sales_record VALUES ('A', '2017-10-13', 100);
INSERT INTO sales_record VALUES ('A', '2017-10-15', 100);
INSERT INTO sales_record VALUES ('A', '2017-10-16', 300);
INSERT INTO sales_record VALUES ('A', '2017-10-17', 150);
INSERT INTO sales_record VALUES ('A', '2017-10-18', 340);
INSERT INTO sales_record VALUES ('A', '2017-10-19', 360);
INSERT INTO sales_record VALUES ('B', '2017-10-11', 400);
INSERT INTO sales_record VALUES ('B', '2017-10-12', 200);
INSERT INTO sales_record VALUES ('B', '2017-10-15', 600);
INSERT INTO sales_record VALUES ('C', '2017-10-11', 350);
INSERT INTO sales_record VALUES ('C', '2017-10-13', 250);
INSERT INTO sales_record VALUES ('C', '2017-10-14', 300);
INSERT INTO sales_record VALUES ('C', '2017-10-15', 400);
INSERT INTO sales_record VALUES ('C', '2017-10-16', 200);
INSERT INTO sales_record VALUES ('D', '2017-10-13', 500);
INSERT INTO sales_record VALUES ('E', '2017-10-14', 600);
INSERT INTO sales_record VALUES ('E', '2017-10-15', 500);
INSERT INTO sales_record VALUES ('D', '2017-10-14', 600);

问题：请求出 table sales_record 表中连续三天有销售记录的店铺

select 
  distinct 
  shopid
from (select 
        shopid,
        t.dt,
        t.第三次有销售记录的日期,
        datediff(t.第三次有销售记录的日期,dt)+1 as date_diff
      from (select 
            shopid,
            dt,
            lead(dt,2) over(partition by shopid order by dt asc) as 第三次有销售记录的日期
            from sales_record
           ) t 
     ) t1
where t1.date_diff=3