mysql commonly used grammar collation

Recently I am doing some development on statistics, and sorted out the following inquiries about various time periods

Query XX statistics for each period of the day (such as hourly passenger flow)

illustrate
Table name: cg_event_tendency_num_hour_statistics
Database day field: event_date
Database time field: event_hour ==> return to the front end as name
The number field of the current time period of the database: event_num ==> return to the front end 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

There will be a problem here, the data query may not be a complete time period

But what should we do if we want to return the complete time 00-23 to the front end?
We can use LEFT JOIN to perform the operation, first use UNION ALL query 00-23 , and then left-associate the real data

The complete sql is as follows:

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

result:

Query the daily XX statistics of the month (such as daily passenger flow)

illustrate
Table name: cg_event_tendency_num_statistics
Database month field: event_month
Database day field: event_date ==> return to the front end as name
The number field of the current time period of the database: event_num ==> return to the front end 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

Here is the same problem, the data query may not be a complete date range

The processing method is hours, except that the method used here generates the number of days per month

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

result:


The annual passenger flow is similar to the time, so I won’t organize it here.