生成诊断分组的检测组合并找出高频组合的SQL解决方案问询

Let's break down how to fix your SQL to handle all test group sizes and then compute the most frequent combination. Here's a complete, optimized solution that covers all your requirements:

WITH patient_with_row AS (
    -- Assign row numbers to tests within each (pat_id, diagnosis) group
    SELECT 
        pat_id, 
        diagnosis, 
        tests,
        ROW_NUMBER() OVER (PARTITION BY pat_id, diagnosis ORDER BY tests) AS rn
    FROM patient
),
grouped_combinations AS (
    -- Case 1: Groups with exactly 1 test (fill remaining spots with NULL)
    SELECT 
        pat_id, 
        diagnosis,
        tests AS test1,
        NULL AS test2,
        NULL AS test3
    FROM patient_with_row
    WHERE rn = 1
    AND (SELECT COUNT(*) FROM patient_with_row p2 WHERE p2.pat_id = patient_with_row.pat_id AND p2.diagnosis = patient_with_row.diagnosis) = 1
    
    UNION ALL
    
    -- Case 2: Groups with exactly 2 tests (fill third spot with NULL)
    SELECT 
        p1.pat_id, 
        p1.diagnosis,
        p1.tests AS test1,
        p2.tests AS test2,
        NULL AS test3
    FROM patient_with_row p1
    JOIN patient_with_row p2 
        ON p1.pat_id = p2.pat_id 
        AND p1.diagnosis = p2.diagnosis 
        AND p1.rn < p2.rn -- Ensure we only get one unique pair (no duplicates like B,A)
    WHERE (SELECT COUNT(*) FROM patient_with_row p3 WHERE p3.pat_id = p1.pat_id AND p3.diagnosis = p1.diagnosis) = 2
    
    UNION ALL
    
    -- Case 3: Groups with 3+ tests (generate all unique 3-test subsets)
    SELECT 
        p1.pat_id, 
        p1.diagnosis,
        p1.tests AS test1,
        p2.tests AS test2,
        p3.tests AS test3
    FROM patient_with_row p1
    JOIN patient_with_row p2 
        ON p1.pat_id = p2.pat_id 
        AND p1.diagnosis = p2.diagnosis 
        AND p1.tests < p2.tests
    JOIN patient_with_row p3 
        ON p2.pat_id = p3.pat_id 
        AND p2.diagnosis = p3.diagnosis 
        AND p2.tests < p3.tests
),
combination_frequencies AS (
    -- Calculate how often each combination appears across all groups
    SELECT 
        test1, 
        test2, 
        test3, 
        COUNT(*) AS occurrence_count,
        MAX(COUNT(*)) OVER () AS max_occurrence
    FROM grouped_combinations
    GROUP BY test1, test2, test3
)
-- Return all combinations that have the highest frequency
SELECT 
    test1, 
    test2, 
    test3, 
    occurrence_count
FROM combination_frequencies
WHERE occurrence_count = max_occurrence;

How This Works: #

1. patient_with_row CTE: Adds a row number to each test entry within its (pat_id, diagnosis) group. This helps us easily reference individual tests in smaller groups and avoid duplicate combinations.

2. grouped_combinations CTE:

   • 1-test groups: Picks the single test and fills the other two positions with NULL.
   • 2-test groups: Joins the two tests (using row numbers to ensure only one unique pair) and adds NULL for the third position.
   • 3+ test groups: Uses your original self-join logic (with < comparisons) to generate all unique 3-test subsets (no duplicate permutations like (A,B,C) and (B,A,C)).

3. combination_frequencies CTE: Counts how many times each combination appears across all (pat_id, diagnosis) groups, and computes the maximum occurrence count using a window function.

4. Final Query: Returns all combinations that match the maximum occurrence count (handles ties if multiple combinations are equally frequent).

Example Output: #

For your sample cases:

• pat_id=1003, diagnosis=Bronchitis will generate (X-Ray, NULL, NULL)
• pat_id=1002, diagnosis=Pneumonia will generate (MRI, Eliza, NULL)
• pat_id=1001, diagnosis=Tonsil (4 tests) will generate 4 unique 3-test combinations
• All combinations are then counted, and the most frequent ones are returned.

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