PostgreSQL中ARRAY_AGG与UNNEST的SQL Server替代方案问题

原PostgreSQL查询语句 #

SELECT
    InvoiceId,
    MatchIssue,
    ids,
    UNNEST(ids) AS Id,
    UNNEST(amounts) AS Amount,
    UNNEST(counterpartyExternalIds) AS counterpartyExternalId,
    UNNEST(companyExternalIds) AS companyExternalId
FROM 
    (SELECT 
         InvoiceId, 
         (CASE WHEN COUNT(distinct InvoiceDate) = 1 THEN '' ELSE 'MATCH ISSUE IN DATES ' END || 
            CASE WHEN COUNT(distinct Currency) = 1 THEN '' ELSE 'MATCH ISSUE IN CURRENCIES ' END || 
            CASE WHEN COUNT (distinct DueDate) = 1 THEN '' ELSE 'MATCH ISSUE IN DUE DATES ' END || 
            CASE WHEN SUM(amount) = 0 THEN '' ELSE 'MATCH ISSUE IN AMOUNTS ' END) AS MatchIssue,
         array_agg(id) ids,
         array_agg(amount) amounts,
         array_agg(counterpartyExternalId) counterpartyExternalIds,
         array_agg(companyExternalId) companyExternalIds
     FROM 
         Invoices
     WHERE 
         invoicestatus = 0 OR invoicestatus = 3
     GROUP BY 
         InvoiceId
     HAVING 
         (NOT COUNT(InvoiceId) = 1) = true) sub

尝试的SQL Server写法（存在数据重复） #

SELECT
    InvoiceId,
    MatchIssue,
    Ids,
    ids_split.[value] AS Id,
    CAST([Amounts].value AS NUMERIC) AS Amount,
    CounterpartyExternalIds.[value] AS CounterpartyExternalId,
    CompanyExternalIds.[value] AS CompanyExternalId
FROM 
    (SELECT 
         InvoiceId, 
         (CASE WHEN COUNT(DISTINCT InvoiceDate) = 1 THEN '' 
                    ELSE 'MATCH ISSUE IN DATES ' 
          END 
          + CASE WHEN COUNT(DISTINCT Currency) = 1 THEN '' 
                    ELSE 'MATCH ISSUE IN CURRENCIES ' 
            END 
          + CASE WHEN COUNT (DISTINCT DueDate) = 1 THEN '' 
                    ELSE 'MATCH ISSUE IN DUE DATES ' 
            END 
          + CASE WHEN SUM(amount) = 0 THEN '' 
                    ELSE 'MATCH ISSUE IN AMOUNTS ' 
            END) AS MatchIssue,
         STRING_AGG(Id, ',') AS Ids,
         STRING_AGG(Amount, ',') AS Amounts,
         STRING_AGG(CounterpartyExternalId, ',') AS CounterpartyExternalIds,
         STRING_AGG(CompanyExternalId, ',') AS CompanyExternalIds
     FROM 
         Invoices
     WHERE 
         InvoiceStatus = 0 OR InvoiceStatus = 3
     GROUP BY 
         InvoiceId
     HAVING 
         COUNT(InvoiceId) <> 1) AS sub
CROSS APPLY 
    STRING_SPLIT(Ids, ',') AS ids_split
CROSS APPLY 
    STRING_SPLIT(Amounts, ',') AS Amounts
CROSS APPLY 
    STRING_SPLIT(CounterpartyExternalIds, ',') AS CounterpartyExternalIds
CROSS APPLY 
    STRING_SPLIT(CompanyExternalIds, ',') AS CompanyExternalIds
WHERE 
    LEN(ids_split.[value]) > 0

问题原因 #

PostgreSQL中同时使用多个UNNEST时，会按数组元素的位置一一对应返回行，比如第一个数组的第1个元素和其他数组的第1个元素组成一行。而上述SQL Server写法中，多次使用CROSS APPLY STRING_SPLIT会产生笛卡尔积，导致每个元素和其他所有元素组合，从而出现大量重复数据。

解决方案 #

方案1：SQL Server 2022及以上版本（推荐） #

利用STRING_SPLIT新增的enable_ordinal参数（设置为1时返回元素的位置序号），通过序号关联各个拆分后的结果，实现位置对应：

SELECT
    sub.InvoiceId,
    sub.MatchIssue,
    sub.Ids,
    ids_split.[value] AS Id,
    CAST(amounts_split.[value] AS NUMERIC) AS Amount,
    cpty_split.[value] AS CounterpartyExternalId,
    comp_split.[value] AS CompanyExternalId
