sql - SQL查询查找事实表中的趋势

Question

我有一个如下所示的事实表，如何找到在给定时间段内值下降的实体？例如，选择在2014-01-01至之间减少的实体和值2014-01-04

entity_id | value | date
12        | 15    | 2014-01-01 
56        | 21    | 2014-01-04
12        | 15    | 2014-01-04 
56        | 21    | 2014-01-02 
12        | 25    | 2014-01-02 
12        | 16    | 2014-01-03 

例如， entity_id 12 值的日期之间减少了两次2014-01-01：2014-01-04

entity_id | value | date       | old_value
12        | 16    | 2014-01-03 | 25
12        | 15    | 2014-01-04 | 16

标签： sqlhiveql

Answer 1

我想你想要lag()：

select t.*
from (select t.*, lag(value) over (partition by entity_id order by date) as prev_value
      from t
     ) t
where prev_value > value;

您可以添加and date >= '2014-01-01' and date <= '2014-01-04'特定日期范围。