Redshift中计算用户类型渗透百分比的SQL实现问询

方法一：使用LEFT JOIN与条件计数 #

SELECT
    t1.type,
    -- 统计各type下table2的用户数
    COUNT(DISTINCT CASE WHEN t2.userid IS NOT NULL THEN t1.userid END) AS table2_user_count,
    -- 统计各type在table1的总用户数
    COUNT(DISTINCT t1.userid) AS table1_total_user_count,
    -- 计算渗透百分比（保留两位小数）
    ROUND(
        COUNT(DISTINCT CASE WHEN t2.userid IS NOT NULL THEN t1.userid END) * 100.0 / 
        COUNT(DISTINCT t1.userid),
        2
    ) AS penetration_rate
FROM table1 t1
-- 关联table2去重后的用户列表，确保保留table1所有type
LEFT JOIN (SELECT DISTINCT owner AS userid FROM table2) t2 
    ON t1.userid = t2.userid
WHERE t1.report_date = 'yyyy-mm-dd'
GROUP BY t1.type
ORDER BY penetration_rate DESC;

方法二：使用CTE拆分统计逻辑（更易读） #

-- 先统计table1各type的总用户数
WITH table1_type_totals AS (
    SELECT 
        type, 
        COUNT(DISTINCT userid) AS total_users
    FROM table1
    WHERE report_date = 'yyyy-mm-dd'
    GROUP BY type
),
-- 再统计各type下属于table2的用户数
table2_type_counts AS (
    SELECT 
        t1.type, 
        COUNT(DISTINCT t1.userid) AS table2_users
    FROM table1 t1
    JOIN (SELECT DISTINCT owner AS userid FROM table2) t2 
        ON t1.userid = t2.userid
    WHERE t1.report_date = 'yyyy-mm-dd'
    GROUP BY t1.type
)
-- 关联两个统计结果，计算占比
SELECT
    ttt.type,
    -- 处理无table2用户的type，默认显示0
    COALESCE(t2c.table2_users, 0) AS table2_user_count,
    ttt.total_users AS table1_total_user_count,
    ROUND(COALESCE(t2c.table2_users, 0) * 100.0 / ttt.total_users, 2) AS penetration_rate
FROM table1_type_totals ttt
LEFT JOIN table2_type_counts t2c 
    ON ttt.type = t2c.type
ORDER BY penetration_rate DESC;

关键说明 #

• DISTINCT的作用：如果table1中同一个userid对应多条同type的记录，COUNT(DISTINCT) 可以避免重复计数，保证统计的是真实用户数量。
• LEFT JOIN的作用：确保table1中所有type都会被统计到，即使该type没有用户出现在table2中（此时table2用户数显示为0）。
• 百分比计算：乘以100.0是为了将整数除法转换为浮点数除法，避免出现截断错误；ROUND函数用于控制百分比的小数位数（这里保留2位）。

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