如何结合DENSE_RANK()与业务规则实现SQL线索与交易精准匹配？

针对你提出的需求——将交易与未匹配过的最早有效线索关联（线索匹配后不可复用，仅交易日期在线索有效期内的配对有效），原方案用DENSE_RANK()按日期排名关联的问题在于无法跟踪线索的已使用状态，且会出现交易早于线索的无效匹配。以下是两种无需循环、纯窗口函数的解决方案：

先明确示例数据与规则 #

基础DDL与数据 #

-- 线索表：valid_until 为 lead_date + 10天，即线索有效期0-10天
CREATE TABLE leads (
    lead_id INT,
    client_id INT,
    lead_date DATE,
    valid_until DATE
);

INSERT INTO leads VALUES
(1, 1, '2023-01-01', '2023-01-11'),
(2, 1, '2023-01-05', '2023-01-15'),
(3, 2, '2023-01-03', '2023-01-13');

-- 交易表
CREATE TABLE transactions (
    transaction_id INT,
    client_id INT,
    transaction_date DATE
);

INSERT INTO transactions VALUES
(1, 1, '2023-01-06'), -- 应匹配lead_id=1（最早有效）
(2, 1, '2023-01-12'), -- 应匹配lead_id=2（lead1已过期）
(3, 1, '2022-12-30'); -- 无有效线索（所有线索日期晚于交易），不应出现在结果中

核心规则 #

1. 每个线索只能匹配一次，匹配后不可再用于后续交易
2. 交易仅能匹配交易日期在线索有效期内的线索
3. 优先匹配客户的最早有效未使用线索

方案1：LATERAL JOIN + 序列号跟踪（推荐，可读性高） #

该方案利用LATERAL JOIN（MySQL 8.0+/PostgreSQL/SQL Server支持）为每个交易动态筛选可用线索，结合序列号跟踪已匹配的线索数量：

WITH ordered_transactions AS (
    -- 给每个客户的交易按日期排序，生成交易序列号
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY transaction_date) AS trans_seq
    FROM transactions
),
ordered_leads AS (
    -- 给每个客户的线索按日期排序，生成线索序列号
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY lead_date) AS lead_seq
    FROM leads
)
SELECT 
    t.transaction_id,
    t.client_id,
    t.transaction_date,
    l.lead_id
FROM ordered_transactions t
LEFT JOIN LATERAL (
    -- 筛选当前交易的可用线索：未被之前交易匹配、且交易在有效期内
    SELECT l.*
    FROM ordered_leads l
    WHERE l.client_id = t.client_id
        AND t.transaction_date BETWEEN l.lead_date AND l.valid_until
        -- 仅选择序列号大于已匹配线索数的线索（保证未被使用）
        AND l.lead_seq > (
            SELECT COUNT(DISTINCT l2.lead_id)
            FROM ordered_transactions t2
            JOIN ordered_leads l2 
                ON t2.client_id = l2.client_id
                AND t2.transaction_date BETWEEN l2.lead_date AND l2.valid_until
            WHERE t2.client_id = t.client_id
                AND t2.trans_seq < t.trans_seq
        )
    ORDER BY l.lead_date -- 取最早的有效线索
    LIMIT 1
) l ON true
WHERE l.lead_id IS NOT NULL -- 过滤无有效线索的交易
ORDER BY t.transaction_id;

逻辑说明 #

1. 先给交易和线索按客户分组、日期排序，生成唯一序列号，方便跟踪顺序
2. 对每个交易，计算该客户之前已经匹配了多少条线索（通过子查询统计已配对的线索数）
3. 在可用线索中，仅选择序列号大于已匹配数的线索（确保未被使用），再取最早的一条
4. 最后过滤掉无有效线索的交易，得到符合要求的配对

方案2：累计匹配标记法（纯窗口函数实现） #

如果你的数据库不支持LATERAL JOIN，可以用纯窗口函数的累计求和来标记已使用的线索：

WITH possible_pairs AS (
    -- 生成所有客户的交易与线索配对，标记是否为有效匹配
    SELECT 
        t.transaction_id,
        t.client_id,
        t.transaction_date,
        l.lead_id,
        l.lead_date,
        l.valid_until,
        CASE WHEN t.transaction_date BETWEEN l.lead_date AND l.valid_until THEN 1 ELSE 0 END AS is_valid,
        -- 给交易和线索按日期排序
        ROW_NUMBER() OVER (PARTITION BY t.client_id ORDER BY t.transaction_date) AS trans_rank,
        ROW_NUMBER() OVER (PARTITION BY l.client_id ORDER BY l.lead_date) AS lead_rank
    FROM transactions t
    JOIN leads l ON t.client_id = l.client_id
),
ranked_pairs AS (
    SELECT 
        *,
        -- 累计计算到当前交易为止，已匹配的有效线索数量
        SUM(is_valid) OVER (PARTITION BY client_id ORDER BY trans_rank, lead_rank) AS cumulative_matches
    FROM possible_pairs
)
SELECT DISTINCT
    transaction_id,
    client_id,
    transaction_date,
    lead_id
FROM ranked_pairs
WHERE is_valid = 1
    -- 线索排名等于累计匹配数时，说明是当前交易的最早可用未匹配线索
    AND lead_rank = cumulative_matches
ORDER BY transaction_id;

逻辑说明 #

1. 先生成所有交易与线索的配对，标记哪些是有效匹配（交易在线索有效期内）
2. 用SUM()窗口函数累计计算到当前交易为止，已成功匹配的线索数量
3. 当线索的排名等于累计匹配数时，这条线索就是当前交易的目标（因为前面的线索已经被之前的交易匹配了）
4. 去重后得到最终的配对结果

验证结果 #

两种方案都会得到以下符合预期的结果：

transaction_id 3 因无有效线索被过滤，不会出现在结果中。

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