如何计算每个客户在一个月内的购买次数?

Question

我想知道自从这家店开业以来，一位顾客每月买了几次东西。

我有这样的代码:

SELECT 
Descrip Customer_Name
, CodClie ID_Customer
, CASE WHEN TipoFac = 'A' THEN Monto
ELSE -Monto END AS Purchase_Amount$
, CONVERT(varchar, FechaE, 1) AS Date
, CodUbic Store_Location
, DENSE_RANK() OVER ( partition by Descrip order by NumeroD) + 
DENSE_RANK() OVER (partition by Descrip order by NumeroD desc) -1 as Times_bought_since_1st_Day


FROM 
dbo.SAFACT
WHERE
TipoFac IN ('A', 'B')
GROUP BY  
FechaE --- Date in timestamp, that's why I converted it
, CodUbic --- Stores Location
, Monto --- Amount spent $
, Descrip --- Customer Name
, TipoFac --- A for purchases, B for returned products, that why I make the Case Statement
, CodClie --- ID Customer
, NumeroD --- NumeroD = Bill

ORDER BY YEAR(FechaE) DESC, MONTH(FechaE) DESC, DAY(FechaE) DESC;

如果我运行这段代码，它将显示如下内容:

Customer_Name ID_Customer Purchase_Amount$    Date     Store_Location Times_bought_since_1st_Day  
------------------------------------------------------------------------------------------------
Peter Grifin   12345678      1.5           06/14/2022    000-1               10
Ralph Lauren   89101112      2.0           06/14/2022    000-4               15
Meg Stacy      13141516      4.0           06/14/2022    000-5               10

这里的问题是，我需要每月购买的次数，即使有可能根据每个客户的名字和他们的消费金额以及购买次数组成一个组

预期的结果类似于:

Customer_Name ID_Customer Purchase_Amount$    Date     StoreLocation TimesBoughtSince1stDay TimesBoughtCurrentMonth  TotalSpentSince1stDay    TotalSpentThisMonth
------------------------------------------------------------------------------------------------------------------------------------------------------------
Peter Grifin   12345678      1.5           06/14/2022    000-1               10                20                      100.000               1.000
Ralph Lauren   89101112      2.0           06/14/2022    000-4               15                05                      25.000                500.00        
Meg Stacy      13141516      4.0           06/14/2022    000-5               18                10                      15.000                200.00

是否有可能分割每月购买的次数?

Answer 1

我认为您很困惑，因为您想从一开始就在一个表中混合不同时间聚合内的计算。如果你把一个问题想象成三个不同的问题，然后把它们混合在一起，就会容易一些。

尝试在不同的表中通过时间聚合然后连接表来计算。

您想要实现的前5列是具有天数聚合的事务数据。每一行都是某一天的销售，顾客每天可以购买1次或更多。

那么，对于列TimesBoughtSince1stDay和TotalSpentSince1stDay，您不需要按日期进行聚合。您需要按客户的整个历史记录进行聚合，您可以这样做:

SELECT 
Descrip Customer_Name
,CodClie ID_Customer
,sum(CASE WHEN TipoFac = 'A' THEN Monto ELSE -Monto END) 'Purchase_Amount$'
, count( DISTINCT NumeroD ) -- here I don´t understand that much your code but you need to count differents SaleId's
FROM dbo.SAFACT
WHERE TipoFac IN ('A', 'B')
GROUP BY Descrip Customer_Name, CodClie ID_Customer

对于月时间聚合，您可以这样做:

SELECT 
Descrip Customer_Name
, CodClie ID_Customer
, sum(CASE WHEN TipoFac = 'A' THEN Monto
ELSE -Monto END) 'Purchase_Amount$'
, count( DISTINCT NumeroD ) -- here I don´t understand that much your code but you need to count differents SaleId's
FROM 
dbo.SAFACT
WHERE
TipoFac IN ('A', 'B')
AND MONTH(CONVERT(DATE,FechaE)) = MONTH(GETDATE())
AND YEAR(CONVERT(DATE,FechaE)) = YEAR(GETDATE())
GROUP BY Descrip Customer_Name, CodClie ID_Customer, MONTH(CONVERT(DATE,FechaE))

一旦你有了3个不同的表(按日期聚合、按客户历史聚合和按月聚合)，你需要在"按日期聚合"上执行左连接。表。

当然，有些代码不是100%可以运行，但我希望你能抓住主要思想。希望能有所帮助!