SQLServer count () over () com distintas

9

Estou trabalhando em um projeto, onde precisamos contar o número de linhas distintas. Uma versão simplificada do cenário inclui uma tabela user , uma tabela keyword e uma tabela keyword_user .

A tabela user inclui apenas metadados de usuário comuns, como nome etc. As outras tabelas estão listadas abaixo.

keyword_user:

id
user_id
keyword_id


    palavra chave:

id,
description

O que eu quero fazer é encontrar um número máximo de usuários (5), com base nos usuários keyword_id's, além de contar o número total de linhas correspondentes. A contagem deve ser distinta.

A consulta:

SELECT TOP 5 u.[id], 
             u.[firstname], 
             u.[lastname], 
             total = Count(*) OVER() 
FROM   [user] u 
       INNER JOIN [keyword_user] ku 
               ON u.[id] = ku.[user_id] 
WHERE  ( ku.keyword_id IN ( '5f6501ec-0a71-4067-a21d-3c5f87a76411', 'c19b95c0-8554-4bbd-9526-db8f1c4f1edf')) 
       AND u.id NOT IN ( '12db3001-b3b9-4626-8a02-2519102cb53a' ) 

Resultset:

+--------------------------------------+-----------+----------+-------+
|                  id                  | firstname | lastname | total |
+--------------------------------------+-----------+----------+-------+
| F0527AC3-747A-45A6-9CF9-B1F6C7F548F8 | Kasper    | Thomsen  |     3 |
| 95988F6D-9C91-4779-B6C3-3D4B4D6AE836 | Michael   | Jacobsen |     3 |
| 95988F6D-9C91-4779-B6C3-3D4B4D6AE836 | Michael   | Jacobsen |     3 |
+--------------------------------------+-----------+----------+-------+


PROBLEMA:

O problema está aqui, que Michael é contado duas vezes, e a contagem total é, portanto, 3, quando eu quero que seja 2. Ao usar count() over() você não pode analisar uma expressão nele, que contém distintas. Além disso, se eu apenas SELECT DISTINCT , meu conjunto de resultados parece bem, além da contagem total, que ainda seria 3.

Se eu precisar incluir mais informações para dar suporte à pergunta, avise-me e tentarei responder da melhor maneira possível.

MSSQL CREATE SCRIPT DO BANCO DE DADOS (SAMPLE DATA)

example_data.sql

queria o conjunto de resultados:

+--------------------------------------+-----------+----------+-------+
|                  id                  | firstname | lastname | total |
+--------------------------------------+-----------+----------+-------+
| F0527AC3-747A-45A6-9CF9-B1F6C7F548F8 | Kasper    | Thomsen  |     2 |
| 95988F6D-9C91-4779-B6C3-3D4B4D6AE836 | Michael   | Jacobsen |     2 |
+--------------------------------------+-----------+----------+-------+
    
por Thomas Teilmann 16.10.2015 в 10:00
fonte

4 respostas

1

Você realmente deve explicar o que precisa na pergunta, não nos comentários.

Em CTE_Users , encontramos todos os usuários distintos para as palavras-chave fornecidas. Em seguida, junte o resultado com user para obter detalhes do usuário. Pelo menos, ele produz o resultado esperado com os dados de amostra pequenos fornecidos.

WITH
CTE_Users
AS
(
    SELECT DISTINCT ku.user_id
    FROM
        keyword_user AS ku
    WHERE
        ku.keyword_id IN (
            '5f6501ec-0a71-4067-a21d-3c5f87a76411', 
            'c19b95c0-8554-4bbd-9526-db8f1c4f1edf')
        AND ku.user_id NOT IN (
            '12db3001-b3b9-4626-8a02-2519102cb53a')
)
SELECT TOP(5)
    u.id
    ,u.firstname
    ,u.lastname
    ,COUNT(*) OVER() AS total
FROM
    user AS u
    INNER JOIN CTE_Users ON CTE_Users.user_id = u.id
;
    
por Vladimir Baranov 16.10.2015 / 12:12
fonte
1

Estou um pouco confuso no seu caso, especialmente com as "palavras-chave" e como elas são relacionadas por usuário (isso é apenas um problema de processo para mim) e acabei fazendo um grupo externo contendo sua consulta inicial como meu tabela de origem.

Por favor, comente abaixo para que possamos melhorar isso.

SELECT 
    id
    , firstname
    , lastname
    , total
    , COUNT(*) AS [per_user_count]
