Матричные таблицы
Допустим, имеется некоторая таблица test, состоящая из двух столбцов - id, type, где type - тип, например, фрукта.
id type
1 1 2
2 2 3
3 3 4
4 4 1
5 5 5
6 6 1
7 7 1
8 8 2
9 9 3
10 10 4
11 11 5
12 12 1
13 13 2
14 14 5
15 15 3
Известно, что type='1' - это яблоко, '2' - груша, '3' - манго, '4' - банан, '5' - апельсин. Необходимо подсчитать количество каждого фрукта и процент от общих.
with PartRes as(
select
count (*) as cntAll
,count(case when t.type='1' then 1 end) as cntApple
,count(case when t.type='2' then 1 end) as cntPear
,count(case when t.type='3' then 1 end) as cntMango
,count(case when t.type='4' then 1 end) as cntBanana
,count(case when t.type='5' then 1 end) as cntOrange
from tets t with (nolock)
)
select
cast(p.cntApple as int) as 'К Я'
,cast(p.cntPear as int) as 'К Г'
,cast(p.cntMango as int) as 'К М'
,cast(p.cntBanana as int) as 'К Б'
,cast(p.cntOrange as int) as 'К А'
,cast(p.cntApple / p.cntAll as varchar) as '% Я'
,cast(p.cntPear / p.cntAll as varchar) as '% Г'
,cast(p.cntMango / p.cntAll as varchar) as '% М'
,cast(p.cntBanana / p.cntAll as varchar) as '% Б'
,cast(p.cntOrange / p.cntAll as varchar) as '% А'
from PartRes p with (nolock)
Такой запрос выведет таблицу, состоящую из одной строки и 10 столбцов:
К яблок | К груш | К манго | К бананов | К апелсинов | % яблок | % груш | % манго | % бананов | % апелсинов
1 4 3 3 2 3 0.26666 0.2 0.2 0.13333 0.2
Как представить результат в следующем виде:
Фрукт | К | %
1 Яблоко 4 0.26666
2 Груша 3 0.2
3 Манго 3 0.2
4 Банан 2 0.13333
5 Апельсин 3 0.2
Я так понимаю, что нужна группировка и использование pivot и unpivot. Подскажите, как можно это сделать?
Ответы (2 шт):
Можно проще сделать - через группировку. А процент - количество отдельного типа разделенного на общее через подзапрос. Быстро набросал таблицу, поэтому не точно соответствует указанной в вопросе. Присутствует так же синтаксис SQL Server, но идея должна быть понятна.
create table temp
(
id int primary key identity,
type int
)
insert into temp(type)
values (1), (2), (1), (2), (3)
select type, count(*) as count, count(*) / cast((select count(*) from temp) as decimal) as percentage
from temp
group by type
На выходе имеем
WITH cte AS ( SELECT type,
COUNT(id) amount
FROM test
GROUP BY type )
SELECT cte.type,
fruits.name,
cte.amount,
CONCAT(100 * cte.amount / SUM(cte.amount) OVER ()) percent
FROM cte
JOIN fruits USING (type);