sql - hive 中两条记录之间的差异

Question

我有一个包含 5 列的表，我需要找到前两条记录的计数列差异。我能够根据某些条件获得前两项记录。例如，

我的 table 看起来像:

name address count current_date_time
john LA      102    2019-07-12 12:24:38
peter MAC    105    2019-07-12 12:24:40
john  NY      210   2019-07-12 12:24:02
john  WD      18    2019-07-12 12:24:12

选择查询以获取前两行:

SELECT count 
FROM table_name 
WHERE name="john" 
ORDER BY current_date_time DESC LIMIT 2

它返回如下:

count
102
18

但我需要 102 和 18 之间的差异。

如何编写子查询？

Answer 1

应用lead()窗口解析函数来确定下一行的列值。

SELECT count - ld as "Difference"
  FROM
 (
  SELECT count, lead(count,1,0) over (order by current_date_time desc ) as ld,
         current_date_time 
    FROM table_name 
   WHERE name="john" 
  ORDER BY current_date_time DESC LIMIT 2
 ) q
ORDER BY q.current_date_time DESC LIMIT 1

where for lead(count,1,0) 1 表示偏移量，即 1 行之后，0 表示默认值。

Demo in PostGres (hive 也有类似的语法)