Oracle SQL需求：统计重复次数≥2的UNIQUE_PROVIDER_PATIENT_COMBO字段

Hey there! Let's work through this together. You're trying to find records where the same provider-patient pair (your UNIQUE_PROVIDER_PATIENT_COMBO) appears at least twice between January 1 and March 31, 2018, and you've already got a great starting query. The key here is first identifying which combos are duplicates, then pulling all the related records for those combos.

Method 1: Using a Common Table Expression (CTE) (Since You Tried This Approach) #

First, we'll create a CTE that counts how many times each UNIQUE_PROVIDER_PATIENT_COMBO appears. Then we'll join this back to your original query to only keep combos with a count ≥ 2.

WITH combo_counts AS (
    SELECT 
        prov.PROV_ID || ' + ' || pat.PAT_ID AS unique_provider_patient_combo,
        COUNT(*) AS combo_count
    FROM CMRCL_TFH.PATIENT pat
    INNER JOIN CMRCL_TFH.PAT_ENC enc ON pat.PAT_ID = enc.PAT_ID
    INNER JOIN CMRCL_TFH.CLARITY_DEP dep ON enc.DEPARTMENT_ID = dep.DEPARTMENT_ID
    INNER JOIN CLARITY_SER prov ON enc.VISIT_PROV_ID = prov.PROV_ID
    WHERE enc.CONTACT_DATE BETWEEN '01-JAN-18' AND '31-MAR-18'
    GROUP BY prov.PROV_ID || ' + ' || pat.PAT_ID
    HAVING COUNT(*) >= 2
)
SELECT 
    pat.PAT_NAME "PATIENT",
    pat.PAT_ID,
    prov.PROV_NAME "VISIT PROVIDER",
    prov.PROV_ID,
    enc.CONTACT_DATE "VISIT DATE",
    prov.PROV_NAME || ' + ' || pat.PAT_NAME AS "PROVIDER + PATIENT",
    prov.PROV_ID || ' + ' || pat.PAT_ID AS "UNIQUE_PROVIDER_PATIENT_COMBO"
FROM CMRCL_TFH.PATIENT pat
INNER JOIN CMRCL_TFH.PAT_ENC enc ON pat.PAT_ID = enc.PAT_ID
INNER JOIN CMRCL_TFH.CLARITY_DEP dep ON enc.DEPARTMENT_ID = dep.DEPARTMENT_ID
INNER JOIN CLARITY_SER prov ON enc.VISIT_PROV_ID = prov.PROV_ID
INNER JOIN combo_counts cc ON cc.unique_provider_patient_combo = prov.PROV_ID || ' + ' || pat.PAT_ID
WHERE enc.CONTACT_DATE BETWEEN '01-JAN-18' AND '31-MAR-18'
ORDER BY "PROVIDER + PATIENT";

Method 2: Using Window Functions (More Concise for Oracle) #

Window functions let you calculate the count directly in the main query without a separate CTE. This is often cleaner for this type of problem:

SELECT *
FROM (
    SELECT 
        pat.PAT_NAME "PATIENT",
        pat.PAT_ID,
        prov.PROV_NAME "VISIT PROVIDER",
        prov.PROV_ID,
        enc.CONTACT_DATE "VISIT DATE",
        prov.PROV_NAME || ' + ' || pat.PAT_NAME AS "PROVIDER + PATIENT",
        prov.PROV_ID || ' + ' || pat.PAT_ID AS "UNIQUE_PROVIDER_PATIENT_COMBO",
        COUNT(*) OVER (PARTITION BY prov.PROV_ID, pat.PAT_ID) AS combo_count
    FROM CMRCL_TFH.PATIENT pat
    INNER JOIN CMRCL_TFH.PAT_ENC enc ON pat.PAT_ID = enc.PAT_ID
    INNER JOIN CMRCL_TFH.CLARITY_DEP dep ON enc.DEPARTMENT_ID = dep.DEPARTMENT_ID
    INNER JOIN CLARITY_SER prov ON enc.VISIT_PROV_ID = prov.PROV_ID
    WHERE enc.CONTACT_DATE BETWEEN '01-JAN-18' AND '31-MAR-18'
) subquery
WHERE combo_count >= 2
ORDER BY "PROVIDER + PATIENT";

Quick Notes: #

• I simplified the combo string to ' + ' instead of ' ' || '+' || ' ' for readability—both work, but the shorter version is cleaner.
• In the window function method, PARTITION BY prov.PROV_ID, pat.PAT_ID does the same thing as grouping by the concatenated combo, but it's more efficient (Oracle doesn't have to process the string concatenation for grouping).
• We don't need DISTINCT in your original query anymore because we're filtering for duplicates, which inherently means multiple records exist for the combo.

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