Redshift SQL：单查询统计ABC看板每周开放TFS工单数量

Let's fix this so you don't have to manually update dates every week. The core idea is to automatically generate all the weeks we need to stats and then for each week, find the latest revision of each work item as of that week's snapshot point, then count those that aren't in Done or Removed state.

Approach 1: Snapshot-Based Count (Matches Your Original Query Logic) #

This method replicates your original manual query but scales it to all weeks automatically. We'll generate all weekly snapshot dates (using Monday as the start of each week), then pull the latest revision of each work item before that snapshot date, and count the open ones.

WITH weekly_snapshots AS (
    -- Generate all weekly snapshot dates (Monday start)
    SELECT
        DATE_TRUNC('week', min_change_date)::date + (n * INTERVAL '1 week')::date AS snapshot_week
    FROM (
        -- Get the earliest and latest change dates for ABC board work items
        SELECT 
            MIN(changedDate) AS min_change_date, 
            MAX(changedDate) AS max_change_date
        FROM tfs.workitem_revisions
        WHERE UPPER(boardName) = UPPER('ABC')
    ) AS date_bounds,
    -- Generate enough weeks to cover from earliest to latest change date
    GENERATE_SERIES(0, (max_change_date - min_change_date)::int / 7) AS n
),
latest_revisions_per_snapshot AS (
    SELECT
        wi.boardName,
        ws.snapshot_week,
        wi.id,
        wi.state,
        -- Rank revisions by date (and revision number) to get the latest one per work item/snapshot
        ROW_NUMBER() OVER (
            PARTITION BY ws.snapshot_week, wi.id 
            ORDER BY wi.changedDate DESC, wi.rev DESC
        ) AS revision_rank
    FROM weekly_snapshots ws
    JOIN tfs.workitem_revisions wi
        ON UPPER(wi.boardName) = UPPER('ABC')
        AND wi.changedDate < ws.snapshot_week -- Only include revisions before the snapshot week starts
)
-- Final count of open work items per week
SELECT
    boardName,
    snapshot_week AS week,
    COUNT(DISTINCT id) AS openCount
FROM latest_revisions_per_snapshot
WHERE 
    revision_rank = 1 -- Only keep the latest revision for each work item
    AND UPPER(state) NOT IN (UPPER('Done'), UPPER('Removed'))
GROUP BY boardName, snapshot_week
ORDER BY snapshot_week DESC;

How This Works: #

1. weekly_snapshots: Generates every Monday starting from the week of the first change on the ABC board to the week of the last change. No manual date updates needed!
2. latest_revisions_per_snapshot: For each snapshot week, joins to all work item revisions from the ABC board that happened before the week started. We use ROW_NUMBER() to mark the newest revision for each work item (sorted by change date, then revision number to break ties).
3. Final Query: Filters to only the latest revision per work item, excludes items in Done/Removed state, and counts unique work items per week.

Approach 2: State Interval-Based Count #

If you want to count work items that were open at any point during the week (not just a snapshot), use this method. It calculates the time each work item was in a non-Done/Removed state and maps those intervals to weeks.

WITH workitem_state_intervals AS (
    -- Calculate the time each work item state was active
    SELECT
        id,
        boardName,
        state,
        changedDate AS state_start,
        -- Next state's start date is the end of this state's interval; use current date if no next state
        LEAD(changedDate) OVER (PARTITION BY id ORDER BY changedDate) AS state_end
    FROM tfs.workitem_revisions
    WHERE UPPER(boardName) = UPPER('ABC')
),
weekly_periods AS (
    -- Generate all weeks covered by any state interval
    SELECT
        DATE_TRUNC('week', min_start)::date + (n * INTERVAL '1 week')::date AS week_start
    FROM (
        SELECT 
            MIN(state_start) AS min_start, 
            MAX(COALESCE(state_end, CURRENT_DATE)) AS max_end
        FROM workitem_state_intervals
    ) AS date_range,
    GENERATE_SERIES(0, (max_end - min_start)::int / 7) AS n
),
weekly_open_items AS (
    -- Match weeks to work items that were open during that week
    SELECT
        wsi.boardName,
        wp.week_start,
        wsi.id
    FROM weekly_periods wp
    JOIN workitem_state_intervals wsi
        ON wp.week_start < COALESCE(wsi.state_end, CURRENT_DATE)
        AND wp.week_start + INTERVAL '6 days' >= wsi.state_start -- Week overlaps with state interval
    WHERE UPPER(wsi.state) NOT IN (UPPER('Done'), UPPER('Removed'))
)
SELECT
    boardName,
    week_start AS week,
    COUNT(DISTINCT id) AS openCount
FROM weekly_open_items
GROUP BY boardName, week_start
ORDER BY week_start DESC;

Notes: #

• Both queries handle case insensitivity with UPPER() to avoid missing items due to capitalization differences.
• The GENERATE_SERIES logic accounts for cross-year weeks correctly by using day differences instead of week numbers.
• Adjust the snapshot logic (e.g., use Sunday as the snapshot date) by modifying the DATE_TRUNC and changedDate < ws.snapshot_week condition if needed.

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