有效使用SQL GROUP BY，SUM，COUNT [英] Efficient use of SQL GROUP BY, SUM, COUNT

问题描述

我有一个产品销售表，可能如下：

I have a table of products sales that may look as follows:

product   | amount   | ptype    | pdate      
p1        | 1.00     | sale     | 01/01
p1        | 2.00     | base     | 01/02
p2        | 1.50     | sale     | 02/03
p3        | 5.25     | base     | 10/10

，我想建立一个表格，每行显示一个产品，金额，如果产品是唯一的显示类型否则显示类型为'VAR'，如果产品是唯一的显示日期，否则显示日期为NULL。所以结果如下：

and I would like to build a table that shows one product per row, the sum of the amounts, if the product is unique show the type else show the type as 'VAR', if the product is unique show the date else show the date as NULL. So that the result look as follows:

product   | total    | ptype    | pdate      
p1        | 3.00     | VAR      | (NULL)
p2        | 1.50     | sale     | 02/03
p3        | 5.25     | base     | 10/10

我通过执行以下操作来完成我需要的结果：

I am accomplishing the result I need by doing the following:

SELECT DISTINCT product
,(SELECT SUM(amount) FROM T as b GROUP BY b.product HAVING a.product = b.product ) as total
,(SELECT CASE WHEN COUNT(*) = 1 THEN a.ptype  ELSE 'VAR' END from T as b GROUP BY b.product HAVING a.product = b.product) as ptype
,(SELECT CASE WHEN COUNT(*) = 1 THEN a.pdate  ELSE NULL END from T as b GROUP BY b.product HAVING a.product = b.product) as pdate
FROM T as a

但我想知道是否有更有效的方法来实现相同的结果。

But I would like to know if there is a more efficient way that accomplishes the same result.

推荐答案

不需要使用任何形式的子查询或内联视图。根据数据库引擎的复杂程度，这些构造可能会对性能产生负面影响。

There is no need to use any form of subquery or inline view. Depending on the sophistication of the database engine, those constructs could negatively impact performance.

这是您请求的，它应该可靠地给结果一个扫描的即使是最原始的SQL引擎也是如此。

Here is what you requested, and it should reliably give the result with a single scan of the table on even the most primitive SQL engine.

select product,
       sum(amount) as amount,
       case when count(*)=1 then min(ptype) else 'VAR' end as ptype,
       case when count(*)=1 then min(pdate) else null end as pdate
  from T
 group by product

以下并不完全符合您的要求，但我认为更接近你实际寻找的。它只报告ptype为VAR或pdate为NULL，如果有多个不同的值组成聚合。

The following is not exactly what you requested, but I think it may be closer to what you are actually looking for. It only reports ptype as VAR or pdate as NULL if there are multiple distinct values making up the aggregate.

我添加了一个pcount列，以便你仍然可以标识单

I've added a pcount column so that you can still identify singlet aggregates, even if both ptype and pdate are not nulll.

select product,
       sum(amount) as amount,
       count(*) as pcount,
       case when count(distinct ptype)=1 then min(ptype) else 'VAR' end as ptype,
       case when count(distinct pdate)=1 then min(pdate) else null end as pdate
  from T
 group by product

这篇关于有效使用SQL GROUP BY，SUM，COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！