周度事件连续统计问题：SQL中Weeks_Charted计算逻辑修正需求

Got it, let's resolve this issue where your Weeks_Charted is incorrectly counting weeks across data gaps. The root problem is using the total date range instead of only counting actual consecutive weekly entries. Below are solutions for both of your requested output formats:

1. Split into Separate Continuous Weekly Segments #

This approach breaks each CH_TTL_ID into distinct groups of consecutive weeks (where each entry is exactly 7 days apart) and calculates metrics for each segment individually.

WITH ranked_data AS (
    SELECT 
        CH_TTL_ID,
        CH_FMT_ID,
        CH_RANK,
        CH_ISS_DT,
        -- Get the previous week's date for the same title/chart
        LAG(CH_ISS_DT) OVER (PARTITION BY CH_TTL_ID, CH_FMT_ID ORDER BY CH_ISS_DT) AS prev_issue_dt
    FROM @t
),
segmented_data AS (
    SELECT 
        *,
        -- Assign a unique segment ID whenever a gap (non-7-day difference) is found
        SUM(CASE WHEN DATEDIFF(day, prev_issue_dt, CH_ISS_DT) = 7 OR prev_issue_dt IS NULL THEN 0 ELSE 1 END) 
            OVER (PARTITION BY CH_TTL_ID, CH_FMT_ID ORDER BY CH_ISS_DT) AS segment_id
    FROM ranked_data
)
SELECT 
    CH_TTL_ID,
    CH_FMT_ID AS Chart,
    MIN(CH_RANK) AS Peak,
    MAX(CH_RANK) AS Trough,
    COUNT(CH_RANK) AS Weeks,
    MIN(CH_ISS_DT) AS EntryDate,
    MAX(CH_ISS_DT) AS ExitDate,
    -- Calculate actual weeks in the continuous segment (count of entries - 1, since each entry is a week apart)
    COUNT(CH_RANK) - 1 AS Weeks_Charted
FROM segmented_data
GROUP BY CH_TTL_ID, CH_FMT_ID, segment_id
ORDER BY CH_TTL_ID, ExitDate DESC;

How this works: #

• ranked_data CTE: Uses LAG() to fetch the prior week's date for each entry in the same CH_TTL_ID/CH_FMT_ID group.
• segmented_data CTE: Creates a segment_id by incrementing the count whenever a gap (difference not equal to 7 days) is detected. This groups all consecutive weekly entries together.
• Final SELECT: Aggregates by each segment, calculating Weeks_Charted as the number of entries minus 1 (since each consecutive entry represents one week apart).

For your sample data, this will split CH_TTL_ID=111111 into two segments: one for 2002 (5 weeks, Weeks_Charted=4) and one for 2011 (2 weeks, Weeks_Charted=1).

2. Only Calculate the Most Recent Continuous Segment #

This solution focuses solely on the latest uninterrupted block of weekly entries, updating all metrics (Peak, Trough, etc.) to reflect only this segment.

WITH ranked_data AS (
    SELECT 
        CH_TTL_ID,
        CH_FMT_ID,
        CH_RANK,
        CH_ISS_DT,
        -- Get the next week's date (reverse order) to detect gaps going backward
        LEAD(CH_ISS_DT) OVER (PARTITION BY CH_TTL_ID, CH_FMT_ID ORDER BY CH_ISS_DT DESC) AS next_issue_dt
    FROM @t
),
latest_segment AS (
    SELECT 
        *,
        -- Assign segment ID starting from the most recent entry; increment when a gap is found
        SUM(CASE WHEN DATEDIFF(day, CH_ISS_DT, next_issue_dt) = 7 OR next_issue_dt IS NULL THEN 0 ELSE 1 END) 
            OVER (PARTITION BY CH_TTL_ID, CH_FMT_ID ORDER BY CH_ISS_DT DESC) AS segment_id
    FROM ranked_data
)
SELECT 
    CH_TTL_ID,
    CH_FMT_ID AS Chart,
    MIN(CH_RANK) AS Peak,
    MAX(CH_RANK) AS Trough,
    COUNT(CH_RANK) AS Weeks,
    MIN(CH_ISS_DT) AS EntryDate,
    MAX(CH_ISS_DT) AS ExitDate,
    COUNT(CH_RANK) - 1 AS Weeks_Charted
FROM latest_segment
WHERE segment_id = 0 -- Only keep the most recent continuous segment
GROUP BY CH_TTL_ID, CH_FMT_ID
ORDER BY CH_TTL_ID;

How this works: #

• ranked_data CTE: Uses LEAD() in reverse date order to check the next (earlier) week's date, helping us identify gaps starting from the most recent entry.
• latest_segment CTE: Creates a segment_id where 0 represents the most recent continuous block (no gaps when moving backward in time).
• Final SELECT: Filters to only the segment_id=0 group, aggregating metrics for just the latest uninterrupted weekly entries.

For your sample data, CH_TTL_ID=111111 will only show the 2011 segment (2 weeks, Weeks_Charted=1), while CH_TTL_ID=397130 (no gaps) will show its full 7-week segment (Weeks_Charted=6).

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