MySQL查询不存在的日期数据

Question

表定义如下：

create table os_day_count
(
    stat_date     varchar(10) not null comment '统计日期',
    os_stat_count int         null comment 'os类型累计用户数',
    primary key (stat_date)
)

表里的数据不是连续的！！没有某个stat_date日期的数据则表示该天没有新增用户，os_stat_count表示的是累计用户数。

现在想要查询【连续】日期的用户数，即实现

select stat_date, os_stat_count from os_day_count where stat_date in ('2023-12-16','2023-12-17','2023-12-18');
// 没有2023-12-18的数据，则取2023-12-17；没有2023-12-17的数据，则取2023-12-16；

最后返回的数据应该有3行，分别是2023-12-16、2023-12-17、2023-12-18，而且因为2023-12-17和2023-12-18没有新增用户。故而查询出来的三行数据结果是一模一样的。

问题：

1. SQL怎么写？
2. 如果是MySQL 8版本，有高级函数，实现起来应该方案多一些。SQL该怎么写？
3. 如果是低于MySQL 8的版本，比如5.7版本，SQL又该怎么写？

Answer 1

该回答引用GitHub Copilot：1. MySQL 8版本的SQL查询：

在MySQL 8.0及以上版本中，可以使用窗口函数LAG来获取前一天的os_stat_count值。如果某一天没有数据，那么就使用前一天的os_stat_count值。以下是一个可能的SQL查询：

WITH dates AS (
    SELECT '2023-12-16' AS stat_date
    UNION ALL SELECT '2023-12-17'
    UNION ALL SELECT '2023-12-18'
),
cte AS (
    SELECT 
        dates.stat_date,
        IFNULL(os_day_count.os_stat_count, LAG(os_day_count.os_stat_count) OVER (ORDER BY dates.stat_date)) AS os_stat_count
    FROM 
        dates
    LEFT JOIN 
        os_day_count ON dates.stat_date = os_day_count.stat_date
)
SELECT 
    stat_date,
    IFNULL(os_stat_count, (SELECT os_stat_count FROM cte WHERE os_stat_count IS NOT NULL ORDER BY stat_date DESC LIMIT 1)) AS os_stat_count
FROM 
    cte
ORDER BY 
    stat_date;

在这个查询中，我们首先创建了一个名为dates的公共表表达式（CTE），它包含了我们想要查询的所有日期。然后，我们在cte中使用LAG函数来获取前一天的os_stat_count值。最后，我们从cte中选择所有的日期和对应的os_stat_count值，如果某一天的os_stat_count值为NULL，那么就使用前一天的os_stat_count值。

1. MySQL 5.7版本的SQL查询：

在MySQL 5.7及以下版本中，没有窗口函数，因此我们需要使用其他方法来获取前一天的os_stat_count值。以下是一个可能的SQL查询：

SELECT 
    dates.stat_date,
    (SELECT os_stat_count FROM os_day_count WHERE stat_date <= dates.stat_date ORDER BY stat_date DESC LIMIT 1) AS os_stat_count
FROM 
    (SELECT '2023-12-16' AS stat_date
    UNION ALL SELECT '2023-12-17'
    UNION ALL SELECT '2023-12-18') AS dates
ORDER BY 
    dates.stat_date;

在这个查询中，我们首先创建了一个名为dates的子查询，它包含了我们想要查询的所有日期。然后，对于每一天，我们都从os_day_count表中选择最近的os_stat_count值。