如何在T-SQL中仅关联一次表实现Cross Apply滚动统计

Got it! Let’s fix that performance drag by cutting out the redundant join with #t2. The core trick is to first combine #t1 and #t2 exactly once into a unified dataset, then run all our rolling stats logic on that pre-combined set. This keeps the query efficient while delivering the exact results you need.

Optimized Query (Cross Apply Approach) #

This version uses a CTE to handle the single join upfront, then reuses that dataset for both daily DAU and rolling MAU calculations:

WITH user_login_country AS (
    -- Single join to bring together user logins and their country
    SELECT 
        t1.email,
        t1.logins,
        t2.country
    FROM #t1 t1
    INNER JOIN #t2 t2 ON t1.email = t2.email
)
SELECT 
    CAST(d.logins AS DATE) AS Dates,
    d.country,
    COUNT(DISTINCT d.email) AS DAU,
    COUNT(DISTINCT m.email) AS MAU
FROM user_login_country d
CROSS APPLY (
    -- Reuse the pre-combined dataset instead of joining #t2 again
    SELECT m.email
    FROM user_login_country m
    WHERE 
        m.country = d.country -- Ensure we only count users in the same country
        AND m.logins BETWEEN d.logins AND DATEADD(dd, 30, d.logins)
) m
GROUP BY CAST(d.logins AS DATE), d.country
ORDER BY d.country, Dates;

Why This Works #

• Single Join Overhead: The user_login_country CTE handles the #t1-#t2 join once, so we don’t pay the performance cost of repeating that operation in the Cross Apply.
• Country Alignment: The m.country = d.country filter ensures we’re only calculating MAU for users in the same country as the current row, which aligns with your grouping requirement.
• Cleaner Logic: Reusing the combined dataset makes the query easier to read and maintain, while still matching your desired output format.

Alternative: Window Function Approach (For Even Better Performance) #

If you’re using SQL Server 2019 or later, you can skip the Cross Apply entirely with window functions that support COUNT(DISTINCT). This can be faster for large datasets:

WITH user_login_country AS (
    SELECT 
        t1.email,
        CAST(t1.logins AS DATE) AS login_date,
        t2.country
    FROM #t1 t1
    INNER JOIN #t2 t2 ON t1.email = t2.email
),
distinct_daily_logins AS (
    -- Deduplicate daily logins (since a user might log in multiple times per day)
    SELECT DISTINCT 
        login_date,
        email,
        country
    FROM user_login_country
)
SELECT 
    login_date AS Dates,
    country,
    COUNT(DISTINCT email) AS DAU,
    -- Calculate MAU as distinct users in the 30-day window starting on the current date
    COUNT(DISTINCT email) OVER (
        PARTITION BY country
        ORDER BY login_date
        RANGE BETWEEN CURRENT ROW AND 29 FOLLOWING -- 29 days after = 30-day total window
    ) AS MAU
FROM distinct_daily_logins
GROUP BY login_date, country
ORDER BY country, Dates;

Example Output #

Both queries will produce the exact format you requested using your sample data:

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