sql - 过去 12 个月和单个月份的列数

Question

我的要求有点复杂，为了便于理解，让我用简单的例子说明一下。假设，我们必须计算过去 12 个月消费者按月购买的产品数量。所以我们要做的是:

SELECT DATENAME(MONTH, m.InvoiceDate) Month,
COUNT(m.Quantity) Quantity FROM CustomerInvoice m WHERE 
DATEDIFF(month, m.InvoiceDate, GETDATE()) <= 12 AND YEAR(m.InvoiceDate) = '2018'
GROUP BY DATENAME(MONTH, m.InvoiceDate)
ORDER BY DATENAME(MONTH, m.InvoiceDate) DESC;

输出 - 这就是我们得到的:

Month       Quantity
January     1
February    1
March       1
April       2
May         1
June        3
July        1
August      2

就我而言，它有点复杂，如以下示例:

因此，如果输入是 July 2018，它应该计算过去 12 个月的数量或值(value)以及个别月份的过去 12 个月的值(value)。这似乎类似于递归调用。但非常不确定如何以正确的方式完成此任务 - 任何想法或示例将不胜感激 - 谢谢。

带有数据的示例表:

CREATE TABLE [dbo].[CustomerInvoice](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ProductID] [int] NULL,
    [Quantity] [float] NULL,
    [Details] [varchar](max) NULL,
    [InvoiceID] [varchar](40) NULL,
    [CustomerID] [int] NULL,
    [InvoiceDate] [datetime] NULL
)

INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (1, 2, 2, N'Awesome Collections', N'20151129115910', 1, CAST(0x0000A94200000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (2, 1, 2, N'Joss Collections', N'20151129115910', 1, CAST(0x0000A94200000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (3, 2, 2, N'Awesome Collections', N'20151129115910', 1, CAST(0x0000A91900000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (4, 1, 2, N'Awesome Collections', N'20151129115910', 1, CAST(0x0000A8FC00000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (5, 2, 2, N'Awesome Collections', N'20151129115912', 2, CAST(0x0000A8F300000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (6, 1, 2, N'Joss Collections', N'20151129115912', 2, CAST(0x0000A8F400000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (7, 1, 4, N'Joss Collections', N'20151130120336', 1, CAST(0x0000A8DF00000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (8, 1, 10, N'Awesome Collections', N'20151130120336', 1, CAST(0x0000A8BA00000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (9, 2, 10, N'Awesome Collections', N'20151130120616', 1, CAST(0x0000A8BE00000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (10, 1, 10, N'Joss Collections', N'20151130120616', 1, CAST(0x0000A8AB00000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (11, 2, 12, N'Great', N'20151130125238', 2, CAST(0x0000A88700000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (12, 1, 12, N'Great', N'20151130125238', 2, CAST(0x0000A86F00000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (13, 2, 4, N'Great', N'20151202085309', 6, CAST(0x0000A85700000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (14, 1, 10, N'Joss Collections', N'20151202085309', 6, CAST(0x0000A81E00000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (15, 2, 10, N'Customer likes this a lot.', N'20151203101624', 1, CAST(0x0000A80F00000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (16, 2, 10, N'Customer likes this a lot.', N'20151203102205', 1, CAST(0x0000A7E500000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (17, 2, 2, N'Wao!! Great.', N'20151203103844', 1, CAST(0x0000A7CE00000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (18, 1, 4, N'Just Awesome.', N'20151203103844', 1, CAST(0x0000A7BD00000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (19, 2, 12, N'Customers just love the product.', N'20151203104143', 2, CAST(0x0000A78600000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (20, 1, 2, N'Awesome Collections.', N'20151203104143', 2, CAST(0x0000A79000000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (21, 2, 6, N'Awesome Collections.', N'20151203104945', 1, CAST(0x0000A78F00000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (22, 1, 6, N'Great Collection.', N'20151203104945', 1, CAST(0x0000A79300000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (23, 1, 10, N'Customer likes this a lot.', N'20151203105528', 1, CAST(0x0000A76F00000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (24, 2, 4, N'Great Collection.', N'20151203105528', 1, CAST(0x0000A75300000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (25, 2, 1, N'Just Great.', N'20151203110653', 7, CAST(0x0000A75C00000000 AS DateTime))
INSERT [dbo].[CustomerInvoice] ([ID], [ProductID], [Quantity], [Details], [InvoiceID], [CustomerID], [InvoiceDate]) VALUES (26, 1, 1, N'Customers are happy with the product.', N'20151203110653', 7, CAST(0x0000A76400000000 AS DateTime))

更新 1 - 此计算针对过去 12 个月进行，请查看下面的数量计算方式:

Month-Year  Quantity
Jul 2018    80 (Sum of quantity from Jul 2018 to Jun 2017)  
Jun 2018    70 (Sum of quantity from Jun 2018 to May 2017)      
May 2018    42 (Sum of quantity from May 2018 to Apr 2017)      
Apr 2018    11 ...........................................  
Mar 2018    6  ...........................................  
Feb 2018    10 ...........................................  
Jan 2018    16 ...........................................
Dec 2017    1  ...........................................
..........................................................
Jun 2017    10 (Sum of quantity from Jun 2017 to May 2016)

Answer 1

这回答了 SQL Server 2012+ 的问题(我回答问题时没有指定 SQL Server 版本)。

我建议使用累计和。这假设您每个月都有数据，但这似乎是合理的。对于累计年初至今:

SELECT YEAR(m.InvoiceDate) as yyyy,
       MONTH(m.InvoiceDate) as mm,
       SUM(m.Quantity) as Quantity,
       SUM(SUM(m.Quantity)) OVER (PARTITION BY YEAR(m.InvoiceDate)
                                  ORDER BY MONTH(m.InvoiceDate)
                                 ) 
FROM CustomerInvoice m
WHERE YEAR(m.InvoiceDate) = 2018
GROUP BY YEAR(m.InvoiceDate), MONTH(m.InvoiceDate)
ORDER BY YEAR(m.InvoiceDate), MONTH(m.InvoiceDate);

请注意，这使用 SUM(m.Quantity) 而不是 COUNT()。 SUM() 根据您的描述更有意义。

如果您想要累计运行 12 个月的总和，忽略年份界限，请使用窗口子句:

SELECT YEAR(m.InvoiceDate) as yyyy,
       MONTH(m.InvoiceDate) as mm,
       SUM(m.Quantity) as Quantity,
       SUM(SUM(m.Quantity)) OVER (PARTITION BY YEAR(m.InvoiceDate)
                                  ORDER BY MONTH(m.InvoiceDate)
                                  ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
                                 ) 
FROM CustomerInvoice m
GROUP BY YEAR(m.InvoiceDate), MONTH(m.InvoiceDate)
ORDER BY YEAR(m.InvoiceDate), MONTH(m.InvoiceDate);

编辑:

在 SQL Server 2008 中，您可以将第一个查询表示为:

WITH ym as (
      SELECT YEAR(m.InvoiceDate) as yyyy,
             MONTH(m.InvoiceDate) as mm,
             SUM(m.Quantity) as Quantity,
      FROM CustomerInvoice m
      WHERE YEAR(m.InvoiceDate) = 2018
      GROUP BY YEAR(m.InvoiceDate), MONTH(m.InvoiceDate)
     )
SELECT ym.yyyymm, ym.quantity, ym2.sum_Quantity)
FROM ym CROSS APPLY
     (SELECT SUM(ym2.quantity) as sum_quantity
      FROM ym ym2
      WHERE ym.yyyy = ym2.yyyy AND ym2.month <= ym.month
     ) ym2
ORDER BY YEAR(m.InvoiceDate), MONTH(m.InvoiceDate);

对于 SQL Server 2008 中的滚动总和:

WITH ym as (
      SELECT YEAR(m.InvoiceDate) as yyyy,
             MONTH(m.InvoiceDate) as mm,
             SUM(m.Quantity) as Quantity,
      FROM CustomerInvoice m
      WHERE YEAR(m.InvoiceDate) = 2018
      GROUP BY YEAR(m.InvoiceDate), MONTH(m.InvoiceDate)
     )
SELECT ym.yyyymm, ym.quantity, ym2.sum_Quantity)
FROM ym CROSS APPLY
     (SELECT SUM(ym2.quantity) as sum_quantity
      FROM ym ym2
      WHERE ym2.InvoiceDate >= DATEADD(year, -1, ym.InvoiceDate) AND
            ym2.InvoiceDate <= ym.InvoiceDate
     ) ym2
ORDER BY YEAR(m.InvoiceDate), MONTH(m.InvoiceDate);