DuckDB中能否基于索引列执行宽表透视？求SQL实现方案

问题背景 #

我正在尝试基于两个DataFrame生成目标DataFrame，但频繁遇到内存不足问题。此前尝试用Python处理但内存问题仍未解决，转而使用Python中的DuckDB。

数据结构 #

event_df #

import pandas as pd
import numpy as np

event_df = {'ID_1': [1, 1, 1, 2, 3],
            'ID_2': [1, 1, 2, 1, 1],
            'Timestamp': ['1984-05-11 14:30:00', 
                          '1984-05-11 15:30:00', 
                          '1990-12-11 09:10:00', 
                          '1975-01-08 23:23:00', 
                          '1984-05-11 14:30:00'],
            'Event': [0, 1, 0, 1, 1]
           }
event_df = pd.DataFrame(event_df)
event_df['Timestamp'] = event_df['Timestamp'].astype('datetime64[s]')

lab_df #

lab_df = {'ID_1': [1, 1, 1, 2, 3],
          'ID_2': [1, 1, 1, 1, 1],
          'Timestamp_Lab': ['1984-05-11 14:00:00', 
                            '1984-05-11 14:15:00', 
                            '1984-05-11 15:00:00',
                            '1975-01-08 20:00:00',
                            '1984-05-11 14:00:00'],
          'Hemoglobin': [np.nan, 14, 13, 10, 11],
          'Leukocytes': [123, np.nan, 123, 50, 110],
          'Platelets': [50, 50, 50, 110, 50]
         }
lab_df = pd.DataFrame(lab_df)
lab_df['Timestamp_Lab'] = lab_df['Timestamp_Lab'].astype('datetime64[s]')

期望结果 #

result = {'ID_1': [1, 1, 1, 2, 3],
          'ID_2': [1, 1, 2, 1, 1],
          'Timestamp': ['1984-05-11 14:30:00', 
                        '1984-05-11 15:30:00', 
                        '1990-12-11 09:10:00', 
                        '1975-01-08 23:23:00', 
                        '1984-05-11 14:30:00'],
          'Event': [0, 1, 0, 1, 1],
          'Hemoglobin_1': [14, 14, np.nan, 10, 11],
          'Hemoglobin_2': [np.nan, 13, np.nan, np.nan, np.nan],
          'Leukocytes_1': [123, 123, np.nan, 50, 110],
          'Leukocytes_2': [np.nan, 123, np.nan, np.nan, np.nan],
          'Platelets_1': [50, 50, np.nan, 110, 50],
          'Platelets_2': [50, 50, np.nan, np.nan, np.nan],
          'Platelets_3': [np.nan, 50, np.nan, np.nan, np.nan]          
}
result = pd.DataFrame(result)

此前尝试 #

Python处理逻辑 #

先按ID_2连接两个DataFrame，过滤掉Timestamp_Lab大于event_df.Timestamp的行，再按Timestamp和ID_2分组生成索引，最后透视转宽表：

parameter = merged_df.columns[5:] # 选择所有参数列
merged_df['Index'] = merged_df.groupby(['Timestamp', 'ID_2']).cumcount() + 1
join = merged_df.pivot_table(index='Timestamp', columns='Index', values = parameter)
event_df = event_df[event_df.columns[0:5]]
event_df = event_df.merge(right=join, how='left',on= ['Timestamp', 'ID_2'])

SQL生成索引逻辑 #

SELECT *,
    ROW_NUMBER() OVER (PARTITION BY event_df.ID_2, Timestamp ORDER BY Timestamp_Lab) AS GroupIndex
FROM event_df
    LEFT JOIN lab_df
        ON event_df.ID_2 = lab_df.ID_2 AND event_df.Timestamp >= lab_df.Timestamp_Lab

但尝试透视时触发ParserError，错误代码如下：

SELECT *
FROM (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY event_df.ID_2, Timestamp ORDER BY Timestamp_Lab) AS GroupIndex
    FROM (
        SELECT *
          FROM event_df
               LEFT JOIN lab_df
                  ON event_df.ID_2 = lab_df.ID_2 AND event_df.Timestamp >= lab_df.Timestamp_Lab
    ) subquery
)
PIVOT (
    ON GroupIndex
    USING (Hemoglobin)
    GROUP BY even_df.ID_2, Timestamp
) AS pivoted_data

