使用审计表时的查询帮助 [英] Query help when using audit table

问题描述

假设我有两个表，一个具有以下列，名为lease_period：

tenant_trading_name，suite_id，lease_id，building_id

，另一个名为lease_period_audit，包含以下内容：

每次在lease_period上更新记录时，在lease_period_audit上输入记录，其中包含一个记录，每个记录包含一个记录，其中包含一个记录。状态更新。我试图找到所有只更新tenant_trading_name字段，但没有取得任何成功。到目前为止，我有以下：

 选择lpa。* 
从property.lease_period_audit lpa 
 inner join property.lease_period lp on lpa.suite_id = lp.suite_id和lpa.lease_id = lp.lease_id和lpa.building_id = lp.building_id 
其中audit_type ='Updated'
和lp.tenant_trading_name<> lpa.tenant_trading_name 
 order by 1 desc 
  

我的思维过程中的缺陷在哪里？

解决方案

假设审计表也记录了 lease_period 主键列，为了简单起见，请参考此处 lp_id ：

1. 查找 audit_type 为'更新'




2. 按 audit_date 排列所有列，并以 lp_id




3. 审计日期排序 lp_id，suite_id，lease_id，building_id




4. 取得两个排名之间的差异。




5. audit_date 再次对行排序，现在通过 lp_id，suite_id，lease_id，building_id

输出所有最后排名值为2或更大的行。

前四个步骤导致一个行集，其中每组连续（按 audit_date lp_id ，具有相同值 suite_id，lease_id，building_id 的行计算为排名＃2& ＃3。

在组内，从第二行开始的每一行将与前一行不同，只有 tenant_trading_name ，这正是我们需要的。因此，我们再次对行进行排名，考虑到我们刚刚获得的组ID，然后返回排名为2或更高的每一行。

这是一个大致的实现：

 标记为AS（
 SELECT 
 *，
 grp = ROW_NUMBER（）OVER（PARTITION BY lp_id 
 ORDER BY audit_date）
  -  ROW_NUMBER（）OVER（PARTITION BY lp_id，suite_id，lease_id，building_id 
 ORDER BY audit_date）
 FROM lease_period_audit 
 WHERE audit_type ='更新'
），
排名AS（
 SELECT 
 *，
 rnk = ROW_NUMBER（）OVER（PARTITION BY lp_id，suite_id， lease_id，building_id，grp 
 ORDER BY audit_date）
从标记的
）
 SELECT 
 audit_date，
 lp_id，
 tenant_trading_name，
 suite_id，
 lease_id，
 building_id 
 FROM列出的
 WHERE rnk = 2 
  

注意。这假设审计表仅记录实际变化，即不能有两个连续的行具有相同的主键，其中所有四列具有相同的值。

Assuming I have two tables, one with the following columns called lease_period:

tenant_trading_name, suite_id, lease_id, building_id

and another, called lease_period_audit with the following:

audit_date, audit_type, tenant_trading_name, suite_id, lease_id, building_id

Each time a record is updated on lease_period and entry is made on lease_period_audit with a status of 'Updated'. I'm trying to find all updates made only to the tenant_trading_name field but haven't had any success. So far I have the following:

select              lpa.*
from                property.lease_period_audit lpa
inner join          property.lease_period lp on lpa.suite_id = lp.suite_id and lpa.lease_id = lp.lease_id and lpa.building_id = lp.building_id
where               audit_type = 'Updated'
                    and lp.tenant_trading_name <> lpa.tenant_trading_name
order by            1 desc  

Where's the flaw in my thought process here? How can this be done / how should I be thinking about this?

解决方案

Assuming the audit table also logs the lease_period primary key column, referenced here lp_id for simplicity, you could try the following approach:

1. Find all the rows where audit_type is 'Updated'.

2. Rank all the rows by audit_date and partitioning them by lp_id.

3. Rank the rows by audit_date partitioning by lp_id, suite_id, lease_id, building_id.

4. Get the difference between the two rankings.

5. Rank the rows again by audit_date, partitioning them now by lp_id, suite_id, lease_id, building_id, (ranking_difference).

6. Output all the rows where the last ranking value is 2 or greater.

The first four steps result in a row set where each group of consecutive (in ascending order of audit_date) rows with identical values of suite_id, lease_id, building_id for the same lp_id will be uniquely distinguished by a value calculated as the difference between the rankings #2 & #3.

Within the group, every row, starting from the second one, will differ from the previous one only in the value of tenant_trading_name, which is just what we need. So, we rank the rows once again, taking into account the 'group ID' we've just obtained, then return every row with the ranking of 2 or higher.

Here's an approximate implementation:

WITH marked AS (
  SELECT
    *,
    grp = ROW_NUMBER() OVER (PARTITION BY lp_id
                                 ORDER BY audit_date)
        - ROW_NUMBER() OVER (PARTITION BY lp_id, suite_id, lease_id, building_id
                                 ORDER BY audit_date)
  FROM lease_period_audit
  WHERE audit_type = 'Updated'
),
ranked AS (
  SELECT
    *,
    rnk = ROW_NUMBER() OVER (PARTITION BY lp_id, suite_id, lease_id, building_id, grp
                                 ORDER BY audit_date)
  FROM marked
)
SELECT
  audit_date,
  lp_id,
  tenant_trading_name,
  suite_id,
  lease_id,
  building_id
FROM ranked
WHERE rnk = 2

Note. This assumes that the audit table logs only real changes, i.e. there can't be two consecutive rows with the same primary key where all four columns have identical values.

这篇关于使用审计表时的查询帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！