经典SQL面试题查询比前一天温度高的日期

查询比前一天温度高的日期
面试常见到的SQL面试题， 查询比前一天气温高的日期是哪天
这里面用到一个开窗函数 lead（）取上一条数据，然后进行对比
建表语句

drop table tp;

 Create table tp (

id int comment 'id',

`day1`  DATETIME   comment '日期',

`temperature`  double comment '温度'

);

插入数据

INSERT OVERWRITE TABLE  tp VALUES

(1,DATETIME'2020-01-01 00:00:00',25),(4,DATETIME'2020-01-02 00:00:00',25),

(2,DATETIME'2020-01-03 00:00:00',27),(5,DATETIME'2020-01-04 00:00:00',25),

(3,DATETIME'2020-01-05 00:00:00',26),(6,DATETIME'2020-01-06 00:00:00',27);

查询逻辑

SELECT CASE WHEN lead_temperature-temperature >0 THEN lead_day1
END
FROM (
    SELECT day1

    ,temperature

    ,lead(temperature,1,temperature)OVER(ORDER BY day1 ) AS lead_temperature

    ,lead(day1,1,day1)OVER(ORDER BY day1 ) AS lead_day1

    FROM tp
) t1
;

分析
①先取到当前数据与上一条数据

SELECT day1

    ,temperature

    ,lead(temperature,1,temperature)OVER(ORDER BY day1 ) AS lead_temperature

    ,lead(day1,1,day1)OVER(ORDER BY day1 ) AS lead_day1

    FROM tp

②在做判断进行计算

SELECT CASE WHEN lead_temperature-temperature >0 THEN lead_day1
END
FROM (
    SELECT day1

    ,temperature

    ,lead(temperature,1,temperature)OVER(ORDER BY day1 ) AS lead_temperature

    ,lead(day1,1,day1)OVER(ORDER BY day1 ) AS lead_day1

    FROM tp
) t1