Vertica SQL问题：如何基于参考表最近日期生成每日URL得分

这个需求本质是典型的**"最新快照关联"**场景——我们需要为Base表中每一条(Date, URL)记录，找到Score Update表中同URL且Update_Date不晚于该Date的最新一条Score记录。下面给出几种适配不同SQL方言的可行方案，同时考虑后续数据新增的兼容性：

方案1：窗口函数（推荐，适配绝大多数现代数据库） #

窗口函数是处理这类"分组取最新"场景的高效方式，尤其适合数据量较大的情况。核心思路是先将Base表与符合条件的Score Update记录关联，再为每个(Date, URL)分组的记录按Update_Date倒序排名，取排名第一的Score（即最新的更新值）。

WITH ranked_updates AS (
    SELECT 
        b.Date,
        b.URL,
        ru.Score,
        -- 按Base的日期和URL分组，更新日期越晚排名越靠前
        ROW_NUMBER() OVER (PARTITION BY b.Date, b.URL ORDER BY ru.Update_Date DESC) AS rn
    FROM Base b
    LEFT JOIN Score_Update ru 
        ON b.URL = ru.URL
        AND ru.Update_Date <= b.Date
)
SELECT Date, URL, Score
FROM ranked_updates
WHERE rn = 1;

说明： #

• 如果Score Update表中存在同一URL、同一Update_Date的多条记录，ROW_NUMBER()会随机选取一条；若要保留所有符合条件的记录，可以替换为RANK()。
• 若某个(Date, URL)在Score Update中没有匹配的历史记录，Score会返回NULL，符合业务逻辑。

方案2：关联子查询（适合小型数据集，写法直观） #

这种写法逻辑简单，直接为Base表的每条记录单独查询对应的最新Score，适合数据量不大的场景。

SELECT 
    b.Date,
    b.URL,
    (
        SELECT Score
        FROM Score_Update ru
        WHERE ru.URL = b.URL
          AND ru.Update_Date <= b.Date
        ORDER BY ru.Update_Date DESC
        -- 注意不同数据库的取第一条语法：
        LIMIT 1 -- MySQL、PostgreSQL使用
        -- TOP 1 -- SQL Server使用
        -- FETCH FIRST 1 ROW ONLY -- Oracle使用
    ) AS Score
FROM Base b;

说明： #

• 子查询会为Base表的每条记录单独执行，数据量较大时可能存在性能瓶颈，此时优先选择窗口函数或LATERAL JOIN方案。

方案3：LATERAL JOIN / OUTER APPLY（适配PostgreSQL、SQL Server等） #

这种写法属于"行级关联"，相当于为Base表的每条记录动态执行一次子查询获取最新Score，性能比普通关联子查询更优，尤其当Score Update表有合适索引时。

PostgreSQL写法： #

SELECT 
    b.Date,
    b.URL,
    ru.Score
FROM Base b
LEFT JOIN LATERAL (
    SELECT Score
    FROM Score_Update ru
    WHERE ru.URL = b.URL
      AND ru.Update_Date <= b.Date
    ORDER BY ru.Update_Date DESC
    LIMIT 1
) ru ON true;

SQL Server写法： #

SELECT 
    b.Date,
    b.URL,
    ru.Score
FROM Base b
OUTER APPLY (
    SELECT TOP 1 Score
    FROM Score_Update ru
    WHERE ru.URL = b.URL
      AND ru.Update_Date <= b.Date
    ORDER BY ru.Update_Date DESC
) ru;

性能优化建议 #

为了确保后续新增数据时查询依然高效，建议为Score Update表创建复合索引：

-- 适配绝大多数数据库的索引语法
CREATE INDEX idx_score_update_url_date ON Score_Update (URL, Update_Date DESC, Score);

这个索引可以让数据库快速定位到每个URL的最新更新记录，避免全表扫描，大幅提升查询效率。

所有方案都能自动兼容后续新增的未来日期数据，无需修改SQL逻辑，只要新增数据符合现有表结构即可。

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