FROM 
    (SELECT 
         InvoiceId, 
         (CASE WHEN COUNT(DISTINCT InvoiceDate) = 1 THEN '' 
                    ELSE 'MATCH ISSUE IN DATES ' 
          END 
          + CASE WHEN COUNT(DISTINCT Currency) = 1 THEN '' 
                    ELSE 'MATCH ISSUE IN CURRENCIES ' 
            END 
          + CASE WHEN COUNT(DISTINCT DueDate) = 1 THEN '' 
                    ELSE 'MATCH ISSUE IN DUE DATES ' 
            END 
          + CASE WHEN SUM(amount) = 0 THEN '' 
                    ELSE 'MATCH ISSUE IN AMOUNTS ' 
            END) AS MatchIssue,
         -- 用WITHIN GROUP保证聚合顺序与PostgreSQL的array_agg一致（可根据业务调整排序字段）
         STRING_AGG(Id, ',') WITHIN GROUP (ORDER BY Id) AS Ids,
         STRING_AGG(Amount, ',') WITHIN GROUP (ORDER BY Id) AS Amounts,
         STRING_AGG(CounterpartyExternalId, ',') WITHIN GROUP (ORDER BY Id) AS CounterpartyExternalIds,
         STRING_AGG(CompanyExternalId, ',') WITHIN GROUP (ORDER BY Id) AS CompanyExternalIds
     FROM 
         Invoices
     WHERE 
         InvoiceStatus = 0 OR InvoiceStatus = 3
     GROUP BY 
         InvoiceId
     HAVING 
         COUNT(InvoiceId) <> 1) AS sub
-- 拆分Ids并获取元素序号
CROSS APPLY 
    STRING_SPLIT(sub.Ids, ',', 1) AS ids_split
-- 通过序号关联Amounts的拆分结果
INNER JOIN 
    STRING_SPLIT(sub.Amounts, ',', 1) AS amounts_split ON amounts_split.ordinal = ids_split.ordinal
-- 通过序号关联CounterpartyExternalIds的拆分结果
INNER JOIN 
    STRING_SPLIT(sub.CounterpartyExternalIds, ',', 1) AS cpty_split ON cpty_split.ordinal = ids_split.ordinal
-- 通过序号关联CompanyExternalIds的拆分结果
INNER JOIN 
    STRING_SPLIT(sub.CompanyExternalIds, ',', 1) AS comp_split ON comp_split.ordinal = ids_split.ordinal
WHERE 
    LEN(ids_split.[value]) > 0

方案2：SQL Server 2022以下版本 #

如果无法使用STRING_SPLIT的序号功能，可通过生成序号序列，结合字符串提取实现位置对应（注意：此方法适用于元素数量不超过4的场景，超过则需替换为XML拆分或自定义函数）：

WITH NumberedInvoices AS (
    SELECT 
        InvoiceId,
        MatchIssue,
        Ids,
        Amounts,
        CounterpartyExternalIds,
        CompanyExternalIds,
        -- 计算每个InvoiceId对应的元素总数
        LEN(Ids) - LEN(REPLACE(Ids, ',', '')) + 1 AS ElementCount
    FROM (
        SELECT 
             InvoiceId, 
             (CASE WHEN COUNT(DISTINCT InvoiceDate) = 1 THEN '' 
                        ELSE 'MATCH ISSUE IN DATES ' 
              END 
              + CASE WHEN COUNT(DISTINCT Currency) = 1 THEN '' 
                        ELSE 'MATCH ISSUE IN CURRENCIES ' 
                END 
              + CASE WHEN COUNT(DISTINCT DueDate) = 1 THEN '' 
                        ELSE 'MATCH ISSUE IN DUE DATES ' 
                END 
              + CASE WHEN SUM(amount) = 0 THEN '' 
                        ELSE 'MATCH ISSUE IN AMOUNTS ' 
                END) AS MatchIssue,
             STRING_AGG(Id, ',') WITHIN GROUP (ORDER BY Id) AS Ids,
             STRING_AGG(Amount, ',') WITHIN GROUP (ORDER BY Id) AS Amounts,
             STRING_AGG(CounterpartyExternalId, ',') WITHIN GROUP (ORDER BY Id) AS CounterpartyExternalIds,
             STRING_AGG(CompanyExternalId, ',') WITHIN GROUP (ORDER BY Id) AS CompanyExternalIds
         FROM 
             Invoices
         WHERE 
             InvoiceStatus = 0 OR InvoiceStatus = 3
         GROUP BY 
             InvoiceId
         HAVING 
             COUNT(InvoiceId) <> 1
    ) AS sub
),
Numbers AS (
    -- 生成足够覆盖最大元素数的序号序列
    SELECT TOP (SELECT MAX(ElementCount) FROM NumberedInvoices) 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_columns
)
SELECT
    ni.InvoiceId,
    ni.MatchIssue,
    ni.Ids,
    -- 提取第n个Id元素
    PARSENAME(REPLACE(ni.Ids, ',', '.'), ni.ElementCount - n + 1) AS Id,
    -- 提取第n个Amount元素并转换类型
    CAST(PARSENAME(REPLACE(ni.Amounts, ',', '.'), ni.ElementCount - n + 1) AS NUMERIC) AS Amount,
    -- 提取第n个CounterpartyExternalId元素
    PARSENAME(REPLACE(ni.CounterpartyExternalIds, ',', '.'), ni.ElementCount - n + 1) AS CounterpartyExternalId,
    -- 提取第n个CompanyExternalId元素
    PARSENAME(REPLACE(ni.CompanyExternalIds, ',', '.'), ni.ElementCount - n + 1) AS CompanyExternalId
FROM NumberedInvoices ni
JOIN Numbers n ON n.n <= ni.ElementCount
WHERE 
    PARSENAME(REPLACE(ni.Ids, ',', '.'), ni.ElementCount - n + 1) IS NOT NULL

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