配置单元中两个记录之间的差异

Question

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

我的表如下所示：

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

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

PostGres中的演示(hive也有类似的语法(

Answer 2

使用超前或滞后分析函数来处理按某些列排序的上一行/下一行：

例如：

with your_data as (
select stack(4,
'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'
) as (name, address, count, current_date_time)
)
select prev_count-count from
(
select s.*, lag(count) over(partition by name order by current_date_time) prev_count,
       row_number() over(partition by name order by current_date_time desc) rn
  from your_data s 
  where name="john" 
)s where rn=2;

返回：

OK
192