FROM (
       SELECT TOP 5 u.[id], 
             u.[firstname], 
             u.[lastname], 
             total = Count(*) OVER() 
       FROM [user] u 
       INNER JOIN [keyword_user] ku 
           ON u.[id] = ku.[user_id] 
       WHERE 
       ( 
           ku.keyword_id IN (
              '5f6501ec-0a71-4067-a21d-3c5f87a76411'
              , 'c19b95c0-8554-4bbd-9526-db8f1c4f1edf'
           )
       ) 
       AND u.id NOT IN ('12db3001-b3b9-4626-8a02-2519102cb53a')
) AS T
GROUP BY
    T.id
    , T.firstname
    , T.lastname
    , T.total

EDIT: Nós realmente tivemos uma confusão lá, então eu criei um script mais simples que deve excluir as palavras-chave, apenas os usuários únicos (para gerar o total geral) e obter o top 5 dele (ordem aleatória). p>

    SELECT 
        TOP 5
        T.id
        , T.firstname
        , T.lastname
        , Total = COUNT(*) OVER()
    FROM (
        SELECT DISTINCT
            u.*
        FROM [keyword_user] ku
        LEFT JOIN [user] u
        ON
            ku.user_id = u.id
        WHERE 
        ( 
            ku.keyword_id IN (
                '5f6501ec-0a71-4067-a21d-3c5f87a76411'
                , 'c19b95c0-8554-4bbd-9526-db8f1c4f1edf')
        ) 
        AND ku.[user_id] NOT IN ( 
                '12db3001-b3b9-4626-8a02-2519102cb53a' 
            )
    ) AS T

Obrigado

EDIT: Seu cenário é um simples "busca de palavras-chave" que está ligado a uma entidade, com uma contagem global e um resultado top 5. Como eu entendo o CTE (e baseado no MSDN ), O CTE é uma ótima solução para mineração de dados hierárquica (não é necessário fazer o tempo necessário e fazer o back-flip para obter sua hierarquia organizacional), o que não se encaixa no cenário que temos aqui.

    
por ken lacoste 16.10.2015 / 10:39
fonte
0

Tente isto: Eu criei duas tabelas temporárias ( #user e #user_key ) e preencha-as.

create table #user (id int, name varchar(20))

create table #user_key (id int, fk_user int, content varchar(50))

insert into #user values 
(1, 'Giuseppe'),
(2, 'Anna'),
(3, 'Angela'),
(4, 'Maria'),
(5, 'Ethra'),
(6, 'Piero')

insert into #user_key values 
(1, 1, 'ciao'),
(2, 1, 'hello'),
(3, 2, 'hallo'),
(4, 4, 'hullo')

A consulta de extração:

Eu uso como tabela principal #user , então adiciono uma subconsulta sobre a contagem total, mas na cláusula order by eu tento ordenar os usuários sobre suas palavras-chave. Você pode adicionar outras condições (como seu In / NOT IN)

select top 5 id, name, (select COUNT(*) from #user_key uk)
from #user u
order by (select COUNT(*) from #user_key uk where uk.fk_user = u.id) desc

Vá para SqlFiddle

EDITAR

Você quer isso?:

97D476C2-B52C-4D44-A460-44472CBF8817    Michael testing 2
F4FE5550-BC69-437E-91A0-5B11E0D9279E    Kasper  Test    2

ou isso?

97D476C2-B52C-4D44-A460-44472CBF8817    Michael testing 2
F4FE5550-BC69-437E-91A0-5B11E0D9279E    Kasper  Test    2
12DB3001-B3B9-4626-8A02-2519102CB53A    Thomas  Teil    2
    
por Joe Taras 16.10.2015 / 10:03
fonte
0

Você pode tentar:

    SELECT TOP 5 * FROM (
    SELECT
        u.[id], 
        u.[firstname], 
        u.[lastname], 
        total = Count(*) OVER(PARTITION BY ku.keyword_id),
        rownum = ROW_NUMBER() OVER(PARTITION BY ku.keyword_id ORDER BY u.ID)
    FROM   [user] u 
    INNER JOIN [keyword_user] ku 
        ON u.[id] = ku.[user_id] 
    WHERE (ku.keyword_id IN ( '5f6501ec-0a71-4067-a21d-3c5f87a76411', 'c19b95c0-8554-4bbd-9526-db8f1c4f1edf')) 
           AND u.id NOT IN ( '12db3001-b3b9-4626-8a02-2519102cb53a' )
    ) AS A ORDER BY A.rownum DESC
    
por Nguyễn Hải Triều 16.10.2015 / 10:14
fonte