mysql 查询统计数据

最新推荐文章于 2023-08-08 21:59:23 发布

Json159

最新推荐文章于 2023-08-08 21:59:23 发布

阅读量214

点赞数

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

本文链接：https://blog.csdn.net/Json159/article/details/127545258

版权

数据库专栏收录该内容

29 篇文章 2 订阅

订阅专栏

分组后查询分组后的平均值

select request_type,request_type_display_name, AVG(request_count) avg_day from (SELECT
	request_type,
CASE
		request_type 
		WHEN 10 THEN
		'上传广告' 
		WHEN 20 THEN
		'价格更新' 
		WHEN 30 THEN
		'库存更新' 
		WHEN 40 THEN
		'广告下架' 
		WHEN 50 THEN
		'上传广告图片' 
		WHEN 60 THEN
		'广告信息更新' 
		when 70 then 
		'广告图片更新' end as request_type_display_name, 
		FROM_UNIXTIME( create_time, '%Y-%m-%d' ) request_date,
		count( id ) request_count 
	FROM
		sfc_jumia_feed 
	WHERE
		create_time < 1664553600 AND create_time > 1661961600 
	GROUP BY
	FROM_UNIXTIME( create_time, '%Y-%m-%d' ),
	request_type) as ddd GROUP BY request_type;

按天分组及请求类型分组

SELECT
	request_type,
CASE
		request_type 
		WHEN 10 THEN
		'上传广告' 
		WHEN 20 THEN
		'价格更新' 
		WHEN 30 THEN
		'库存更新' 
		WHEN 40 THEN
		'广告下架' 
		WHEN 50 THEN
		'上传广告图片' 
		WHEN 60 THEN
		'广告信息更新' 
		when 70 then 
		'广告图片更新' end as request_type_display_name, 
		FROM_UNIXTIME( create_time, '%Y-%m-%d' ) request_date,
		count( id ) request_count 
	FROM
		sfc_jumia_feed 
	WHERE
		create_time < 1664553600 AND create_time > 1661961600 
	GROUP BY
	FROM_UNIXTIME( create_time, '%Y-%m-%d' ),
	request_type

分组后查询数据

SELECT
	request_type,
CASE
		request_type 
		WHEN 10 THEN
		'上传广告' 
		WHEN 20 THEN
		'价格更新' 
		WHEN 30 THEN
		'库存更新' 
		WHEN 40 THEN
		'广告下架' 
		WHEN 50 THEN
		'上传广告图片' 
		WHEN 60 THEN
		'广告信息更新' 
		when 70 then 
		'广告图片更新' end as request_type_display_name, 
		FROM_UNIXTIME( create_time, '%Y-%m-%d' ) request_date,
		count( id ) request_count 
	FROM
		sfc_jumia_feed 
	WHERE
		create_time < 1664553600 AND create_time > 1661961600 
	GROUP BY
	FROM_UNIXTIME( create_time, '%Y-%m-%d' ),
	request_type HAVING request_type = 10;