题解 | #月总刷题数和日均刷题数#

SELECT
    date_format (A.SUBMIT_TIME, '%Y%m') as submit_month,
    COUNT(1) as month_q_cnt,
    ROUND(COUNT(1) / DAY_M, 3) as avg_day_q_cnt
FROM
    (
        SELECT
            *,
            DAYOFMONTH (LAST_DAY (SUBMIT_TIME)) DAY_M
        FROM
            practice_record
    ) A
WHERE
    YEAR (A.SUBMIT_TIME) = '2021'
GROUP BY
    date_format (A.SUBMIT_TIME, '%Y%m'),
    DAY_M
UNION ALL
SELECT
    '2021汇总',
    sum(X.month_q_cnt),
    ROUND(sum(X.month_q_cnt) / 31, 3)
FROM
    (
        SELECT
            date_format (A.SUBMIT_TIME, '%Y%m') as submit_month,
            COUNT(1) as month_q_cnt,
            ROUND(COUNT(1) / DAY_M, 3) as avg_day_q_cnt
        FROM
            (
                SELECT
                    *,
                    DAYOFMONTH (LAST_DAY (SUBMIT_TIME)) DAY_M
                FROM
                    practice_record
            ) A
        WHERE
            YEAR (A.SUBMIT_TIME) = '2021'
        GROUP BY
            date_format (SUBMIT_TIME, '%Y%m'),
            DAY_M
    ) X
group by
    YEAR (X.submit_month)
ORDER BY submit_month ASC;