如果汇总值为null，则跳过整行 [英] Skip whole row if aggregated value is null

问题描述

这是我的方法：

select distinct (invoice_no) as no,sum(total),
                sum(case when department_id=2 then total end) as a2,
                sum(case when department_id=3 then total end) as a3,
                sum(case when department_id=4 then total end) as a4,
                sum(case when department_id=5 then total end) as a5,
                sum(case when department_id=6 then total end) as a6
from article_sale
where  invoice_date = '2018-10-01' group by no order by no ASC

查询返回的输出如下：

no      sum a2      a3    a4    a5      a6
68630   690 NULL    75    404   NULL    210.8
68631   0   NULL    NULL  NULL  NULL    NULL
68632   132 NULL    45    87    NULL    NULL
68633   75  NULL    75    NULL  NULL    NULL
68634   523 NULL    130   NULL  NULL    392.55
68635   0   NULL    NULL  NULL  NULL    NULL
68636   310 NULL    NULL  218   NULL    91.91
68637   273 NULL    NULL  NULL  NULL    273.24
68638   0   NULL    NULL  NULL  NULL    NULL

我只想获取 a6 为 NOT NULL 的行。

所需的输出：

I only want to get rows where a6 is NOT NULL. Other rows shall be filtered.
Desired output:

no      sum a2      a3    a4    a5      a6
68630   690 NULL    75    404   NULL    210.8
68634   523 NULL    130   NULL  NULL    392.55
68636   310 NULL    NULL  218   NULL    91.91
68637   273 NULL    NULL  NULL  NULL    273.24

如何最好地实现这一目标？

How to best achieve this?

推荐答案

添加 收藏 子句：

SELECT invoice_no                                  AS no
     , sum(total)                                  AS sum_total
     , sum(total) FILTER (WHERE department_id = 2) AS a2
     , sum(total) FILTER (WHERE department_id = 3) AS a3
     , sum(total) FILTER (WHERE department_id = 4) AS a4
     , sum(total) FILTER (WHERE department_id = 5) AS a5
     , sum(total) FILTER (WHERE department_id = 6) AS a6
FROM   article_sale
WHERE  invoice_date = '2018-10-01'
GROUP  BY 1
HAVING sum(total) FILTER (WHERE department_id = 6) IS NOT NULL
ORDER  BY 1;

但是首先删除冗余的，昂贵的 DISTINCT 。在应用 GROUP BY 之后，行必然是不同的。也不要将 DISTINCT（invoice_no）与 DISTINCT ON（invoice_no）混淆。第一个被误导的括号被删除了。第二个有不同的含义。请参阅：

But first drop the redundant, expensive DISTINCT. Rows are bound to be distinct after GROUP BY has been applied. Also don't confuse DISTINCT (invoice_no) with DISTINCT ON (invoice_no). The first one has misleading parentheses that are stripped away. The second one has different meaning. See:

• 在每个GROUP BY组中选择第一行？

• Select first row in each GROUP BY group?

还可以使用现代的，更快的 FILTER 子句可用于条件聚合。请参阅：

Also using the modern, faster FILTER clause for your conditional aggregates. See:

• 如何简化此游戏统计信息查询？

• How can I simplify this game statistics query?

这篇关于如果汇总值为null，则跳过整行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！