hive sql系列（五）——按照时间轴顺序， 发生了状态变化的数据行

每天分享一个sql，帮助大家找到sql的快乐

需求

一个日志表中记录了某个商户费率变化状态的所有信息，
现在有个需求,要取出按照时间轴顺序，
发生了状态变化的数据行；

建表语句

create table shop(
    id string,
    rate string,
    rq date
);

数据

insert into shop values
(100,0.1,'2021-03-02'),
(100,0.1,'2021-02-02'),
(100,0.2,'2021-03-05'),
(100,0.2,'2021-03-06'),
(100,0.3,'2021-03-07'),
(100,0.1,'2021-03-09'),
(100,0.1,'2021-03-10'),
(100,0.1,'2021-03-10'),
(200,0.1,'2021-03-10'),
(200,0.1,'2021-02-02'),
(200,0.2,'2021-03-05'),
(200,0.2,'2021-03-06'),
(200,0.3,'2021-03-07'),
(200,0.1,'2021-03-09'),
(200,0.1,'2021-03-10'),
(200,0.1,'2021-03-10');

实现

select
    t1.id,
    t1.rate,
    t1.rq,
    t1.rate2
from
    (select
        id,
        rate,
        rq,
        lag(rate,1,0) over(partition by id order by rq) rate2
    from
        shop
    )t1
where t1.rate != t1.rate2
;

结果

t1.id	t1.rate	t1.rq	t1.rate2
100	0.1	2021-02-02	0
100	0.2	2021-03-05	0.1
100	0.3	2021-03-07	0.2
100	0.1	2021-03-09	0.3
200	0.1	2021-02-02	0
200	0.2	2021-03-05	0.1
200	0.3	2021-03-07	0.2
200	0.1	2021-03-09	0.3
Time taken: 17.429 seconds, Fetched: 8 row(s)

分析

1、某个商户、时间顺序关键词，就是对商户开窗，然后按照时间排序
2、这里需要比较当前行和上一行，所以需要上一行的数据取出放在当前行
3、使用lag函数取出上一行，在进行比较即可

扩展

1、这里有一个需要考虑去重的问题，如果一个商户之前是0.1的费率，第一次改动时变成了0.2，之后又改回了0.1，那么0.2和0.1应该算两次改动，因为这里需求是发生了状态变化的数据行，要根据实际情况是否去重
2、初始状态是没有上一行的，这里默认值给了0，初始状态算不算状态变化，这个也要约定好，如果不算则需要加一个条件判断rate2!=0

知识点

lag用法:
1、lag(字段,n,默认值)
2、如果不设默认值lag(字段,n)，则返回值是NULL
3、n不能为负数——Underlying error: org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException: Lag amount can not be nagative. Specified: -1
4、n如果设为0，则取出当前行，没意义

更多关于lag的用法和测试，参考链接：

hive——lag函数使用_你好，我叫罗鹏程的博客-CSDN博客_hive lag