Início / dba / Perguntas / 184639
Accepted
user45867
Asked: 2017-08-30 14:20:09 +0800 CST

Junte a tabela de fatos ao SCD Tipo 2 ... como escrever a consulta? (Servidor SQL)

  • 772

Eu não consigo encontrar este simples fato em qualquer lugar.

Eu tenho uma tabela de fatos como tal no SQL Server.

fact_picked
Emp_Name     Date     Apples_Picked
John        May 1 17    100

E uma tabela de dimensão tipo 2 como esta

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

Como juntaria o fato à tabela de dimensões para saber qual "empresa" colheu as 100 maçãs?

Nesse caso, logicamente, dados os dados, é 'Da_Big_Apple' ... desde que John começou a trabalhar lá em 17 de março até outubro de 2017, em que sua tarefa de colheita de maçãs ocorreu.

Como faço para fazer uma junção dessas tabelas? (assumindo milhares de registros).

Eu só fico preso. Eu sei que deveria fazer algo como

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 .... ???

Acho que não tenho certeza. Acho que posso usar uma visão para converter o tipo 2 em um tipo 4 (com uma data de término na tabela). Então é mais simples. Certifique-se de que a data do fato seja maior que a data de início, mas antes da data de término. Mas essa é a solução mais elegante?

sql-server join

1 respostas

  1. Best Answer

    Configure alguns dados de exemplo:

    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');

    Solução proposta:

    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
    • subconsulta é usada para encontrar o registro dim_company com a data mais recente (ou seja, max()) Effective_Since que é menor que um determinado fact_picked.[Date]
    • use o resultado da subconsulta para determinar com qual registro dim_company se unir na consulta de nível superior

    Para ter certeza de que left joinestá funcionando corretamente, suponha que temos apenas as seguintes linhas na tabela dim_company:

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

    Execute a consulta proposta novamente:

    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

    E aqui está um dbfiddle para o acima.

    • 2

relate perguntas