SELECT查询列选择时添加WHERE条件的方法及访客TOTAL_TIMEONSITE均值计算异常排查

你的查询目前出现两个核心问题：一是子查询没有和主查询的访客ID关联，导致算出的是全局统一平均值而非每个访客的专属值；二是子查询里的GROUP BY TOTAL_TIMEONSITE逻辑错误，我们需要按访客分组而非按访问时长分组。下面直接给你修正后的方案，并详细解释逻辑：

方案一：关联子查询（逻辑清晰，适合小数据量） #

SELECT 
    FULLVISITORID AS VISITOR_ID,
    VISITID AS VISIT_ID,
    VISITSTARTTIME_TS,
    USER_ACCOUNT_TYPE,
    -- 关联主查询的访客ID，计算该访客6-8月的平均时长
    (SELECT AVG(TOTAL_TIMEONSITE)
     FROM "ACRO_DEV"."GA"."GA_MAIN" sub
     WHERE sub.FULLVISITORID = main.FULLVISITORID
       AND CAST(sub.visitstarttime_ts AS DATE) BETWEEN TO_DATE('2020-06-01', 'YYYY-MM-DD') 
                                                   AND TO_DATE('2020-08-31', 'YYYY-MM-DD')) 
       AS AVG_TOTAL_TIME_ON_SITE_LAST_3M,
    CHANNELGROUPING,
    GEONETWORK_CONTINENT
FROM "ACRO_DEV"."GA"."GA_MAIN" main
WHERE 
    -- 主查询直接限定为2020年8月的数据，替代原有的IN子查询，性能更优
    CAST(main.visitstarttime_ts AS DATE) BETWEEN TO_DATE('2020-08-01', 'YYYY-MM-DD') 
                                            AND TO_DATE('2020-08-31', 'YYYY-MM-DD')
    AND main.user_account_type IN ('anonymous', 'registered');

关键修正点： #

1. 子查询中通过sub.FULLVISITORID = main.FULLVISITORID关联主查询的当前访客，确保计算的是该访客的专属平均值
2. 简化日期条件为BETWEEN，可读性更强
3. 去掉了冗余的IN子查询，直接在主查询WHERE中限定8月数据和账号类型，逻辑更简洁

方案二：窗口函数（高效，适合大数据量场景） #

如果你的数据量较大，窗口函数的性能会远优于关联子查询：

SELECT 
    FULLVISITORID AS VISITOR_ID,
    VISITID AS VISIT_ID,
    VISITSTARTTIME_TS,
    USER_ACCOUNT_TYPE,
    -- 按访客分组，自动计算该访客近3个月的平均时长
    AVG(TOTAL_TIMEONSITE) OVER (PARTITION BY FULLVISITORID
                                ORDER BY CAST(visitstarttime_ts AS DATE)
                                RANGE BETWEEN INTERVAL '3 months' PRECEDING AND CURRENT ROW)
       AS AVG_TOTAL_TIME_ON_SITE_LAST_3M,
    CHANNELGROUPING,
    GEONETWORK_CONTINENT
FROM "ACRO_DEV"."GA"."GA_MAIN"
WHERE 
    CAST(visitstarttime_ts AS DATE) BETWEEN TO_DATE('2020-08-01', 'YYYY-MM-DD') 
                                        AND TO_DATE('2020-08-31', 'YYYY-MM-DD')
    AND user_account_type IN ('anonymous', 'registered');

窗口函数逻辑说明： #

• PARTITION BY FULLVISITORID：确保我们按每个访客单独分组计算
• RANGE BETWEEN INTERVAL '3 months' PRECEDING AND CURRENT ROW：自动限定时间范围为当前记录日期的前3个月到当前日期，正好覆盖6-8月（因为主查询仅取8月数据）

关于「在SELECT列选择中添加WHERE条件」的方法 #

你可以通过两种方式实现：

1. 子查询内部加WHERE：就像方案一里的子查询，直接在子查询中过滤计算所需的时间范围和条件
2. CASE条件判断：如果需要针对不同情况计算不同值，可以用CASE语句在列中做条件筛选，比如只计算注册用户的平均时长：

SELECT 
    FULLVISITORID AS VISITOR_ID,
    -- 仅对注册用户的访问时长计算平均值
    AVG(CASE WHEN user_account_type = 'registered' THEN TOTAL_TIMEONSITE ELSE NULL END) 
        OVER (PARTITION BY FULLVISITORID) AS AVG_REGISTERED_TIME,
    -- 其他列...
FROM "ACRO_DEV"."GA"."GA_MAIN"
WHERE ...

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