如何基于visits数据集每月为患者匹配过往最多7次就诊的主诊Provider

• 从2020年1月1日起，每月1日为每位患者回溯最多7次过往就诊（无时间范围限制），匹配就诊量最多的Provider作为当月关联Provider
• 规则细节：
  1. 患者就诊次数不足7次时，规则依然适用
  2. 若多个Provider就诊量持平，选取最近接诊的Provider
  3. 允许患者当月无就诊记录

示例基础数据 #

ApptDate | Patient | Provider | ApptRank | ApptProvRank |
1/15/2020|    A    |    BOB   |    1     |      1       |
3/01/2020|    A    |    BOB   |    2     |      2       |
3/08/2020|    A    |    BOB   |    3     |      3       |
3/20/2020|    A    |    BOB   |    4     |      4       |
4/01/2020|    A    |    BOB   |    5     |      5       |
4/15/2020|    A    |    SMITH |    6     |      1       |
6/07/2020|    A    |    SMITH |    7     |      2       |
6/21/2020|    A    |    BOB   |    8     |      6       |
7/01/2020|    A    |    JANE  |    9     |      1       |
7/15/2020|    A    |    JANE  |    10    |      2       |
7/21/2020|    A    |    JANE  |    11    |      3       |
8/01/2020|    A    |    JANE  |    12    |      4       |
8/15/2020|    A    |    JANE  |    13    |      5       |
9/20/2020|    A    |    JANE  |    14    |      6       |

已完成的中间处理结果 #

Month    | Patient | Provider |   Appts    |Cumulative Appt Rank | Cumulative Appt Provider Rank
1/1/2020 |     A   |    BOB   |     1      |   1                 |   1
2/1/2020 |     A   |    NULL  |    NULL    |  NULL               |   NULL
3/1/2020 |     A   |    BOB   |     3      |   4                 |   4
4/1/2020 |     A   |    BOB   |     1      |   5                 |   5
4/1/2020 |     A   |    SMITH |     1      |   6                 |   1
5/1/2020 |     A   |    NULL  |    NULL    |  NULL               |   NULL
6/1/2020 |     A   |    SMITH |     1      |   7                 |   2
6/1/2020 |     A   |    BOB   |     1      |   8                 |   6
7/1/2020 |     A   |    JANE  |     3      |   11                |   3
8/1/2020 |     A   |    JANE  |     2      |   13                |   5
9/1/2020 |     A   |    JANE  |     2      |   14                |   6

期望输出格式 #

Month    | Patient | Provider | Appt Count (of Past 7)
2/1/2020 |     A   |    BOB   |     1      |            ----> starting 2/1/2020 for the 'lookback'
3/1/2020 |     A   |    BOB   |     1      |
4/1/2020 |     A   |    BOB   |     4      |
5/1/2020 |     A   |    BOB   |     5      |  
6/1/2020 |     A   |    BOB   |     5      |  
7/1/2020 |     A   |    BOB   |     5      |  
8/1/2020 |     A   |    JANE  |     3      |  
9/1/2020 |     A   |    JANE  |     5      |  
10/1/2020|     A   |    JANE  |     6      |

实现方案（以SQL为例） #

步骤1：生成每月1日的时间序列表 #

为每个患者生成从2020-01-01开始的每月1日记录，覆盖所有需要统计的月份：

WITH date_series AS (
    SELECT 
        generate_series(
            '2020-01-01'::DATE,
            (SELECT MAX(ApptDate)::DATE + INTERVAL '1 month' FROM visits)::DATE,
            '1 month'
        ) AS month_start
),
patient_month AS (
    SELECT DISTINCT
        ds.month_start,
        v.Patient
    FROM date_series ds
    CROSS JOIN (SELECT DISTINCT Patient FROM visits) v
)

步骤2：关联原始就诊数据，筛选每月1日前的最多7次就诊 #

为每个患者的每月1日，找出该日期之前的最近7次就诊：

, patient_past_visits AS (
    SELECT
        pm.month_start,
        pm.Patient,
        v.Provider,
        v.ApptDate,
        v.ApptRank,
        ROW_NUMBER() OVER (PARTITION BY pm.Patient, pm.month_start ORDER BY v.ApptDate DESC) AS rn
    FROM patient_month pm
    LEFT JOIN visits v 
        ON pm.Patient = v.Patient
        AND v.ApptDate < pm.month_start
)
, top7_visits AS (
    SELECT * FROM patient_past_visits WHERE rn <=7
)

步骤3：统计每个Provider的就诊量，并处理平局规则 #

对每个患者-月份，统计Top7就诊中各Provider的次数，按就诊量降序、最近就诊日期降序排序，取优先级最高的Provider：

, provider_stats AS (
    SELECT
        month_start,
        Patient,
        Provider,
        COUNT(*) AS appt_count,
        MAX(ApptDate) AS latest_appt_date,
        ROW_NUMBER() OVER (
            PARTITION BY month_start, Patient 
            ORDER BY COUNT(*) DESC, MAX(ApptDate) DESC
        ) AS rank
    FROM top7_visits
    WHERE Provider IS NOT NULL
    GROUP BY month_start, Patient, Provider
)

步骤4：生成最终结果 #

筛选出每个患者-月份排名第一的Provider，处理无就诊记录的情况：

SELECT
    TO_CHAR(pm.month_start, 'MM/DD/YYYY') AS Month,
    pm.Patient,
    COALESCE(ps.Provider, 'NULL') AS Provider,
    COALESCE(ps.appt_count, 0) AS "Appt Count (of Past 7)"
FROM patient_month pm
LEFT JOIN provider_stats ps 
    ON pm.month_start = ps.month_start
    AND pm.Patient = ps.Patient
    AND ps.rank = 1
ORDER BY pm.Patient, pm.month_start;

说明 #

• 上述SQL基于PostgreSQL语法，其他数据库可调整generate_series（如MySQL用递归CTE生成时间序列）
• 若需保留示例中10/1/2020这类无就诊的月份，需扩展时间序列的结束日期
• 平局处理通过MAX(ApptDate) DESC确保就诊量相同时取最近接诊的Provider

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