Redshift使用SQL窗口函数执行JOIN报语法错误问题排查

语法报错的核心原因是第一个子查询（别名ci）括号闭合位置错误，子查询内部缺失FROM数据源，同时原有写法存在大量逻辑冗余，哪怕修好语法也容易返回重复结果。

• 第一个ci子查询结构残缺：子查询括号内仅写了SELECT和LISTAGG逻辑，没有引入存储city字段的数据源表，反而把取city数据的FROM语句写到了子查询括号外部，数据库解析到子查询右括号时找不到字段来源，直接抛出语法错误，和报错提示的位置完全对应。
• 别名引用错误：第二个子查询中，你给内层去重的临时表起了别名sm，外层PARTITION BY时直接引用sm.management_firm_id，但外层查询作用域内不存在sm这个别名，修好语法后也会触发字段不存在的错误。
• 逻辑冗余：你的需求是对同一张表的两个字段分别按management_firm_id去重聚合，完全不需要拆成两个子查询做JOIN，原有写法用窗口函数生成全量聚合值再DISTINCT去重的方式，不仅效率低，还容易触发笛卡尔积导致结果重复。

Redshift的LISTAGG函数原生支持DISTINCT去重参数，直接单表GROUP BY聚合即可得到你要的3列结果，写法最简单效率最高：

SELECT
  management_firm_id AS fund_manager_id,
  LISTAGG(DISTINCT city, ',') WITHIN GROUP (ORDER BY city) AS secondary_asset,
  LISTAGG(DISTINCT sub_market, ',') WITHIN GROUP (ORDER BY sub_market) AS sub_market
FROM tableau_prep.dom_complete_manager_info
GROUP BY management_firm_id;

如果你使用的是较老版本的Redshift，不支持在LISTAGG内直接写DISTINCT，可以用CTE分别聚合两个字段再关联，避免语法错误和重复值：

-- 老版本Redshift兼容写法
WITH city_agg AS (
  SELECT
    management_firm_id,
    LISTAGG(city, ',') WITHIN GROUP (ORDER BY city) AS secondary_asset
  FROM (
    SELECT DISTINCT management_firm_id, city 
    FROM tableau_prep.dom_complete_manager_info
  )
  GROUP BY management_firm_id
),
submarket_agg AS (
  SELECT
    management_firm_id,
    LISTAGG(sub_market, ',') WITHIN GROUP (ORDER BY sub_market) AS sub_market
  FROM (
    SELECT DISTINCT management_firm_id, sub_market 
    FROM tableau_prep.dom_complete_manager_info
  )
  GROUP BY management_firm_id
)
SELECT
  c.management_firm_id AS fund_manager_id,
  c.secondary_asset,
  s.sub_market
FROM city_agg c
INNER JOIN submarket_agg s
  ON c.management_firm_id = s.management_firm_id;

注意：不要用窗口函数+DISTINCT的方式做这类聚合，窗口函数会为分组内每一行都生成相同的聚合字符串，再去重的性能远差于直接GROUP BY聚合，数据量大时差距会非常明显。

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