SQL查询：获取每个Agent可用前最近的队列分配记录

我有两张表：Assignment表（对应示例中的Tabela_Atribuicao）和Status表（对应示例中的Tabela_Status）。Assignment表记录AgentID被分配到队列的时间（queue_started_at）及对应队列；Status表记录AgentID变为可用状态的时间（status_started_at）。需要编写SQL查询，返回每个Agent在status_started_at时间点之前，最近一次队列分配的完整信息（包括queue_started_at和队列名称）。

示例数据 #

Assignment表（Tabela_Atribuicao） #

agent_id    queue_started_at    queue
1           2024-12-31          queueA
1           2025-01-03          queueA
1           2025-01-12          queueB

Status表（Tabela_Status） #

agent_id    status_started_at
1           2025-01-01
1           2025-01-02
1           2025-01-10
1           2025-01-13

期望结果 #

agent_id    queue_started_at    status_started_at   queue
1           2024-12-31          2025-01-01          queueA
1           2024-12-31          2025-01-02          queueA
1           2025-01-03          2025-01-10          queueB
1           2025-01-12          2025-01-13          queueB

以下是两种高效且通用的实现方案：

方案1：窗口函数筛选最近记录 #

先关联所有符合时间条件的分配记录，再通过窗口函数为每个状态记录标记最近的分配记录：

WITH ranked_assignments AS (
    SELECT
        s.agent_id,
        s.status_started_at,
        a.queue_started_at,
        a.queue,
        ROW_NUMBER() OVER (
            PARTITION BY s.agent_id, s.status_started_at
            ORDER BY a.queue_started_at DESC
        ) AS rn
    FROM Tabela_Status s
    LEFT JOIN Tabela_Atribuicao a
        ON s.agent_id = a.agent_id
        AND a.queue_started_at <= s.status_started_at
)
SELECT
    agent_id,
    queue_started_at,
    status_started_at,
    queue
FROM ranked_assignments
WHERE rn = 1;

方案2：横向连接直接获取最近记录 #

针对每个状态记录，直接查询出符合条件的最近分配记录，逻辑更直观：

SELECT
    s.agent_id,
    a.queue_started_at,
    s.status_started_at,
    a.queue
FROM Tabela_Status s
LEFT JOIN LATERAL (
    SELECT queue_started_at, queue
    FROM Tabela_Atribuicao a
    WHERE a.agent_id = s.agent_id
      AND a.queue_started_at <= s.status_started_at
    ORDER BY queue_started_at DESC
    LIMIT 1
) a ON true;

方案说明 #

• 两种方案都能精准匹配每个状态时间点之前的最近队列分配记录
• 使用LEFT JOIN会保留没有前置分配记录的状态条目，对应分配字段为NULL；若需过滤这类记录，可改为INNER JOIN
• 窗口函数方案兼容性更强，支持绝大多数现代SQL数据库；横向连接方案在MySQL 8.0+、PostgreSQL、SQL Server中支持，执行效率通常更优

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