在AWS Athena中替代Qlik peek()函数生成STATE字段的方案

需求说明 #

需要为每个PROJECT+INVESTOR分组，根据IS_ACTIF的变化生成递增的STATE字段：

• 每个分组初始STATE为1
• 当IS_ACTIF与前一行值相同时，STATE保持不变
• 当IS_ACTIF与前一行值不同时，STATE递增1

源数据 #

期望结果 #

Qlik实现代码 #

STATE:
NoConcatenate
LOAD DISTINCT  
    PROJECT,
    INVESTOR, 
    DATE,
    IS_ACTIF,    
    IF(RowNo()=1 OR Previous(PROJECT&INVESTOR)<>PROJECT&INVESTOR,1,
        IF(  IS_ACTIF=Previous(IS_ACTIF)
               ,peek(STATE),peek(STATE)+1) )                AS STATE
    
RESIDENT DATA ORDER BY PROJECT, INVESTOR,DATE;

Athena SQL实现方案 #

AWS Athena基于Presto SQL，可通过窗口函数实现该逻辑，核心思路是先识别状态变化，再对变化次数进行累加：

完整SQL代码 #

WITH data_with_prev AS (
    SELECT 
        PROJECT,
        INVESTOR,
        DATE,
        IS_ACTIF,
        -- 获取同分组内前一行的IS_ACTIF值，分组第一行返回NULL
        LAG(IS_ACTIF) OVER (PARTITION BY PROJECT, INVESTOR ORDER BY DATE) AS prev_is_actif
    FROM your_source_table -- 替换为你的实际源表名称
)
SELECT 
    PROJECT,
    INVESTOR,
    DATE,
    IS_ACTIF,
    -- 累加状态变化次数，生成递增的STATE
    SUM(CASE 
            WHEN prev_is_actif IS NULL THEN 1 -- 分组第一行初始化为1
            WHEN IS_ACTIF != prev_is_actif THEN 1 -- 状态变化时加1
            ELSE 0 -- 状态不变时保持当前值
        END) OVER (PARTITION BY PROJECT, INVESTOR ORDER BY DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS STATE
FROM data_with_prev
ORDER BY PROJECT, INVESTOR, DATE;

逻辑解释 #

• PARTITION BY PROJECT, INVESTOR：按项目和投资者分组，确保每个分组独立计算状态序列
• LAG(IS_ACTIF)：获取分组内上一行的激活状态，用于判断当前行是否发生状态切换
• SUM(...) OVER (...)：对分组内从第一行到当前行的状态变化标识进行累加，得到最终的STATE值：
  • 分组第一行无前置行，直接计为1
  • 当当前行与前一行IS_ACTIF不同时，累加1；否则累加0，保持STATE不变

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