用日期表填补日期空白 [英] 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
时,客户字段为 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 join
ing 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屋!