基于开户后月份维度构建账户交易统计时间序列方法

核心逻辑是把所有交易记录和对应账户的开户日期做关联，以账户自身开户日为时间锚点计算间隔周期，完全脱离自然月的统计规则，分两部分实现：

首期：开户后首月交易笔数统计 #

首月判定规则：交易发生时间在「开户日（含）~ 开户日顺延1个月的当日（不含）」区间内，用这个区间判定可以完全规避大小月、闰年的日期偏移问题，不会出现开户日是3月31日、顺延1个月到4月30日时的统计错误。
直接关联两张表聚合即可，标准SQL写法如下：

SELECT
  a.Account_id AS account_id,
  COUNT(b.transaction_id) AS count_of_transactions_month1_post_opening
FROM 表A a
LEFT JOIN 表B b
  ON a.Account_id = b.Account_id
  AND TO_DATE(b.transaction_date, 'dd-MM-yy') >= TO_DATE(a.creation_date, 'dd-MM-yy')
  AND TO_DATE(b.transaction_date, 'dd-MM-yy') < ADD_MONTHS(TO_DATE(a.creation_date, 'dd-MM-yy'), 1)
GROUP BY a.Account_id;

代入提供的样例数据，返回结果和预期完全一致：

• 账户0x66756首月（21年5月12日~6月12日）有2笔交易，6月25日的交易超出首月区间不计入
• 账户0x77734首月（21年7月6日~8月6日）有1笔交易，8月28日的交易超出首月区间不计入

迭代扩展：全周期开户后月度交易时间序列 #

要统计开户后每一个月的交易笔数，只需要额外计算每笔交易距离开户日的间隔月份，按「账户+间隔月份」维度聚合即可：

SELECT
  a.Account_id AS account_id,
  -- 首月返回值为1，后续月份依次递增
  MONTHS_BETWEEN(
    TRUNC(TO_DATE(b.transaction_date, 'dd-MM-yy'), 'MONTH'),
    TRUNC(TO_DATE(a.creation_date, 'dd-MM-yy'), 'MONTH')
  ) + 1 AS months_post_opening,
  COUNT(b.transaction_id) AS transaction_count
FROM 表A a
LEFT JOIN 表B b
  ON a.Account_id = b.Account_id
  AND TO_DATE(b.transaction_date, 'dd-MM-yy') >= TO_DATE(a.creation_date, 'dd-MM-yy')
GROUP BY
  a.Account_id,
  MONTHS_BETWEEN(
    TRUNC(TO_DATE(b.transaction_date, 'dd-MM-yy'), 'MONTH'),
    TRUNC(TO_DATE(a.creation_date, 'dd-MM-yy'), 'MONTH')
  ) + 1
ORDER BY account_id, months_post_opening;

注意事项 #

• 不同数据库的日期函数有差异：MySQL环境可以把ADD_MONTHS替换为DATE_ADD(xx, INTERVAL 1 MONTH)，MONTHS_BETWEEN替换为PERIOD_DIFF；PostgreSQL环境直接用xx + INTERVAL '1 month'做日期偏移即可，核心判定逻辑不变。
• 不要用「日期差/30」的方式计算间隔月份，大小月、2月天数差异会带来明显统计误差，必须用数据库原生的月份差函数。
• 如果需要补全无交易月份的0值记录，可以先生成「所有账户+所有开户后月份序号」的维表，再左关联上述聚合结果，把空值填充为0即可。

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