时间戳

首页 > 解决方案 > RedShift Error when using COUNT (Distinct XXX) ERROR: XX000: This type of associated subquery patterns is not supported due to internal error

sql - RedShift Error when using COUNT (Distinct XXX) ERROR: XX000: This type of associated subquery patterns is not supported due to internal error

问题描述

我通过 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

标签： sqlamazon-redshiftaginity

解决方案

如果您在子查询中使用窗口函数，它会起作用吗？

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

推荐阅读