时间戳

首页 > 解决方案 > 获取最新更改的信息

sql - 获取最新更改的信息

问题描述

我有一个包含大量信息的审计表。我需要找到对每个项目的特定列进行最新更改的时间和人员。

我能够执行返回正确结果的查询，但我发现它是一团糟。特别是因为我需要为很多不同的列执行此操作（我可能会使用联合来执行此操作）并将这些值与最新发布的值进行比较。无论如何......有没有比做3级选择更好的方法？

create table Z_AUDIT
(
  v CHAR(1) not null,      -- A value that is part of a project (ex: project manager)
  t DATE not null,         -- Time of change
  w VARCHAR2(10) not null, -- Who did the change
  p VARCHAR2(10)           -- Project
)

INSERT INTO Z_AUDIT (p, v, t, w) VALUES ('project 1', 'a', sysdate, 'bob');
INSERT INTO Z_AUDIT (p, v, t, w) VALUES ('project 1', 'a', sysdate-1, 'judy');
INSERT INTO Z_AUDIT (p, v, t, w) VALUES ('project 1', 'a', sysdate-2, 'bob');
INSERT INTO Z_AUDIT (p, v, t, w) VALUES ('project 1', 'b', sysdate-3, 'judy');
INSERT INTO Z_AUDIT (p, v, t, w) VALUES ('project 1', 'b', sysdate-4, 'bob');
INSERT INTO Z_AUDIT (p, v, t, w) VALUES ('project 1', 'a', sysdate-5, 'judy');
INSERT INTO Z_AUDIT (p, v, t, w) VALUES ('project 1', 'a', sysdate-6, 'bob');
INSERT INTO Z_AUDIT (p, v, t, w) VALUES ('project 1', 'b', sysdate-7, 'judy');
INSERT INTO Z_AUDIT (p, v, t, w) VALUES ('project 1', 'c', sysdate-8, 'bob');

INSERT INTO Z_AUDIT (p, v, t, w) VALUES ('project 2', 'b', sysdate, 'bob');
INSERT INTO Z_AUDIT (p, v, t, w) VALUES ('project 2', 'a', sysdate-1, 'judy');
INSERT INTO Z_AUDIT (p, v, t, w) VALUES ('project 2', 'a', sysdate-2, 'bob');
INSERT INTO Z_AUDIT (p, v, t, w) VALUES ('project 2', 'b', sysdate-3, 'judy');
INSERT INTO Z_AUDIT (p, v, t, w) VALUES ('project 2', 'c', sysdate-4, 'bob');

 -- Get the latest change from the rank 1
 select p, w, t, current_value, previous_value
   from ( -- Get only the changes with the where clause and get the rank
         select i.p,
                 i.w,
                 i.t,
                 i.current_value,
                 i.previous_value,
                 rank() over(partition by i.p order by i.t desc) r
           from ( -- Get the previous value for each audit record
                  select p,
                          w,
                          t,
                          v as current_value,
                          LAG(v, 1) OVER(partition by p ORDER BY t) as previous_value
                    from Z_AUDIT) i
          where nvl(current_value, 'a') <> nvl(previous_value, 'a'))
  where r = 1;

项目 1，鲍勃，2020 年 5 月 9 日上午 7:08:55，a，b
项目 2，鲍勃，2020 年 5 月 11 日上午 7:12:39，b，a

标签： sqloracleoptimizationoracle12c

解决方案

有几个更简单的方法来写这个。注意我都使用 CTE 编写了它们，因为它们更容易阅读（IMO）。您可以使用FIRST_VALUE：

WITH cp AS (
  SELECT p, w, t, v AS curr,
         LAG(v, 1, '') OVER (PARTITION BY p ORDER BY t) AS prev
  FROM Z_AUDIT
)
SELECT DISTINCT p,
       FIRST_VALUE(w) OVER (PARTITION BY p ORDER BY t DESC) AS w,
       FIRST_VALUE(t) OVER (PARTITION BY p ORDER BY t DESC) AS t,
       FIRST_VALUE(curr) OVER (PARTITION BY p ORDER BY t DESC) AS curr,
       FIRST_VALUE(prev) OVER (PARTITION BY p ORDER BY t DESC) AS prev
FROM cp
WHERE curr != prev

或 Oracle 特定的KEEP ... FIRST：

WITH cp AS (
  SELECT p, w, t, v AS curr,
         LAG(v, 1, '') OVER (PARTITION BY p ORDER BY t) AS prev
  FROM Z_AUDIT
)
SELECT p,
       MIN(w) KEEP (DENSE_RANK FIRST ORDER BY t DESC) AS w,
       MIN(t) KEEP (DENSE_RANK FIRST ORDER BY t DESC) AS t,
       MIN(curr) KEEP (DENSE_RANK FIRST ORDER BY t DESC) AS curr,
       MIN(prev) KEEP (DENSE_RANK FIRST ORDER BY t DESC) AS prev
FROM cp
WHERE curr != prev
GROUP BY p

在这两种情况下，输出都是：

P           W       T           CURR    PREV
project 1   bob     09-MAY-20   a       b
project 2   bob     11-MAY-20   b       a

dbfiddle上的演示（还包括原始查询的 CTE 版本）

推荐阅读