Tenho uma tabela descrevendo os usuários do meu aplicativo com alguns detalhes como nome, sobrenome, data de nascimento, nacionalidade, email, etc...
Gostaria de saber para cada propriedade, e para cada categoria de usuário, o valor mais frequente e a porcentagem de ocorrência.
Por exemplo :
create table test ( userId int identity(1,1),
categoryId int,
name varchar(50),
surname varchar(50))
insert into test(categoryId, name, surname)
values (1, 'John', 'Locke'),
(1, 'John', 'Millar'),
(1, 'James', 'Mill'),
(1, 'John Stuart', 'Mill'),
(2, 'Thomas', 'Bayes'),
(2, 'Laurent', 'Schwartz'),
(2, 'Herrmann Amandus', 'Schwartz'),
(2, 'Thomas', 'Simpson'),
(2, 'Leonhard', 'Euler')
Resultado deve ser:
+------------+-------+--------+---------+----------+------------+
| categoryId | total | name | namePct | surname | surnamePct |
+------------+-------+--------+---------+----------+------------+
| 1 | 4 | John | 0.50 | Mill | 0.50 |
| 2 | 5 | Thomas | 0.40 | Schwartz | 0.40 |
+------------+-------+--------+---------+----------+------------+
Para este exemplo simples, posso descobrir como posso conseguir isso, com uma consulta como:
select t.categoryId,
t.total,
n.name,
1. * n.total / t.total as namePct,
sn.surname,
1. * sn.total / t.total as surnamePct
from (
select categoryId, count(*) as total
from test
group by categoryId
) t
join (
select categoryId, name, total
from (
select categoryId, name, total, row_number() over(partition by categoryId order by total desc) as rn
from (
select categoryId, name, count(*) as total
from test
group by categoryId, name
) t
) t
where rn = 1
) n on t.categoryId = n.categoryId
join (
select categoryId, surname, total
from (
select categoryId, surname, total, row_number() over(partition by categoryId order by total desc) as rn
from (
select categoryId, surname, count(*) as total
from test
group by categoryId, surname
) t
) t
where rn = 1
) sn on t.categoryId = sn.categoryId
Mas, no meu caso de uso real, minha tabela tem milhões de linhas, centenas de categorias e uma dúzia de atributos.
Existe uma maneira de tornar a consulta mais simples e eficiente (ou seja, sem um monte de sub-select para cada atributo)?
Atualmente estou usando o SQL Server 2008, mas respostas usando versões mais recentes são bem-vindas.
Você pode usar a versão em janela de function
COUNT()
, dividida por cada categoriaPARTITION BY
para obter contagens e totais sem uma subconsulta (observe a falta deGROUP BY
):Resultado:
Então você pode usar esse resultado para obter cada porcentagem simplesmente dividindo as ocorrências por cada total. Você também pode calcular o melhor nome e sobrenome (mais frequente) usando um
ROW_NUMBER()
nesta etapa:Resultado:
Finalmente, para cada categoria disponível...
podemos obter o nome e sobrenome mais frequentes com suas porcentagens com algumas junções:
Resultado:
Pode ser um pouco grande, mas você pode editar essa consulta com quantas novas colunas quiser sem adicionar new
SELECT
, apenas repetindo a mesma lógica com cada nova coluna que deseja exibir e uma junção adicional no final.Se você tiver milhões de registros e a consulta demorar muito, convém dividir cada CTE em uma tabela temporária com
SELECT ... INTO
+CREATE INDEX
porcategoryId
para acelerar o processo (se estiver disposto a gastar alguns recursos criando essas tabelas)