mysql - 在不使用日历表的情况下从非每日时间序列创建每日报告

Question

我有一个带有日期间隔(不是现有日期)的“time_serie”表，如下所示:

+-----+-------+-------------+------+
| id  | TS_ID | Date_publi  | Val  |
+-----+-------+-------------+------+
|   4 |     3 | 1996-11-01  | 50.5 |
|   5 |     3 | 1996-12-02  |   53 |
|   6 |     3 | 1997-01-02  | 55.2 |
 ...       ...  ..........      ... 

我想创建一个输出，用零或 #N/A 或以前的值替换缺失值，因此它看起来像:

1996-10-30 : #N/A
1996-10-31 : #N/A
1996-11-01 : 50.5
1996-11-02 : #N/A
1996-11-03 : #N/A
....          ...

为此，我考虑创建一个包含每个日期的“日历”表，然后调用右连接查询:

    SELECT calendar.dates AS DATE, IFNULL(time_serie.val, "#N/A") AS val
      FROM time_serie RIGHT JOIN calendar ON (DATE(time_serie.date_publi) = calendar.dates)
      WHERE (calendar.datefield BETWEEN start_date AND end_date)

但是，我宁愿不必创建和管理日历表。

有人知道如何在不使用日历表的情况下制作这样的报告吗？

Answer 1

您可以使用以下内容来完成它，而无需使用 Calendar 表。它不在 MySQL 中，但可以解决问题:

DECLARE @temp TABLE (Date DATETIME, Users NVARCHAR(40), Score INT) ---- Temporary table strucrure

INSERT INTO @temp (Date, Users, Score)
VALUES ---- Default values
    ('20120101', 'User1', 17),('20120201', 'User1', 19),
    ('20120401', 'User1', 15),('20120501', 'User1', 16),
    ('20120701', 'User1', 14),('20120801', 'User1', 15),
    ('20120901', 'User1', 15),('20121001', 'User1', 13),
    ('20121201', 'User1', 11),('20130101', 'User1', 10),
    ('20130201', 'User1', 15),('20130301', 'User1', 13),
    ('20130501', 'User1', 18),('20130601', 'User1', 14),
    ('20130801', 'User1', 15),('20130901', 'User1', 14),
    ('20161001', 'User1', 10),('20120601', 'User1', 10)

;WITH cte AS ---- Created a common table expression. You can say another query executed here
(
   SELECT Users, StartDate = MIN(Date), EndDate = MAX(Date) ---- Retrieved the max and min date from the table
   FROM @temp
   GROUP BY Users

   UNION ALL ---- Used 'UNION ALL' to combine all the dates missing and existing one

   SELECT Users, DATEADD(MONTH, 1, StartDate), EndDate ---- Checks the months that are missing
   FROM cte 
   WHERE StartDate <= EndDate
)

SELECT e.StartDate, t.Users, Score = ISNULL(t.Score, 0) ---- Finally checks the user scores for the existing and non-existing months using 'CTE'
FROM cte e
LEFT JOIN @temp t ON e.StartDate = t.Date AND e.Users = t.Users
ORDER BY e.StartDate, t.Users

如果没有特定月份的条目，以上返回 0 或 null。

请查看更多信息:Find Missing Dates - MySQL

更具体地说，这样就可以了:Find Missing Dates - MySQL 2