sql - 每次更改时如何选择列？

Question

我正在尝试创建一个缓慢变化的维度(类型 2 维度)，但对如何从逻辑上写出来有点迷茫。假设我们有一个源表，其中包含 Person |国家 |部门|登录时间。我想用 Person | 创建这个维度表国家 |部门|效果开始时间 |效果结束时间。

数据可能是这样的:

Person | Country | Department | Login Time
------------------------------------------
Bob    | CANADA  | Marketing  | 2009-01-01
Bob    | CANADA  | Marketing  | 2009-02-01
Bob    | USA     | Marketing  | 2009-03-01
Bob    | USA     | Sales      | 2009-04-01
Bob    | MEX     | Product    | 2009-05-01
Bob    | MEX     | Product    | 2009-06-01
Bob    | MEX     | Product    | 2009-07-01
Bob    | CANADA  | Marketing  | 2009-08-01

我想要的 Type 2 维度是这样的:

Person | Country | Department | Eff Start time | Eff End Time
------------------------------------------------------------------
Bob    | CANADA  | Marketing  | 2009-01-01     | 2009-03-01
Bob    | USA     | Marketing  | 2009-03-01     | 2009-04-01
Bob    | USA     | Sales      | 2009-04-01     | 2009-05-01
Bob    | MEX     | Product    | 2009-05-01     | 2009-08-01
Bob    | CANADA  | Marketing  | 2009-08-01     | NULL 

假设 Bob 的姓名、国家和部门自 2009-08-01 以来未更新，因此保留为 NULL

什么功能在这里最有效？这是在 Netezza 上，它使用了 Postgres 的风格。

