Как выбрать TOP 5 PERCENT из каждой группы?

У меня есть пример таблицы:

CREATE TABLE #TEMP(Category VARCHAR(100), Name VARCHAR(100))

INSERT INTO #TEMP VALUES('A', 'John')
INSERT INTO #TEMP VALUES('A', 'John')
INSERT INTO #TEMP VALUES('A', 'John')
INSERT INTO #TEMP VALUES('A', 'John')
INSERT INTO #TEMP VALUES('A', 'John')
INSERT INTO #TEMP VALUES('A', 'John')
INSERT INTO #TEMP VALUES('A', 'Adam')
INSERT INTO #TEMP VALUES('A', 'Adam')
INSERT INTO #TEMP VALUES('A', 'Adam')
INSERT INTO #TEMP VALUES('A', 'Adam')
INSERT INTO #TEMP VALUES('A', 'Lisa')
INSERT INTO #TEMP VALUES('A', 'Lisa')
INSERT INTO #TEMP VALUES('A', 'Bucky')
INSERT INTO #TEMP VALUES('B', 'Lily')
INSERT INTO #TEMP VALUES('B', 'Lily')
INSERT INTO #TEMP VALUES('B', 'Lily')
INSERT INTO #TEMP VALUES('B', 'Lily')
INSERT INTO #TEMP VALUES('B', 'Lily')
INSERT INTO #TEMP VALUES('B', 'Tom')
INSERT INTO #TEMP VALUES('B', 'Tom')
INSERT INTO #TEMP VALUES('B', 'Tom')
INSERT INTO #TEMP VALUES('B', 'Tom')
INSERT INTO #TEMP VALUES('B', 'Ross')
INSERT INTO #TEMP VALUES('B', 'Ross')
INSERT INTO #TEMP VALUES('B', 'Ross')

SELECT Category, Name, COUNT(Name) Total
FROM #TEMP
GROUP BY Category, Name
ORDER BY Category, Total DESC

DROP TABLE #TEMP

Дает мне следующее:

A   John    6
A   Adam    4
A   Lisa    2
A   Bucky   1
B   Lily    5
B   Tom     4
B   Ross    3

Теперь, как выбрать записи TOP 5 PERCENT из каждой категории, если каждая категория содержит более 100 записей (в таблице примеров здесь не отображается)? Например, в моей фактической таблице он должен удалить запись John из A и Lily записи из B в соответствии с этим (опять же, я не показал полную таблицу здесь), чтобы получить:

A   Adam    4
A   Lisa    2
A   Bucky   1
B   Tom     4
B   Ross    3

Я пытаюсь использовать предложения CTE и PARTITION BY, но, похоже, не могу достичь того, чего хочу. Он удаляет TOP 5 PERCENT из общего результата, но не из каждой категории. Любые предложения?

Ответ 1

Вы можете использовать CTE (Common Table Expression) в паре с функцией NTILE windowing - это позволит срезать ваши данные на столько фрагментов, сколько вам нужно, например. в вашем случае, на 20 ломтиков (каждый 5%).

;WITH SlicedData AS
(
   SELECT Category, Name, COUNT(Name) Total,
            NTILE(20) OVER(PARTITION BY Category ORDER BY COUNT(Name) DESC) AS  'NTile'
   FROM #TEMP
   GROUP BY Category, Name
)
SELECT *
FROM SlicedData
WHERE NTile > 1

Это в основном группирует ваши данные с помощью Category,Name, заказы на что-то еще (не уверен, что COUNT(Name) действительно то, что вам нужно здесь), а затем разрезает его на 20 штук, каждый из которых представляет 5% вашего раздела данных, Срез с NTile = 1 является верхним 5% срезом - просто игнорируйте это при выборе из CTE.

См:

для получения дополнительной информации

Ответ 2

Изменить: я добавил второе решение

SELECT   b.Id
        ,b.Category
        ,b.Name
        ,b.CategoryNameCount
FROM
(
        SELECT   a.Id   
                ,a.Category
                ,a.Name
                ,COUNT(*)OVER(PARTITION BY a.Category, a.Name) CategoryNameCount
                ,COUNT(*)OVER(PARTITION BY a.Category) CategoryCount
        FROM    #TEMP a
) b
WHERE   b.CategoryCount*5.0/100 > b.CategoryCount*b.CategoryNameCount*1.0/100
ORDER BY b.Category, b.CategoryNameCount DESC, b.Name

Результаты:

Id          Category Name       CategoryNameCount
----------- -------- ---------- -----------------
7           A        Adam       4
8           A        Adam       4
9           A        Adam       4
10          A        Adam       4
11          A        Lisa       2
12          A        Lisa       2
13          A        Bucky      1
19          B        Tom        4
20          B        Tom        4
21          B        Tom        4
22          B        Tom        4
23          B        Ross       3
24          B        Ross       3
25          B        Ross       3

или

SELECT   b.Category, b.Name, b.CategoryNameCount
FROM
(
        SELECT  
                 a.Category
                ,a.Name
                ,COUNT(*)OVER(PARTITION BY a.Category, a.Name) CategoryNameCount
                ,COUNT(*)OVER(PARTITION BY a.Category) CategoryCount
        FROM    #TEMP a
) b
WHERE   b.CategoryCount*5.0/100 > b.CategoryCount*b.CategoryNameCount*1.0/100
GROUP BY b.Category, b.Name, b.CategoryNameCount
ORDER BY b.Category, b.CategoryNameCount DESC, b.Name

Результаты:

Category Name       CategoryNameCount
-------- ---------- -----------------
A        Adam       4
A        Lisa       2
A        Bucky      1
B        Tom        4
B        Ross       3

Ответ 3

select Category,name,CountTotal,RankSeq,(50*CountTotal)/100 from (
select Category,name,COUNT(*)
over (partition by Category,name ) as CountTotal,
ROW_NUMBER()
over (partition by Category,name order by Category) RankSeq from #TEMP
--group by Category,Name 
) temp
where RankSeq <= ((50*CountTotal)/100)
order by Category,Name,RankSeq

Вывод:

Category    name     CountTotal RankSeq     50*CountTotal)/100
A           Adam     4          1           2
A           Adam     4          2           2
A           John     6          1           3
A           John     6          2           3
A           John     6          3           3
A           Lisa     2          1           1
B           Lily     5          1           2
B           Lily     5          2           2
B           Ross     3          1           1
B           Tom      4          1           2
B           Tom      4          2           2

Надеюсь, это поможет:)

Ответ 4

;WITH SlicedData AS
(
   SELECT Category, Name, COUNT(Name) Total,
            **PERCENT_RANK() OVER(PARTITION BY Category ORDER BY COUNT(Name) DESC) * 100** AS  'Percent'
   FROM #TEMP
   GROUP BY Category, Name
)
SELECT *
FROM SlicedData
WHERE Percent < 5

NTile не будет работать, если количество записей меньше вашего номера плитки.