sql - Spark-SQL - 根据日期间隔生成多行

Question

我需要编写一个查询，Spark-SQL根据日期间隔从一个生成多个记录。在这里，我们尝试模拟 SCD-Type 2。要求是：在这里，一行将有 2 个日期列（eff_from_dt 和 eff_end_dt）以及其他列。对于任何现有记录，如果今天输入发生变化（在其任何列的值中），则需要使用新的 eff_from_dt 值插入新记录；并且现有记录需要通过使用 (new-date -1) 值更新 eff_end_dt 来结束日期。现在，需要为更新记录的 eff_from_dt 和 eff_to_dt 之间存在的每个月生成记录。

Date Dim:
--------

currdt | first_day_of_mth | last_day_of_mth  
-------------------------------------------  
2019-01-15 | 2019-01-01 | 2019-01-31  
2019-01-16 | 2019-01-01 | 2019-01-31  
.......
2019-07-25 | 2019-07-01 | 2019-07-31 

第 1 天的表 Prdct：

-------------------- --- 
prod_id | price | eff_from_dt | eff_end_dt
------------------------------------------
123 | 30.99 | 2019-02-26 | Null 

现在想象一个新的记录出现在同一个 Prdct id (123) 的新价格 (55.99) 和新的 eff_frm_dt (2019-07-16)。所以现在应该插入新行，并且现有记录的 eff_end_dt 列需要更新为（新记录的 eff_frm_dt 值 - 1 天）。现在为出现在 eff_frm_dt 和现有记录的 eff_end_dt 之间的每个月创建新记录（从现有记录）。

假设新记录（今天）如下：

prod_id | price | eff_from_dt | eff_end_dt
------------------------------------------
123 | 55.99 | 2019-07-16 | Null 

Target expected:
-----------------

prod_id | price | eff_from_dt | eff_end_dt
------------------------------------------
123 | 30.99 | 2019-02-26 | 2019-07-15  <<--- existing record updated with (eff_frm_dt of new record - 1 )
123 | 30.99 | 2019-03-01 | 2019-03-31  <<--- record generated from existing row for March 2019
123 | 30.99 | 2019-04-01 | 2019-04-30  <<--- record generated from existing row April 2019
123 | 30.99 | 2019-05-01 | 2019-05-31  <<--- record generated from existing row May 2019
123 | 30.99 | 2019-06-01 | 2019-06-30  <<--- record generated from existing row  June 2019
123 | 55.99 | 2019-02-26 | Null        <<--- Today's new record inserted with new eff_frm_dt 

在上面的示例中，每个月创建 4 个历史行，位于现有行的 eff_frm_dt 和 eff_end_dt 之间。另外，请注意，新的（今天的）记录也插入到底部，eff_end_dt 中的 Null 表示它自己是有效记录。

标签： sqlapache-spark-sqlhiveql