SQLServer count () over () with distinct

Я работаю над проектом, в котором нам нужно подсчитать количество различных строк. Упрощенная версия сценария включает таблицу user, таблицу keyword и таблицу keyword_user.

Таблица user просто включает общие пользовательские метаданные, такие как имя и т. д. Остальные таблицы перечислены ниже.

Keyword_user: 

id
user_id
keyword_id


ключевое слово: 

id,
description

То, что я хочу сделать, это найти максимальное число пользователей (5), основываясь на пользователях keyword_id, а также подсчитывая общее число совпадающих строк. Граф должен быть четким.

Запрос: 

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' )

Результирующий набор: 

+--------------------------------------+-----------+----------+-------+
|                  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 |
+--------------------------------------+-----------+----------+-------+


задача:

Проблема в том, что Майкл считается дважды, и поэтому общее число равно 3, Когда я хочу, чтобы оно было 2. При использовании count() over() вы не можете разобрать выражение в нем, которое содержит distinct. Кроме того, если я просто SELECT DISTINCT, мой результирующий набор выглядит нормально, кроме общего количества, которое все равно будет 3.

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

MSSQL CREATE DB SCRIPT (SAMPLE DATA)

Example_data.sql

Требуемый результирующий набор: 

+--------------------------------------+-----------+----------+-------+
|                  id                  | firstname | lastname | total |
+--------------------------------------+-----------+----------+-------+
| F0527AC3-747A-45A6-9CF9-B1F6C7F548F8 | Kasper    | Thomsen  |     2 |
| 95988F6D-9C91-4779-B6C3-3D4B4D6AE836 | Michael   | Jacobsen |     2 |
+--------------------------------------+-----------+----------+-------+
4 9
sql-server window-functions inner-join

4 ответа:

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

В CTE_Users мы находим всех различных пользователей для заданных ключевых слов. Затем соедините результат с user, чтобы получить сведения о пользователе. По крайней мере, это дает результат, который вы ожидаете с заданными небольшими выборками данных.

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
;
1

Я немного запутался в вашем случае, особенно с "ключевыми словами" и тем, как они связаны с каждым пользователем (это просто проблема процесса для меня), поэтому я сделал внешнюю группу, содержа ваш первоначальный запрос в качестве исходной таблицы.

Пожалуйста, прокомментируйте ниже, чтобы мы могли улучшить это.

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: у нас действительно была путаница, поэтому я создал более простой скрипт, который должен исключить ключевые слова, только уникальных пользователей (чтобы генерировать общий итог) и получить ТОП-5 из него (случайный порядок).

    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

Спасибо

EDIT: ваш сценарий-это прямой "поиск ключевых слов", связанный с сущностью, с общим количеством и результатом топ-5. Как я понимаю CTE (и основанный на MSDN ), CTE-это отличное решение для иерархического интеллектуального анализа данных (не нужно делать while и делать любые обратные сальто, чтобы получить вашу организационную иерархию), которое на самом деле не соответствует сценарию, который мы имеем здесь.

1

Попробуйте это: Я создал две временные таблицы (#user и #user_key) и заполнил их.

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')

Запрос на извлечение:

Я использую в качестве основной таблицы #user, поэтому я добавляю подзапрос о total count, но в предложении order by я пытаюсь упорядочить пользователей по их ключевым словам. Вы можете добавить другие условия (как ваш 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

Перейти к SqlFiddle

EDIT

Ты хочешь этого?: 

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

Или это?

97D476C2-B52C-4D44-A460-44472CBF8817    Michael testing 2
F4FE5550-BC69-437E-91A0-5B11E0D9279E    Kasper  Test    2
12DB3001-B3B9-4626-8A02-2519102CB53A    Thomas  Teil    2
0

Вы можете попробовать:

    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
0