如何用单SQL查询实现选定日期范围余额及历史余额展示？

需求说明 #

在Apache Superset场景中，用户选择日期范围后，报表需同时展示：

• 该日期范围内的客户当前余额记录
• 该日期范围之前的客户历史余额记录
  所有数据均来自balance表，按customerID关联两类数据。

举例：若用户选择日期范围为2023-01-01至2023-01-01，查询结果需包含该日期的所有客户余额记录，以及对应客户在2023-01-01之前的历史余额记录。

原查询的问题 #

你当前使用的LEFT JOIN写法虽能实现需求，但存在两个明显问题：

1. 子查询嵌套层级冗余，可读性差
2. 若同一客户在历史区间有多条记录，会与范围内记录产生笛卡尔积，导致结果集膨胀、数据重复且性能低下

优化后的SQL实现 #

场景1：保留所有历史余额记录（与范围内记录关联） #

如果需要保留历史区间的每一条余额记录，可简化JOIN逻辑，去掉多余子查询：

SELECT 
    a.* AS current_balance,
    b.* AS historical_balance
FROM balance a
LEFT JOIN balance b 
    ON a.customerID = b.customerID
    AND b.date < '{{ start_date }}'  -- Superset日期变量
WHERE a.date BETWEEN '{{ start_date }}' AND '{{ end_date }}'

场景2：仅保留历史区间的最新余额记录（更常用） #

多数报表场景下，用户需要的是截至查询范围前的最新历史余额，而非所有历史记录，此时可结合窗口函数实现：

SELECT 
    a.*,
    b.latest_historical_date,
    b.latest_historical_balance
FROM balance a
LEFT JOIN (
    SELECT 
        customerID,
        date AS latest_historical_date,
        balance AS latest_historical_balance,
        ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY date DESC) AS rn
    FROM balance
    WHERE date < '{{ start_date }}'
) b ON a.customerID = b.customerID AND b.rn = 1
WHERE a.date BETWEEN '{{ start_date }}' AND '{{ end_date }}'

说明 #

• 用{{ start_date }}和{{ end_date }}替换硬编码日期，适配Apache Superset的用户日期选择器变量
• 场景2通过ROW_NUMBER()窗口函数，按客户分组后取历史区间内最新的一条余额记录，避免结果集膨胀

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