如何在DuckDB中按组计算基于当前行的条件滚动计数

需求说明 #

需要生成「Rolling Count Very Late (> 90 days) by group」列，统计同组内满足以下条件的历史行数：

• days 字段值大于90
• 历史行的Date Closed 小于当前行的Invoice Date

已通过Excel的COUNTIFS公式实现原型，但尝试的窗口函数SQL逻辑错误，Python方案效率过低，需高效SQL解决方案。

错误SQL分析 #

以下是尝试的错误SQL：

SELECT 
  "Invoice Date",
  "Due Date",
  "Date Closed",
  "days",
  "amount",
  "group",
  SUM(CASE WHEN days > 90 AND "Date Closed" < "Invoice Date" THEN 1 ELSE 0 END) 
    OVER (PARTITION BY "group" ORDER BY "Invoice Date" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
    AS "Rolling Count Very Late (> 90 days) by group"
FROM 
  main.example_tbl 
ORDER BY 
   "Invoice Date";

错误原因：窗口函数的CASE判断中，"Date Closed" < "Invoice Date"是拿窗口内每一行自己的Date Closed和自己的Invoice Date比较，而非历史行的Date Closed和当前正在计算聚合的行的Invoice Date比较，导致统计逻辑完全偏离需求。

高效解决方案 #

方案一：修正窗口函数逻辑（推荐，性能最优） #

通过子查询给当前行的Invoice Date起别名，确保窗口内的历史行与当前行的Invoice Date做比较：

SELECT 
  "Invoice Date",
  "Due Date",
  "Date Closed",
  "days",
  "amount",
  "group",
  SUM(CASE WHEN days > 90 AND "Date Closed" < current_invoice_date THEN 1 ELSE 0 END) 
    OVER (PARTITION BY "group" ORDER BY "Invoice Date" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
    AS "Rolling Count Very Late (> 90 days) by group"
FROM (
  SELECT 
    *,
    "Invoice Date" AS current_invoice_date  -- 标记当前行的Invoice Date
  FROM main.example_tbl
) AS sub_query
ORDER BY 
  "Invoice Date";

方案二：使用LATERAL JOIN（逻辑更直观） #

通过横向关联同组内符合条件的历史行，直接计数：

SELECT 
  t."Invoice Date",
  t."Due Date",
  t."Date Closed",
  t."days",
  t."amount",
  t."group",
  COALESCE(sub.late_count, 0) AS "Rolling Count Very Late (> 90 days) by group"
FROM 
  main.example_tbl t
LEFT JOIN LATERAL (
  SELECT COUNT(*) AS late_count
  FROM main.example_tbl t2
  WHERE t2."group" = t."group"
    AND t2."Invoice Date" <= t."Invoice Date"  -- 限定为当前行及之前的历史记录
    AND t2."days" > 90
    AND t2."Date Closed" < t."Invoice Date"   -- 历史行的Date Closed小于当前行的Invoice Date
) AS sub ON true
ORDER BY 
  t."Invoice Date";

性能说明 #

• 方案一基于窗口函数实现，时间复杂度为O(n log n)，数据量越大优势越明显，适合大规模数据集。
• 方案二逻辑直观，但需注意如果数据量极大，可能因关联操作带来额外开销，建议优先测试方案一。

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