如何在T-SQL中按条件实现活跃账户的累计运行总计

你需要统计每个日历月内的唯一活跃订阅账户总数：账户只要在该月处于订阅期（合同开始≤当月末，合同到期≥当月初）就计入统计，同时需要计算运行总计（累计到该月的所有曾经活跃过的唯一账户数）。

原SQL的问题 #

• 仅筛选了当前未到期（End_Date__c > GETDATE()）的账户，无法统计历史月份的活跃情况；
• 未生成日历月份序列，无法按月份维度做统计；
• 未实现账户去重统计和运行总计逻辑。

修改后的SQL方案（基于SQL Server） #

-- 1. 生成覆盖所有订阅周期的日历月份序列
WITH CalendarMonths AS (
    SELECT 
        DATEFROMPARTS(YEAR(MIN(CA.Start_Date__c)), MONTH(MIN(CA.Start_Date__c)), 1) AS CalendarMonth
    FROM [salesforce].[Client_Asset__c] CA
    WHERE CA.Product_Code_CPQ__c IN (
        'DSWPSTRSUB','DSWPESSSUB','DSWPPROSUB','DSWPHOSTSUB','DSWPMULTIHOSTSUB','DSWPOLXWRAPFPE',
        'DSWPOLXWRAPSUB','WPCALENDARFORALT','WPCALHOSTINGBUN','IMWPTM','SBWPRET','SBWPRETNR','WORDPLUMWEBSUCCESS',
        'WORDPWEBSUCCESS','WORDPOGS','FDSTRWORDPDESGNSUB','FDWPFPE','WORDPEMERGHOST','WORDPSUBBUN','WPOLXPLUGIN',
        'POSTSTARTWORDPAF','POSTWORDPSTARTBUN','LUMWORDPSSUBBUN','WORDPLUMOGS','LUMFDSTRWPDESGNSUB',
        'LUMPSTWORDPSTRBUN','LUMPOSTSTRTWORDPAF','FDWPEMERGFPE'
    )
    UNION ALL
    SELECT DATEADD(MONTH, 1, CalendarMonth)
    FROM CalendarMonths
    WHERE DATEADD(MONTH, 1, CalendarMonth) <= (
        SELECT DATEFROMPARTS(YEAR(MAX(CA.End_Date__c)), MONTH(MAX(CA.End_Date__c)), 1)
        FROM [salesforce].[Client_Asset__c] CA
        WHERE CA.Product_Code_CPQ__c IN (
            'DSWPSTRSUB','DSWPESSSUB','DSWPPROSUB','DSWPHOSTSUB','DSWPMULTIHOSTSUB','DSWPOLXWRAPFPE',
            'DSWPOLXWRAPSUB','WPCALENDARFORALT','WPCALHOSTINGBUN','IMWPTM','SBWPRET','SBWPRETNR','WORDPLUMWEBSUCCESS',
            'WORDPWEBSUCCESS','WORDPOGS','FDSTRWORDPDESGNSUB','FDWPFPE','WORDPEMERGHOST','WORDPSUBBUN','WPOLXPLUGIN',
            'POSTSTARTWORDPAF','POSTWORDPSTARTBUN','LUMWORDPSSUBBUN','WORDPLUMOGS','LUMFDSTRWPDESGNSUB',
            'LUMPSTWORDPSTRBUN','LUMPOSTSTRTWORDPAF','FDWPEMERGFPE'
        )
    )
),
-- 2. 关联账户与订阅记录，标记当月是否活跃
ActiveAccountsPerMonth AS (
    SELECT
        cm.CalendarMonth,
        A.Id AS AccountId,
        CASE 
            WHEN CA.Start_Date__c <= EOMONTH(cm.CalendarMonth) AND CA.End_Date__c >= cm.CalendarMonth 
            THEN 1 ELSE 0 
        END AS IsActive
    FROM CalendarMonths cm
    CROSS JOIN [salesforce].[Client_Asset__c] CA
    INNER JOIN salesforce.Account A ON CA.Account__c = A.Id
    WHERE CA.Product_Code_CPQ__c IN (
        'DSWPSTRSUB','DSWPESSSUB','DSWPPROSUB','DSWPHOSTSUB','DSWPMULTIHOSTSUB','DSWPOLXWRAPFPE',
        'DSWPOLXWRAPSUB','WPCALENDARFORALT','WPCALHOSTINGBUN','IMWPTM','SBWPRET','SBWPRETNR','WORDPLUMWEBSUCCESS',
        'WORDPWEBSUCCESS','WORDPOGS','FDSTRWORDPDESGNSUB','FDWPFPE','WORDPEMERGHOST','WORDPSUBBUN','WPOLXPLUGIN',
        'POSTSTARTWORDPAF','POSTWORDPSTARTBUN','LUMWORDPSSUBBUN','WORDPLUMOGS','LUMFDSTRWPDESGNSUB',
        'LUMPSTWORDPSTRBUN','LUMPOSTSTRTWORDPAF','FDWPEMERGFPE'
    )
    AND CA.Active__c = 1
),
-- 3. 统计每月唯一活跃账户，记录账户首次活跃月份
MonthlyStats AS (
    SELECT
        CalendarMonth,
        COUNT(DISTINCT CASE WHEN IsActive = 1 THEN AccountId END) AS MonthlyUniqueActiveAccounts,
        MIN(CASE WHEN IsActive = 1 THEN CalendarMonth END) OVER (PARTITION BY AccountId) AS FirstActiveMonth
    FROM ActiveAccountsPerMonth
    GROUP BY CalendarMonth, AccountId
),
-- 4. 计算累计唯一账户数（运行总计）
FinalStats AS (
    SELECT
        CalendarMonth,
        SUM(MonthlyUniqueActiveAccounts) AS MonthlyUniqueActiveAccounts,
        COUNT(DISTINCT CASE WHEN FirstActiveMonth <= CalendarMonth THEN AccountId END) AS RunningTotalUniqueAccounts
    FROM MonthlyStats
    GROUP BY CalendarMonth
)
-- 格式化输出结果
SELECT
    FORMAT(CalendarMonth, 'yyyy-MM') AS 月份,
    MonthlyUniqueActiveAccounts AS 当月唯一活跃账户数,
    RunningTotalUniqueAccounts AS 累计唯一账户数
FROM FinalStats
ORDER BY CalendarMonth;

关键逻辑说明 #

1. 日历月份生成：用递归CTE生成所有覆盖订阅周期的月份，确保历史和未来到期的月份都被统计；
2. 活跃判断：通过合同开始≤当月末且合同到期≥当月初的规则，判断账户在该月是否处于有效订阅期；
3. 去重统计：用COUNT(DISTINCT AccountId)确保同一个账户在当月只被统计一次；
4. 运行总计：通过记录每个账户的首次活跃月份，统计累计到当前月的所有曾经活跃过的唯一账户总数。

注意事项 #

• 如果使用非SQL Server环境，需要替换EOMONTH、DATEFROMPARTS等函数（如MySQL用LAST_DAY()，PostgreSQL用DATE_TRUNC('month', ...)）；
• 原SQL中的End_Date__c > GETDATE()被移除，确保历史到期的账户也能被统计到对应月份；
• 若一个账户有多个订阅记录，COUNT(DISTINCT)会自动去重，避免重复统计。

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