如何使用PIVOT同时显示其单元格中的平均值和计数? [英] How can I use PIVOT to show simultationly average and count in its cells?

问题描述

看看语法我印象深刻，那就是PIVOT除了要为单元格计算的单个聚合函数外，不支持任何其他功能.

Looking at the syntax I get the strong impression, that PIVOT doesn't support anything beyond a single aggregate function to be calculated for a cell.

从统计视​​图中仅显示一些平均值，而没有给出平均值所指的案例数(那是礼貌的版本).

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 ).

是否存在一些不错的模式来评估基于avg的数据透视表和基于计数的数据透视表，并将它们混合在一起以得出良好的结果?

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?

推荐答案

同时存在于其单元格中.那么您是说在同一个单元格中，因此是作为varchar吗?

Simultaneous.. in its cells. So you mean within the same cell, therefore as a varchar?

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

You could calc the avg and count values in an aggregate query before using the pivot, and concatenate them together as text.

此处PIVOT运算符的作用仅是将行转换为列，并且仅会使用某些聚合函数(例如MAX/MIN)，因为语法需要它-您预先计算的aggregate query仅会每个枢轴列都有一个值.

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 query would only have one value per pivoted column.

在bernd_k的oracle/mssql解决方案之后，我想指出另一种在SQL Server中执行此操作的方法.它需要将多个列简化为一个列.

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.

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 =>数字).如果不是，则将它们都显式转换为兼容类型.

In the example above, AVG and COUNT are compatible (count - int => numeric). If they are not, convert both explicitly to a compatible type.

注意-由于整数平均，第一个查询将M2/A的AVG显示为2.第二个(枢轴)查询显示了考虑小数点后的实际平均值.

这篇关于如何使用PIVOT同时显示其单元格中的平均值和计数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！