不可修改数据库中，如何查询含未变动余额的近7天账户历史数据？

要实现所有账户（含近7天无余额变动的账户）的近7天余额历史查询及均值计算，核心是保证全账户覆盖，并对无记录的账户用当前余额补全，以下是具体实现方案：

核心思路 #

1. 以tAccount作为主表做左连接，确保所有账户都能出现在结果中；
2. 将tBalHist的近7天数据过滤条件放在连接的ON子句中，避免用WHERE子句过滤导致无记录账户被排除；
3. 用COALESCE函数把无历史记录产生的NULL余额替换为账户的currentbalance；
4. 按需选择仅计算均值，或按天展示余额明细并附带均值。

具体SQL实现 #

场景1：仅计算每个账户的近7天余额均值 #

SELECT
    a.acctnum,
    a.owner,
    a.type,
    -- 无历史记录时均值直接取当前余额；有记录则取历史余额的平均值
    AVG(COALESCE(bh.bal, a.currentbalance)) AS avg_7day_balance
FROM tAccount a
LEFT JOIN tBalHist bh
    ON a.acctnum = bh.acctnum
    -- 限定近7天范围（2023-09-01至2023-09-07）
    AND bh.baldate BETWEEN '2023-09-01' AND '2023-09-07'
GROUP BY a.acctnum, a.owner, a.type, a.currentbalance;

场景2：按天展示每个账户的近7天余额明细，并附带均值 #

如果需要查看每日的余额情况（包括无变动日期的余额），需要先生成近7天的日期序列，再关联账户和历史记录：

生成近7天日期序列（不同数据库语法）

• MySQL：

  WITH date_range AS (
      SELECT DATE_SUB('2023-09-07', INTERVAL n DAY) AS baldate
      FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) AS days(n)
  )
  

• PostgreSQL：

  WITH date_range AS (
      SELECT generate_series('2023-09-01'::date, '2023-09-07'::date, '1 day')::date AS baldate
  )
  

• SQL Server：

  WITH date_range AS (
      SELECT DATEADD(DAY, n, '2023-09-01') AS baldate
      FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) AS days(n)
  )
  

完整查询语句（以MySQL为例）

WITH date_range AS (
    SELECT DATE_SUB('2023-09-07', INTERVAL n DAY) AS baldate
    FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) AS days(n)
)
SELECT
    a.acctnum,
    a.owner,
    dr.baldate,
    -- 无当日变动记录时，用当前余额填充
    COALESCE(bh.bal, a.currentbalance) AS daily_balance,
    -- 计算该账户近7天的余额平均值
    AVG(COALESCE(bh.bal, a.currentbalance)) OVER (PARTITION BY a.acctnum) AS avg_7day_balance
FROM tAccount a
-- 关联所有日期，确保每个账户都有7天的记录
CROSS JOIN date_range dr
LEFT JOIN tBalHist bh
    ON a.acctnum = bh.acctnum
    AND bh.baldate = dr.baldate
ORDER BY a.acctnum, dr.baldate;

关键细节说明 #

• 左连接的条件位置：必须把日期过滤放在ON子句而非WHERE子句，否则会将无历史记录的账户过滤掉（左连接退化为内连接）；
• COALESCE的作用：当tBalHist中无匹配记录时，bh.bal为NULL，COALESCE会自动替换为currentbalance，保证均值计算的准确性；
• 分组与窗口函数：场景1用GROUP BY聚合计算均值，场景2用窗口函数OVER(PARTITION BY a.acctnum)在保留明细的同时展示均值。

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