如何使用PIVOT在单元格中同时显示平均值和计数？

Question

看到语法，我强烈感觉PIVOT不支持计算单元格中超过一个聚合函数的内容。

从统计角度来看，仅显示一些平均值而未给出平均值所涉及的案例数量是非常不满意的（这是礼貌版本）。

是否有一些好的模式可以基于平均值和基于计数的数据透视表进行评估，并将它们混合在一起以得到良好的结果？

- bernd_k

Answer 1

是的，你需要使用旧格式的交叉表来实现这个功能。 PIVOT只是语法糖，实质上与旧格式的方法基本相同。

SELECT AVG(CASE WHEN col='foo' THEN col END) AS AvgFoo,
       COUNT(CASE WHEN col='foo' THEN col END) AS CountFoo,...

如果你有很多聚合数据，你可以使用CTE。

WITH cte As
(
SELECT CASE WHEN col='foo' THEN col END AS Foo...
)
SELECT MAX(Foo),MIN(Foo), COUNT(Foo), STDEV(Foo)
FROM cte

- Martin Smith

Answer 2

同时...在它的单元格中。所以你的意思是在同一个单元格内，因此作为 varchar？

您可以在使用 pivot 之前在聚合查询中计算平均值和计数值，并将它们作为文本连接在一起。

PIVOT 运算符在这里的作用仅是将行转换为列，而某些聚合函数（例如 MAX/MIN）仅被使用是因为语法要求 - 您预先计算的聚合查询每个透视列只有一个值。

编辑

根据 bernd_k 的 Oracle/MSSQL 解决方案，我想指出 SQL Server 中另一种方法来完成此操作。它需要将多个列简化为单个列。

SELECT MODULE,
  modus + '_' + case which when 1 then 'AVG' else 'COUNT' end AS modus,
  case which when 1 then AVG(duration) else COUNT(duration) end AS value
FROM test_data, (select 1 as which union all select 2) x
GROUP BY MODULE, modus, which

SELECT *
FROM (
 SELECT MODULE,
  modus + '_' + case which when 1 then 'AVG' else 'COUNT' end AS modus,
  case which when 1 then CAST(AVG(1.0*duration) AS NUMERIC(10,2)) else COUNT(duration) end AS value
 FROM test_data, (select 1 as which union all select 2) x
 GROUP BY MODULE, modus, which
) P
PIVOT (MAX(value) FOR modus in ([A_AVG], [A_COUNT], [B_AVG], [B_COUNT])
) AS pvt
ORDER BY pvt.MODULE

在上面的例子中，AVG和COUNT是兼容的（count - int => numeric）。如果它们不兼容，则将两者都显式转换为兼容类型。

注意-第一个查询显示M2 / A的平均值为2，因为是整数平均值。第二个（旋转）查询显示实际平均值，考虑小数点。

- RichardTheKiwi

---

你的答案针对SQL Server是正确的，解决了我的问题。但是相比Oracle，SQL Server实现PIVOT似乎较差。 - bernd_k

Answer 3

针对 Oracle 11g + 的解决方案：

create table test_data (
    module varchar2(30),
    modus   varchar2(30),
    duration Number(10)
);


insert into test_data values ('M1', 'A', 5); 
insert into test_data values ('M1', 'A', 5); 
insert into test_data values ('M1', 'B', 3); 
insert into test_data values ('M2', 'A', 1); 
insert into test_data values ('M2', 'A', 4); 


select   *
FROM (
select   *
from test_data
) 
PIVOT (
    AVG(duration) avg , count(duration) count
    FOR modus in ( 'A', 'B')
) pvt
ORDER BY pvt.module;

我不喜欢包含撇号的列名，但结果包含了我想要的内容：

MODULE                            'A'_AVG  'A'_COUNT    'B'_AVG  'B'_COUNT
------------------------------ ---------- ---------- ---------- ----------
M1                                      5          2          3          1
M2                                    2.5          2                     0

我真的很好奇微软的开发人员在允许透视表中只使用一个聚合函数时做了什么。如果没有相应的计数，我称之为使用平均值进行评估是统计谎言。

SQL-Server 2005 +（基于Cyberwiki）：

CREATE TABLE test_data (
    MODULE VARCHAR(30),
    modus   VARCHAR(30),
    duration INTEGER
);


INSERT INTO test_data VALUES ('M1', 'A', 5); 
INSERT INTO test_data VALUES ('M1', 'A', 5); 
INSERT INTO test_data VALUES ('M1', 'B', 3); 
INSERT INTO test_data VALUES ('M2', 'A', 1); 
INSERT INTO test_data VALUES ('M2', 'A', 4); 


SELECT MODULE, modus, ISNULL(LTRIM(STR(AVG(duration))), '') + '|' + ISNULL(LTRIM(STR(COUNT(duration))), '') RESULT
FROM test_data
GROUP BY MODULE, modus;

SELECT   *
FROM (
SELECT MODULE, modus, ISNULL(LTRIM(STR(AVG(duration))), '') + '|' + ISNULL(LTRIM(STR(COUNT(duration))), '') RESULT
FROM test_data
GROUP BY MODULE, modus
) T
PIVOT (
 MAX(RESULT)
 FOR modus in ( [A], [B])
) AS pvt
ORDER BY pvt.MODULE

结果：

MODULE                         A                     B
------------------------------ --------------------- ---------------------
M1                             5|2                   3|1
M2                             2|2                   NULL

- bernd_k

---

m2 - a_avg 看起来很奇怪 - 对于 Oracle (4)？ - RichardTheKiwi

明白了，应该是平均值而不是最大值。我已经修复了。 - bernd_k