SQL实现账户交易状态动态计算与月度聚合需求

针对你的需求，以下是适配千万级数据量的高效SQL实现方案：

核心思路 #

1. 定位状态切换点：先获取每个账户首次发生Public交易的日期，以此为分界判断各月份的账户状态。
2. 生成连续月份序列：构造全年的月份起始日期，确保每个账户每个月都有一行结果（即使无交易）。
3. 月度交易聚合：按账户+月份维度聚合交易数据，同时保留当月最新交易的关联信息。

完整SQL代码 #

WITH AccountFirstPublic AS (
    -- 获取每个账户首次Public交易的日期
    SELECT 
        ua.AccountId,
        MIN(ua.FilepathDate) AS FirstPublicDate
    FROM [dbo].[incremental_UserActions] ua
    INNER JOIN tmp_Industry i ON ua.partnerid = i.partnerid
    WHERE i.[Public/Private] = 'Public'
      AND YEAR(ua.FilepathDate) = 2022
    GROUP BY ua.AccountId
),
ContinuousMonths AS (
    -- 生成2022年的连续月份起始日期
    SELECT DATEFROMPARTS(2022, month_num, 1) AS MonthStart
    FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) AS Months(month_num)
),
AccountMonthlyTransactions AS (
    -- 按账户+月份聚合交易数据，提取关键信息
    SELECT 
        ua.AccountId,
        DATEFROMPARTS(YEAR(ua.FilepathDate), MONTH(ua.FilepathDate), 1) AS MonthStart,
        COUNT(*) AS TotalTransactions,
        MAX(ua.FilepathDate) AS LatestTransactionDate,
        -- 取当月最后一笔交易对应的Partner和Industry
        FIRST_VALUE(ua.PartnerId) OVER (
            PARTITION BY ua.AccountId, DATEFROMPARTS(YEAR(ua.FilepathDate), MONTH(ua.FilepathDate), 1) 
            ORDER BY ua.FilepathDate DESC
        ) AS LatestPartnerId,
        FIRST_VALUE(i.industry) OVER (
            PARTITION BY ua.AccountId, DATEFROMPARTS(YEAR(ua.FilepathDate), MONTH(ua.FilepathDate), 1) 
            ORDER BY ua.FilepathDate DESC
        ) AS LatestIndustry
    FROM [dbo].[incremental_UserActions] ua
    INNER JOIN tmp_Industry i ON ua.partnerid = i.partnerid
    WHERE YEAR(ua.FilepathDate) = 2022
    GROUP BY ua.AccountId, DATEFROMPARTS(YEAR(ua.FilepathDate), MONTH(ua.FilepathDate), 1)
)
-- 关联所有表生成最终结果
SELECT 
    cm.MonthStart,
    accounts.AccountId,
    amt.LatestPartnerId AS PartnerId,
    amt.LatestIndustry AS Industry,
    -- 计算账户当前状态
    CASE
        WHEN afp.FirstPublicDate IS NULL THEN 'Private Only'
        WHEN cm.MonthStart < DATEFROMPARTS(YEAR(afp.FirstPublicDate), MONTH(afp.FirstPublicDate), 1) THEN 'Private Only'
        ELSE 'Public + Private'
    END AS Result,
    ISNULL(amt.TotalTransactions, 0) AS Transactions,
    amt.LatestTransactionDate AS LatestTransaction
FROM ContinuousMonths cm
-- 关联所有目标账户（可按需调整过滤条件）
CROSS JOIN (SELECT DISTINCT AccountId FROM [dbo].[incremental_UserActions] WHERE YEAR(FilepathDate)=2022) accounts
LEFT JOIN AccountMonthlyTransactions amt 
    ON accounts.AccountId = amt.AccountId 
    AND cm.MonthStart = amt.MonthStart
LEFT JOIN AccountFirstPublic afp 
    ON accounts.AccountId = afp.AccountId
-- 可移除该条件以查询所有账户
WHERE accounts.AccountId = 6906
ORDER BY accounts.AccountId, cm.MonthStart;

关键逻辑说明 #

• AccountFirstPublic：定位每个账户状态切换的时间节点——首次出现Public交易的日期，之后该账户的状态将永久变为Public + Private。
• ContinuousMonths：硬编码生成全年月份，解决无交易月份的空行问题，保证输出的连续性。
• AccountMonthlyTransactions：按账户和月份聚合交易数据，用FIRST_VALUE窗口函数高效获取当月最新交易的Partner和Industry，避免额外子查询的性能损耗。
• 状态计算：通过CASE语句实现状态判断：
  • 从未发生Public交易的账户，所有月份均标记为Private Only；
  • 首次Public交易之前的月份标记为Private Only；
  • 首次Public交易及之后的月份标记为Public + Private。

千万级数据性能优化建议 #

• 给incremental_UserActions表创建(AccountId, FilepathDate)复合索引，加速分组、过滤和窗口函数计算。
• 确保tmp_Industry表的PartnerId字段有主键或非聚集索引，减少JOIN操作的开销。
• 若仅需查询特定账户，在accounts子查询中提前过滤，避免全表扫描。

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