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

问题描述

我有两张桌子.

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

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

订单表不包含给定月份中每个日期的活动，但我需要返回一个结果集，以填补日期和客户之间的空白.

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屋！