使用 COUNT(非重复 XXX)时出现 RedShift 错误:XX000:由于内部错误，不支持这种类型的相关子查询模

Question

我有一个通过 Aginity 在 RedShift 中运行的小查询，该查询出现以下错误：

错误：

XX000：由于内部错误，不支持这种类型的相关子查询模式

当前查询：

SELECT 'MYSCHEMA.MYTABLE.FIELDA' as obj_name,
COUNT(*) as rows, 
COUNT(DISTINCT FIELDA) as distinct_vals, 
SUM(CASE WHEN FIELDA in ('00DK','00DC','00DE','00DD','00DB') THEN 1 ELSE 0 END) as enter, 
SUM(CASE WHEN TRIM(FIELDA) IN ('',null) THEN 1 ELSE 0 END) as nulls 
FROM MYSCHEMA.MYTABLE

但是，如果我删除 DISTINCT 它会运行：

SELECT 'MYSCHEMA.MYTABLE.FIELDA' as obj_name,
COUNT(*) as rows, 
COUNT(FIELDA) as distinct_vals, 
SUM(CASE WHEN FIELDA in ('00DK','00DC','00DE','00DD','00DB') THEN 1 ELSE 0 END) as enter, 
SUM(CASE WHEN TRIM(FIELDA) IN ('',null) THEN 1 ELSE 0 END) as nulls 
FROM MYSCHEMA.MYTABLE

Answer 1

如果在子查询中使用窗口函数，它是否有效？

SELECT 'MYSCHEMA.MYTABLE.FIELDA' as obj_name,
COUNT(*) as rows, 
SUM( (seqnum = 1)::INT ) as distinct_vals, 
SUM(CASE WHEN FIELDA in ('00DK','00DC','00DE','00DD','00DB') THEN 1 ELSE 0 END) as enter, 
SUM(CASE WHEN TRIM(FIELDA) IN ('',null) THEN 1 ELSE 0 END) as nulls 
FROM (SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY FIELDA ORDER BY FIELDA) as seqnum
FROM MYSCHEMA.MYTABLE t
) t