sql - 使用 postgresql 设计一个渐变维度类型 2 脚本
问题描述
假设我有以下目标表:
CREATE TABLE DimCustomer (
CustomerKey serial PRIMARY KEY,
CustomerNum int NOT NULL,
CustomerName varchar(25) NOT NULL,
Planet varchar(25) NOT NULL,
RowIsCurrent char(1) NOT NULL DEFAULT 'Y',
RowStartDate date NOT NULL DEFAULT CURRENT_TIMESTAMP,
RowEndDate date NOT NULL DEFAULT '12/31/9999'
);
INSERT INTO DimCustomer
(CustomerNum, CustomerName, Planet, RowStartDate)
VALUES (101,'Anakin Skywalker', 'Tatooine', CURRENT_TIMESTAMP - INTERVAL '101 days'),
(102,'Yoda', 'Coruscant', CURRENT_TIMESTAMP - INTERVAL '100 days'),
(103,'Obi-Wan Kenobi', 'Coruscant', CURRENT_TIMESTAMP - INTERVAL '100 days')
我有一个以下临时表:
CREATE TABLE Staging_DimCustomer
(
CustomerNum int NOT NULL,
CustomerName varchar(25) NOT NULL,
Planet varchar(25) NOT NULL,
ChangeDate date NOT NULL DEFAULT CURRENT_TIMESTAMP,
RankNo int NOT NULL DEFAULT 1
)
INSERT INTO Staging_DimCustomer(CustomerNum, CustomerName, Planet, ChangeDate)
VALUES
(103,'Ben Kenobi', 'Coruscant', CURRENT_TIMESTAMP - INTERVAL '99 days')
在临时表中,看起来'Obi-Wan Kenobi'
( customernum 103
) 将他的名字更改为
'Ben Kenobi'
。我想创建一个实现 scd 类型 2 并产生以下结果的脚本(慢慢改变维度类型 2):
以下是我的尝试:
INSERT INTO DimCustomer (
CustomerNum, CustomerName, Planet, RowIsCurrent, RowStartDate, RowEndDate
)
select CustomerNum, CustomerName, Planet, 'Y', ChangeDate, '12/31/9999'
from Staging_DimCustomer
ON CONFLICT (CustomerNum) and RowIsCurrent = 'Y'
DO UPDATE SET
CustomerName = EXCLUDED.CustomerName,
Planet = EXCLUDED.Planet,
RowIsCurrent = 'N',
RowEndDate = EXCLUDED.ChangeDate
我不知道如何查找已更改的值,更新现有行以将其淘汰,然后插入带有rowiscurrent = 'Y'
标志的新行。我正在尝试根据这篇 sql server 文章
http://www.made2mentor.com/2013/08/how-to-load-slowly-sharing-dimensions-using-t-sql-merge/为我的解决方案建模。
解决方案
假设更改都在最新行上,那么您可以更新当前行然后插入:
with u as (
update dimCustomer c
set RowIsCurrent = 'N',
RowEndDate = sc.ChangeDate
from Staging_DimCustomer sc
where sc.CustomerNum = c.CustomerNum and
c.RowIsCurrent = 'Y'
)
insert into dimCustomer (CustomerNum, CustomerName, Planet, RowIsCurrent, RowStartDate, RowEndDate
)
select CustomerNum, CustomerName, Planet, 'Y', ChangeDate, '9999-12-31'::date
from Staging_DimCustomer sc;
这假定更改发生在最新记录上。实施历史性更改相当棘手,我猜这没有必要。
请注意,您可能需要额外检查插入的行实际上与当前行不同。
编辑:
如果要避免对已存在的行进行更改,可以执行以下操作:
with sc as (
select *
from Staging_DimCustomer
where not exists (select 1
from DimCustomer c
where c.CustomerNum = sc.CustomerNum and
c.CustomerName = sc.CustomerName and
. . . -- whatever other columns you want to check
)
),
u as (
update dimCustomer c
set RowIsCurrent = 'N',
RowEndDate = sc.ChangeDate
from sc
where sc.CustomerNum = c.CustomerNum and
c.RowIsCurrent = 'Y'
)
insert into dimCustomer (CustomerNum, CustomerName, Planet, RowIsCurrent, RowStartDate, RowEndDate
)
select CustomerNum, CustomerName, Planet, 'Y', ChangeDate, '9999-12-31'::date
from sc;
推荐阅读
- kdb - KDB - 如果不同的 col = 某个值,则将 col 乘以 -1
- python - 从子记录 Odoo V8 更新父级
- php - PHP:使用 lighttpd 获取用户的 IP 地址
- ruby-on-rails - 需要帮助重构以使用 .where 子句 - ruby on rails
- powershell - 我将如何编写一种方法来遍历 bitBucket 中的每个发布分支并将它们分隔在本地驱动器中自己的文件夹中?
- jquery - /?wc-ajax=checkout 504 错误但仍在付款
- laravel - Laravel Sanctum:机器对机器
- flutter - Flutter - 如何使用 ChangeNotifier 正确获取数据
- sql - 选择查询不返回任何记录
- r - gganimate “顺序错误(ind):参数 1 不是向量”