进行月、周、天的分组查询(年分组进行依次类推可拓展)
SELECT
SUM(
IFNULL( countNumber, 0 )) countNumber,
SUM(
IFNULL( countPeopleNumber, 0 )) countPeopleNumber,
CONCAT(
MIN(
DATE_FORMAT( aa.date, '%Y-%m-%d' )),
'-',
MAX(
DATE_FORMAT( aa.date, '%Y-%m-%d' ))) dateRange,
DATE_FORMAT( aa.date, '%Y%m%d' ) days,
DATE_FORMAT( aa.date, '%Y%u' ) weeks,
DATE_FORMAT( aa.date, '%Y%m' ) months
FROM
(
SELECT
@cdate := date_add( @cdate, INTERVAL - 1 DAY ) date
FROM
( SELECT @cdate := date_add( CURDATE(), INTERVAL 1 DAY ) FROM user_oper_log ) a
) aa
LEFT JOIN (
SELECT
upl.*
FROM
( SELECT count( 1 ) countNumber, count( DISTINCT user_id ) countPeopleNumber, DATE_FORMAT( oper_time, '%Y-%m-%d' ) operTime FROM user_oper_log GROUP BY operTime ) upl
WHERE
upl.operTime BETWEEN DATE_FORMAT( '2022-11-18', '%Y-%m-%d') and DATE_FORMAT( '2022-11-18', '%Y-%m-%d')
) b ON aa.date = b.operTime
GROUP BY
weeks
ORDER BY
days DESC