MYSQL按日期分组

最新推荐文章于 2024-05-01 08:00:00 发布

manitod

最新推荐文章于 2024-05-01 08:00:00 发布

阅读量2.3k

点赞数

CC 4.0 BY-SA版权

分类专栏： Mysql 文章标签： mysql 数据库 database

本文链接：https://blog.youkuaiyun.com/manitod/article/details/122087011

Mysql 专栏收录该内容

6 篇文章

订阅专栏

自然日周月季年分组

SELECT new_date as submitDate, ROUND(check_right/(check_count + check_add)*100,2) as rightRate, stat_time as statTime
		FROM
		(
			SELECT
			<choose>
				<when test="fieldCollectIndexReq.statCycle == &quot;Y&quot;">
					DATE_FORMAT(work_date, '%Y') as new_date
				</when>
				<when test="fieldCollectIndexReq.statCycle == &quot;Q&quot;">
					CONCAT(DATE_FORMAT(work_date, '%Y'), '-', QUARTER(submit_date)) as new_date
				</when>
				<when test="fieldCollectIndexReq.statCycle == &quot;M&quot;">
					DATE_FORMAT(work_date, '%Y-%m') as new_date
				</when>
				<when test="fieldCollectIndexReq.statCycle == &quot;W&quot;">
					DATE_FORMAT(work_date, '%Y-%u') as new_date
				</when>
				<otherwise>
					work_date as new_date
				</otherwise>
			</choose>

			, sum(check_count) as check_count
			, sum(check_right) as check_right
			, sum(check_add) as check_add
			, max(update_time) as stat_time
			FROM t_final_index
			WHERE work_date BETWEEN #{fieldCollectIndexReq.submitDateStart} AND #{fieldCollectIndexReq.submitDateEnd}
			AND dept_id = #{fieldCollectIndexReq.deptId} AND work_user = #{fieldCollectIndexReq.workUser}
			AND flow_node = #{fieldCollectIndexReq.flowNode}
			<if test="fieldCollectIndexReq.adcodeP != null and fieldCollectIndexReq.adcodeP != ''">
				and adcode_p = #{fieldCollectIndexReq.adcodeP}
			</if>
			<if test="fieldCollectIndexReq.adcodeC != null and fieldCollectIndexReq.adcodeC != ''">
				and adcode_c = #{fieldCollectIndexReq.adcodeC}
			</if>
			<if test="fieldCollectIndexReq.adcode != null and fieldCollectIndexReq.adcode != ''">
				and adcode = #{fieldCollectIndexReq.adcode}
			</if>
			<if test="fieldCollectIndexReq.elementCode != null and fieldCollectIndexReq.elementCode != ''">
				and element_code = #{fieldCollectIndexReq.elementCode}
			</if>
			GROUP BY new_date, work_user
		) AS T ORDER BY new_date

特定日期间隔分组

从后往前每7天分1组

'2021-12-17’是查询的截止日期

SELECT
		dept_id AS deptId,
		SUM(work_ratio)AS work_ratio,
		COUNT(dept_id)AS countNum,
		min(work_date) as miwork_date,
		max(work_date) as mxwork_date,
		(
				TIMESTAMPDIFF(DAY, work_date, '2021-12-17') DIV 7
	  )AS groupNum
 FROM
     t_work_ratio
WHERE
     work_date <= '2021-12-17'
GROUP BY
     dept_id, groupNum

从前往后每7天分1组

'2021-12-17’是查询的其实日期

SELECT
		dept_id AS deptId,
		SUM(work_ratio)AS work_ratio,
		COUNT(dept_id)AS countNum,
		min(work_date) as miwork_date,
		max(work_date) as mxwork_date,
		(
				TIMESTAMPDIFF(DAY, '2021-12-17', work_date) DIV 7
	  )AS groupNum
 FROM
     t_work_ratio
WHERE
     work_date >= '2021-12-17'
GROUP BY
     dept_id, groupNum

按年从前往后7天分1组

解决跨年问题，同理可处理跨季度扩月份周等问题

SELECT
      dept_id AS deptId,
      SUM(work_ratio)AS work_ratio,
      COUNT(dept_id)AS countNum,
			min(work_date) as miwork_date,
			max(work_date) as mxwork_date,
				(
          YEAR(work_date)* 1000 +(
              DAYOFYEAR(work_date) + 7 - MOD(DAYOFYEAR(CURDATE()) ,7)
        )DIV 7
     )AS groupNum
 FROM
     t_stat_interior_work_ratio
WHERE
     work_date <= '2021-12-17'
GROUP BY
     dept_id, groupNum