Teradata 15中基于每周六结束的30天窗口计算唯一用户数

Got it, let's work through this problem for your Teradata 15 environment. Since you need to calculate distinct users for each Saturday-ending 30-day window using only interactive SQL (no stored procs, UDFs, or loops), here's a straightforward, efficient approach:

Step 1: Generate all relevant Saturday dates #

First, we need a list of every Saturday that falls within the range of your activity dates in table1. We can use Teradata's built-in sys_calendar.calendar table for this—it's a reliable way to generate date sequences without recursion, though recursion works too if you prefer.

Step 2: Calculate distinct users per Saturday window #

For each Saturday, we'll join back to table1 to count unique users whose activity dates fall within the 30-day period ending on that Saturday.

Here's the full SQL query:

WITH saturday_dates AS (
    -- Pull all Saturdays that cover the range of activity dates in table1
    SELECT calendar_date AS saturday_end
    FROM sys_calendar.calendar
    WHERE day_of_week_name = 'Saturday'
      AND calendar_date BETWEEN 
          -- Get the first Saturday on or before the earliest activity date
          (SELECT MIN(activitydate) - MOD((MIN(activitydate) - DATE '1900-01-06'), 7) FROM table1)
          -- Up to the latest activity date
          AND (SELECT MAX(activitydate) FROM table1)
)
SELECT 
    s.saturday_end,
    -- Use COALESCE to return 0 instead of NULL if no users are in the window
    COALESCE(COUNT(DISTINCT t.userid), 0) AS unique_user_count
FROM saturday_dates s
LEFT JOIN table1 t
    -- Include all activity dates from 30 days before the Saturday up to the Saturday itself
    ON t.activitydate BETWEEN s.saturday_end - 30 AND s.saturday_end
GROUP BY s.saturday_end
ORDER BY s.saturday_end;

Key notes: #

• Date range handling: The MOD calculation ensures we start with the first Saturday that's on or before your earliest activity date, so we don't miss any windows that might include early activity.
• Left join: Using a left join guarantees we get a row for every Saturday, even if there were no user activities in that 30-day window (hence the COALESCE to turn NULL into 0).
• Teradata compatibility: This uses only standard interactive SQL features available in Teradata 15—no stored procedures, UDFs, or loops required.

If you prefer using a recursive CTE instead of sys_calendar, here's an alternative version:

WITH saturday_dates AS (
    -- Start with the first Saturday on or after the earliest activity date
    SELECT 
        CASE 
            WHEN DAY_OF_WEEK_NAME(min_activity) = 'Saturday' THEN min_activity
            ELSE NEXT_DAY(min_activity, 'Saturday') 
        END AS saturday_end
    FROM (SELECT MIN(activitydate) AS min_activity FROM table1) AS init
    UNION ALL
    -- Recursively add each subsequent Saturday until we reach the latest activity date
    SELECT NEXT_DAY(saturday_end, 'Saturday')
    FROM saturday_dates
    WHERE NEXT_DAY(saturday_end, 'Saturday') <= (SELECT MAX(activitydate) FROM table1)
)
SELECT 
    s.saturday_end,
    COALESCE(COUNT(DISTINCT t.userid), 0) AS unique_user_count
FROM saturday_dates s
LEFT JOIN table1 t
    ON t.activitydate BETWEEN s.saturday_end - 30 AND s.saturday_end
GROUP BY s.saturday_end
ORDER BY s.saturday_end;

Both approaches will give you the desired output: each Saturday paired with the count of unique users active in the 30 days leading up to and including that Saturday.

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