返回具有特定关联值更改的帐户的所有历史记录 [英] Return All Historical Records for Accounts with Change in Specific Associated Value

问题描述

我试图为每个帐户选择时变帐户表中的所有记录，并更改关联值（例如，到期日期）。值的更改将导致帐户的最新记录到期，并创建新记录（包含第二天的新生效日期）。此表中帐户的最新记录的结束日期为12/31/9000。

I am trying to select all records in a time-variant Account table for each account with a change in an associated value (e.g. the maturity date). A change in the value will result in the most recent record for an account being end-dated and a new record (containing a new effective date of the following day) being created. The most recent records for accounts in this table have an end-date of 12/31/9000.

例如，在下图中，将不包括帐户44444444在我的查询结果集中，因为它的值没有变化（因此，除了原始值外，没有其他记录）；但是，其他帐户的值（和多个记录）有多个更改，因此我想查看返回的值。

For instance, in the below illustration, account 44444444 would not be included in my query result set since it hasn't had a change in the value (and thus also has no additional records aside from the original); however, the other accounts have multiple changes in values (and multiple records), so I would want to see those returned.

此外，该表还有许多其他字段（列）未在下面包括，但针对这些字段的值的更改可能会触发创建新记录；但是，我只想检索值列中的数字已更改的那些帐户的所有记录。有什么方法可以获得我需要的结果？

Also, the table has a number of other fields (columns) not included below but for which changes in the values for these fields can trigger a new record being created; however, I only want to retrieve all records for those accounts where the figure in the "value" column has changed. What are some ways to obtain the results I need?

注意：该表的主键包括acct_id和eff_dt，并且我在Greenplum环境中使用PostgreSQL。

Note: The primary key for this table includes the acct_id and eff_dt, and I'm using PostgreSQL within a Greenplum environment.

以下是我尝试使用的两种查询，但它们产生了有问题的结果：

Here are two types of queries I tried to use but which produced problematic results:

查询1
查询2

推荐答案

我认为您希望窗口函数比较该值：

I think you want window functions to compare the value:

select t.*
from (select t.*,
             min(t.value) over (partition by t.acct_id) as min_value,
             max(t.value) over (partition by t.acct_id) as max_value
      from t
     ) t
where min_value <> max_value;

这篇关于返回具有特定关联值更改的帐户的所有历史记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！