Redshift中计算DAU、WAU、MAU用户粘性比率时关联子查询报错的解决方案咨询

The error you're hitting is a common limitation in Amazon Redshift when using multiple correlated subqueries with COUNT(DISTINCT) in the SELECT clause. Redshift's query optimizer struggles with this pattern, leading to the internal error you see—even though individual subqueries work fine on their own.

The core issue is that each correlated subquery re-scans the entire my_table for every row in your result set, which is not only inefficient but also triggers unsupported query planning logic. Here's a more reliable, efficient alternative that avoids correlated subqueries entirely:

WITH date_range AS (
    -- Generate all dates in our target window: past 6 months (excluding current month)
    SELECT DATE_TRUNC('day', dt) AS dt
    FROM generate_series(
        current_date - INTERVAL '7 months' + INTERVAL '1 day',
        current_date - INTERVAL '1 month',
        INTERVAL '1 day'
    ) AS dt
),
daily_active_users AS (
    -- Get all user-day pairs for the extended window (covers 30 days before our earliest target date)
    SELECT 
        DATE_TRUNC('day', datetime_field) AS dt,
        user_id
    FROM my_table
    WHERE datetime_field > current_date - INTERVAL '7 months'
      AND datetime_field <= current_date - INTERVAL '1 month'
),
dau_calculations AS (
    -- Calculate daily active users (DAU) for each date
    SELECT 
        dt,
        COUNT(DISTINCT user_id)::float AS dau
    FROM daily_active_users
    GROUP BY dt
)
-- Calculate WAU, MAU, and stickiness ratios using conditional aggregation
SELECT 
    dr.dt,
    COALESCE(dc.dau, 0) AS dau,
    -- Count distinct users active in the past 7 days (including current date)
    COUNT(DISTINCT CASE WHEN dau_dt.dt BETWEEN dr.dt - INTERVAL '7 days' AND dr.dt THEN dau_dt.user_id END) AS wau,
    -- Count distinct users active in the past 30 days (including current date)
    COUNT(DISTINCT CASE WHEN dau_dt.dt BETWEEN dr.dt - INTERVAL '30 days' AND dr.dt THEN dau_dt.user_id END) AS mau,
    -- Calculate stickiness ratios, handling division by zero
    COALESCE(dc.dau, 0) / NULLIF(COUNT(DISTINCT CASE WHEN dau_dt.dt BETWEEN dr.dt - INTERVAL '7 days' AND dr.dt THEN dau_dt.user_id END), 0) AS dau_wau,
    COUNT(DISTINCT CASE WHEN dau_dt.dt BETWEEN dr.dt - INTERVAL '7 days' AND dr.dt THEN dau_dt.user_id END) / NULLIF(COUNT(DISTINCT CASE WHEN dau_dt.dt BETWEEN dr.dt - INTERVAL '30 days' AND dr.dt THEN dau_dt.user_id END), 0) AS wau_mau
FROM date_range dr
-- Join to cover all users active in the past 30 days for each target date
LEFT JOIN daily_active_users dau_dt 
    ON dau_dt.dt BETWEEN dr.dt - INTERVAL '30 days' AND dr.dt
-- Join to get precomputed DAU values
LEFT JOIN dau_calculations dc 
    ON dr.dt = dc.dt
GROUP BY dr.dt, dc.dau
ORDER BY dr.dt;

How This Works: #

1. date_range: Generates every date in your target 6-month window (excluding the current month). This ensures even dates with no active users appear in your results.
2. daily_active_users: Extracts all user-day pairs for the extended window (7 months back) to cover the 30-day lookback needed for MAU.
3. dau_calculations: Precomputes DAU once for each date, avoiding redundant calculations.
4. Main Query: Uses conditional aggregation to count distinct users within the 7-day and 30-day windows for each date. By joining once to the daily user data, we avoid repeated full-table scans.

Key Improvements: #

• Avoids the unsupported correlated subquery pattern entirely
• Only scans the raw user data once, making the query much more efficient
• Handles dates with no active users gracefully using COALESCE
• Prevents division by zero with NULLIF

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