sql - SQL Server：选择每个类别中的前 N ​​条记录：Count-Rank-Select in one script

Question

现有的 问题已经有每个类别的基础数量，然后考虑如何选择每个类别的前 10 个，但是否可以在一个脚本中端到端地进行，即每个类别计数，按类别排名，然后选择每个类别的前 10 个? 我已经完成了前两个，现在想知道如何合并第三个，即在同一脚本中选择前 N 个结果：

;with cte as 
(
    select 
        custID, 
        day(tradedate) as TradeDate, 
        sum(tradecount) as TradeCountSum 
    from 
        tradetable
    where 
        tradedate >= '2018-6-17' 
    and 
        tradedate <= '2019-6-17'  
    group by 
        custID, 
        day(tradedate) 
)
select 
    custID, 
    tradedate, 
    TradeCountSum * 100 / sum(TradeCountSum) over (partition by custID) as TradeCountPercent 
    from cte 
    order by custID asc, TradeCountPercent desc 

谢谢

标签： sqlsql-server

Answer 1

一种方法是将 ntile 与结果集一起使用，然后将其包装到派生查询中，并在 where 子句中使用 ntile 值。

在下面的示例中，我将您的结果集拆分为按 tradecountpercent 降序排序的 10% 块，然后查询其中的顶部。

我还删除了“;” from 在 with 子句之前，因为这不是必需的，只要 with 子句之前的语句以分号终止即可。

declare @tradetable table (
    custid int,
    tradedate date,
    tradecount int
);

insert @tradetable (custid, tradedate, tradecount)
values
(1, '2018-06-17', 3),
(1, '2018-06-24', 1),
(1, '2018-07-02', 12),
(1, '2018-07-15', 4),
(1, '2018-07-21', 8),
(1, '2018-07-30', 56),
(1, '2018-08-29', 7),
(1, '2018-09-12', 2),
(1, '2018-10-17', 8),
(2, '2018-06-17', 3),
(2, '2018-06-24', 1),
(2, '2018-07-02', 12),
(2, '2018-07-15', 4),
(3, '2018-07-21', 8),
(3, '2018-07-30', 56),
(3, '2018-08-29', 7),
(3, '2018-09-12', 2),
(4, '2018-10-17', 8);


with cte as 
(
    select 
        custID, 
        day(tradedate) as TradeDate, 
        sum(tradecount) as TradeCountSum 
    from 
        @tradetable
    where 
        tradedate >= '2018-6-17' 
    and 
        tradedate <= '2019-6-17'  
    group by 
        custID, 
        day(tradedate) 
)

select * from (

select ntile(10)over(order by tradecountpercent desc) percents, * from
(
select 
    custID, 
    tradedate, 
    TradeCountSum * 100 / sum(TradeCountSum) over (partition by custID) as TradeCountPercent 
    from cte )x ) y where percents = 1
    order by custID asc, TradeCountPercent desc