使用日历表填补日期范围查询中的缺失空白 [英] Fill missing gaps in date range query using calendar table

问题描述

我正在使用以下查询在某个日期范围内拉回产品总价，但是在没有销售的日子，我仍然希望显示日期，但其值为0.

I'm using the below query to pull back the total product price for a date range, however on the days that no sales where made, i'd like to still show the date but with a value of 0.

我创建的日历表具有较大的日期范围，但无法找到最佳的日历处理方法，我所做的一切都错过了没有销售的日子.

I've created a calendar table with a large date range, but haven't been able to work out the best way to do it, everything i'm doing just misses out the days with no sales.

以下是我正在使用的查询(没有日历表):

Below is the query i'm using (Without the calendar table):

SELECT DATE_FORMAT(b.purchase_date, '%d %b %Y') as date, sum(price) as price 
    FROM order_products a 
    INNER JOIN order_saved b 
         ON a.order_id = b.id 
    WHERE b.purchase_date
         BETWEEN '2011-09-16 23:59' AND '2011-10-16 23:59' 
    AND b.status > 2
    AND a.usr_id = 'XXXX'
    GROUP BY DAY(b.purchase_date)
    ORDER BY b.purchase_date ASC

我尝试过的日历表仅包含从2010-01-01到2014-12-30的日期范围列表(calendar.date是table.row)

The calendar table i've tried with just contains a list of date ranges starting from 2010-01-01 up to 2014-12-30 (calendar.date is the table.row)

任何帮助都会很棒.

谢谢！

推荐答案

您需要在日历表上进行外部联接.像

You need an outer join on the calendar table. Something like

SELECT c.cal_date, coalesce(t1.price, 0) as total
FROM calendar c
LEFT JOIN (SELECT b.purchase_date::date, sum(price) as price 
           FROM order_products a 
           INNER JOIN order_saved b 
                   ON a.order_id = b.id 
                   AND b.purchase_date BETWEEN '2011-09-16 23:59' AND '2011-10-16 23:59' 
                   AND b.status > 2
                   AND a.usr_id = 'XXXX'
            GROUP BY b.purchase_date) t1 on t1.purchase_date = c.cal_date
WHERE cal_date BETWEEN '2011-09-16 23:59' AND '2011-10-16 23:59' 
ORDER BY c.cal_date

如果发布DDL(CREATE TABLE)语句和最小的INSERT语句，这些语句将为我们提供足够的数据来实际测试我们的答案，那么您将获得更好的答案. (编辑您的问题，并粘贴DDL和INSERT语句.)

You'll get better answers if you post the DDL (CREATE TABLE) statements, and minimal INSERT statements that give us enough data to actually test our answers. (Edit your question and paste the DDL and INSERT statements.)

这篇关于使用日历表填补日期范围查询中的缺失空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！