SQL日期计算求助：计算订阅者终止与复职的日期间隔

Alright, let's work through this problem together. To solve this, we need to identify subscribers in your target group who had a terminated subscription and later reactivated, then compute the time between their termination date and the effective date of their reactivation.

First, let's assume your subscription table has these key columns (adjust if your schema differs):

• subscriber_id: Unique ID for each subscriber
• group_id: ID for the specific group you're focusing on
• status: Subscription status (e.g., 'termed' for terminated, 'active' for reactivated)
• term_date: Date when the subscription ended (only populated when status = 'termed')
• effective_date: Date when the subscription became active (populated for active statuses)

Method 1: Self-Join to Match Termination and Reactivation Records #

This approach uses a self-join to pair each termination record with the earliest subsequent reactivation for the same subscriber:

SELECT
    s_termed.subscriber_id,
    s_termed.term_date,
    s_active.effective_date AS reactivation_date,
    -- Calculate days between dates (adjust function for your SQL dialect)
    DATEDIFF(day, s_termed.term_date, s_active.effective_date) AS days_between
FROM
    subscriptions s_termed
INNER JOIN
    subscriptions s_active 
    ON s_termed.subscriber_id = s_active.subscriber_id
WHERE
    -- Target your specific group
    s_termed.group_id = 'YOUR_TARGET_GROUP_ID'
    -- Only include terminated records
    AND s_termed.status = 'termed'
    -- Only include reactivation records that happen after termination
    AND s_active.status = 'active'
    AND s_active.effective_date > s_termed.term_date
    -- Ensure we get the FIRST reactivation after each termination
    AND NOT EXISTS (
        SELECT 1
        FROM subscriptions s_mid
        WHERE s_mid.subscriber_id = s_termed.subscriber_id
          AND s_mid.status = 'active'
          AND s_mid.effective_date > s_termed.term_date
          AND s_mid.effective_date < s_active.effective_date
    )
ORDER BY
    s_termed.subscriber_id,
    s_termed.term_date;

How this works: #

• We join the table to itself, linking each terminated record to active records for the same subscriber.
• The NOT EXISTS clause filters out any reactivations that aren't the first one after a termination, so you don't get multiple matches for a single termination.

Method 2: Window Functions (LEAD) for Cleaner Logic #

If your SQL dialect supports window functions (most modern databases do), this method is more concise. We use LEAD to look ahead to the next record for each subscriber:

WITH ordered_subs AS (
    SELECT
        subscriber_id,
        group_id,
        status,
        term_date,
        effective_date,
        -- Grab the next active effective date for the subscriber
        LEAD(CASE WHEN status = 'active' THEN effective_date END) OVER (
            PARTITION BY subscriber_id
            ORDER BY effective_date
        ) AS next_active_date,
        -- Grab the next status to confirm it's an activation
        LEAD(status) OVER (
            PARTITION BY subscriber_id
            ORDER BY effective_date
        ) AS next_status
    FROM
        subscriptions
    WHERE
        group_id = 'YOUR_TARGET_GROUP_ID'
        AND status IN ('termed', 'active') -- Focus only on relevant statuses
)
SELECT
    subscriber_id,
    term_date,
    next_active_date AS reactivation_date,
    DATEDIFF(day, term_date, next_active_date) AS days_between
FROM
    ordered_subs
WHERE
    status = 'termed'
    AND next_status = 'active' -- Ensure the next record is a reactivation
    AND next_active_date IS NOT NULL;

How this works: #

• The CTE (ordered_subs) sorts each subscriber's records by effective_date and uses LEAD to pull in the next active date and status.
• We then filter to only terminated records where the next status is active, giving us direct pairs of termination and reactivation.

Key Notes: #

• Date Difference Functions: Adjust the date calculation based on your database:
  • MySQL: DATEDIFF(s_active.effective_date, s_termed.term_date)
  • PostgreSQL: DATE_PART('day', s_active.effective_date - s_termed.term_date)
  • SQL Server: DATEDIFF(day, s_termed.term_date, s_active.effective_date)
  • Oracle: TRUNC(s_active.effective_date) - TRUNC(s_termed.term_date)
• Multiple Terminations: Both methods will capture every termination-reactivation pair for subscribers who've been terminated and reactivated multiple times.
• Edge Cases: If a subscriber has a termination but no subsequent reactivation, they'll be excluded from the results (which is what we want per your requirement).

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