Início / dba / Perguntas / 213183
Accepted
irimias
Asked: 2018-07-26 03:54:37 +0800 CST

Avaliando os valores mais frequentes de colunas em uma única consulta

  • 772

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.

sql-server sql-server-2008

1 respostas

  1. Best Answer

    Você pode usar a versão em janela de function COUNT(), dividida por cada categoria PARTITION BYpara obter contagens e totais sem uma subconsulta (observe a falta de GROUP BY):

    SELECT
    T.categoryId,

    T.name,
    NameOccurencies = COUNT(T.name) OVER (PARTITION BY T.categoryId, T.name),
    NameTotals = COUNT(T.name) OVER (PARTITION BY T.categoryId),

    T.surname,
    SurnameOccurencies = COUNT(T.surname) OVER (PARTITION BY T.categoryId, T.surname),
    SurnameTotals = COUNT(T.surname) OVER (PARTITION BY T.categoryId)
FROM
    #test AS T

    Resultado:

    categoryId  name                NameOccurencies NameTotals  surname             SurnameOccurencies  SurnameTotals
1           John                2               4           Locke               1                   4
1           John Stuart         1               4           Mill                2                   4
1           James               1               4           Mill                2                   4
1           John                2               4           Millar              1                   4
2           Thomas              2               5           Bayes               1                   5
2           Leonhard            1               5           Euler               1                   5
2           Herrmann Amandus    1               5           Schwartz            2                   5
2           Laurent             1               5           Schwartz            2                   5
2           Thomas              2               5           Simpson             1                   5

    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:

    ;WITH Totals AS
(
    SELECT
        T.categoryId,

        T.name,
        NameOccurencies = COUNT(T.name) OVER (PARTITION BY T.categoryId, T.name),
        NameTotals = COUNT(T.name) OVER (PARTITION BY T.categoryId),

        T.surname,
        SurnameOccurencies = COUNT(T.surname) OVER (PARTITION BY T.categoryId, T.surname),
        SurnameTotals = COUNT(T.surname) OVER (PARTITION BY T.categoryId)
    FROM
        #test AS T
)
SELECT
    T.categoryId,

    T.name,
    NamePercentage = T.NameOccurencies * 1.0 / NULLIF(T.NameTotals, 0),
    NameMostFrequentRanking = ROW_NUMBER() OVER (
        PARTITION BY
            T.categoryId
        ORDER BY 
            T.NameOccurencies * 1.0 / NULLIF(T.NameTotals, 0) DESC), -- NamePercentage

    T.surname,
    SurnamePercentage = T.SurnameOccurencies * 1.0 / NULLIF(T.SurnameTotals, 0),
    SurnameMostFrequentRanking = ROW_NUMBER() OVER (
        PARTITION BY
            T.categoryId
        ORDER BY 
            T.SurnameOccurencies * 1.0 / NULLIF(T.SurnameTotals, 0) DESC) -- SurnamePercentage
FROM
    Totals AS T

    Resultado:

    categoryId  name                NamePercentage  NameMostFrequentRanking surname     SurnamePercentage   SurnameMostFrequentRanking
1           John Stuart         0.250000000000  3                       Mill        0.500000000000      1
1           James               0.250000000000  4                       Mill        0.500000000000      2
1           John                0.500000000000  1                       Millar      0.250000000000      3
1           John                0.500000000000  2                       Locke       0.250000000000      4
2           Herrmann Amandus    0.200000000000  4                       Schwartz    0.400000000000      1
2           Laurent             0.200000000000  5                       Schwartz    0.400000000000      2
2           Thomas              0.400000000000  1                       Simpson     0.200000000000      3
2           Thomas              0.400000000000  2                       Bayes       0.200000000000      4
2           Leonhard            0.200000000000  3                       Euler       0.200000000000      5

    Finalmente, para cada categoria disponível...

    SELECT
    T.categoryId,
    TotalRecords = COUNT(1)
FROM
    #test AS T
GROUP BY
    T.categoryId

    podemos obter o nome e sobrenome mais frequentes com suas porcentagens com algumas junções:

    ;WITH Totals AS
(
    SELECT
        T.categoryId,

        T.name,
        NameOccurencies = COUNT(T.name) OVER (PARTITION BY T.categoryId, T.name),
        NameTotals = COUNT(T.name) OVER (PARTITION BY T.categoryId),

        T.surname,
        SurnameOccurencies = COUNT(T.surname) OVER (PARTITION BY T.categoryId, T.surname),
        SurnameTotals = COUNT(T.surname) OVER (PARTITION BY T.categoryId)
    FROM
        #test AS T
),
MostFrequentRanking AS
(
    SELECT
        T.categoryId,

        T.name,
        NamePercentage = T.NameOccurencies * 1.0 / NULLIF(T.NameTotals, 0),
        NameMostFrequentRanking = ROW_NUMBER() OVER (
            PARTITION BY
                T.categoryId
            ORDER BY 
                T.NameOccurencies * 1.0 / NULLIF(T.NameTotals, 0) DESC),

        T.surname,
        SurnamePercentage = T.SurnameOccurencies * 1.0 / NULLIF(T.SurnameTotals, 0),
        SurnameMostFrequentRanking = ROW_NUMBER() OVER (
            PARTITION BY
                T.categoryId
            ORDER BY 
                T.SurnameOccurencies * 1.0 / NULLIF(T.SurnameTotals, 0) DESC)
    FROM
        Totals AS T
),
AvailableCategories AS
(
    SELECT
        T.categoryId,
        TotalRecords = COUNT(1)
    FROM
        #test AS T
    GROUP BY
        T.categoryId
)
SELECT
    A.categoryId,
    A.TotalRecords,
    MN.name,
    NamePercentage = CONVERT(DECIMAL(3, 2), MN.NamePercentage),
    MS.surname,
    SurnamePercentage = CONVERT(DECIMAL(3, 2), MS.SurnamePercentage)
FROM
    AvailableCategories AS A
    LEFT JOIN MostFrequentRanking AS MN ON 
        A.categoryId = MN.categoryId AND
        MN.NameMostFrequentRanking = 1
    LEFT JOIN MostFrequentRanking AS MS ON 
        A.categoryId = MS.categoryId AND
        MS.SurnameMostFrequentRanking = 1

    Resultado:

    categoryId  TotalRecords    name    NamePercentage  surname     SurnamePercentage
1           4               John    0.50            Mill        0.50
2           5               Thomas  0.40            Schwartz    0.40

    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 INDEXpor categoryIdpara acelerar o processo (se estiver disposto a gastar alguns recursos criando essas tabelas)

    • 4

relate perguntas