sql - SQL 查询以获取许多表中的最后更改

问题描述

给定三个相关表，我想找出给定过滤条件的最后更改日期是什么以及谁进行了这些更改。

示例：三个表，其中A <1--n> B <1--n> C

CREATE TABLE table_a
(
    a_id               bigint    not null,
    some_data          varchar(255),
    last_changed_at    timestamp not null,
    last_changed_by_id bigint    not null,
    primary key (a_id)
);

CREATE TABLE table_b
(
    b_id               bigint    not null,
    a_id               bigint    not null,
    some_data          varchar(255),
    last_changed_at    timestamp not null,
    last_changed_by_id bigint    not null,
    primary key (b_id)
);

CREATE TABLE table_c
(
    c_id               bigint    not null,
    b_id               bigint    not null,
    a_id               bigint    not null,
    some_data          varchar(255),
    last_changed_at    timestamp not null,
    last_changed_by_id bigint    not null,
    primary key (c_id)
);

alter table table_b add constraint table_a_fk foreign key (a_id) references table_a;
alter table table_c add constraint table_a_fk foreign key (a_id) references table_a;
alter table table_c add constraint table_b_fk foreign key (b_id) references table_b;

我们希望在所有三个表 ( table_a, table_b, ) 中查看与给定条件(例如)table_c相关的最后一个范围a_id以及由谁完成的范围。table_aWHERE table_a.some_data like 'abc%'

由于我们对所有三个表都使用了 Hibernate Envers，因此我们还可以将审计表定义为：

CREATE TABLE table_a_aud
(
    rev                bigint    not null,
    revtype            smallint  not null,
    revend             bigint    not null,
    a_id               bigint    not null,
    some_data          varchar(255),
    last_changed_at    timestamp not null,
    last_changed_by_id bigint    not null,
    primary key (a_id, rev)
);

CREATE TABLE table_b_aud
(
    rev                bigint    not null,
    revtype            smallint  not null,
    revend             bigint    not null,
    b_id               bigint    not null,
    a_id               bigint    not null,
    some_data          varchar(255),
    last_changed_at    timestamp not null,
    last_changed_by_id bigint    not null,
    primary key (b_id, rev)
);

CREATE TABLE table_c_aud
(
    rev                bigint    not null,
    revtype            smallint  not null,
    revend             bigint    not null,
    c_id               bigint    not null,
    b_id               bigint    not null,
    a_id               bigint    not null,
    some_data          varchar(255),
    last_changed_at    timestamp not null,
    last_changed_by_id bigint    not null,
    primary key (c_id, rev)
);

CREATE TABLE revinfo
(
    rev                bigint    not null,
    revtstmp           bigint,
    last_changed_at    timestamp not null,
    last_changed_by_id bigint    not null,
    primary key (rev)
);

alter table table_a_aud add constraint revinfo_a_fk foreign key (rev) references revinfo;
alter table table_b_aud add constraint revinfo_b_fk foreign key (rev) references revinfo;
alter table table_c_aud add constraint revinfo_c_fk foreign key (rev) references revinfo;

我尝试了以下变体，但我不确定其中一种是否是最好的方法。

与所有三个表联合，窗口功能选择最新的。
与所有三个修订表联合（有更多数据），选择最新的窗口功能
创建一个由 a_id、last_changed_at、last_changed_by_id 填充的最新更改的新表，其中 (a) 触发器或 (b) 由我们的应用程序填充。

变体 1可能如下所示：

SELECT tab_a.*, lc.last_changed_at, lc.last_changed_by_id
FROM table_a tab_a
         JOIN (
    (SELECT a_id, last_changed_at, last_changed_by_id
     FROM (
              SELECT a_id,
                     last_changed_at,
                     last_changed_by_id,
                     ROW_NUMBER() OVER (PARTITION BY unioned.a_id ORDER BY unioned.last_changed_at DESC) AS rk
              FROM (
                           (SELECT a_id, last_changed_at, last_changed_by_id FROM table_a)
                           UNION ALL
                           (SELECT a_id, last_changed_at, last_changed_by_id FROM table_b)
                           UNION ALL
                           (SELECT a_id, last_changed_at, last_changed_by_id FROM table_c)) unioned) unioned_with_rank
     WHERE unioned_with_rank.rk = 1)) lc ON tab_a.a_id = lc.a_id
WHERE tab_a.some_data like 'abc%';

table_a尽管在,table_b和Postgres上添加了索引，table_c但仍在对所有表进行 seq 扫描。从性能的角度来看，联合然后使用窗口函数可能不是最好的。

变体 2可能如下所示：

SELECT tab_a.*, lc.last_changed_at, lc.last_changed_by_id
FROM table_a tab_a
         JOIN (
    SELECT a_id, MAX(rev) rev
    FROM (SELECT a_id, rev
          FROM table_a_aud
          UNION ALL
          SELECT a_id, rev
          FROM table_b_aud
          UNION ALL
          SELECT a_id, rev
          FROM table_c_aud) unioned
    GROUP BY a_id) uniongrouped ON tab_a.a_id = uniongrouped.a_id
         JOIN revinfo_ lc ON uniongrouped.rev = lc.rev;
WHERE tab_a.some_data like 'abc%';

我们仍然有一个联合，但我们可以有一个排序索引，(a_id, rev DESC)这可能会有所帮助。但*_aud表包含的数据比其他表多 10 倍以上。IMO 这可能仍然是使用此方法的更好方法。

变体 3

在我们的应用程序中实现，不需要特殊的数据库查询。结果可以是一个简单的连接：

SELECT tab_a.*, lc.last_changed_at, lc.last_changed_by_id
FROM table_a tab_a JOIN last_change lc ON tab_a.a_id = lc.a_id

这将使查询语句更快，但我们需要将更新代码分布在整个应用程序代码中，这是我不喜欢的。此外，我看不到可以在事务结束前不久执行的触发器，并且如果在表table_a上table_b和table_c同一个事务中添加/更新了许多条目，则只添加一个插入/更新语句而不是多个语句。

有没有人做过类似的事情？
解决此要求的最佳方法是什么？
是否有适用于 PostgreSQL、SQL Server 以及 H2 和 Oracle 的通用方法？

标签： sqlsql-serverpostgresqlh2window-functions

sql - SQL 查询以获取许多表中的最后更改

问题描述

解决方案

推荐阅读