Как я могу использовать PIVOT для отображения среднего значения и количества клеток в его ячейках?

Глядя на синтаксис, я получаю сильное впечатление, что PIVOT не поддерживает ничего, кроме одной агрегатной функции, рассчитываемой для ячейки.

Из статистического представления, показывающего только некоторые средние значения без указания количества случаев, на которые ссылается среднее значение, очень неудовлетворительно (это вежливая версия).

Есть ли какой-нибудь хороший шаблон для оценки пивотов на основе avg и пивотов на основе количества и смешивания их вместе, чтобы получить хороший результат?

Источник

3 ответа

Решение

Одновременно.. в своих клетках. Таким образом, вы имеете в виду в одной клетке, следовательно, как varchar?

Вы можете вычислить значения avg и count в агрегированном запросе перед использованием сводной диаграммы и объединить их вместе в виде текста.

Роль оператора PIVOT здесь будет заключаться только в преобразовании строк в столбцы, а некоторая агрегатная функция (например, MAX/MIN) будет использоваться только потому, что этого требует синтаксис - ваш предварительный расчет aggregate query будет иметь только одно значение на поворотный столбец.

РЕДАКТИРОВАТЬ

Следуя решению 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). Если это не так, конвертируйте оба явно в совместимый тип.

Примечание. Первый запрос показывает AVG для M2/A как 2 из-за целочисленного усреднения. 2-й (сводный) запрос показывает фактическое среднее значение с учетом десятичных дробей.

Источник

Да, вам нужно использовать старый стиль cross tab за это. 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
Источник

Решение для 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

Мне действительно интересно, что сделали парни из Microsoft, когда они разрешали только одну функцию агрегирования в сводной таблице. Я называю оценку avgs без учета подсчета статистической ложью.

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
Источник
Другие вопросы по тегам