sql-server - 为范围内的每个月选择前 5 个 SUM(每个客户一个)

Question

我有一个查询，可以提取客户的月/年总计，并添加 ntile 排名。如果我能够提取 ntile 1、2、3、4 和 5 的最大小计，我几乎可以得到我想要的，但我不知道如何继续。

例如，我想要的结果类似于:

Month   Year   CustomerCode   SubTotal   ntile
1       2012   CCC            131.45     1
1       2012   CCC            342.95     2
1       2012   ELITE          643.92     3
1       2012   CCC            1454.05    4
1       2012   CCC            12971.78   5
2       2012   CCC            135.99     1
2       2012   CCI            370.47     2
2       2012   NOC            766.84     3
2       2012   ELITE          1428.26    4
2       2012   VBC            5073.20    5
3       2012   CCC            119.02     1
3       2012   CCC            323.78     2
3       2012   HUCC           759.66     3
3       2012   ELITE          1402.95    4
3       2012   CCC            7964.20    5 

除了 - 我希望排名会像第 2 个月的不同客户一样，但我的基本查询没有给我那个结果 - 我显然不知道如何在 SQL SERVER 2005 上的 T-SQL 中获得它 -事实上我不确定我得到了什么。

我的下一个选择是在 C# 中拉一个 DataTable 并做一些体操来到达那里，但必须有一个更简单的方法:)

我的基本查询是

SELECT 
i.DateOrdered
,LTRIM(STR(DATEPART(MONTH,i.DateOrdered))) AS [Month]   
,LTRIM(STR(YEAR(i.Dateordered))) AS [Year]   
,c.CustomerCode 
,SUM(i.Jobprice) AS Subtotal  
,NTILE(5) OVER(ORDER BY SUM(i.JobPrice)) AS [ntile]
FROM Invoices i 
JOIN 
Customers c 
ON i.CustomerID = c.ID 
WHERE i.DateOrdered >= '1/1/2012'
AND i.DateOrdered <= '9/30/2012' 
GROUP BY YEAR(i.DateOrdered),  MONTH(i.DateOrdered), i.DateOrdered, c.CustomerCode
ORDER BY LTRIM(STR(DATEPART(MONTH,i.DateOrdered))),   
TRIM(STR(YEAR(i.Dateordered))),     
SUM(i.JobPrice), c.CustomerCode ASC

我非常感谢能帮助我解决这个问题。

提前致谢

悬崖

Answer 1

如果我没看错的话，你想要的是

对于范围内的每个月，
显示当月 SUM 最高的 5 位客户
并针对每个客户显示相应的 SUM。

在那种情况下，这个 SQL Fiddle创建一个示例表并运行为您提供上述输出的查询。如果您想查看创建的表中的内容，只需在右侧面板上执行简单的 SELECT。

查询是:

;     WITH G as -- grouped by month and customer
(
    SELECT DATEADD(D,1-DAY(i.DateOrdered),i.DateOrdered) [Month],
           c.CustomerCode,
           SUM(i.Jobprice) Subtotal
      FROM Invoices i
      JOIN Customers c ON i.CustomerID = c.ID
     WHERE i.DateOrdered >= '1/1/2012' AND i.DateOrdered <= '9/30/2012'
  GROUP BY DATEADD(D,1-DAY(i.DateOrdered),i.DateOrdered), c.CustomerCode
)
    SELECT MONTH([Month]) [Month],
           YEAR([Month]) [Year],
           CustomerCode,
           SubTotal,
           Rnk [Rank]
      FROM
(
    SELECT *, RANK() OVER (partition by [Month] order by Subtotal desc) Rnk
      FROM G
) X
     WHERE Rnk <= 5
  ORDER BY Month, Rnk

解释一下，第一部分(WITH block )只是一种编写子查询的奇特方式，它按月份和客户对数据进行分组。表达式 DATEADD(D,1-DAY(i.DateOrdered),i.DateOrdered) 将每个日期变成该月的第一天，这样数据就可以很容易地按月分组。以传统形式编写的下一个子查询按小计在每个月内添加一个 RANK 列，最终 SELECTed 给出前 5*。

请注意，RANK 允许相同的排名，如果其中 3 个客户在第 4 位的排名相同，则最终可能会在一个月内显示 6 个客户。如果这不是您想要的，那么您可以更改单词 RANK 到 ROW_NUMBER，这将在相等的小计之间随机打破平局。