显然 GROUP BY 在这里不起作用，因为稍后会出现相同的分组(我在最后一行添加了 Bob | CANADA | Marketing 以显示这一点。

编辑

包括一个关于 Person、Country 和 Department 的散列列，是有意义的，对吗？想用

的逻辑

SELECT PERSON, COUNTRY, DEPARTMENT
FROM table t1
where 
    person = person 
    AND t1.hash <> hash_function(person, country, department)

Answer 1

回答

create table so (
  person varchar(32)
  ,country varchar(32)
  ,department varchar(32)
  ,login_time date
) distribute on random;

insert into so values ('Bob','CANADA','Marketing','2009-01-01');
insert into so values ('Bob','CANADA','Marketing','2009-02-01');
insert into so values ('Bob','USA','Marketing','2009-03-01');
insert into so values ('Bob','USA','Sales','2009-04-01');
insert into so values ('Bob','MEX','Product','2009-05-01');
insert into so values ('Bob','MEX','Product','2009-06-01');
insert into so values ('Bob','MEX','Product','2009-07-01');
insert into so values ('Bob','CANADA','Marketing','2009-08-01');

/* ************************************************************************** */

with prm as ( --Create an ordinal primary key.
  select
    *
    ,row_number() over (
      partition by person
      order by login_time
    ) rwn
  from
    so
), chn as ( --Chain events to their previous and next event.
  select
    cur.rwn
    ,cur.person
    ,cur.country
    ,cur.department
    ,cur.login_time cur_login
    ,case
      when
        cur.country = prv.country
        and cur.department = prv.department
        then 1
      else 0
    end prv_equal
    ,case
      when
        (
          cur.country = nxt.country
          and cur.department = nxt.department
        ) or nxt.rwn is null --No next record should be equivalent to matching.
        then 1
      else 0
    end nxt_equal
    ,case prv_equal
      when 0 then cur_login
      else null
    end eff_login_start_sparse
    ,case
      when eff_login_start_sparse is null
        then max(eff_login_start_sparse) over (
          partition by cur.person
          order by rwn
          rows unbounded preceding --The secret sauce.
        )
      else eff_login_start_sparse
    end eff_login_start
    ,case nxt_equal
      when 0 then cur_login
      else null
    end eff_login_end
  from
    prm cur
    left outer join prm nxt on
      cur.person = nxt.person
      and cur.rwn + 1 = nxt.rwn
    left outer join prm prv on
      cur.person = prv.person
      and cur.rwn - 1 = prv.rwn
), grp as ( --Group by login starts.
  select
    person
    ,country
    ,department
    ,eff_login_start
    ,max(eff_login_end) eff_login_end
  from
    chn
  group by
    person
    ,country
    ,department
    ,eff_login_start
), led as ( --Change the effective end to be the next start, if desired.
  select
    person
    ,country
    ,department
    ,eff_login_start
    ,case
      when eff_login_end is null
        then null
      else
        lead(eff_login_start) over (
          partition by person
          order by eff_login_start
        )
    end eff_login_end
  from
    grp
)
select * from led order by eff_login_start;

此代码返回下表。

 PERSON | COUNTRY | DEPARTMENT | EFF_LOGIN_START | EFF_LOGIN_END
--------+---------+------------+-----------------+---------------
 Bob    | CANADA  | Marketing  | 2009-01-01      | 2009-03-01
 Bob    | USA     | Marketing  | 2009-03-01      | 2009-04-01
 Bob    | USA     | Sales      | 2009-04-01      | 2009-05-01
 Bob    | MEX     | Product    | 2009-05-01      | 2009-08-01
 Bob    | CANADA  | Marketing  | 2009-08-01      |

说明

这几年肯定解决过四五次了，一直忘了正式写下来。我很高兴有机会这样做，所以这是一个很好的问题。

尝试这样做时，我喜欢以矩阵形式写下问题。这是输入，假设所有值在 SCD 中都具有相同的键。

 Cv | Ce
----|----
 A  | 10
 A  | 11
 B  | 14
 C  | 16
 D  | 18
 D  | 25
 D  | 34
 A  | 40

其中 Cv 是我们需要比较的值(同样，假设 SCD 的键值在此数据中相等；我们将在整个过程中对键值进行分区，因此它与解)，Ce 为事件时间。

首先，我们需要一个序号主键。我在表中指定了这个 Ck。这将允许我们将表连接到自身以获取上一个和下一个事件。我将这些列称为 Pk(上一个键)、Nk(下一个键)、Pv 和 Nv。

 Cv | Ce | Ck | Pk | Pv | Nk | Nv |
----|----|----|----|----|----|----|
 A  | 10 | 1  |    |    | 2  | A  |
 A  | 11 | 2  | 1  | A  | 3  | B  |
 B  | 14 | 3  | 2  | A  | 4  | C  |
 C  | 16 | 4  | 3  | B  | 5  | D  |
 D  | 18 | 5  | 4  | C  | 6  | D  |
 D  | 25 | 6  | 5  | D  | 7  | D  |
 D  | 34 | 7  | 6  | D  | 8  | A  |
 A  | 40 | 8  | 7  | D  |    |    |

现在我们需要一些列来查看我们是在连续事件 block 的开头还是结尾。我将这些 Pc 和 Nc 称为连续的。 Pc 定义为 Pv = Cv => 真。 1 代表真，0 代表假。 Nc 的定义类似，只是 null 情况默认为​​ true(稍后我们会看到原因)

 Cv | Ce | Ck | Pk | Pv | Nk | Nv | Pc | Nc |
----|----|----|----|----|----|----|----|----|
 A  | 10 | 1  |    |    | 2  | A  | 0  | 1  |
 A  | 11 | 2  | 1  | A  | 3  | B  | 1  | 0  |
 B  | 14 | 3  | 2  | A  | 4  | C  | 0  | 0  |
 C  | 16 | 4  | 3  | B  | 5  | D  | 0  | 0  |
 D  | 18 | 5  | 4  | C  | 6  | D  | 0  | 1  |
 D  | 25 | 6  | 5  | D  | 7  | D  | 1  | 1  |
 D  | 34 | 7  | 6  | D  | 8  | A  | 1  | 0  |
 A  | 40 | 8  | 7  | D  |    |    | 0  | 1  |

现在您可以开始了解 Pc,Nc 的 1,1 组合是一个完全无用的记录。我们凭直觉知道这一点，因为 Bob 在第 6 行的 Mex/Product 组合在构建 SCD 时几乎是无用的信息。

所以让我们去掉无用的信息。我将在此处添加两个新列:称为 Sn 的几乎完整的有效开始时间和称为 Ee 的实际完整的有效结束时间。 Pc为0时Sn填充Ce，Nc为0时Ee填充Ce。

 Cv | Ce | Ck | Pk | Pv | Nk | Nv | Pc | Nc | Sn | Ee |
----|----|----|----|----|----|----|----|----|----|----|
 A  | 10 | 1  |    |    | 2  | A  | 0  | 1  | 10 |    |
 A  | 11 | 2  | 1  | A  | 3  | B  | 1  | 0  |    | 11 |
 B  | 14 | 3  | 2  | A  | 4  | C  | 0  | 0  | 14 | 14 |
 C  | 16 | 4  | 3  | B  | 5  | D  | 0  | 0  | 16 | 16 |
 D  | 18 | 5  | 4  | C  | 6  | D  | 0  | 1  | 18 |    |
 D  | 25 | 6  | 5  | D  | 7  | D  | 1  | 1  |    |    |
 D  | 34 | 7  | 6  | D  | 8  | A  | 1  | 0  |    | 34 |
 A  | 40 | 8  | 7  | D  |    |    | 0  | 1  | 40 |    |

这看起来非常接近，但我们仍然存在无法按 Cv(个人/国家/部门)分组的问题。我们需要的是让 Sn 用 Sn 的先前值填充所有这些空值。您可以在 rwn < rwn 上将此表与其自身连接并获得最大值，但我会偷懒并使用 Netezza 的分析函数和 rows unbounded preceding条款。这是我刚才描述的方法的快捷方式。所以我们要创建另一个名为 Es 的列，effective start，定义如下。

case
  when Sn is null
    then max(Sn) over (
      partition by k --key value of the SCD
      order by Ck
      rows unbounded preceding
    )
  else Sn
end Es

有了这个定义，我们就明白了。

 Cv | Ce | Ck | Pk | Pv | Nk | Nv | Pc | Nc | Sn | Ee | Es |
----|----|----|----|----|----|----|----|----|----|----|----|
 A  | 10 | 1  |    |    | 2  | A  | 0  | 1  | 10 |    | 10 |
 A  | 11 | 2  | 1  | A  | 3  | B  | 1  | 0  |    | 11 | 10 |
 B  | 14 | 3  | 2  | A  | 4  | C  | 0  | 0  | 14 | 14 | 14 |
 C  | 16 | 4  | 3  | B  | 5  | D  | 0  | 0  | 16 | 16 | 16 |
 D  | 18 | 5  | 4  | C  | 6  | D  | 0  | 1  | 18 |    | 18 |
 D  | 25 | 6  | 5  | D  | 7  | D  | 1  | 1  |    |    | 18 |
 D  | 34 | 7  | 6  | D  | 8  | A  | 1  | 0  |    | 34 | 18 |
 A  | 40 | 8  | 7  | D  |    |    | 0  | 1  | 40 |    | 40 |

其余的都是微不足道的。按Es分组，取Ee的最大值得到这张表。

 Cv | Es | Ee |
----|----|----|
 A  | 10 | 11 |
 B  | 14 | 14 |
 C  | 16 | 16 |
 D  | 18 | 34 |
 A  | 40 |    |

如果您想用下一次开始填充有效结束时间，请再次将表连接到自身或使​​用 lead()抓取它的窗口函数。