Sql Выберите топ-3 в группе свертки

Question

Как мне вернуть только первые 3 значения для пары Name-datee в следующем?

DECLARE @t TABLE(NAME NVARCHAR(MAX),datee date,val money)

insert INTO @t SELECT 'a','2012-01-02',100
insert INTO @t SELECT 'a','2012-01-02',100
insert INTO @t SELECT 'a','2012-01-03',100
insert INTO @t SELECT 'a','2012-01-05',150
insert INTO @t SELECT 'a','2012-01-06',200
insert INTO @t SELECT 'b','2012-01-07',200
insert INTO @t SELECT 'b','2012-01-07',400
insert INTO @t SELECT 'b','2012-01-09',500
insert INTO @t SELECT 'b','2012-01-12',600
insert INTO @t SELECT 'b','2012-01-13',100

SELECT Name, datee, SUM(val) sumval from @t 
GROUP BY rollup(NAME ,datee)
order by Name, sumval desc

Эта текущая версия возвращает:

Name    datee       sumval
NULL    NULL        2450.00
a       NULL        650.00
a       2012-01-02  200.00
a       2012-01-06  200.00
a       2012-01-05  150.00
a       2012-01-03  100.00
b       NULL        1800.00
b       2012-01-07  600.00
b       2012-01-12  600.00
b       2012-01-09  500.00
b       2012-01-13  100.00

Я хотел бы вернуться:

Name    datee       sumval
NULL    NULL        2450.00
a       NULL        650.00
a       2012-01-02  200.00
a       2012-01-06  200.00
a       2012-01-05  150.00
b       NULL        1800.00
b       2012-01-07  600.00
b       2012-01-12  600.00
b       2012-01-09  500.00

Я думал, что будет простой способ, но не могу понять это!

Answer 1

;with cteBase as (
Select Name
      ,datee
      ,sumval=SUM(val)
      ,rowNr=ROW_NUMBER() over (Partition By Name Order by sum(Val) Desc)
 From @t 
GROUP BY rollup(NAME ,datee)
)
Select * 
 From cteBase 
 Where RowNr<=4
order by Name, sumval desc

Возвращает

Name    datee       sumval  rowNr
NULL    NULL        2450.00 1
a       NULL        650.00  1
a       2012-01-02  200.00  2
a       2012-01-06  200.00  3
a       2012-01-05  150.00  4
b       NULL        1800.00 1
b       2012-01-07  600.00  2
b       2012-01-12  600.00  3
b       2012-01-09  500.00  4

Answer 2

Это большой сложный из-за rollup, Я думаю, что будет работать следующее:

SELECT t.*
FROM (SELECT Name, datee, SUM(val) as sumval,
             ROW_NUMBER() OVER (PARTITION BY Name ORDER BY SUM(val) DESC) as seqnum
      FROM @t  t
      GROUP BY rollup(NAME, datee)
     ) t
WHERE seqnum <= 3 or Datee is NULL
ORDER BY Name, sumval desc;

Если это не сработает (я не могу проверить прямо сейчас), это будет:

SELECT Name, datee, sumval
FROM (SELECT Name, datee, SUM(sumval) as sumval, MAX(seqnum) as seqnum
      FROM (SELECT Name, datee, SUM(val) as sumval,
                   ROW_NUMBER() OVER (PARTITION BY Name ORDER BY SUM(val) DESC) as seqnum
            FROM @t  t
            GROUP BY Name, datee
           ) t
      GROUP BY rollup(NAME, datee)
     ) t
WHERE seqnum <= 3 or Date is NULL
ORDER BY Name, sumval desc;

Answer 3

Возможно используя row_number с subquery можете достичь ваших результатов:

select * 
from (
    select *, 
           row_number() over (partition by name order by sumval desc) rn
    from (
        select Name, datee, SUM(val) sumval
        from @t 
        group by rollup(NAME ,datee)
        order by Name, sumval desc
    ) t
) t
where rn <= 3 or datee is null

---

Вы могли бы использовать row_number() over (partition by name order by sum(val)) вместо этого и удалите один из подзапросов.

select * 
from (
        select Name, datee, SUM(val) sumval, 
        row_number() over (partition by name order by SUM(val) desc) rn
        from @t 
        group by rollup(NAME ,datee)
        order by Name, sumval desc
) t
where rn <= 3 or datee is null

Answer 4

Вам нужно будет использовать RANK() или же ROW_NUMBER() чтобы проиндексировать вывод, вам нужно будет заключить его в подзапрос и отфильтровать все строки выше любого нужного вам числа.

Как это:

SELECT 
    t.Name, 
    t.date,
    t.sumval 
FROM
(
    SELECT 
        Name, 
        date, 
        SUM(val) sumval, 
        ROW_NUMBER() OVER (ORDER BY sumval, PARTITION BY name) RowId
    from @t GROUP BY rollup(NAME ,datee)
) t 
WHERE RowId <= 3
order by Name, sumval desc