如何获取Pandas DataFrame按session_id分组的各列唯一值及MS SQL实现方案

Pandas 实现方案 #

以下代码可适配任意新增列的场景，无需修改逻辑即可自动生成对应带序号后缀的新列：

import pandas as pd

def reshape_session_data(group):
    result = {}
    for col in group.columns:
        # 提取当前列的唯一非空值
        unique_vals = group[col].dropna().unique()
        for idx, val in enumerate(unique_vals, start=1):
            # 第一个值保留原列名，后续值添加序号后缀
            col_name = col if idx == 1 else f"{col}_{idx}"
            result[col_name] = val
    return pd.Series(result)

# 按session_id分组处理，重置索引得到最终结果
final_df = df.groupby('session_id', group_keys=False).apply(reshape_session_data).reset_index(drop=True)

测试你提供的示例数据，输出结果和你预期的结构完全一致。如果后续出现多patient_id、多patient_name等场景，代码会自动生成patient_id_2、patient_name_2等对应列。

MS SQL 实现方案 #

固定列基础写法 #

如果表结构固定，可直接使用以下条件聚合查询：

WITH distinct_data AS (
    -- 先对全表去重，排除完全重复的无效行
    SELECT DISTINCT session_id, signature, patient_id, patient_name 
    FROM 你的实际表名
),
ranked_data AS (
    -- 对每个session_id下的唯一行编号
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY (SELECT 1)) AS rn
    FROM distinct_data
)
SELECT 
    session_id,
    MAX(CASE WHEN rn = 1 THEN signature END) AS signature,
    MAX(CASE WHEN rn = 2 THEN signature END) AS signature_2,
    MAX(CASE WHEN rn = 1 THEN patient_id END) AS patient_id,
    MAX(CASE WHEN rn = 2 THEN patient_id END) AS patient_id_2,
    MAX(CASE WHEN rn = 1 THEN patient_name END) AS patient_name,
    MAX(CASE WHEN rn = 2 THEN patient_name END) AS patient_name_2
FROM ranked_data
GROUP BY session_id

动态列适配写法 #

如果表结构会频繁新增字段，可使用动态SQL自动适配所有列，无需修改查询逻辑：

DECLARE @cols NVARCHAR(MAX),
        @query NVARCHAR(MAX),
        @table_name NVARCHAR(128) = N'你的实际表名'; -- 替换为实际表名

-- 计算单个session_id下最多有多少条唯一行，确定需要生成的列数
DECLARE @max_rn INT;
DECLARE @max_rn_sql NVARCHAR(MAX) = N'
SELECT @max_rn = MAX(rn)
FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY (SELECT 1)) AS rn
    FROM (SELECT DISTINCT * FROM ' + QUOTENAME(@table_name) + N') t
) t';
EXEC sp_executesql @max_rn_sql, N'@max_rn INT OUTPUT', @max_rn = @max_rn OUTPUT;

-- 生成所有需要聚合的列语句
WITH all_columns AS (
    SELECT name AS col_name
    FROM sys.columns
    WHERE object_id = OBJECT_ID(@table_name)
    AND name != 'session_id' -- 排除分组用的session_id字段
)
SELECT @cols = STRING_AGG(
    CONCAT(
        'MAX(CASE WHEN rn = ', n, ' THEN ', QUOTENAME(col_name), ' END) AS ',
        QUOTENAME(IIF(n = 1, col_name, CONCAT(col_name, '_', n)))
    ), ','
)
FROM all_columns
CROSS JOIN (
    SELECT TOP (@max_rn) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS n
    FROM sys.all_objects
) AS numbers;

-- 拼接最终查询语句
SET @query = N'
WITH distinct_data AS (
    SELECT DISTINCT * FROM ' + QUOTENAME(@table_name) + N'
),
ranked_data AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY (SELECT 1)) AS rn
    FROM distinct_data
)
SELECT session_id, ' + @cols + N'
FROM ranked_data
GROUP BY session_id';

-- 执行查询
EXEC sp_executesql @query;

把代码中的你的实际表名替换为业务表的真实名称即可使用。

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