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

需求

代码语言：javascript复制

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

建表

代码语言：javascript复制

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

数据

代码语言：javascript复制

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');

实现

代码语言：javascript复制

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
;

结果

代码语言：javascript复制

#结果
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)

分析

代码语言：javascript复制

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

扩展

代码语言：javascript复制

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

知识点

代码语言：javascript复制

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的用法和测试，参考链接：

https://blog.csdn.net/luo981695830/article/details/111211773

lag 测试 数据

0 人点赞