加入日历表中的缺失日期 [英] Joining missing dates from calendar table

问题描述

我有一个包含信息和日期的表，其中包含一些缺失的信息，因此我想将该表与日历表连接起来以填充缺失的日期，并将同一行另一列中的值设置为null.这是一个例子:

I have a table with information and dates, which have some missing ones, so I want to join that table with a calendar table to fill missing dates and set values in another column in the same row to null. This is an example:

Steps | Date
 10   | 2018-04-30
 20   | 2018-04-28

它想要执行以下操作:

Steps | Date
 10   | 2018-04-30
 null | 2018-04-29
 20   | 2018-04-28

这是我尝试过的(真正的查询，因此您可以指出我做错了什么):

This is what I tried (real query, so you can point out if I'm doing something wrong):

SELECT sum(steps), date(from_unixtime(u.in_date)) as stepdate
    FROM userdata u
        RIGHT JOIN
    time_dimension td
    ON date(from_unixtime(u.in_date)) = td.db_date
    AND user_id = 8
    GROUP BY day(from_unixtime(in_date))
    ORDER BY stepdate DESC;

我希望此查询可以执行我想要的操作，但不是.表time_dimension及其列db_date具有所有日期(范围从2017-01-01到2030-01-01)，这是我要加入的userdata的in_date列(该时间在unix_time中.)

I expected this query to do what I wanted, but it doesn't. The table time_dimension and its column db_date have all dates (ranging from 2017-01-01 to 2030-01-01), which is the one I'm trying to join userdata's in_date column (which is in unix_time).

我在SO中检查了以下问题:

I checked the following questions in SO:

• 加入日历表-5个工作日

针对重复项进行特别是，该问题是使用时间间隔和date_add与表进行比较.我正在使用日历表来代替它们.虽然相似，但我认为他们不会有相同的解决方案.

Edit, regarding the duplicate: That question in particular is using intervals and date_add to compare against their table. I am using a calendar table instead to join them. While similar, I don't think they won't have the same solution.

解决方案:感谢xQBert指出了错误:

Solution: Thanks to xQBert, who pointed out the mistake:

问题:在用户数据表和选择项上都具有分组依据，您基本上就忽略了时间维度数据.用户数据权利中没有2018-4-29日期(对于用户8)，请修正选择&根据时间维度数据进行分组并解决问题.

PROBLEM: Having the group by be on the userdata table as well as the select, you're basically ignoring the time dimension data. There is no 2018-4-29 date in Userdata right (for user 8) Fix the select & group by to source from time dimension data and problem solved.

因此，我将GROUP BY day(from_unixtime(in_date))更改为GROUP BY td.db_date.

推荐答案

您需要left join而不是right join，或者您也可以更改表

You need left join rather than right join or you may also change the position of tables

SELECT sum(steps), date(from_unixtime(td.db_date)) as stepdate
FROM time_dimension td  
LEFT JOIN userdata u
     ON date(from_unixtime(u.in_date)) = td.db_date 
WHERE user_id = 8
GROUP BY date(from_unixtime(td.db_date))
ORDER BY stepdate DESC;

但是，这假定time_dimension表被视为日历表.

However, this assumes time_dimension table treating as calender table.

这篇关于加入日历表中的缺失日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！