用窗口函数比较相邻两行

记录某次工作中摸索出来的骚操作，不确定有无更好的“正统方法”。

数据

假设有以下样式的数据，其中“行号”不是数据内容，只是为了本文索引方便。
表名：content

目标是分日期、城市维度，时间升序，统计状态由 A 到 B 且数值降低的记录，分自然小时出现的次数分布。
表中只有第 3、8 行符合这一要求，则分小时的次数统计应该是如下结果：

思路

我想到两种思路实现相邻行的对比。一是建立行号列，用嵌套子查询中的相邻行号做条件，在最内层比较目标字段值，这在《SQL进阶教程》中有类似的介绍。另一种是我这次图快的骚操作，利用窗口函数和字段值的特性来做判断。

1 寻找状态从 A 变更为 B 的行

• SELECT 语句中，用窗口函数选择当前行和上一行。
• 将 first_value() 的值作为当前行的新字段查出来。

• 在父查询中对比该字段与当前行原字段。

  SELECT *
  FROM (
        SELECT date, 
               city,
               time,
               status,
               first_value(status) over(PARTITION BY date, city ORDER BY time ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS former_status
          FROM content
       )
  WHERE former_status = 'A'
   AND status = 'B'

  2 寻找数值降低的行

  可以使用上文的方法，或者对于数值：

• 我最早想到的配合窗口函数的常用聚合函数是 avg()，也可以解决这个问题，只不过绕路了。

• 可以用窗口含数求当前行和上一行的平均值，作为新字段。
• 在父查询中对比数值与平均值，若数值小于平均值，则认为当前行数值比上一行数值更小。

  SELECT *
  FROM (
        SELECT date,
                   city,
               time,
               avg(value) over(PARTITION BY date, city ORDER BY time ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg_with_former_value
          FROM content
       )
  WHERE value < avg_with_former_value
  

最终

SELECT hour(time) as hour,
       count(*) as times
  FROM (
        SELECT date,
                   city,
               time,
               status,
               value,
               first_value(status) over(PARTITION BY date, city ORDER BY time ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS former_status,
                             first_value(value) over(PARTITION BY date, city ORDER BY time ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS former_value
          FROM content
       )
 WHERE former_status = 'A'
   AND status = 'B'
   AND value < former_value
 GROUP BY 1

// 原来想这个方法时感觉挺复杂的，怎么写出来觉得这么简单……