如何用SQL实现Pandas中groupby+transform生成PairRef列的逻辑？

核心思路 #

要生成PairRef列，核心是按TagNo分组，将组内的TagLetter排序后用/拼接，再与TagNo组合。不同SQL方言的字符串聚合函数略有差异，以下是主流数据库的实现示例（包含TagNo和TagLetter的提取逻辑）：

1. PostgreSQL 实现 #

PostgreSQL的STRING_AGG()支持聚合时直接排序，结合窗口函数可完成需求：

SELECT
    TagName,
    Value,
    -- 提取TagNo（去除末尾字母）和TagLetter（末尾单个字母）
    REGEXP_REPLACE(TagName, '[A-Za-z]$', '') AS TagNo,
    RIGHT(TagName, 1) AS TagLetter,
    -- 生成PairRef：TagNo + 排序拼接后的字母串
    CONCAT(
        REGEXP_REPLACE(TagName, '[A-Za-z]$', ''),
        '-',
        STRING_AGG(RIGHT(TagName, 1), '/' ORDER BY RIGHT(TagName, 1)) OVER (PARTITION BY REGEXP_REPLACE(TagName, '[A-Za-z]$', ''))
    ) AS PairRef
FROM your_table;

2. MySQL 8.0+ 实现 #

MySQL 8.0及以上支持窗口函数结合GROUP_CONCAT()排序：

SELECT
    TagName,
    Value,
    REGEXP_REPLACE(TagName, '[A-Za-z]$', '') AS TagNo,
    RIGHT(TagName, 1) AS TagLetter,
    CONCAT(
        REGEXP_REPLACE(TagName, '[A-Za-z]$', ''),
        '-',
        GROUP_CONCAT(RIGHT(TagName, 1) ORDER BY RIGHT(TagName, 1) SEPARATOR '/') OVER (PARTITION BY REGEXP_REPLACE(TagName, '[A-Za-z]$', ''))
    ) AS PairRef
FROM your_table;

如果是MySQL 5.x（不支持窗口函数），需先聚合再关联：

WITH tag_groups AS (
    SELECT
        REGEXP_REPLACE(TagName, '[A-Za-z]$', '') AS TagNo,
        GROUP_CONCAT(RIGHT(TagName, 1) ORDER BY RIGHT(TagName, 1) SEPARATOR '/') AS letter_concat
    FROM your_table
    GROUP BY TagNo
)
SELECT
    t.TagName,
    t.Value,
    g.TagNo,
    RIGHT(t.TagName, 1) AS TagLetter,
    CONCAT(g.TagNo, '-', g.letter_concat) AS PairRef
FROM your_table t
JOIN tag_groups g ON REGEXP_REPLACE(t.TagName, '[A-Za-z]$', '') = g.TagNo;

3. SQL Server 实现 #

SQL Server 2017+支持STRING_AGG()，通过WITHIN GROUP指定排序规则：

SELECT
    TagName,
    Value,
    LEFT(TagName, LEN(TagName)-1) AS TagNo,
    RIGHT(TagName, 1) AS TagLetter,
    CONCAT(
        LEFT(TagName, LEN(TagName)-1),
        '-',
        STRING_AGG(RIGHT(TagName, 1), '/' WITHIN GROUP (ORDER BY RIGHT(TagName, 1))) OVER (PARTITION BY LEFT(TagName, LEN(TagName)-1))
    ) AS PairRef
FROM your_table;

附：参考Pandas代码 #

import pandas as pd

# 示例数据
df = pd.DataFrame({
    'TagName': ['123A', '123B', '456X', '456Y', '456Z'],
    'Value': [10, 20, 30, 40, 50]
})

# 提取TagNo和TagLetter
df['TagNo'] = df['TagName'].str.extract(r'(\d+)', expand=False)
df['TagLetter'] = df['TagName'].str.extract(r'([A-Za-z])$', expand=False)

# 生成PairRef
df['PairRef'] = df.groupby('TagNo')['TagLetter'].transform(
    lambda x: f"{x.name}-{'/'.join(sorted(x))}"
)

print(df)

原始表与转换后表示例 #

原始表 #

转换后表 #

内容的提问来源于stack exchange，提问作者Rafał Nojek