主页 / dba / 问题 / 184639
Accepted
user45867
Asked: 2017-08-30 14:20:09 +0800 CST

将 Fact 表加入 SCD 类型 2 ...如何编写查询？（SQL 服务器）

  • 772

我似乎在任何地方都找不到这个简单的事实。

我在 SQL Server 中有一个类似的事实表。

fact_picked
Emp_Name     Date     Apples_Picked
John        May 1 17    100

还有一个像这样的二维表

dim_company
Emp_Name   Company     Effective_Since
John      Blue_Apples      June 1 2015
John      Apple_N_Stuff    Jan 1 2016
John      Da_Big_Apple     March 17 2017
John      Big_Tech         October 20 2017

我如何将事实加入维度表，以便知道哪个“公司”挑选了 100 个苹果？

在这种情况下，从逻辑上讲，鉴于数据，它是“Da_Big_Apple”……因为 John 于 3 月 17 日开始在那里工作，直到 2017 年 10 月，他的苹果采摘任务就发生了。

我如何连接这些表呢？（假设有数千条记录）。

我只是卡住了。我知道我应该做类似的事情

Select fp.emp_name, fp.date, fp.apples_picked
from fact_picked fp
left join dim_company dc
on fp.emp_name = dc.emp_name
and fp.date > dc.effective_since .... ???

我想我不太确定。我想我可以使用视图将类型 2 转换为类型 4（表中有结束日期）。然后就更简单了。确保事实日期大于开始日期，但在结束日期之前。但这是最优雅的解决方案吗？

sql-server join

1 个回答

  1. Best Answer

    设置一些示例数据：

    create table fact_picked
(Emp_Name         varchar(30)
,[Date]           date
,Apples_Picked    int);

insert into fact_picked values
('John','10/17/15',175),
('John','05/01/17',100);

create table dim_company
(Emp_Name         varchar(30)
,Company          varchar(30)
,Effective_Since  date);

insert into dim_company values
('John','Blue_Apples',  '06/01/2015'),
('John','Apple_N_Stuff','01/01/2016'),
('John','Da_Big_Apple', '03/17/2017'),
('John','Big_Tech',     '10/20/2017');

    建议的解决方案：

    select fp.Emp_Name, 
       fp.[Date],
       fp.Apples_Picked,
       dc.Company,
       dc.Effective_Since

from   fact_picked fp

left
join   dim_company dc
on     dc.Emp_Name        = fp.Emp_Name
and    dc.Effective_Since = (select max(Effective_Since)
                             from   dim_company dc2
                             where  dc2.Emp_Name         = fp.Emp_Name
                             and    dc2.Effective_Since <= fp.[Date])

order by 1,2,5;

Emp_Name | Date                | Apples_Picked | Company      | Effective_Since    
-------- | ------------------- | ------------- | ------------ | -------------------
John     | 17/10/2015 00:00:00 |           175 | Blue_Apples  | 01/06/2015 00:00:00
John     | 01/05/2017 00:00:00 |           100 | Da_Big_Apple | 17/03/2017 00:00:00
    • 子查询用于查找具有最新（即 max()）Effective_Since 日期小于给定 fact_picked.[Date] 的 dim_company 记录
    • 使用子查询结果来确定在顶级查询中加入哪个 dim_company 记录

    为了确保left join正常工作，假设我们在 dim_company 表中只有以下行：

    truncate table dim_company;
insert into dim_company values
('John','Da_Big_Apple','03/17/2017'),
('John','Big_Tech',    '10/20/2017');

    再次运行建议的查询：

    select fp.Emp_Name, 
       fp.[Date],
       fp.Apples_Picked,
       dc.Company,
       dc.Effective_Since

from   fact_picked fp

left
join   dim_company dc
on     dc.Emp_Name        = fp.Emp_Name
and    dc.Effective_Since = (select max(Effective_Since)
                             from   dim_company dc2
                             where  dc2.Emp_Name         = fp.Emp_Name
                             and    dc2.Effective_Since <= fp.[Date])

order by 1,2,5;

Emp_Name | Date                | Apples_Picked | Company      | Effective_Since    
-------- | ------------------- | ------------- | ------------ | -------------------
John     | 17/10/2015 00:00:00 |           175 | null         | null               
John     | 01/05/2017 00:00:00 |           100 | Da_Big_Apple | 17/03/2017 00:00:00

    这是上面的一个dbfiddle。

    • 2

相关问题