是否可以合并分组依据、拥有和总和?

Question

我有一个表格：

------------------------
|id|p_id|desired|earned|
------------------------
|1 | 1  |  5    |  7   |
|2 | 1  |  15   |  0   |
|3 | 1  |  10   |  0   |
|4 | 2  |  2    |  3   |
|5 | 2  |  2    |  3   |
|6 | 2  |  2    |  3   |
------------------------

我需要进行一些计算，并尝试在一个不太复杂的请求中进行，否则我知道如何用请求数来计算它。 我需要如下所示的结果表：

---------------------------------------------------------
|p_id|total_earned|    AVG   |      Count     |  SUM    |
|    |            | (desired)|(if earned != 0)|(desired)|
---------------------------------------------------------
|  1 |      7     |     10   |       1        |    30   |
|  2 |      9     |      2   |       3        |    6    |
---------------------------------------------------------

到目前为止，我构建：

SELECT p_id, SUM(earned), AVG(desired), Sum(desired) 
FROM table GROUP BY p_id

但是我不知道如何计算有条件的分组记录的数量。我可以用HAVING但单独的请求获得这个号码。

我几乎可以肯定什么SQL应该具有这种能力。

Answer 1

你可以为此使用CASE表达式。

试试这个，

SELECT p_id
,SUM(earned) AS total_earned
,AVG(desired) AS avg_desired
,COUNT(CASE WHEN Earned!=0 THEN 1 END) AS earned_count
,SUM(desired) AS sum_desired
FROM table
GROUP BY p_id;

Answer 2

CASE的较短替代方案是

SELECT p_id,
SUM(earned) AS total_earned,
AVG(desired) AS average_desired,
COUNT(earned != 0 OR NULL) AS earned_count,
SUM(desired) AS sum_desired
FROM table GROUP BY p_id;

因为NULL不计算在内。

Answer 3

您几乎完成了查询，只需借助case表达式添加条件聚合即可获得计数

SELECT 
p_id,
SUM(earned) [total_earned],
AVG(desired) [desired],
SUM(CASE WHEN earned <> 0 THEN 1 ELSE 0 END) [COUNT],
SUM(desired) [SUM] FROM <table>
GROUP BY p_id

结果

p_id    total_earned  desired   COUNT  SUM
1       7             10        1      30
2       9             2         3      6

Answer 4

NULLIF()是标准的SQL，可能是最短的：

SELECT p_id
, count(NULLIF(earned, 0)) AS earned_count
-- , more ...
FROM   table
GROUP  BY 1;

count()仅计算非空值。

更多变体：

• 对于绝对性能，是 SUM 更快还是 COUNT 更快？