oracle 如何使用 PIVOT 显示其单元格中的模拟平均值和计数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4782168/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I use PIVOT to show simultationly average and count in its cells?
提问by bernd_k
Looking at the syntaxI get the strong impression, that PIVOT doesn't support anything beyond a single aggregate function to be calculated for a cell.
查看语法，我得到了强烈的印象，即 PIVOT 不支持为单元格计算的单个聚合函数之外的任何内容。
From statistical view showing just some averages without giving the number of cases an average refers to is very unsatisfying ( that is the polite version).
从统计角度来看，只显示一些平均值而没有给出平均值所指的案例数量是非常令人不满意的（这是礼貌版本）。
Is there some nice pattern to evaluate pivots based on avg and pivots based on count and mix them together to give a nice result?
是否有一些很好的模式来评估基于 avg 的支点和基于计数的支点并将它们混合在一起以获得不错的结果？
采纳答案by RichardTheKiwi
Simultaneous.. in its cells. So you mean within the same cell, therefore as a varchar?
同时……在它的细胞中。所以你的意思是在同一个单元格内，因此作为一个varchar？
You could calc the avg and count values in an aggregate query before using the pivot, and concatenate them together as text.
您可以在使用数据透视之前计算聚合查询中的 avg 和 count 值，并将它们作为文本连接在一起。
The role of the PIVOT operator here would only be to transform rows to columns, and some aggregate function (e.g. MAX/MIN) would be used only because it is required by the syntax - your pre-calculated aggregate querywould only have one value per pivoted column.
此处 PIVOT 运算符的作用仅是将行转换为列，并且某些聚合函数（例如 MAX/MIN）仅在语法需要时才会使用 - 您预先计算的aggregate query每个枢轴列只有一个值.
EDIT
编辑
Following bernd_k's oracle/mssql solution, I would like to point out another way to do this in SQL Server. It requires streamlining the multiple columns into a single column.
按照 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
In the example above, AVG and COUNT are compatible (count - int => numeric). If they are not, convert both explicitly to a compatible type.
在上面的例子中，AVG 和 COUNT 是兼容的（count - int => numeric）。如果不是，则将两者显式转换为兼容类型。
Note - The first query shows AVG for M2/A as 2, due to integer averaging. The 2nd (pivoted) query shows the actual average taking into account decimals.
注 - 由于整数平均，第一个查询将 M2/A 的 AVG 显示为 2。第二个（旋转的）查询显示考虑到小数的实际平均值。
回答by Martin Smith
Yes you need to use the old style cross tabfor this. The PIVOTis just syntactic sugar that resolves to pretty much the same approach.
是的，您需要为此使用旧样式cross tab。这PIVOT只是解析为几乎相同方法的语法糖。
SELECT AVG(CASE WHEN col='foo' THEN col END) AS AvgFoo,
COUNT(CASE WHEN col='foo' THEN col END) AS CountFoo,...
If you have many aggregates you could always use a CTE
如果你有很多聚合，你总是可以使用 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
回答by bernd_k
Solution for Oracle 11g + :
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;
I do not like the column names containing apostrophes, but the result contains what I want:
我不喜欢包含撇号的列名，但结果包含我想要的：
MODULE 'A'_AVG 'A'_COUNT 'B'_AVG 'B'_COUNT
------------------------------ ---------- ---------- ---------- ----------
M1 5 2 3 1
M2 2.5 2 0
I really wonder what the Microsoft boys did, when they only allowed one aggregate function within pivot. I call evaluation avgs without accompanying counts statistical lies.
我真的很想知道微软男孩做了什么，当他们只允许一个聚合函数中的枢轴时。我将没有附带计数的评估平均值称为统计谎言。
SQL-Server 2005 +(based on Cyberwiki):
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
result:
结果：
MODULE A B
------------------------------ --------------------- ---------------------
M1 5|2 3|1
M2 2|2 NULL
