时间戳

首页 > 解决方案 > MS SQL - 按月对订单进行分组并显示该年的年度订单总额

sql - MS SQL - 按月对订单进行分组并显示该年的年度订单总额

问题描述

我需要创建一个查询来连接来自两个表的数据：

并给出以下输出：

这里的诀窍是每年的订单量。虽然每月的订单数量应按月份细分，但每个给定月份和销售人员的年度数量应相同。

我首先想到的是使用OVER (PARTITION BY <column x> ORDER BY <column y>).

所以，我想出了这个查询：

SELECT
    ORD.SalespersonPersonID,
    PEO.FullName,
    [Order Month] = MONTH(ORD.OrderDate),
    [Monthly Amount of Orders] = COUNT(ORD.OrderID),
    [Yearly Amount of Orders] = COUNT(ORD.OrderID) OVER (PARTITION BY ORD.SalespersonPersonID ORDER BY MONTH(ORD.OrderDate))
FROM [Sales].[Orders] ORD LEFT JOIN [Application].[People] PEO
ON ORD.SalespersonPersonID = PEO.PersonID
WHERE YEAR(ORD.OrderDate) = '2016'
GROUP BY ORD.SalespersonPersonID, PEO.FullName, MONTH(ORD.OrderDate), ORD.OrderID
ORDER BY PEO.FullName

问题是因为我不得不提到ORD.OrderID它GROUP BY打破了每月的聚合。

我采用了不同的方法并解决了这个问题（使用 CTE）：

WITH table1 AS
(
SELECT 
    SalespersonPersonID,
    [Yearly Amount of Orders] = COUNT(OrderID)
FROM [Sales].[Orders]
WHERE YEAR(OrderDate) = '2016'
GROUP BY SalespersonPersonID
)
SELECT
    ORD.SalespersonPersonID,
    PEO.FullName,
    [Order Month] = MONTH(ORD.OrderDate),
    [Monthly Amount of Orders] = COUNT(ORD.OrderID),
    T1.[Yearly Amount of Orders]
FROM [Sales].[Orders] ORD
LEFT JOIN [Application].[People] PEO
ON ORD.SalespersonPersonID = PEO.PersonID
LEFT JOIN table1 AS T1
ON ORD.SalespersonPersonID = T1.SalespersonPersonID
WHERE YEAR(ORD.OrderDate) = '2016'
GROUP BY ORD.SalespersonPersonID, PEO.FullName, MONTH(ORD.OrderDate), T1.[Yearly Amount of Orders]
ORDER BY PEO.FullName

我仍然想知道如何使用OVER (PARTITION BY <column x> ORDER BY <column y>). 感觉这是解决这个问题的更优雅的方法。

谢谢，

迈克尔

标签： sqlgroup-bycountsumwindow-functions

解决方案

不需要 cte 或类似的东西。从您的原始group by查询开始，您将执行以下操作：

SUM(COUNT(ORD.OrderID)) OVER (PARTITION BY ORD.SalespersonPersonID 
    as [Yearly Amount of Orders]

从概念上讲，窗口函数在执行聚合后对结果集进行操作。然后，您可以访问每行的每月计数，并为销售人员总结全年。

笔记：

  • 您希望同一个人的所有行都具有相同的值，因此不要在order by子句中使用over()子句

  • 我怀疑这OrderID永远不会null；如果是这样，则使用COUNT(*)而不是COUNT(ORD.OrderID). 它更有效，因为数据库不需要检查nullity

推荐阅读