用日期表填写日期间隔 [英] Fill In The Date Gaps With Date Table

问题描述

我有两个桌子.

包含客户和日期的订单表. 数据仓库中的日期维度表.

An orders table with customer, and date. A date dimension table from a data warehouse.

orders表并不包含给定月份中每个日期的活动，但是我需要返回一个填充日期和客户缺口的结果集.

The orders table does not contain activity for every date in a given month, but I need to return a result set that fills in the gaps with date and customer.

例如，我需要这个:

Customer   Date
===============================
Cust1       1/15/2012   
Cust1       1/18/2012
Cust2       1/5/2012
Cust2       1/8/2012

外观如下:

Customer   Date
============================
Cust1       1/15/2012   
Cust1       1/16/2012   
Cust1       1/17/2012       
Cust1       1/18/2012
Cust2       1/5/2012
Cust2       1/6/2012
Cust2       1/7/2012
Cust2       1/8/2012

这似乎是一个左外部联接，但它没有返回预期的结果. 这就是我正在使用的内容，但这并没有按预期返回日期表中的每个日期.

This seems like a left outer join, but it is not returning the expected results. Here is what I am using, but this is not returning every date from the date table as expected.

SELECT o.customer, 
       d.fulldate
FROM   datetable d 
       LEFT OUTER JOIN orders o 
                    ON d.fulldate = o.orderdate 
WHERE  d.calendaryear IN ( 2012 ); 

推荐答案

问题是您需要所有日期的所有客户.当您执行left outer join时，customer字段将为NULL.

The problem is that you need all customers for all dates. When you do the left outer join, you are getting NULL for the customer field.

以下通过cross join输入客户名称和日期来设置驱动程序表:

The following sets up a driver table by cross joining the customer names and dates:

SELECT driver.customer, driver.fulldate, o.amount 
FROM   (select d.fulldate, customer
        from datetable d cross join
             (select customer
              from orders
              where year(orderdate) in (2012)
             ) o
        where d.calendaryear IN ( 2012 )
       ) driver LEFT OUTER JOIN
       orders o 
       ON driver.fulldate = o.orderdate and
          driver.customer = o.customer;

请注意，此版本假定calendaryear与year(orderdate)相同.

Note that this version assumes that calendaryear is the same as year(orderdate).

这篇关于用日期表填写日期间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！