如何让MySQL返回指定日期范围的全量点击统计（含0值日期）

我有一个名为logclick的点击日志表，结构如下：

当前查询返回的结果仅包含有点击记录的日期：

我期望的结果是包含指定日期范围内的所有日期，无点击记录的日期显示0：

目前返回的结果仅包含有数据的记录，请问如何获取指定日期范围内的全量统计，无记录的日期显示0？

以下是我的PHP代码：

$date = date('Y-m-d');
$defaultdateto = new DateTime(date('Y-m-d', strtotime($date."+1 day")));
$defaultdateat = new DateTime(date('Y-m-d', strtotime($date."-7 day")));
$period = new DatePeriod($startdate, new DateInterval('P1D'), $enddate);

foreach ($period as $date) {
    $dates[] = $date->format("Y-m-d");
}

function loaddata($ads) {
    $sql = '';
    foreach($GLOBALS['dates'] as $ngay) {
        
        $query_parts[] = "(SELECT
        Count(logclick.id) AS vcount, date( logclick.ctimes ) AS vdate,
    logclick.adname
    FROM 
    logclick
    WHERE ctimes BETWEEN '" . $ngay . "' AND DATE_ADD('" . $ngay . "',INTERVAL 1 DAY) and adname = '" . $ads . "'
    GROUP BY
    logclick.adname,
    date( logclick.ctimes )
    ORDER BY
    vcount DESC)";
    }
    $sql .= implode(' UNION ALL ', $query_parts);  
    //  die($sql);
      $query = mysqli_query($GLOBALS['conn'], $sql);
    $result = array();
      if ($query && mysqli_num_rows($query) > 0) {
      
      while ($row = mysqli_fetch_array($query))
      {
          array_push($result, $row); 
      }
      mysqli_free_result($query);
    }
    return $result;
    }

$fulllist = loaddata("abc");

// return list click by date

方案一：PHP端补全数据（兼容性好） #

核心思路：先生成指定范围内的所有日期数组，再将数据库查询到的有数据的记录转为以日期为键的关联数组，最后遍历日期数组，补全无数据日期的统计值为0。同时优化原代码的SQL查询，避免多次UNION，改用预处理语句防止SQL注入：

$date = date('Y-m-d');
// 修正原代码变量名错误，明确日期范围
$startdate = new DateTime(date('Y-m-d', strtotime($date."-7 day")));
$enddate = new DateTime(date('Y-m-d', strtotime($date."+1 day")));
$period = new DatePeriod($startdate, new DateInterval('P1D'), $enddate);

$dates = [];
foreach ($period as $dateObj) {
    $dates[] = $dateObj->format("Y-m-d");
}

function loaddata($ads, $conn) {
    global $dates;
    // 一次性查询目标广告的所有点击数据
    $sql = "SELECT 
        DATE(logclick.ctimes) AS vdate,
        COUNT(logclick.id) AS vcount,
        logclick.adname
    FROM logclick
    WHERE logclick.adname = ? 
        AND DATE(logclick.ctimes) BETWEEN ? AND ?
    GROUP BY logclick.adname, DATE(logclick.ctimes)";
    
    // 使用预处理语句防SQL注入
    $stmt = mysqli_prepare($conn, $sql);
    $startDate = reset($dates);
    $endDate = end($dates);
    mysqli_stmt_bind_param($stmt, "sss", $ads, $startDate, $endDate);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    
    $dataMap = [];
    if ($result && mysqli_num_rows($result) > 0) {
        while ($row = mysqli_fetch_assoc($result)) {
            $dataMap[$row['vdate']] = [
                'date' => $row['vdate'],
                'name' => $row['adname'],
                'c' => $row['vcount']
            ];
        }
        mysqli_free_result($result);
    }
    mysqli_stmt_close($stmt);
    
    // 补全所有日期的统计数据
    $fullList = [];
    foreach ($dates as $date) {
        $fullList[] = $dataMap[$date] ?? [
            'date' => $date,
            'name' => $ads,
            'c' => 0
        ];
    }
    return $fullList;
}

// 传入数据库连接实例
$fulllist = loaddata("abc", $conn);

方案二：数据库端生成全量结果（效率更高，需MySQL 8.0+） #

利用MySQL的递归CTE生成指定日期范围的所有日期，再通过左连接logclick表统计点击数，直接返回全量结果：

WITH date_range AS (
    SELECT '2023-10-10' AS date
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_range
    WHERE date < '2023-10-15'
)
SELECT 
    dr.date,
    'abc' AS name,
    COUNT(lc.id) AS c
FROM date_range dr
LEFT JOIN logclick lc 
    ON DATE(lc.ctimes) = dr.date 
    AND lc.adname = 'abc'
GROUP BY dr.date
ORDER BY dr.date;

内容的提问来源于stack exchange，提问作者Duc Minh