如何在MS SQL Server用窗口函数按fund_name分组提取security_name差异值

1. MS SQL 实现方案 #

核心逻辑为按Fund_Name分组计算同组所有Security_Name的最长公共前缀长度，再删除每个字符串的公共前缀部分得到结果，无需预设固定规则即可适配你提供的所有样本场景。

WITH -- 替换下方YourTableName为你的实际表名
SourceData AS (
    SELECT Fund_Name, Security_Name, Desired_Output FROM YourTableName
),
-- 计算每组可检查的最大字符长度、组内行数
GroupBase AS (
    SELECT 
        Fund_Name,
        MIN(LEN(Security_Name)) AS MaxCheckLen,
        COUNT(*) AS GroupRowCount
    FROM SourceData
    GROUP BY Fund_Name
),
-- 生成逐位比较用的数字序列
NumSeries AS (
    SELECT 1 AS Pos
    UNION ALL
    SELECT Pos + 1 FROM NumSeries 
    WHERE Pos <= (SELECT MAX(MaxCheckLen) FROM GroupBase)
),
-- 计算每组最长公共前缀的长度
CommonPrefixLength AS (
    SELECT 
        g.Fund_Name,
        ISNULL(MIN(n.Pos) - 1, 0) AS PrefixLen
    FROM GroupBase g
    LEFT JOIN NumSeries n ON n.Pos <= g.MaxCheckLen
    CROSS APPLY (
        SELECT DISTINCT SUBSTRING(s.Security_Name, n.Pos, 1) AS CharAtPos
        FROM SourceData s
        WHERE s.Fund_Name = g.Fund_Name
    ) t
    GROUP BY g.Fund_Name, g.GroupRowCount
    HAVING COUNT(t.CharAtPos) > 1
)
-- 输出最终结果
SELECT 
    s.Fund_Name,
    s.Security_Name,
    LTRIM(STUFF(s.Security_Name, 1, cp.PrefixLen, '')) AS Actual_Output,
    s.Desired_Output
FROM SourceData s
JOIN CommonPrefixLength cp ON s.Fund_Name = cp.Fund_Name
OPTION (MAXRECURSION 1000); -- 若公共前缀长度超过1000可自行调大该参数

2. Python 实现方案 #

数据量不大的场景下用pandas处理更简洁，直接调用内置方法计算公共前缀即可：

import pandas as pd
from os.path import commonprefix

# 可替换为从数据库/CSV读取数据的逻辑
df = pd.read_sql("SELECT Fund_Name, Security_Name FROM YourTableName", your_db_connection)

def extract_diff_suffix(group):
    # 计算组内最长公共前缀，删除前缀后去除左侧空格
    prefix = commonprefix(group.tolist())
    return group.str.removeprefix(prefix).str.lstrip()

# 按Fund_Name分组计算结果
df["Desired_Output"] = df.groupby("Fund_Name")["Security_Name"].transform(extract_diff_suffix)

print(df)

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