第一个月的客户购买数量

Question

我有一个客户订单列表。我可以很容易地计算每个客户的首次购买月份和年份(例如，客户1在2021年9月首次购买，客户2在2021年10月首次购买等(。我想添加的是一个额外的列，统计客户在第一个月的购买次数。

现有数据表(订单(：

订单日期2021年11月15日[/tr>

	订单ID	客户ID
1	1	2021年9月15日
2	1	2021年10月15日
3	1	2021年1月11日
4	2021年1月10日
5	2	2021年6月10日
6	2021年7月10日
7	2021年9月11日
8	3

根据客户id、订单的年份和月份进行聚合，并使用窗口函数获取第一个订单的年份、月份和第一个月的计数：

SELECT DISTINCT CustomerId,
FIRST_VALUE(MONTH(OrderDate)) OVER (PARTITION BY CustomerId ORDER BY YEAR(OrderDate), MONTH(OrderDate)) FirstOrderMonth,
MIN(YEAR(OrderDate)) OVER (PARTITION BY CustomerId) FirstOrderYear,
FIRST_VALUE(COUNT(*)) OVER (PARTITION BY CustomerId ORDER BY YEAR(OrderDate), MONTH(OrderDate)) FirstMonthPurchaseCount
FROM Orders
GROUP BY CustomerId, YEAR(OrderDate), MONTH(OrderDate);

请参阅演示

您可以使用RANK()函数来识别每个用户第一个月的购买情况，如下所示：

Select D.CustomerId, MONTH(OrderDate) FirstOrderMonth, 
YEAR(OrderDate) FirstOrderYear, COUNT(*) FirstMonthPurchaseCount
From
(
Select *, RANK() Over (Partition By CustomerId Order By YEAR(OrderDate), MONTH(OrderDate)) rnk
From table_name
) D
Where D.rnk = 1
Group By  D.CustomerId, MONTH(OrderDate), YEAR(OrderDate)

查看演示。

如果你想找到第二、第三。。。月份采购时，您可以使用DENSE_RANK()函数而不是RANK()，并将where子句中的值更改为所需的月份订单。

select   CustomerId
,min(month(OrderDate))   as FirstOrderMonth
,min(year(OrderDate))    as FirstOrderYear
,count(first_month_flag) as FirstMonthPurchaseCount

from    (select *
,case when month(OrderDate) = month(min(OrderDate) over(partition by CustomerId)) then 1 end as first_month_flag
from Orders) Orders
group by CustomerId

FirstOrderYear20212021<1>

CustomerId	FirstOrderMonth	FirstMonthPurchaseCount
1	9	1
2	10	3
3	11	2021