是否有SQL逻辑可以沿维度减少类型2表

Question

我有一个缓慢变化的类型2价格变化表，我需要缩小它的大小以提高性能。通常，即使没有发生价格变化(当其他维度字段发生变化时)，也会向表中写入行，结果是，对于任何产品，如果只包括价格变化，则表的大小可能是所需大小的3-10倍。

我想压缩表格，使其只包含每个价格的第一个生效日期和最后一个到期日期，直到价格发生变化，也可以

• 处理相同价格的未知行数
• 处理恢复旧价格的产品

例如，如果我有以下原始数据：

价格12012085858580

产品		价格生效日期	价格到期日期
12346	6/12/22
12346	9/120/18	120
12346	2018年9月11日	18年11月29日
12346	2018年11月30日	2018年12月6日	120
12346	12/7/18	12/19/18
12346	12/20/18
12346	1/2/19	85
12346	20/20/19	2/20/19	120
123456	2019年2月21日	2019年3月19日	85
12346	19年3月20日	2019年5月22日
12346	19年5月23日	85
12346	10/11/19		6/19/19
12346	6/20

您可以首先找到价格不变的区间，然后按这些区间分组：

with to_r as (select row_number() over (order by (select 1)) r, t.* from data_table t),
to_group as (select t.*, (select sum(t1.r < t.r and t1.price != t.price) from to_r t1) c from to_r t)
select t.product, min(t.effective), max(t.expiration), max(t.price) from to_group t group by t.c order by t.r;

输出：

价格12/6/1202/19/19120//tr>8512/31/99>td>80//tr>

产品	价格生效日期	价格到期日期
12346	6/12/22
12346	12/7/18	85
12346	20/20/19
12346	2019年2月21日	19年10月10日
12346	10/11/19

这是一种间隙和孤岛问题。我建议重新构建数据，将其保存在临时表中，然后重新加载现有表。

重建数据的代码是：

select product, price, min(effective_date), max(expiration_date)
from (select t.*,
sum(case when prev_expiration_date = effective_date - interval '1 day' then 0 else 1 end) over (partition by product order by effective_date) as grp
from (select t.*,
lag(expiration_date) over (partition by product, price order by effective_date) as prev_expiration_date
from t
) t
) t
group by product, price, grp;

请注意，日期运算的逻辑因数据库而异。

使用select into、create table as或数据库支持的任何内容，将此结果保存到临时表temp_t或其他任何内容中。

然后清空当前表格并重新加载：

truncate table t;
insert into t
select product, price, effective_date, expiration_date
from temp_t;

注：

• 在使用truncate_table之前验证数据
• 如果存在具有默认值的触发器或列，则可能需要小心

听起来你在要求一个时态模式？在给定的日期，你在哪里可以知道资产的价格？

这是用两张表来完成的；price_current和price_history。

回收创建'2015-04-18'

price_id	item_id	price
1	1	100

您想要的是一个版本控制系统。许多RDBMS平台实现了对这种开箱即用的支持(这是SQL标准)，根据您的需求，这种支持可能是合适的。

您没有标记特定的平台，因此不可能针对您的具体情况。我经常在MSSqlServer中使用系统版本控制的概念，您可以在这里实现它：

假定模式"；CCD_ 5"；存在，

alter table dbo.MyTable add
ValidFrom datetime2 generated always as row start hidden constraint DF_MyTableSysStart default sysutcdatetime(),
ValidTo datetime2 generated always as row end hidden constraint DF_MyTableSysEnd default convert(datetime2, '9999-12-31 23:59:59.9999999'),
period for system_time (ValidFrom, ValidTo);
end
alter table MyTable set (system_versioning = on (history_table = History.MyTable));
create clustered index ix_MyTable on History.MyTable (ValidTo, ValidFrom) with (data_compression = page,drop_existing=on) on History;

存在许多语法扩展以帮助查询时间数据，例如查找某个时间点的历史数据。

或者，要使用单个表但处理重复，可以创建instead of触发器。

这里的想法是，触发器在插入之前拦截数据，在那里您可以检查值是否与上一个值不同，并根据需要丢弃或插入。

类似于的东西

WITH keeps AS
(
SELECT p.product_id, p.effective, p.expires, p.price, CASE WHEN EXISTS(SELECT 1 FROM prices p1 WHERE p1.effective = DATEADD(DAY, p.exires, 1) AND p1.price <> p.price) THEN 1 ELSE 0 END AS has_after, CASE WHEN EXISTS(SELECT 1 FROM prices p1 WHERE p1.expires = DATEADD(DAY, p.effective, -1) AND p1.price <> p.price) THEN 1 ELSE 0 END AS has_before
FROM prices p
)
SELECT * FROM keeps
WHERE has_after = 1
OR has_before = 1
UNION ALL
SELECT p.product_id, p.effective, p.exires, p.price
FROM prices p
WHERE p.effective = (SELECT MIN(effective) FROM prices p1 WHERE p1.product_id = p.product_id)

它在做什么：

查找存在另一个条目的所有条目，该条目的生效日期为上一个条目到期日期+1，并且该新条目的价格不同。这给了我们所有价格的实际变化。但我们错过了第一个价格条目，所以我们只是将其包含在结果中。

例如：

到期>>has_after9/19/120011/8/120<120><120>00<1>12/19/18>0>0<1>120

product_id	有效	价格	1has_befor
12346	6/12/22	0
12346	9/10/20		0
12346	2018年9月11日	18年11月29日
12346	2018年11月30日	18年12月6日	120	0
12346	12/7/18	85
12346	12/20/18	85	0
12346	2/1/19	2/19	85
12346	2/20/19	20/20/120
12346	21/21/19	3/19/19			1	0