基于分区/行函数的日期计算：新业务金额统计需求实现问询

嘿，我来帮你搞定这个新业务金额统计的需求，刚好可以用窗口函数（PARTITION BY/ROW_NUMBER()这类）来处理日期判断和业务规则落地，下面是具体的实现思路和代码示例：

先把业务规则拆成可落地的逻辑 #

先把你说的需求拆解成数据库能识别的条件：

• 触发新业务的两种情况：
  • 客户是纯新客户：在所有发票记录里是第一次出现的客户
  • 客户是回流客户：当前这笔发票的日期往前推6个月，完全没有任何销售记录
• 新业务有效期规则：一旦某笔订单触发了新业务，这个客户在首笔触发新业务的发票日期之后的3个月内的所有订单，都算新业务金额

完整SQL示例（适配PostgreSQL/BigQuery这类支持窗口函数的数据库） #

假设你的发票表叫invoices，核心字段有：customer_id（客户编号）、invoice_id（发票编号）、invoice_date（发票日期）、amount（发票金额）、sales_rep_id（销售代表编号）

WITH customer_invoice_ordered AS (
    -- 第一步：给每个客户的发票按日期排序，拿到上一笔发票的日期
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY invoice_date) AS row_num,
        LAG(invoice_date) OVER (PARTITION BY customer_id ORDER BY invoice_date) AS prev_invoice_date
    FROM invoices
),
new_business_triggers AS (
    -- 第二步：标记哪些订单是新业务的触发点，同时算出对应的3个月有效期
    SELECT
        *,
        -- 判断是否是触发新业务的订单
        CASE
            -- 新客户的首单直接触发
            WHEN row_num = 1 THEN TRUE
            -- 回流客户：上一笔发票和当前间隔超过6个月
            WHEN invoice_date > prev_invoice_date + INTERVAL '6 months' THEN TRUE
            ELSE FALSE
        END AS is_new_business_trigger,
        -- 计算该触发订单的有效期（首笔触发后3个月）
        CASE
            WHEN row_num = 1 OR invoice_date > prev_invoice_date + INTERVAL '6 months' THEN invoice_date + INTERVAL '3 months'
            ELSE NULL
        END AS new_business_valid_until
    FROM customer_invoice_ordered
),
customer_valid_periods AS (
    -- 第三步：把触发点的有效期同步给后续所有订单，避免重复计算
    SELECT
        *,
        -- 用LAST_VALUE把最近的有效期同步到当前订单
        LAST_VALUE(new_business_valid_until IGNORE NULLS) OVER (
            PARTITION BY customer_id 
            ORDER BY invoice_date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS current_valid_until
    FROM new_business_triggers
),
new_business_orders AS (
    -- 第四步：标记所有属于新业务的订单
    SELECT
        *,
        CASE
            -- 触发订单本身肯定算新业务
            WHEN is_new_business_trigger THEN TRUE
            -- 后续订单在有效期内的，也算新业务
            WHEN current_valid_until IS NOT NULL AND invoice_date <= current_valid_until THEN TRUE
            ELSE FALSE
        END AS is_new_business
    FROM customer_valid_periods
)
-- 最终：按销售代表汇总新业务总金额
SELECT
    sales_rep_id,
    SUM(CASE WHEN is_new_business THEN amount ELSE 0 END) AS total_new_business_amount
FROM new_business_orders
GROUP BY sales_rep_id
ORDER BY total_new_business_amount DESC;

代码逻辑通俗解释 #

1. customer_invoice_ordered：给每个客户的发票按时间排好队，同时拿到上一笔发票的日期，方便判断是不是超过6个月没下单。
2. new_business_triggers：找出那些能触发新业务的订单（新客户首单或者回流客户的订单），并给这些订单设置3个月的有效期。
3. customer_valid_periods：把触发订单的有效期“继承”给后面的订单，这样后面的订单不用再重新判断，直接看有没有在有效期里就行。
4. new_business_orders：把所有属于新业务的订单标记出来，包括触发订单和有效期内的后续订单。
5. 最后一步：按销售代表分组，把他们对应的新业务金额加起来就行。

适配不同数据库的小提示 #

• 如果用MySQL，把INTERVAL '6 months'改成INTERVAL 6 MONTH，LAST_VALUE的语法可能需要调整（比如用COALESCE结合变量来实现类似效果）。
• 要是需要按月份/季度统计新业务，可以在最后一步加上DATE_TRUNC('month', invoice_date)一起分组。
• 可以根据实际业务调整日期判断的精度（比如是否包含当天，或者用DATE_DIFF函数来计算间隔天数）。

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