从源数据生成/重建完整历史类型2表

o2rvlv0m 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(330)

我在psa中有一个表，在该表中捕获对源表中记录的更改。假设它看起来像这样：

+-----+------------+----------------+------------------+------------------+
| PK  | Check_cols | Dont_care_cols |     start_ts     |      end_ts      |
+-----+------------+----------------+------------------+------------------+
| 123 | abc        | def            | 1/1/20 00:10:00  | 1/2/20 13:13:23  |
| 123 | abc        | dhf            | 1/2/20 13:13:23  | 1/3/20 04:21:00  |
| 123 | abc        | dhz            | 1/3/20 04:21:00  | 1/5/20 12:15:00  |
| 123 | abd        | dyz            | 1/5/20 12:15:00  | 1/9/20 15:16:00  |
| 123 | abc        | dyz            | 1/9/20 15:16:00  | null             |
| 456 | ghi        | jkl            | 1/2/20 03:45:00  | 1/10/20 00:00:00 |
| 456 | lmn        | opq            | 1/10/20 00:00:00 | null             |
+-----+------------+----------------+------------------+------------------+

我想从该表中仅使用check\u cols的值构建一个类型2维度（跟踪记录开始和停止时间的变化），如下所示。我正在寻找一个纯sql解决方案，没有循环。
check\u cols由多个列组成，但我将使用md5散列来查找更改。因为我的维度只关心check\u cols，所以有时时间戳记录不是我所需要的。例如，如果dont\u care\u cols中的值发生更改，但是check\u cols中的值都没有更改。
根据上面的数据，我需要以下结果集：

+-----+------------+------------------+------------------+
| PK  | Check_cols |     start_ts     |      end_ts      |
+-----+------------+------------------+------------------+
| 123 | abc        | 1/1/20 00:10:00  | 1/5/20 12:15:00  |
| 123 | abd        | 1/5/20 12:15:00  | 1/9/20 15:16:00  |
| 123 | abc        | 1/9/20 15:16:00  | null             |
| 456 | ghi        | 1/2/20 03:45:00  | 1/10/20 00:00:00 |
| 456 | lmn        | 1/10/20 00:00:00 | null             |
+-----+------------+------------------+------------------+

我尝试过使用窗口函数来比较超前和滞后值，得到最小值和最大值，等等，但是我无法理解第一个表中PK123的这种边缘情况。我也没有通过google/stackoverflow/等找到解决方案。大多数方法依赖于每日运行的快照。如果有逻辑更改，我希望能够重建目标表。有人有想法吗？

sql data-warehouse snowflake-cloud-data-platform

来源：https://stackoverflow.com/questions/62633850/build-rebuild-a-full-history-type-2-table-from-source-data

1条答案

按热度按时间

cgh8pdjw1#

我不知道这是不是最好的答案，也不知道它是否解决了您的所有用例，但请尝试一下，让我知道是否有边缘的情况，绊倒了它。有点像黑客。此外，我还向用例中添加了一些记录：

CREATE OR REPLACE TEMP TABLE tran_data (pk int, check_cols varchar, dont_care_cols varchar, start_ts timestamp, end_ts timestamp);

INSERT INTO tran_data
SELECT *
FROM (VALUES(123,'abc','def',TO_TIMESTAMP('1/1/20 00:10:00','MM/DD/YY hh:mi:ss'),TO_TIMESTAMP('1/2/20 13:13:23','MM/DD/YY hh:mi:ss')),
              (123,'abc','dhf',TO_TIMESTAMP('1/2/20 13:13:23','MM/DD/YY hh:mi:ss'),TO_TIMESTAMP('1/3/20 04:21:00','MM/DD/YY hh:mi:ss')),
              (123,'abc','dhz',TO_TIMESTAMP('1/3/20 04:21:00','MM/DD/YY hh:mi:ss'),TO_TIMESTAMP('1/5/20 12:15:00','MM/DD/YY hh:mi:ss')),
              (123,'abd','dyz',TO_TIMESTAMP('1/5/20 12:15:00','MM/DD/YY hh:mi:ss'),TO_TIMESTAMP('1/9/20 15:16:00','MM/DD/YY hh:mi:ss')),
              (123,'abd','dyz',TO_TIMESTAMP('1/9/20 15:16:00','MM/DD/YY hh:mi:ss'),TO_TIMESTAMP('1/11/20 14:14:00','MM/DD/YY hh:mi:ss')),
              (123,'abc','dyz',TO_TIMESTAMP('1/11/20 14:14:00','MM/DD/YY hh:mi:ss'),TO_TIMESTAMP('1/14/20 09:14:00','MM/DD/YY hh:mi:ss')),
              (123,'abc','dyz',TO_TIMESTAMP('1/14/20 09:14:00','MM/DD/YY hh:mi:ss'),null),
              (456,'ghi','jkl',TO_TIMESTAMP('1/2/20 03:45:00','MM/DD/YY hh:mi:ss'),TO_TIMESTAMP('1/10/20 00:00:00','MM/DD/YY hh:mi:ss')),
              (456,'lmn','opq',TO_TIMESTAMP('1/10/20 00:00:00','MM/DD/YY hh:mi:ss'),null)
        );

从那以后，我试图找到一种创建“组”的方法，使用一种我希望能经得起您所有用例考验的方法：

SELECT DISTINCT
     PK
   , check_cols
   , FIRST_VALUE(start_ts) OVER (PARTITION BY PK, check_cols, group_num ORDER BY start_ts) as new_start_ts
   , LAST_VALUE(end_ts) OVER (PARTITION BY PK, check_cols, group_num ORDER BY start_ts) as new_end_ts
FROM (
  SELECT
       ROW_NUMBER() OVER (PARTITION BY PK, check_cols ORDER BY start_ts) as group_cnt
     , group_cnt - pk_row as group_num
     , *
  FROM (
    SELECT
         ROW_NUMBER() OVER (PARTITION BY PK ORDER BY start_ts) as pk_row
       , IFNULL(LAG(check_cols) OVER (PARTITION BY PK ORDER BY start_ts),check_cols) as prev_check_cols
       , *
    FROM tran_data
    )
  )
ORDER BY pk, new_start_ts;

赞(0）回复(0）举报 2021-08-09

我来回答

从源数据生成/重建完整历史类型2表

1条答案

相关问题

热门标签

最新问答