基于购买历史的客户分组SQL优化：CASE WHEN实现问询

我需要根据客户历史购买记录，将客户划分为以下5个分组：

• Lost（流失客户）：前12个月有购买，但当前12个月无购买
• New（新客户）：当前12个月有购买，且此前无任何购买记录
• Restarted (1 year)（重启客户-1年期）：当前12个月有购买，前12个月（13-24个月前）无购买，但25-36个月前有购买
• Restarted (other)（重启客户-其他）：当前12个月有购买，前12个月（13-24个月前）无购买，但历史其他时段有购买
• Continuing（持续购买客户）：当前12个月和前12个月均有购买

示例分组结果：

我目前通过创建多个临时表分别存储各分组的账户列表，原本计划用SELECT的CASE WHEN直接生成Group字段但遇到困难，希望获得更简洁优雅的解决方案。

现有SQL代码：

/*Group customers who bought in current 12 month window*/
drop table if exists #current12
select distinct txn.account_no
into #current12
from transactions txn
where tran_date between dateadd(day,-365,getdate()) and getdate()

/*Group customers who bought in previous 12 month window (13 - 24 months ago)*/
drop table if exists #previous12
select distinct txn.account_no
into #previous12
from transactions txn
where tran_date between dateadd(day,-730,getdate()) and dateadd(day,-366,getdate())

/*Group customers who bought in prior 12 month window (25 - 36 months ago)*/
drop table if exists #prior12
select distinct txn.account_no
into #prior12
from transactions txn
where tran_date between dateadd(day,-1095,getdate()) and dateadd(day,-731,getdate())

/*Group customers who bought at any time before current 12 month window (13+ months ago)*/
drop table if exists #beforecurrent12
select distinct txn.account_no
into #beforecurrent12
from transactions txn
where tran_date < dateadd(day,-365,getdate())

/*Create table of lost customers*/
drop table if exists #lost
select distinct txn.account_no
into #lost
from transactions txn
where txn.account_no IN (select account_no from #previous12)
and txn.account_no NOT IN (select account_no from #current12)

/*Create table of new customers*/
drop table if exists #new
select distinct txn.account_no
into #new
from transactions txn
where txn.account_no IN (select account_no from #current12)
and txn.account_no NOT IN (select distinct account_no from client.dbo.acct_sales_trans where tran_date < dateadd(day,-366,getdate()))

/*Create table of restarted 1 yr customers*/
drop table if exists #restarted1yr
select distinct txn.account_no
into #restarted1yr
from transactions txn
where txn.account_no IN (select account_no from #current12)
and txn.account_no NOT IN (select account_no from #previous12)
and txn.account_no IN (select account_no from #prior12)

/*Create table of restarted other customers*/
drop table if exists #restartedother
select distinct txn.account_no
into #restartedother
from transactions txn
where txn.account_no IN (select account_no from #current12)
and txn.account_no NOT IN (select account_no from #previous12)
and txn.account_no NOT IN (select account_no from #prior12)
and txn.account_no IN (select distinct account_no from transactions where tran_date < dateadd(day,-1095,getdate()))

/*Create table of returning customers*/
drop table if exists #returning
select distinct txn.account_no
into #returning
from transactions txn
where txn.account_no IN (select account_no from #previous12)
and txn.account_no IN (select account_no from #current12)

可以通过先聚合每个客户在各个时间窗口的购买标记，再用CASE WHEN一次性完成分组，无需创建多个临时表，效率更高且逻辑更清晰。

优化后的SQL代码 #

WITH customer_txn_flags AS (
    SELECT 
        account_no,
        -- 标记当前12个月是否有购买
        MAX(CASE WHEN tran_date BETWEEN DATEADD(day, -365, GETDATE()) AND GETDATE() THEN 1 ELSE 0 END) AS has_current_12,
        -- 标记前12个月（13-24个月前）是否有购买
        MAX(CASE WHEN tran_date BETWEEN DATEADD(day, -730, GETDATE()) AND DATEADD(day, -366, GETDATE()) THEN 1 ELSE 0 END) AS has_previous_12,
        -- 标记25-36个月前是否有购买
        MAX(CASE WHEN tran_date BETWEEN DATEADD(day, -1095, GETDATE()) AND DATEADD(day, -731, GETDATE()) THEN 1 ELSE 0 END) AS has_prior_12,
        -- 标记36个月之前是否有购买
        MAX(CASE WHEN tran_date < DATEADD(day, -1095, GETDATE()) THEN 1 ELSE 0 END) AS has_before_prior_12,
        -- 标记当前12个月之前是否有任何购买（用于判断新客户）
        MAX(CASE WHEN tran_date < DATEADD(day, -365, GETDATE()) THEN 1 ELSE 0 END) AS has_any_before_current
    FROM transactions
    GROUP BY account_no
)
SELECT 
    account_no,
    CASE
        -- 流失客户：前12个月有购买，当前12个月无
        WHEN has_previous_12 = 1 AND has_current_12 = 0 THEN 'Lost'
        -- 新客户：当前12个月有购买，之前无任何购买
        WHEN has_current_12 = 1 AND has_any_before_current = 0 THEN 'New'
        -- 持续购买：当前和前12个月都有购买
        WHEN has_current_12 = 1 AND has_previous_12 = 1 THEN 'Continuing'
        -- 重启客户-1年期：当前有购买，前12个月无，25-36个月有
        WHEN has_current_12 = 1 AND has_previous_12 = 0 AND has_prior_12 = 1 THEN 'Restarted (1 year)'
        -- 重启客户-其他：当前有购买，前12个月无，且更早时段有购买
        WHEN has_current_12 = 1 AND has_previous_12 = 0 AND has_before_prior_12 = 1 THEN 'Restarted (other)'
        -- 兜底：处理未匹配到的情况（比如从未有购买的客户，可根据需求调整）
        ELSE 'Unknown'
    END AS [Group]
FROM customer_txn_flags
ORDER BY account_no;

方案说明 #

1. CTE聚合标记：通过customer_txn_flags公共表表达式，一次性统计每个客户在各个时间窗口的购买情况，用1/0标记是否有购买，避免多次扫描transactions表。
2. CASE WHEN分组：基于聚合后的标记字段，按照分组规则依次判断，注意逻辑顺序（优先级高的规则放在前面）。
3. 性能优化：原方案多次创建临时表并重复扫描业务表，优化后只需扫描一次表，大幅提升效率，同时逻辑集中便于维护。

内容的提问来源于stack exchange，提问作者user15260186