使用Impala SQL查找IND字段从0变为1的最新交易记录

我有一张包含ID、DATE和IND（对应原字段INDICATOR）字段的表my_table，需要找出IND字段从0变为1的最新交易记录。原始数据未排序，为便于理解已将数据排序展示，示例数据如下：

create table my_table (id integer, date date, ind integer);

insert into my_table values 
(1, '2022-07-08', 0),
(1, '2022-08-01', 1),
(1, '2022-08-15', 1),
(1, '2022-12-01', 0),
(1, '2023-02-18', 0),
(1, '2023-05-01', 1),
(1, '2023-06-15', 0),
(1, '2023-07-01', 1);
 
SELECT * FROM my_table;

执行上述查询后得到的排序后结果：

1|2022-07-08|0
1|2022-08-01|1
1|2022-08-15|1
1|2022-12-01|0
1|2023-02-18|0
1|2023-05-01|1
1|2023-06-15|0
1|2023-07-01|1

期望得到的结果是IND字段从0变为1的最后一条交易记录：

1|2023-07-01|1

解决方案 #

可以使用窗口函数LAG()获取每条记录的上一条记录的IND值，筛选出符合条件的过渡记录后取最新的一条：

WITH transition_records AS (
    SELECT 
        id,
        date,
        ind,
        LAG(ind) OVER (PARTITION BY id ORDER BY date) AS prev_ind
    FROM my_table
)
SELECT id, date, ind
FROM transition_records
WHERE ind = 1 AND prev_ind = 0
ORDER BY date DESC
LIMIT 1;

逻辑说明：

1. LAG(ind) OVER (PARTITION BY id ORDER BY date)：按id分组、date排序，获取每条记录的前一条记录的IND值，命名为prev_ind。
2. 筛选条件ind = 1 AND prev_ind = 0：精准定位所有从0变为1的过渡记录。
3. ORDER BY date DESC LIMIT 1：对过渡记录按日期倒序排列，取第一条即为最新的目标记录。

执行上述查询后，将得到期望的结果：

1|2023-07-01|1

内容的提问来源于stack exchange，提问作者ANUJ PATEL