如何仅用窗口函数实现会话级PV聚合，排除登录页作为最后访问页

实现方案 #

完全可以在不使用子查询、自连接的前提下满足需求，核心通过调整窗口函数的排序规则实现末次有效页面的提取，最终查询如下（兼容Spark SQL、Snowflake、BigQuery等主流大数据SQL引擎）：

select 
    visitorid,
    sessionid,
    first_value(webpagepath) over (
        partition by visitorid, sessionid 
        order by timestamp asc
        rows between unbounded preceding and unbounded following
    ) as FirstPageview,
    first_value(case when webpagepath != 'login' then webpagepath end) over (
        partition by visitorid, sessionid 
        order by case when webpagepath != 'login' then 1 else 2 end asc, timestamp desc
        rows between unbounded preceding and unbounded following
    ) as LastPageview,
    sum(iff(webpagepath = 'login', 1, 0)) over (
        partition by visitorid, sessionid
    ) as `#ofvisitstoLogin`
from pageviews
qualify row_number() over (partition by visitorid, sessionid order by timestamp asc) = 1;

逻辑说明 #

• 首次页面访问：直接沿用first_value按会话内时间正序取值的逻辑，符合需求。
• 末次有效页面访问：通过自定义排序规则，将所有非登录页的排序优先级设为1、登录页设为2，同优先级下按时间倒序排列，此时排序后的第一个非空值就是会话内最新的非登录页，完美规避末尾登录页的问题。
• 登录页访问次数：去掉原有sum窗口函数中的order by子句，避免返回累积计数，直接统计整个会话的登录页总出现次数。
• 结果去重：通过qualify关键字直接过滤每个会话的第一条记录，无需额外子查询或分组操作，性能损耗极低。

如果你的SQL引擎不支持qualify语法，也可以在select后加distinct关键字替代，同样不需要子查询。

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