问题 #

能否在SQL中实现类似Python中基于索引列的宽表透视？如果可以，正确的写法是怎样的？即使仅针对单个参数（如Hemoglobin）实现也能帮到我。

解决方案（针对Hemoglobin） #

在DuckDB中，用CASE表达式配合聚合函数实现透视更稳定灵活，以下是针对Hemoglobin的正确写法：

WITH joined_data AS (
    SELECT 
        e.ID_1,
        e.ID_2,
        e.Timestamp,
        e.Event,
        l.Hemoglobin,
        ROW_NUMBER() OVER (PARTITION BY e.ID_2, e.Timestamp ORDER BY l.Timestamp_Lab) AS GroupIndex
    FROM event_df e
    LEFT JOIN lab_df l
        ON e.ID_2 = l.ID_2 AND e.Timestamp >= l.Timestamp_Lab
)
SELECT 
    ID_1,
    ID_2,
    Timestamp,
    Event,
    MAX(CASE WHEN GroupIndex = 1 THEN Hemoglobin END) AS Hemoglobin_1,
    MAX(CASE WHEN GroupIndex = 2 THEN Hemoglobin END) AS Hemoglobin_2,
    MAX(CASE WHEN GroupIndex = 3 THEN Hemoglobin END) AS Hemoglobin_3
FROM joined_data
GROUP BY ID_1, ID_2, Timestamp, Event
ORDER BY ID_1, Timestamp;

说明 #

1. CTE预处理：先用joined_data完成关联和分组索引生成，只保留必要字段，减少冗余数据占用内存。
2. CASE表达式实现透视：用CASE配合MAX聚合函数（每个分组索引对应唯一值）实现透视，比DuckDB原生PIVOT更直观，避免语法错误。
3. 分组排序：按ID_1, ID_2, Timestamp, Event分组，确保每个事件行对应正确结果，排序后输出顺序与期望一致。

如果需要扩展到其他参数，只需在SELECT和CASE部分添加对应列即可：

WITH joined_data AS (
    SELECT 
        e.ID_1,
        e.ID_2,
        e.Timestamp,
        e.Event,
        l.Hemoglobin,
        l.Leukocytes,
        l.Platelets,
        ROW_NUMBER() OVER (PARTITION BY e.ID_2, e.Timestamp ORDER BY l.Timestamp_Lab) AS GroupIndex
    FROM event_df e
    LEFT JOIN lab_df l
        ON e.ID_2 = l.ID_2 AND e.Timestamp >= l.Timestamp_Lab
)
SELECT 
    ID_1,
    ID_2,
    Timestamp,
    Event,
    MAX(CASE WHEN GroupIndex = 1 THEN Hemoglobin END) AS Hemoglobin_1,
    MAX(CASE WHEN GroupIndex = 2 THEN Hemoglobin END) AS Hemoglobin_2,
    MAX(CASE WHEN GroupIndex = 3 THEN Hemoglobin END) AS Hemoglobin_3,
    MAX(CASE WHEN GroupIndex = 1 THEN Leukocytes END) AS Leukocytes_1,
    MAX(CASE WHEN GroupIndex = 2 THEN Leukocytes END) AS Leukocytes_2,
    MAX(CASE WHEN GroupIndex = 3 THEN Leukocytes END) AS Leukocytes_3,
    MAX(CASE WHEN GroupIndex = 1 THEN Platelets END) AS Platelets_1,
    MAX(CASE WHEN GroupIndex = 2 THEN Platelets END) AS Platelets_2,
    MAX(CASE WHEN GroupIndex = 3 THEN Platelets END) AS Platelets_3
FROM joined_data
GROUP BY ID_1, ID_2, Timestamp, Event
ORDER BY ID_1, Timestamp;

这种写法内存占用更低，适合大数据量场景，同时完全匹配你需要的透视逻辑。

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