多CTE关联实现网站用户行为统计的最优方案及计数问题问询

需求说明 #

需要生成一张统计报表，展示指定页面（www.url.com）的以下指标：

• 唯一用户数（Sessions）
• 按营销渠道、**点击事件（EA/EL）**分组
• 统计每组的点击事件总数量、归因下载量
• 用于计算各分组的行级转化率

现有问题 #

当前通过SESSIONS CTE筛选符合条件的用户ID并关联其他CTE，但最终得到的计数结果不符合预期，需要验证该实现逻辑是否正确，并寻求更优方案。

原代码展示 #

WITH SESSIONS AS(
    SELECT DISTINCT(EVENTS.USER_ID) 
    FROM PAGE
        INNER JOIN VISITS
            ON PAGE.USER_ID = VISITS.USER_ID
        INNER JOIN MARKETING
            ON PAGE.USER_ID = MARKETING.USER_ID
        INNER JOIN EVENTS
            ON PAGE.USER_ID = EVENTS.USER_ID
    WHERE 1=1
        AND PAGE.URL='www.url.com'
    ),
EVENT AS(
    SELECT EVENTS.URL,EVENTS.USER_ID,EVENTS.EA,EVENTS.EL 
    FROM EVENTS
    WHERE  EVENTS.EA = 'Next Page Click' 
    AND EVENTS.URL='www.url.com'
    GROUP BY EVENTS.URL,EVENTS.USER_ID,EVENTS.EA,EVENTS.EL 
    ),
CONV AS(
    SELECT EVENTS.USER_ID, 
        CASE WHEN EVENTS.EA = 'Download' THEN 1 ELSE NULL END AS Download
    FROM EVENTS
    WHERE  EVENTS.EA = 'Download' 
    GROUP BY EVENTS.USER_ID,EVENTS.EA
    ),
MKTING AS(
    SELECT MARKETING.USER_ID, MARKETING.CHANNEL
    FROM MARKETING
    )
SELECT  COUNT(DISTINCT(SE.USER_ID)) Sessions,
        PAGE.URL,MKTING.CHANNEL,EVENT.EA,EVENT.EL,
        SUM(CONV.Download) AS Download
FROM SESSIONS AS SE
    LEFT JOIN EVENT
                ON SE.USER_ID = EVENT.USER_ID   
    LEFT JOIN CONV
        ON EVENT.USER_ID = CONV.USER_ID 
    LEFT JOIN MKTING
        ON SE.USER_ID = MKTING.USER_ID 
GROUP BY PAGE.URL,MKTING.CHANNEL,EVENT.EA,EVENT.EL
ORDER BY Sessions DESC;

原代码核心问题 #

1. 主查询引用未关联的表：SELECT中的PAGE.URL没有在主查询中做关联，会直接报错。
2. 用户计数重复：分组维度包含点击事件（EA/EL），同一个用户如果触发多个不同的点击事件，会被拆分到多行，导致COUNT(DISTINCT SE.USER_ID)在多行重复统计该用户，最终总Sessions虚高。
3. 点击事件统计缺失：原代码未统计每组的点击事件总数量，不符合需求。
4. 下载关联逻辑错误：通过EVENT.USER_ID关联下载数据，会遗漏未触发点击事件但直接完成下载的用户，导致下载量统计不全。
5. 冗余的JOIN与GROUP BY：SESSIONS CTE中多次INNER JOIN会产生笛卡尔积，后续DISTINCT虽能去重但逻辑冗余；EVENT CTE中的GROUP BY无意义，反而会丢失同一用户多次触发同一事件的计数。

优化后的实现方案 #

逻辑思路 #

1. 先筛选出访问指定页面的所有唯一用户，作为基础数据集。
2. 对每个用户的营销渠道做去重（假设一个用户对应一个渠道，若有多个需补充归因规则）。
3. 聚合统计每个用户的点击事件数量（按EA/EL分组）。
4. 聚合统计每个用户的下载转化次数（每个用户最多计1次，若需多次下载可调整逻辑）。
5. 最后关联所有聚合结果，按指定维度分组统计，确保用户计数不重复，指标统计准确。

修正后的SQL代码 #

WITH base_users AS (
    -- 筛选访问目标页面的唯一用户
    SELECT DISTINCT p.USER_ID
    FROM PAGE p
    WHERE p.URL = 'www.url.com'
),
user_channel AS (
    -- 获取每个用户对应的营销渠道（假设一个用户对应唯一渠道，若多渠道需补充归因逻辑）
    SELECT 
        USER_ID,
        MAX(CHANNEL) AS CHANNEL -- 若用户有多个渠道，这里用MAX取一个，可根据实际归因规则调整
    FROM MARKETING
    WHERE USER_ID IN (SELECT USER_ID FROM base_users)
    GROUP BY USER_ID
),
user_clicks AS (
    -- 统计每个用户的各点击事件（EA/EL）的触发次数
    SELECT
        USER_ID,
        EA,
        EL,
        COUNT(*) AS click_count
    FROM EVENTS
    WHERE 
        URL = 'www.url.com'
        AND EA = 'Next Page Click'
        AND USER_ID IN (SELECT USER_ID FROM base_users)
    GROUP BY USER_ID, EA, EL
),
user_conversions AS (
    -- 统计每个用户的下载转化次数（每个用户最多计1次）
    SELECT
        USER_ID,
        CASE WHEN COUNT(*) >= 1 THEN 1 ELSE 0 END AS download_count
    FROM EVENTS
    WHERE 
        EA = 'Download'
        AND USER_ID IN (SELECT USER_ID FROM base_users)
    GROUP BY USER_ID
)
-- 最终聚合统计
SELECT
    COUNT(DISTINCT bu.USER_ID) AS sessions,
    'www.url.com' AS page_url, -- 固定目标页面，无需关联表
    uc.CHANNEL,
    uc_k.EA,
    uc_k.EL,
    SUM(uc_k.click_count) AS total_clicks,
    SUM(ucv.download_count) AS total_downloads,
    -- 计算行级转化率（下载量/点击量，注意处理除数为0的情况）
    CASE 
        WHEN SUM(uc_k.click_count) > 0 
        THEN ROUND(SUM(ucv.download_count)::FLOAT / SUM(uc_k.click_count), 4) 
        ELSE 0 
    END AS conversion_rate
FROM base_users bu
LEFT JOIN user_channel uc ON bu.USER_ID = uc.USER_ID
LEFT JOIN user_clicks uc_k ON bu.USER_ID = uc_k.USER_ID
LEFT JOIN user_conversions ucv ON bu.USER_ID = ucv.USER_ID
GROUP BY uc.CHANNEL, uc_k.EA, uc_k.EL
ORDER BY sessions DESC, conversion_rate DESC;

优化点说明 #

• 避免笛卡尔积：通过分步聚合用户的渠道、点击、下载数据，再关联基础用户集，避免多表直接JOIN产生的重复数据。
• 准确统计用户数：在最外层用COUNT(DISTINCT bu.USER_ID)统计唯一用户，确保每个用户在分组中只被计数一次。
• 补充点击事件统计：新增total_clicks字段统计每组的点击总次数。
• 修正下载关联逻辑：直接关联基础用户集，确保所有下载用户都被统计，不管是否触发点击事件。
• 新增转化率计算：直接在查询中计算行级转化率，并处理除数为0的异常情况。

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