Как выбрать 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 не будет работать, если количество записей меньше вашего номера плитки.
