用日期表填补日期空白

本文介绍了用日期表填补日期空白的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两张桌子.

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

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 时，客户字段为 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).