无需关联子查询如何用窗口函数实现MIN/MAX并匹配发票对应文档日期

解决方案 #

实现逻辑 #

核心思路是通过行号对齐实现无重复匹配：

• 按客户分组，给所有发票按开票日期降序排列生成序号，序号越小的发票日期越新，优先匹配
• 按客户分组，过滤掉所有晚于对应客户最大开票日期的无效文档后，给剩余文档按文档日期降序排列生成序号
• 同客户下序号相同的发票和文档直接匹配，既保证每个文档只被使用一次，也满足最新发票优先匹配最新可用文档的规则

完整查询语句 #

WITH ranked_invoices AS (
  -- 给发票按客户分组、日期降序编行号
  SELECT 
    Client,
    InvoiceDate,
    ROW_NUMBER() OVER (PARTITION BY Client ORDER BY InvoiceDate DESC) AS inv_rn
  FROM Invoices
),
client_max_inv_dt AS (
  -- 聚合查询取每个客户的最大开票日期，用于过滤无效文档，不属于关联子查询，Zoho Analytics支持
  SELECT Client, MAX(InvoiceDate) AS max_inv_dt FROM Invoices GROUP BY Client
),
ranked_docs AS (
  -- 过滤无效文档后给文档按客户分组、日期降序编行号
  SELECT 
    d.Client,
    d.DocumentDate,
    ROW_NUMBER() OVER (PARTITION BY d.Client ORDER BY d.DocumentDate DESC) AS doc_rn
  FROM Documents d
  INNER JOIN client_max_inv_dt t 
    ON d.Client = t.Client 
    AND d.DocumentDate < t.max_inv_dt
)
-- 按客户+行号关联得到匹配结果
SELECT 
  ri.Client,
  ri.InvoiceDate,
  rd.DocumentDate
FROM ranked_invoices ri
INNER JOIN ranked_docs rd 
  ON ri.Client = rd.Client 
  AND ri.inv_rn = rd.doc_rn
ORDER BY ri.Client, ri.InvoiceDate DESC

效果验证 #

针对你提供的样例数据：

• 排序后的发票行号：7/3/2021（行号1）、6/1/2021（行号2）、3/2/2021（行号3）
• 过滤掉7/20/2021后的文档行号：5/29/2021（行号1）、5/10/2021（行号2）、2/9/2021（行号3）
• 同行号关联后输出结果和你期望的完全一致

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