基于起止时间计算has_impact字段的SQL查询问题求助

需求说明 #

针对会话中每条feed视图填充has_impact字段，规则如下：

• 当(view_end_time - view_start_time) > 3且view_perc > 0.8时，has_impact为yes
• 其他情况为no

表结构及测试数据 #

create table view_logs(session_id varchar(10), post_id int, ts int, event_name varchar(50), view_perc float);
insert into view_logs(session_id, post_id, ts, event_name, view_perc) values 
('m1', 1000, 1524600, 'view_start', null), 
('m1', 1000, 1524602, 'view_end', 0.85), 
('m1', 1000, 1524650, 'view_start', null), 
('m1', 1000, 1524654, 'view_end', 0.9), 
('m1', 2000, 1524700, 'view_start', null), 
('m1', 2000, 1524707, 'view_end', 0.3), 
('m1', 2000, 1524710, 'view_start', null), 
('m1', 2000, 1524713, 'view_end', 0.9);

用户尝试的SQL（存在问题） #

with cte as ( 
    select pv1.session_id, pv1.post_id, pv2.view_perc, pv1.ts as start_time, min(pv2.ts) as end_time 
    from view_logs pv1 
    join view_logs pv2 on pv1.session_id = pv2.session_id and pv1.post_id = pv2.post_id and pv1.event_name <> pv2.event_name and pv1.ts < pv2.ts 
    group by pv1.session_id, pv1.post_id, pv2.view_perc, pv1.ts 
) 
select session_id, post_id, start_time, end_time, 
case when (end_time - start_time > 3 and view_perc > 0.8 )then 'yes' else 'no' end as has_meaningful_view 
from cte

期望输出 #

问题分析 #

1. 连接条件逻辑错误：原SQL仅用pv1.event_name <> pv2.event_name匹配事件，未限定pv1是view_start、pv2是view_end，会出现view_end匹配后续view_start的错误关联，导致时间计算混乱。
2. 分组字段冗余：分组时包含pv2.view_perc，会让同一start_time因对应不同view_perc被拆分成多条记录，不符合“一条start对应一条end”的业务逻辑。
3. 字段名不匹配：查询结果字段为has_meaningful_view，与期望的has_impact不一致。

修正后的SQL #

WITH view_events AS (
    SELECT 
        session_id,
        post_id,
        ts AS start_time,
        -- 取同会话同帖子的下一条事件的时间（即对应end时间）
        LEAD(ts) OVER (PARTITION BY session_id, post_id ORDER BY ts) AS end_time,
        -- 取对应end事件的view_perc
        LEAD(view_perc) OVER (PARTITION BY session_id, post_id ORDER BY ts) AS view_perc
    FROM view_logs
    -- 只保留start事件，后续匹配对应的end事件
    WHERE event_name = 'view_start'
)
SELECT 
    session_id,
    post_id,
    start_time,
    end_time,
    CASE 
        WHEN (end_time - start_time > 3 AND view_perc > 0.8) THEN 'yes'
        ELSE 'no'
    END AS has_impact
FROM view_events
ORDER BY session_id, post_id, start_time;

验证结果 #

执行上述修正后的SQL，将得到与期望完全一致的输出。

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