按年份选择前n％

Question

我建立了一个查询，使年初至今的净销售额回升到最近完成的月份。查询联合来自发票和贷项凭证的总计。效果很好。我在另一个工具中使用查询，该工具按卡代码进行求和，并允许我进行有趣的操作等。这是该查询：

select x.cardcode, 
       x.customer,
       case 
         when x.rep is null then (select slpname from ocrd inner join oslp on ocrd.slpcode = oslp.slpcode where ocrd.cardcode = x.cardcode)
         else
           x.rep
       end as rep, 
       x.city, 
       x.state, 
       x.country,
       case
         when isnumeric(x.total) = 0 then 0
         else x.total
       end as [net total],
       x.docdate

from (
  select t0.cardcode as cardcode, 
         t0.[cardname] as customer, 
         t1.city as city,
         t1.state as state,
         t1.country as country, 
         t4.slpname as rep, 
         sum(t3.linetotal) - t2.discsum as total,
         t2.docdate as [docdate]

  from ocrd t0  
       inner join crd1 t1 on (t0.cardcode = t1.cardcode and t0.shiptodef = t1.address) 
       left outer join oinv t2 on t0.cardcode = t2.cardcode 
       left outer join inv1 t3 on t2.docentry = t3.docentry 
       left outer join oslp t4 on t2.slpcode = t4.slpcode

  where t0.[cardtype] = 'C' and 
            t1.adrestype = 'S'

  group by t0.cardcode, t0.cardname, t1.city, t1.state, t1.country, t4.slpname, t2.discsum, t2.docdate

  union all

  select t0.cardcode as cardcode, 
         t0.cardname as customer, 
         t1.city as city,
         t1.state as state, 
         t1.country as country,
         t4.slpname as rep, 
         -1*(sum(t3.linetotal) - t2.discsum) as total,
         t2.docdate

  from ocrd t0  
      inner join crd1 t1 on (t0.cardcode = t1.cardcode and t0.shiptodef = t1.address) 
      left outer join orin t2 on t0.cardcode = t2.cardcode 
      left outer join rin1 t3 on t2.docentry = t3.docentry 
      left outer join oslp t4 on t2.slpcode = t4.slpcode

  where t0.[cardtype] = 'C' and 
          t1.adrestype = 'S'

  group by t0.cardcode, 
           t0.cardname, 
           t1.city,
           t1.state, 
           t1.country,
           t4.slpname, 
           t2.discsum,
           t2.docdate) x

where (x.docdate between '2008/01/01' and dateadd(day, -1, '2008/' + cast(month(getdate()) as varchar(2)) + '/01')
        or x.docdate between '2009/01/01' and dateadd(day, -1, '2009/' + cast(month(getdate()) as varchar(2)) + '/01')
          or x.docdate between '2010/01/01' and dateadd(day, -1, '2010/' + cast(month(getdate())  as varchar(2)) + '/01'))

group by x.cardcode, x.customer, x.rep, x.city, x.state, x.country, x.total, x.docdate

现在，我想修改查询以返回每年客户净额的前n个，例如20％。这是我遇到麻烦的地方。我正在使用SQL
Server，所以首先我想我会尝试使用row_number（）over（partition .... ）。这是我的第一次尝试：

