如何基于系统版本化账户表创建含创建/修改时间的视图？

假设你的版本化账户表名为account_versioned，核心字段包括account_no（自增主键）、version_no（版本号，数据变更时递增）、valid_from（生效起始时间）、valid_to（生效结束时间），以及其他账户业务字段（如account_name、balance等）。以下是两种高效的实现方式：

方法一：使用窗口函数（推荐，适用于PostgreSQL、MySQL 8.0+、SQL Server等支持窗口函数的数据库） #

窗口函数可一次性完成最新版本筛选与时间字段计算，性能更优：

CREATE VIEW account_latest_with_timestamps AS
SELECT
    account_no,
    -- 保留最新版本的账户业务字段
    account_name,
    balance,
    -- 提取该账户首个版本（version_no=1）的生效时间作为创建时间
    MAX(CASE WHEN version_no = 1 THEN valid_from END) OVER (PARTITION BY account_no) AS time_created,
    -- 提取该账户所有版本中最新的生效时间作为修改时间
    MAX(valid_from) OVER (PARTITION BY account_no) AS time_modified,
    valid_from AS current_valid_from,
    valid_to AS current_valid_to
FROM account_versioned
-- 仅保留每个账户的最新版本记录
QUALIFY ROW_NUMBER() OVER (PARTITION BY account_no ORDER BY version_no DESC) = 1;

关键逻辑说明： #

• PARTITION BY account_no：按账户分组，计算每个账户的聚合值
• QUALIFY 子句：筛选出每个账户中version_no最大的记录（即最新版本）
• MAX(CASE...)：每个账户仅有一条version_no=1的记录，MAX函数可精准提取该记录的valid_from作为创建时间

方法二：使用子查询（兼容低版本MySQL等不支持QUALIFY的数据库） #

如果你的数据库不支持窗口函数的QUALIFY子句，可通过关联子查询实现：

CREATE VIEW account_latest_with_timestamps AS
SELECT
    av.account_no,
    av.account_name,
    av.balance,
    tc.time_created,
    av.valid_from AS time_modified,
    av.valid_from AS current_valid_from,
    av.valid_to AS current_valid_to
FROM account_versioned av
-- 关联子查询获取每个账户的创建时间（version_no=1的valid_from）
JOIN (
    SELECT account_no, valid_from AS time_created
    FROM account_versioned
    WHERE version_no = 1
) tc ON av.account_no = tc.account_no
-- 筛选每个账户的最新版本
WHERE av.version_no = (
    SELECT MAX(version_no)
    FROM account_versioned
    WHERE account_no = av.account_no
);

可选扩展：保留所有版本记录并附带时间字段 #

若需要视图包含所有版本的账户记录，同时显示该账户的创建时间和最新修改时间，只需去掉筛选最新版本的条件，直接用窗口函数计算时间字段即可：

CREATE VIEW account_all_versions_with_timestamps AS
SELECT
    account_no,
    version_no,
    account_name,
    balance,
    MAX(CASE WHEN version_no = 1 THEN valid_from END) OVER (PARTITION BY account_no) AS time_created,
    MAX(valid_from) OVER (PARTITION BY account_no) AS time_modified,
    valid_from,
    valid_to
FROM account_versioned;

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