Phoenix中基于分组查询的Upsert实现及数组列赋值可行性咨询

嘿，我来帮你梳理这个问题，先直接说结论：你给出的伪代码不可行，主要是聚合数组的方式有误，还有子查询逻辑不成立。下面我会拆解问题，然后给出正确的Phoenix Upsert实现方案。

1. 子查询逻辑错误：(SELECT DISTINCT ACCOUNT_ID)这个子查询没有和外层的GROUP BY EFFECTIVE_DATE关联，它会返回SOURCE_TABLE中所有的ACCOUNT_ID，而不是当前分组下的ID，完全不符合你要按日期聚合的需求。
2. 数组聚合方式不对：Phoenix中要将分组后的列值生成数组，需要用专门的聚合函数，而不是子查询。

假设你的需求是：对SOURCE_TABLE中的每条EFFECTIVE_DATE，将该日期下所有唯一的ACCOUNT_ID存入SOURCES数组，同时用序列生成唯一的UID作为主键的一部分，那么正确的SQL应该是：

UPSERT INTO DESTINATION_TABLE (EFFECTIVE_DATE, UID, SOURCES)
SELECT 
    EFFECTIVE_DATE,
    NEXT VALUE FOR CIBC_COPY.AUM_AGGREGATES_SEQ AS UID,
    ARRAY_AGG(DISTINCT ACCOUNT_ID) AS SOURCES
FROM SOURCE_TABLE
GROUP BY EFFECTIVE_DATE;

关键说明： #

• ARRAY_AGG(DISTINCT ACCOUNT_ID)：这是Phoenix支持的聚合函数，会自动将当前EFFECTIVE_DATE分组下的所有唯一ACCOUNT_ID打包成VARCHAR[]类型的数组，完美匹配目标表的SOURCES列。
• 序列生成的UID：因为你的主键是(EFFECTIVE_DATE, UID)，每次执行这个SQL时，每个日期分组都会生成一个新的UID，所以会插入新的记录（而不是更新已有记录）。

如果你的需求是针对同一EFFECTIVE_DATE，把新的ACCOUNT_ID追加到已有的SOURCES数组中，那当前的主键设计就有问题了——因为UID是自动生成的，无法匹配到已有的记录。这时候需要调整主键（比如把主键改为EFFECTIVE_DATE单独唯一），然后用以下SQL实现：

UPSERT INTO DESTINATION_TABLE (EFFECTIVE_DATE, SOURCES)
SELECT 
    st.EFFECTIVE_DATE,
    ARRAY_UNION(
        COALESCE(dt.SOURCES, ARRAY[]::VARCHAR[]), -- 处理第一次插入时数组为空的情况
        ARRAY_AGG(DISTINCT st.ACCOUNT_ID)
    ) AS SOURCES
FROM SOURCE_TABLE st
LEFT JOIN DESTINATION_TABLE dt ON st.EFFECTIVE_DATE = dt.EFFECTIVE_DATE
GROUP BY st.EFFECTIVE_DATE, dt.SOURCES;

关键说明： #

• ARRAY_UNION：用于合并已有数组和新聚合的数组，自动去重，避免重复ID存入。
• COALESCE：当目标表中还没有该日期的记录时，dt.SOURCES为NULL，用空数组替代，确保ARRAY_UNION能正常执行。

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