时间戳

首页 > 解决方案 > 向每个位置添加缺失的日期 - MySQL 5.7

mysql - 向每个位置添加缺失的日期 - MySQL 5.7

问题描述

所以我的 SQL 输出是：

DUNF    2021-04-01  18
DUNF    2021-04-02  17
DUNF    2021-04-03  7
DUNF    2021-04-04  10
DUNF    2021-04-05  18
DUNF    2021-04-06  20
DUNF    2021-04-07  17
DUNF    2021-04-08  14
LEEDS   2021-04-01  4
LEEDS   2021-04-02  4
LEEDS   2021-04-03  5
LEEDS   2021-04-05  9
LEEDS   2021-04-06  3
LEEDS   2021-04-07  1
LEEDS   2021-04-08  3
etc.

但是我需要每天都有一个条目，即使最后一个字段是一个卷（有时那天在那个位置什么都没有发生）。

我什么都试过了，但我的 SQL 5.7 知识很薄弱。我可以生成一个日历表，但它不能填补每个站点的空白（您可以看到 Leeds 缺了一天，但还有其他站点也缺了日期）。

到目前为止我使用的代码是：

SELECT location, begin_date, count(*) as volume
  FROM abs_raw_data
  WHERE begin_date >= (SELECT date_sub(max(begin_date), interval 7 day) from abs_raw_data)
  GROUP BY location, begin_date
  ORDER BY location asc, begin_date asc

我该怎么做呢？

标签： mysqldatejoincalendargaps-in-data

解决方案

看来你需要

SELECT locations.location, 
       calendar.begin_date, 
       COALESCE(COUNT(abs_raw_data.location), 0) as volume
FROM ( {calendar generating subquery} ) AS calendar
CROSS JOIN ( SELECT DISTINCT location
             FROM abs_raw_data ) locations
CROSS JOIN ( SELECT MAX(begin_date) - INTERVAL 7 DAY AS begin_date 
             FROM abs_raw_data ) maxdate
LEFT JOIN abs_raw_data ON calendar.begin_date = abs_raw_data.begin_date 
                      AND locations.location = abs_raw_data.location
                      AND abs_raw_data.begin_date >= maxdate.begin_date 
GROUP BY locations.location, calendar.begin_date
ORDER BY locations.location ASC, calendar.begin_date ASC

日历生成子查询可以是，例如，

SELECT abs_raw_data.begin_date - INTERVAL nums.n DAY
FROM ( SELECT MAX(begin_date) begin_date
       FROM abs_raw_data) abs_raw_data 
CROSS JOIN ( SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
             UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 ) nums
-- https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=49ef261eaf01956973d4ce99f6203a91

推荐阅读