最近在做一些对于统计方面的开发，整顿了以下对于各个时间段的查问

查问当天内各时段的 XX 统计(比方小时客流量)

阐明
表名：cg_event_tendency_num_hour_statistics
数据库天字段：event_date
数据库工夫字段：event_hour ==> 返回给前端 as 为 name
数据库以后时间段数量字段：event_num ==> 返回给前端 as 为 value

SELECT event_hour,SUM(event_num) as event_num  FROM cg_event_tendency_num_hour_statistics WHERE date_format(event_date, '%Y-%m-%d') = DATE_FORMAT(now(), '%Y-%m-%d') GROUP BY event_hour

这里会呈现一个问题，数据查问进去的可能不是残缺的时间段

然而咱们想返回给前端残缺的工夫 00-23 该怎么操作呢？
咱们能够应用 LEFT JOIN 来进行操作，先应用 UNION ALL 查问出 00-23 的全副工夫, 再将实在数据进行左关联

残缺 sql 如下：

SELECT timetable.HOUR AS NAME, ifnull(sumtable.count, 0) AS VALUE
FROM (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT 1 AS HOUR
    UNION ALL
    SELECT 2 AS HOUR
    UNION ALL
    SELECT 3 AS HOUR
    UNION ALL
    SELECT 4 AS HOUR
    UNION ALL
    SELECT 5 AS HOUR
    UNION ALL
    SELECT 6 AS HOUR
    UNION ALL
    SELECT 7 AS HOUR
    UNION ALL
    SELECT 8 AS HOUR
    UNION ALL
    SELECT 9 AS HOUR
    UNION ALL
    SELECT 10 AS HOUR
    UNION ALL
    SELECT 11 AS HOUR
    UNION ALL
    SELECT 12 AS HOUR
    UNION ALL
    SELECT 13 AS HOUR
    UNION ALL
    SELECT 14 AS HOUR
    UNION ALL
    SELECT 15 AS HOUR
    UNION ALL
    SELECT 16 AS HOUR
    UNION ALL
    SELECT 17 AS HOUR
    UNION ALL
    SELECT 18 AS HOUR
    UNION ALL
    SELECT 19 AS HOUR
    UNION ALL
    SELECT 20 AS HOUR
    UNION ALL
    SELECT 21 AS HOUR
    UNION ALL
    SELECT 22 AS HOUR
    UNION ALL
    SELECT 23 AS HOUR
) timetable
    LEFT JOIN (SELECT event_hour AS HOUR, SUM(event_num) AS count
        FROM cg_event_tendency_num_hour_statistics
        WHERE date_format(event_date, '%Y-%m-%d') =  DATE_FORMAT(now(), '%Y-%m-%d')
        GROUP BY HOUR
    ) sumtable
    ON timetable.HOUR = sumtable.HOUR
ORDER BY NAME

后果：

查问当月内每天的 XX 统计(比方日客流量)

阐明
表名：cg_event_tendency_num_statistics
数据库月字段：event_month
数据库天字段：event_date ==> 返回给前端 as 为 name
数据库以后时间段数量字段：event_num ==> 返回给前端 as 为 value

SELECT event_date , SUM(event_num) as event_num FROM cg_event_tendency_num_statistics WHERE event_month = DATE_FORMAT(now(), '%Y-%m' ) GROUP BY event_date

这里也是同样的问题，数据查问进去的可能不是残缺的日期段

解决形式和 小时 的同理, 只是这里采纳办法生成了每月的天数

SELECT DAY(timetable.date) AS NAME, ifnull(sumtable.count, 0) AS VALUE
FROM (
    SELECT date, count
    FROM (SELECT DATE_SUB(last_day(curdate()), INTERVAL xc - 1 DAY) AS date, 0 AS count
        FROM (
            SELECT @xi := @xi + 1 AS xc
            FROM (
                SELECT 1
                UNION
                SELECT 2
                UNION
                SELECT 3
                UNION
                SELECT 4
                UNION
                SELECT 5
                UNION
                SELECT 6
            ) xc1, (
                    SELECT 1
                    UNION
                    SELECT 2
                    UNION
                    SELECT 3
                    UNION
                    SELECT 4
                    UNION
                    SELECT 5
                    UNION
                    SELECT 6
                ) xc2, (SELECT @xi := 0) xc0
        ) xcxc
    ) x0
    WHERE x0.date >= (SELECT date_add(curdate(), INTERVAL -DAY(curdate()) + 1 DAY)
    )
    ORDER BY date
) timetable
    LEFT JOIN (SELECT event_date AS date, SUM(event_num) AS count
        FROM cg_event_tendency_num_statistics
        WHERE event_month = DATE_FORMAT(now(), '%Y-%m')
        GROUP BY date
    ) sumtable
    ON timetable.date = sumtable.date
ORDER BY NAME

后果：

年客流量的形式和工夫的差不多，这里就不整顿了其余的后续再进行更新