如何对Client治疗Episode表分组，筛选2021年第一季度首次入院且历史Discharge_date无空值的非活跃客户记录

Got it, let's break down how to solve this query problem step by step. Based on your requirements and sample data, here's a solution that should work with standard SQL (tweakable for specific databases like PostgreSQL or SQL Server if needed):

Solution Code #

WITH client_episodes AS (
    -- Clean and convert date fields to proper date types
    SELECT 
        Client,
        Episode,
        -- Convert admission date string to date type (MM-DD-YYYY format)
        TO_DATE(Admission_date, 'MM-DD-YYYY') AS Admission_date,
        -- Handle discharge status: mark 'In Process'/'Null' as incomplete (NULL)
        CASE 
            WHEN Discharge_date IN ('Null', 'In Process') OR Discharge_date IS NULL THEN NULL
            ELSE TO_DATE(Discharge_date, 'MM-DD-YYYY')
        END AS Discharge_date
    FROM your_table_name -- Replace with your actual table name
),
ranked_episodes AS (
    SELECT 
        *,
        -- Assign a rank to each episode per client (ordered by admission date)
        ROW_NUMBER() OVER (PARTITION BY Client ORDER BY Admission_date) AS episode_rank,
        -- Get the discharge date of the previous episode for the client
        LAG(Discharge_date) OVER (PARTITION BY Client ORDER BY Admission_date) AS prev_discharge_date,
        -- Check if any prior episode has an incomplete discharge (NULL)
        MAX(CASE WHEN Discharge_date IS NULL THEN 1 ELSE 0 END) 
            OVER (PARTITION BY Client ORDER BY Admission_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 
            AS has_prev_incomplete
    FROM client_episodes
)
SELECT 
    Client,
    Episode,
    -- Convert date back to original MM-DD-YYYY string format
    TO_CHAR(Admission_date, 'MM-DD-YYYY') AS Admission_date,
    -- Restore original discharge status display
    CASE 
        WHEN Discharge_date IS NULL THEN 'In Process'
        ELSE TO_CHAR(Discharge_date, 'MM-DD-YYYY')
    END AS Discharge_date
FROM ranked_episodes
WHERE 
    -- Filter for Q1 2021 admissions
    Admission_date BETWEEN '2021-01-01' AND '2021-03-31'
    -- Ensure this is the client's first admission in Q1 2021
    AND episode_rank = (
        SELECT MIN(episode_rank)
        FROM ranked_episodes re
        WHERE re.Client = ranked_episodes.Client
          AND re.Admission_date BETWEEN '2021-01-01' AND '2021-03-31'
    )
    -- Exclude clients with any incomplete prior episodes
    AND has_prev_incomplete = 0
    -- Ensure admission is after the previous episode's discharge
    AND Admission_date > prev_discharge_date;

How It Works #

Let's walk through each component:

1. client_episodes CTE:

   • Converts string-based admission/discharge dates to actual date types for proper comparison.
   • Normalizes incomplete discharge statuses (both Null strings, actual SQL NULL, and In Process) into a single NULL value for easier checking.

2. ranked_episodes CTE:

   • ROW_NUMBER() assigns an order to each episode per client, sorted by admission date—this helps identify the first episode in Q1 2021.
   • LAG() pulls the discharge date of the client's previous episode, which we use to verify the current admission is not overlapping.
   • The MAX(CASE...) window function checks if any episode before the current one has an incomplete discharge. If so, it returns 1; otherwise 0.

3. Main Query:

   • Filters for admissions in Q1 2021 (Jan 1 to Mar 31, 2021).
   • Ensures we only select the client's first admission in that quarter.
   • Excludes any client who had an incomplete discharge in prior episodes.
   • Verifies the current admission date is after the previous episode's discharge date.
   • Converts dates back to the original string format for consistency with your sample output.

Verification Against Sample Data #

For client y:

• Episode 3 is the first admission in Q1 2021 (Feb 1, 2021).
• All prior episodes (1 and 2) have complete discharge dates.
• Feb 1, 2021 is after Nov 23, 2020 (the discharge date of episode 2).

This matches your expected output exactly. Client x is excluded because their first episode has a Null discharge date, violating the "no incomplete prior episodes" rule.

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