基于MySQL/MySQLi实现仪表盘日周月会员统计图表的方案咨询

1. MySQL 分维度聚合查询 #

先约定你的表名为member_stats，可根据实际情况修改，三种展示模式对应的查询语句如下：

日度模式 #

按天分组，直接取单日数据：

SELECT 
    `date` as stat_date,
    DATE_FORMAT(`date`, '%b %e') as display_date,
    AVG(`new`) as avg_new,
    AVG(`total`) as avg_total
FROM member_stats
WHERE `date` BETWEEN ? AND ?
GROUP BY `date`
ORDER BY `date` ASC;

周度模式 #

按周分组，默认以周一为一周的第一天，可通过YEARWEEK的第二个参数调整：

SELECT 
    MIN(`date`) as stat_date,
    DATE_FORMAT(MIN(`date`), '%b %e') as display_date,
    AVG(`new`) as avg_new,
    AVG(`total`) as avg_total
FROM member_stats
WHERE `date` BETWEEN ? AND ?
-- YEARWEEK第二个参数1=周一开始，0=周日开始
GROUP BY YEARWEEK(`date`, 1)
ORDER BY stat_date ASC;

月度模式 #

按年月分组：

SELECT 
    MIN(`date`) as stat_date,
    DATE_FORMAT(MIN(`date`), '%b %e') as display_date,
    AVG(`new`) as avg_new,
    AVG(`total`) as avg_total
FROM member_stats
WHERE `date` BETWEEN ? AND ?
GROUP BY YEAR(`date`), MONTH(`date`)
ORDER BY stat_date ASC;

2. PHP 接口逻辑处理 #

使用PDO预处理查询避免SQL注入，同时做参数合法性校验，示例代码如下：

<?php
// 数据库配置修改为你自己的实际配置
$dbHost = '127.0.0.1';
$dbName = '你的数据库名';
$dbUser = '数据库账号';
$dbPass = '数据库密码';

// 接收并校验入参
$startDate = $_GET['start_date'] ?? '';
$endDate = $_GET['end_date'] ?? '';
$type = $_GET['type'] ?? 'day';
$allowedType = ['day', 'week', 'month'];
// 基础参数校验
if (!in_array($type, $allowedType) || !strtotime($startDate) || !strtotime($endDate) || strtotime($endDate) < strtotime($startDate)) {
    http_response_code(400);
    echo json_encode(['code' => 400, 'msg' => '参数错误']);
    exit;
}

// 匹配对应查询SQL
switch ($type) {
    case 'day':
        $sql = "SELECT `date` as stat_date, DATE_FORMAT(`date`, '%b %e') as display_date, AVG(`new`) as avg_new, AVG(`total`) as avg_total FROM member_stats WHERE `date` BETWEEN ? AND ? GROUP BY `date` ORDER BY `date` ASC";
        break;
    case 'week':
        $sql = "SELECT MIN(`date`) as stat_date, DATE_FORMAT(MIN(`date`), '%b %e') as display_date, AVG(`new`) as avg_new, AVG(`total`) as avg_total FROM member_stats WHERE `date` BETWEEN ? AND ? GROUP BY YEARWEEK(`date`, 1) ORDER BY stat_date ASC";
        break;
    case 'month':
        $sql = "SELECT MIN(`date`) as stat_date, DATE_FORMAT(MIN(`date`), '%b %e') as display_date, AVG(`new`) as avg_new, AVG(`total`) as avg_total FROM member_stats WHERE `date` BETWEEN ? AND ? GROUP BY YEAR(`date`), MONTH(`date`) ORDER BY stat_date ASC";
        break;
}

// 执行查询返回结果
try {
    $pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$startDate, $endDate]);
    $list = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // 平均值取整，符合样例输出格式
    foreach ($list as &$item) {
        $item['avg_new'] = round($item['avg_new']);
        $item['avg_total'] = round($item['avg_total']);
    }
    
    // 返回JSON给前端渲染
    echo json_encode([
        'code' => 200,
        'data' => $list
    ]);
} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode(['code' => 500, 'msg' => '查询失败']);
}
?>

3. 前端渲染适配 #

接口返回的data数组中，每一项的avg_new为新增会员数、avg_total为会员总数、display_date为横轴显示文本，直接填充到你的图表配置对应字段即可实现需求中的展示效果。

优化建议 #

• 给date字段添加普通索引，可大幅提升日期范围查询的速度
• 如果数据量级较大，可提前做预聚合，把周、月维度的统计结果存入单独的汇总表，查询时直接读取汇总表降低数据库压力
• 可根据业务需求调整周的计算规则，修改YEARWEEK的第二个参数即可切换周起始日为周日/周一

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