select m.Cardcode, m.Customer, m.Rep, m.City, m.State, m.Country, m.Nettotal as 'Net Total', m.docdate as 'Posting Date'
from (
  select t.cardcode, t.customer, t.rep, t.city, t.state, t.country, t.nettotal, t.docdate, row_number() over(partition by t.docdate order by t.nettotal desc) as rownum
  from (
      select x.cardcode, 
             x.customer,
             case 
               when x.rep is null then (select slpname from ocrd inner join oslp on ocrd.slpcode = oslp.slpcode where ocrd.cardcode = x.cardcode)
               else
                 x.rep
             end as rep, 
             x.city, 
             x.state, 
             x.country,
             case
               when isnumeric(x.total) = 0 then 0
               else x.total
             end as nettotal,
             x.docdate

      from (
        select t0.cardcode as cardcode, 
               t0.[cardname] as customer, 
               t1.city as city,
               t1.state as state,
               t1.country as country, 
               t4.slpname as rep, 
               sum(t3.linetotal) - t2.discsum as total,
               t2.docdate as docdate

        from ocrd t0  
             inner join crd1 t1 on (t0.cardcode = t1.cardcode and t0.shiptodef = t1.address) 
             left outer join oinv t2 on t0.cardcode = t2.cardcode 
             left outer join inv1 t3 on t2.docentry = t3.docentry 
             left outer join oslp t4 on t2.slpcode = t4.slpcode

        where t0.[cardtype] = 'C' and 
                  t1.adrestype = 'S'

        group by t0.cardcode, 
                 t0.cardname, 
                 t1.city, 
                 t1.state, 
                 t1.country, 
                 t4.slpname, 
                 t2.discsum, 
                 t2.docdate

        union all

        select t0.cardcode as cardcode, 
               t0.cardname as customer, 
               t1.city as city,
               t1.country as country,
               t1.state as state, 
               t4.slpname as rep, 
               -1*(sum(t3.linetotal) - t2.discsum) as total,
               t2.docdate

        from ocrd t0  
            inner join crd1 t1 on (t0.cardcode = t1.cardcode and t0.shiptodef = t1.address) 
            left outer join orin t2 on t0.cardcode = t2.cardcode 
            left outer join rin1 t3 on t2.docentry = t3.docentry 
            left outer join oslp t4 on t2.slpcode = t4.slpcode

        where t0.[cardtype] = 'C' and 
                    t1.adrestype = 'S'

        group by t0.cardcode, 
                 t0.cardname, 
                 t1.city,
                 t1.state, 
                 t1.country,
                 t4.slpname, 
                 t2.discsum,
                 t2.docdate) x

  where (x.docdate between '2008/01/01' and dateadd(day, -1, '2008/' + cast(month(getdate()) as varchar(2)) + '/01')
            or x.docdate between '2009/01/01' and dateadd(day, -1, '2009/' + cast(month(getdate()) as varchar(2)) + '/01')
              or x.docdate between '2010/01/01' and dateadd(day, -1, '2010/' + cast(month(getdate())  as varchar(2)) + '/01'))

  group by x.cardcode, 
           x.customer, 
           x.rep, 
           x.city, 
           x.state, 
           x.country, 
           x.total, 
           x.docdate) as t
) as m

where rownum <= 20

即使我做对了，走这条路还是很麻烦的，因为它不允许我获得前n个百分点，只能得到前n个。

我还没有尝试使用交叉应用或子选择来达到我想要的结果。

有人可以帮我解决这个问题吗？另外，如何编写它可能不是很有效，并且硬编码日期范围选择也不是一个好的解决方案。我想有很多需要改进的地方：)

感谢您的帮助。

Answer 1

您可以在SQL Server中使用一种排名功能-但不能，ROW_NUMBER()但是可以使用NTILE()。

NTILE()会将结果集分解为您指定的尽可能多的数据块-由于您希望排在前20％，因此您可能会使用NTILE(5)。

因此，您的CTE应该看起来像这样：

WITH CustomerPerYear AS
(
   SELECT 
       c.Name, s.Sales, 
       NTILE(5) OVER (PARTITION BY c.CustomerID ORDER BY s.Amount DESC) as 'NTile'
   FROM
       dbo.Customer c
   INNER JOIN
       dbo.Sales s ON s.CustomerID = c.CustomerID
)
SELECT *
FROM CustomerPerYear
WHERE NTile = 1

因此，基本上，您是按客户划分数据，然后将每个客户的销售按销售量排序为5个NTile组。对于每个客户，NTILE = 1是销售额的前20％。

如果需要，请参阅NTILE上的MSDN文档以获取更多详细信息。