使用生产日期表添加缺失行

Question

这个问题与这篇SO帖子有关。

不使用递归CTE，如何使用DimDates表添加缺失数据（通过日期确定为缺失）？

我有以下两个表：

create table the_table 
(
  [Date] datetime,
  Category2 varchar(10),
  Amount INT
)
insert into the_table
values
( '01 jan 2012', 'xx', 10),
( '03 jan 2012', 'yy', 50)


create table DimDate 
(
  [Date] datetime
)
insert into DimDate
values
( '01 jan 2012'),
( '02 jan 2012'),
( '03 jan 2012'),
( '04 jan 2012')

以下是我希望获得的结果。我没有使用递归CTE，因为我错误地认为使用我们的仓库DimDate表会更容易：

好的 - 如果我错了，请指出以下可能的解决方案的漏洞：

select

  coalesce(x.[Date], y.[Date]) AS Date ,
  coalesce(x.Category2, y.Category2) AS Category2 ,
  isnull(Amount,0) as Amount
from the_table x
full outer join 
(
select 
    d.Date
    , t.Category2
from 
        the_table t
        cross join DimDate d 
) y
    on
    x.Category2 = y.Category2
    and 
    x.Date = y.Date

我最终得到了这个结果。这是标记答案和Aaron帖子中的cte的组合：

;WITH 
    Dates_cte ([Date]) AS
            (
            SELECT [Date] = DayMarker 
            FROM WHData.dbo.vw_DimDate x
            WHERE
                    x.DayMarker >= (SELECT MIN([Date]) FROM #Data1 WHERE Period = 'Daily') AND
                    x.DayMarker <= GETDATE()
            )   
    ,Categories ([Operator], [Market], [Product], [Measure]) AS 
                ( 
                SELECT DISTINCT 
                        [Operator]
                        , [Market]
                        , [Product]
                        , [Measure] 
                FROM #Data1 
                WHERE [Period] = 'Daily'
                ) 
INSERT INTO #Data1 
    SELECT 
         c.[Operator]
        , c.[Market]
        , c.[Product]
        , [Period] = CONVERT(VARCHAR(100), 'Daily')
        , d.[Date]  
        , c.[Measure]   
        , 0 
    FROM Dates_cte d CROSS JOIN Categories c
    WHERE NOT EXISTS 
            ( 
            SELECT * 
            FROM #Data1 AS T 
            WHERE 
                    t.[Period] = 'Daily' AND
                    t.[Operator] = c.[Operator] AND 
                    t.[Market] = c.[Market] AND 
                    t.[Product] = c.[Product] AND 
                    t.[Measure] = c.[Measure] AND 
                    t.[Date] = d.[Date] 
            ) 

- whytheq

---

CROSS JOIN做同样的事情吗？- 我添加它的原因是为了获得笛卡尔积。 - whytheq

不，我从来没有使用过“CROSS JOIN”；所以你的评论是正确的 - 我会编辑原始帖子。 - whytheq

在你的问题中，你想要“添加”缺失的行。从你的编辑中我猜测你只是指当你“SELECT”而不是插入缺失的行时？如果是这样，那么这是一个更简单的模板：SELECT dates.date, categories.category, ISNULL(data.val, 0) FROM dates CROSS JOIN categories LEFT JOIN data ON data.date = dates.date AND data.category = categories.category [并不是所有情况都需要使用FULL OUTER JOIN.] - MatBailie

在你最近的编辑中，t CROSS JOIN d 将产生一个巨大的表格，远不止是“所有日期和类别的组合”。你需要换个思路来得到一个好的查询：先创建一个包含所有你应该有的组合的模板，然后将你的数据左连接到它上面。 - MatBailie

@Dems - 那么这是否也适用于我标记为答案的帖子；它包括一个 CROSS JOIN？ - whytheq

不，被接受的答案是“categories CROSS JOIN dates”。而你的答案是“all_data CROSS JOIN dates”。跨连接并不是问题所在，问题在于交叉连接的内容是什么。 - MatBailie

Answer 1

使用INSERT INTO ... SELECT FROM DimDate CROSS JOIN categories WHERE NOT EXISTS ...语句。

尝试这个：

INSERT INTO the_table
([Date], Category2, Amount)
SELECT [Date], category2, 0
FROM DimDate
CROSS JOIN
(
    SELECT DISTINCT category2 FROM the_table
) AS categories
WHERE NOT EXISTS
(
    SELECT *
    FROM thetable AS T
    WHERE T.category2 = categories.Category2
    AND T.[Date] = DimDate.[Date]
)

在线演示链接: ideone

如果您正在创建数据仓库，我建议您将类别放入维度表中。

- Mark Byers

---

+1：特别是对于建议类别维度表。 - MatBailie

可爱的脚本。我只是使用WH - 而不是创建它。 - whytheq

马克 - 在OP的评论中，Dems提到我可能使用CROSS JOIN的解决方案是不效率的，因为数据会爆炸....你使用的CROSS JOIN有何不同之处...它是否同样低效？ - whytheq

我们有大量的维度表 - 在这个特定的查询中，我已经从不同的维度中提取了几个类别。 - whytheq

您可能需要向“categories”派生表添加一个where子句。您需要告诉我们：您将如何仅识别您感兴趣的“the_table”中的类别？ - Aaron Bertrand

@AaronBertrand; 我已经在 OP 中添加了我的生产查询...我对所有类别都感兴趣。 - whytheq

Answer 2

这是一段明显的糟糕伪代码，展示了一个可能的解决方案。

insert into table1
    select  from table2 
        where not exists (select from table1 where table1.date = table2.date)

这假设你正在尝试将数据添加到表1中。

如果你只是想要它在内存中，

select * from table 1
union 
select * from table 2 where not exists (select from table1 where table1.date = table2.date)

或者只是一个外连接

- Jason Coyne

---

@aF。与Mark Byers的答案相比，这显然是一个很好的逻辑开端。 - whytheq

@whytheq 是的，我没有说过任何不同的话 :) - aF.

Answer 3

;WITH cat AS (SELECT Category2 FROM the_table GROUP BY Category2)
INSERT the_table([Date], Category2, Amount)
SELECT d.[Date], cat.Category2, 0
FROM DimDate AS d CROSS JOIN cat
LEFT OUTER JOIN the_table AS t
ON d.[Date] = t.[Date]
AND cat.Category2 = t.Category2
WHERE t.[Date] IS NULL;

- Aaron Bertrand

---

离题：刚刚看了这篇文章，真希望三年前就读过它！文章链接：https://sqlblog.org/2008/10/30/my-stored-procedure-best-practices-checklist - whytheq

Answer 4

步骤1，插入缺失的日期：

select [Date], '', 0 from DimDate
where [Date] not in (select [Date] from the_table)

第二步，更新Categoriy2列：

update the_table
set Category2 =
     (select aux.Category from the_table aux where t.Date = 
        (select max(t.Date) from the_table t
         where t.Category2 <> '' and t.Date < aux.Date)

- aF.