按年龄列分组展示患者医疗操作的MySQL自动化实现需求

现有activities_procedures表存储患者已执行的医疗操作，需实现以下核心需求：

• 按患者生命周期对应的年龄区间，以列维度展示操作执行情况
• 结合操作的强制年龄要求判断状态：0=非强制未执行、1=强制已执行、2=非强制已执行
• 自动适配cycles表中所有生命周期分类，无需硬编码年龄区间
• 支持半年度/五年期等周期性操作，同一单元格返回多状态数组（如[2,1]格式）

涉及表结构 #

• cycles：id, cycle（定义生命周期分类，如early childhood、infancy等）
• cupsrutas：id, nom_servicio, cups, ciclos_id（关联生命周期的医疗服务）
• CUPS_AGES：id, AGE, PERIOD（A=年/M=月）, cupsrutas_id（操作的强制年龄范围）
• activities_procedures：存储患者已执行操作（核心字段假设为patient_id, cups, execution_age, execution_period）

1. 动态映射生命周期与年龄区间 #

先将所有强制年龄统一转换为月单位，避免年/月混淆，同时提取每个生命周期对应的所有年龄节点：

WITH lifecycle_ages AS (
    SELECT 
        c.id AS cycle_id,
        c.cycle,
        cr.cups,
        cr.nom_servicio,
        -- 统一转换为月：年*12 + 月
        CASE WHEN ca.PERIOD = 'A' THEN ca.AGE * 12 ELSE ca.AGE END AS age_in_months,
        ca.AGE,
        ca.PERIOD
    FROM cycles c
    JOIN cupsrutas cr ON c.id = cr.ciclos_id
    JOIN CUPS_AGES ca ON cr.id = ca.cupsrutas_id
),
lifecycle_age_ranges AS (
    SELECT 
        cycle_id,
        cycle,
        cups,
        nom_servicio,
        -- 收集当前生命周期下的所有年龄节点，按顺序排列
        ARRAY_AGG(DISTINCT age_in_months ORDER BY age_in_months) AS age_nodes
    FROM lifecycle_ages
    GROUP BY cycle_id, cycle, cups, nom_servicio
)

2. 关联操作记录，判断每个年龄节点的状态 #

遍历每个生命周期的年龄节点，结合患者操作记录判断状态，同时收集周期性操作的多状态结果：

, patient_operation_status AS (
    SELECT 
        ap.patient_id,
        lar.cycle,
        lar.cups,
        lar.nom_servicio,
        age_node,
        -- 判断当前年龄节点是否为该操作的强制节点
        EXISTS (
            SELECT 1 FROM lifecycle_ages la 
            WHERE la.cups = lar.cups AND la.age_in_months = age_node
        ) AS is_mandatory,
        -- 收集该年龄节点的所有执行状态，生成数组
        ARRAY_AGG(
            CASE 
                -- 已执行操作的状态判断
                WHEN EXISTS (
                    SELECT 1 FROM activities_procedures ap2 
                    WHERE ap2.patient_id = ap.patient_id 
                      AND ap2.cups = lar.cups 
                      AND CASE WHEN ap2.execution_period = 'A' THEN ap2.execution_age *12 ELSE ap2.execution_age END = age_node
                ) THEN 
                    CASE WHEN EXISTS (
                        SELECT 1 FROM lifecycle_ages la 
                        WHERE la.cups = lar.cups AND la.age_in_months = age_node
                    ) THEN 1 ELSE 2 END
                -- 未执行操作统一返回0
                ELSE 0 
            END
        ) AS status_array
    FROM lifecycle_age_ranges lar
    -- 展开每个生命周期的年龄节点
    CROSS JOIN UNNEST(lar.age_nodes) AS age_node
    LEFT JOIN activities_procedures ap ON lar.cups = ap.cups
    GROUP BY ap.patient_id, lar.cycle, lar.cups, lar.nom_servicio, age_node, is_mandatory
)

3. 动态转置为列维度展示 #

以PostgreSQL为例，使用crosstab函数实现动态列转置；若使用MySQL等数据库，可通过动态SQL拼接CASE WHEN实现相同效果：

-- 生成所有需要转置的列名（格式：生命周期_年龄单位）
, column_names AS (
    SELECT 
        DISTINCT CONCAT(cycle, '_', la.AGE, la.PERIOD) AS column_name,
        la.age_in_months
    FROM lifecycle_ages la
    JOIN cycles c ON la.cycle_id = c.id
    ORDER BY age_in_months
)

-- 生成最终列维度结果
SELECT * FROM crosstab(
    'SELECT 
        patient_id,
        cycle,
        cups,
        nom_servicio,
        CONCAT(cycle, ''_'', la.AGE, la.PERIOD) AS age_column,
        -- 单状态返回数值，多状态返回数组格式
        CASE WHEN ARRAY_LENGTH(pos.status_array, 1) = 1 THEN pos.status_array[1]::TEXT ELSE pos.status_array::TEXT END AS status
    FROM patient_operation_status pos
    JOIN lifecycle_ages la ON pos.cups = la.cups AND pos.age_node = la.age_in_months
    ORDER BY 1,2,3,4',
    'SELECT column_name FROM column_names'
) AS ct (
    patient_id INT,
    cycle TEXT,
    cups TEXT,
    nom_servicio TEXT,
    -- 示例列：根据实际生命周期节点自动生成，动态SQL可省略硬编码
    infancy_6M TEXT, infancy_7M TEXT, infancy_8M TEXT,
    "early childhood_1A" TEXT, "early childhood_2A" TEXT
);

关键说明 #

• 单位统一：将所有年龄转换为月单位，彻底避免年/月单位混淆导致的判断错误
• 全生命周期适配：通过关联cycles与CUPS_AGES自动获取所有生命周期的年龄节点，无需硬编码固定区间
• 周期性操作处理：使用ARRAY_AGG收集同一节点的多次执行状态，数组长度大于1时保留数组格式
• 动态列生成：若不支持crosstab，可通过动态SQL拼接CASE WHEN语句实现列转置

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