SQL查询语句--统计 

 
-- 1、日统计查询填补(变量i为开始与终止时间相差的天数  2022-05-10为终止时间)
-- 构造连续日期
SET @i :=- 1;
SELECT  date_format( DATE_SUB( '2022-05-10', INTERVAL ( @i := @i + 1 ) DAY ), '%Y-%m-%d' ) AS `day`
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 WHERE  @i < 10 ;
 
-- 关联连续日期给空数据补0示例
SET @i :=- 1;
select x.day,ifnull(visit_pv,0) as visit_pv, ifnull(visit_uv,0) as visit_uv, ifnull(visit_uv_new,0) as visit_uv_new
from  (SELECT  date_format( DATE_SUB( '20220530', INTERVAL ( @i := @i + 1 ) DAY ), '%Y%m%d' ) AS `day`
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 WHERE  @i < 30 ) x
left join (select visit_pv, visit_uv, visit_uv_new,ref_date from t_access_trend_daily) d
on x.day = d.ref_date order by x.day;
 
-- 2、周统计查询填补(变量i为开始与终止时间相差的天数  2022-06-04为终止时间)
-- 按周查询一
-- 构造连续周
SET @i :=- 1;
SELECT  date_format( DATE_SUB( '2022-06-04', INTERVAL ( @i := @i + 7 ) DAY ), '%Y-%m-%d' ) AS `week`
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 WHERE  @i < 100 ;
 
-- 关联连续周给空数据补0示例
SET @i :=- 1;
select x.time,d.ref_date,ifnull(visit_pv,0) as visit_pv, ifnull(visit_uv,0) as visit_uv, ifnull(visit_uv_new,0) as visit_uv_new
from  (SELECT  date_format( DATE_SUB( '2022-06-04', INTERVAL ( @i := @i + 7 ) DAY ), '%Y-%m-%d' ) AS `time`
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 WHERE  @i < 40 ) x
left join (select DATE_FORMAT(DATE_SUB( ref_date, INTERVAL DAYOFWEEK( ref_date )- 1 DAY ),'%Y-%m-%d') as `week`,CONCAT(DATE_FORMAT(DATE_SUB( ref_date, INTERVAL DAYOFWEEK( ref_date )- 1 DAY ),'%Y%m%d'),"-",DATE_FORMAT(DATE_SUB( ref_date, INTERVAL DAYOFWEEK( ref_date )- 7 DAY ),'%Y%m%d')) as `ref_date`,sum(visit_pv) visit_pv,sum(visit_uv) visit_uv,sum(visit_uv_new) visit_uv_new FROM t_access_trend_daily WHERE 1 = 1 GROUP BY `week` ) d
on x.time = d.week ORDER BY x.time;
 
 
-- 按周查询二('2023-08-19'为开始时间  '2023-08-31'为终止时间)
-- 按周统计分组查询空数据补0示例
SELECT x.`time` AS weekTime,FLOOR(ifnull( d.downCounts, 0)) AS downCounts
FROM
(
		select DATE_FORMAT(date_add(date_sub('2023-08-19',interval dayofweek('2023-08-19')-1 day), interval row DAY),'%Y-%m-%d') time from
		 ( 
				SELECT @row := @row + 7 as row FROM 
				(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
				(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
				(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
				(SELECT @row:=-7) r
		 ) se where DATE_FORMAT(date_add(date_sub('2023-08-19',interval dayofweek('2023-08-19')-1 day), interval row DAY),'%Y-%m-%d') <= DATE_FORMAT('2023-08-31','%Y-%m-%d')
) x
LEFT JOIN 
(
	SELECT DATE_FORMAT( DATE_SUB( create_time, INTERVAL DAYOFWEEK( create_time )- 1 DAY ), '%Y-%m-%d' ) AS `week`,sum( DOWN_COUNT ) downCounts
	FROM MP_STATISTICS_DOWNLOAD_DAY 
	WHERE 1 = 1 GROUP BY `week` 
) d 
ON x.time = d.`week`
ORDER BY x.time;
 
 
-- 3、月统计查询填补(变量i为开始与终止时间相差的天数  2022-05-10为终止时间)
-- 构造连续月
SET @i :=- 1;
SELECT  date_format( DATE_SUB( '2022-05-10', INTERVAL ( @i := @i + 1 ) MONTH ), '%Y-%m' ) AS `month`
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 WHERE  @i < 5 ;
 
-- 关联连续月给空数据补0示例
SET @i :=- 1;
select x.time,ifnull(visit_pv,0) as visit_pv, ifnull(visit_uv,0) as visit_uv, ifnull(visit_uv_new,0) as visit_uv_new
from  (SELECT  date_format( DATE_SUB( '20220630', INTERVAL ( @i := @i + 1 ) MONTH ), '%Y%m' ) AS `time`
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 WHERE  @i < 5 ) x
left join (select  DATE_FORMAT(ref_date, '%Y%m') AS `month`,sum(visit_pv) visit_pv, sum(visit_uv) visit_uv, sum(visit_uv_new) visit_uv_new
from t_access_trend_daily GROUP BY month) d
on x.time = d.month ORDER BY x.time;
 
-- 4、以某个字段为类型进行统计某个字段的总量
select ref_date,sum(properties_type) as total,
sum(case when user_type = '1' then properties_type end) as total1,
sum(case when user_type = '0' then properties_type end) as total0
from t_user_portrait where 1 = 1 GROUP BY ref_date
 
-- 5、以某个字段为类型进行统计某个字段的记录数（即多少条）
select ref_date,count(*) as total,
count(case when user_type = '1' then 1 end) as total1,
count(case when user_type = '0' then 1 end) as total0
from t_user_portrait where 1 = 1 GROUP BY ref_date
 
-- 6、日期格式拼接(周)
select DATE_FORMAT(DATE_SUB( ref_date, INTERVAL DAYOFWEEK( ref_date )- 1 DAY ),'%Y-%m-%d') as `week`,
CONCAT(DATE_FORMAT(DATE_SUB( ref_date, INTERVAL DAYOFWEEK( ref_date )- 1 DAY ),'%Y%m%d'),"-",
DATE_FORMAT(DATE_SUB( ref_date, INTERVAL DAYOFWEEK( ref_date )- 7 DAY ),'%Y%m%d')) as `ref_date`,
sum(visit_pv) visit_pv,sum(visit_uv) visit_uv,sum(visit_uv_new) visit_uv_new FROM t_access_trend_daily 
WHERE 1 = 1 GROUP BY `week` 
 
-- 7、按小时查询
-- 按小时第一种写法
SELECT FLOOR(ifnull( d.downCounts, 0 )) AS downCounts,c.create_time AS createTime 
FROM
	(
		SELECT date_format( date_sub( '2023-07-24 10', INTERVAL ( @i := @i - 1 ) HOUR ), '%Y-%m-%d %H' ) AS create_time 
		FROM mysql.help_topic JOIN ( SELECT @i := 1 ) c 
		WHERE help_topic_id <= (TIMESTAMPDIFF( HOUR, '2023-07-24 10', '2023-07-25 10'))
	) c
	LEFT JOIN (
		SELECT sum( DOWN_COUNT ) downCounts,date_format ( create_time, '%Y-%m-%d %H') AS create_time 
		FROM MP_STATISTICS_DOWNLOAD_HOUR 
		WHERE 1 = 1 
			AND create_time BETWEEN str_to_date( '2023-07-24 10', '%Y-%m-%d %H' ) 
			AND str_to_date( '2023-07-25 100', '%Y-%m-%d %H' ) 
		GROUP BY create_time 
	) d 
ON c.create_time = d.create_time 
ORDER BY c.create_time ASC;
 
 
-- 按小时第二种写法
SELECT FLOOR(ifnull( d.downCounts, 0 )) AS downCounts,c.create_time AS createTime,c.channel 
FROM
	(
		SELECT cc.CHANNELKEY AS channel,cd.create_time AS create_time 
		FROM
			CVMMP_CHANNELMANAGE cc,(select DATE_FORMAT(date_add('2023-08-23 15', interval row hour),'%Y-%m-%d %H') create_time from
			 ( 
					SELECT @row := @row + 1 as row FROM 
					(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
					(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
					(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
					(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
					(SELECT @row:=-1) r
			 ) se where DATE_FORMAT(date_add('2023-08-23 15', interval row hour),'%Y-%m-%d %H') <= DATE_FORMAT('2023-08-23 21','%Y-%m-%d %H')) cd
		WHERE cc.STATUS = 1 
	) c
	LEFT JOIN 
	(
		SELECT sum( DOWN_COUNT ) downCounts,date_format ( create_time, '%Y-%m-%d %H' ) AS create_time,channel 
		FROM MP_STATISTICS_DOWNLOAD_HOUR 
		WHERE 1 = 1 
			AND create_time BETWEEN str_to_date( '2023-08-23 15', '%Y-%m-%d %H' ) 
			AND str_to_date( '2023-08-23 21', '%Y-%m-%d %H' ) 
		GROUP BY create_time,channel 
	) d 
ON c.create_time = d.create_time 
AND c.channel = d.channel 
ORDER BY c.create_time,c.channel ASC;

image

image

 
-- 按日统计
SELECT
	FLOOR(ifnull( d.downCounts, 0)) AS downCounts,
	c.daytime AS createTime 
FROM
	(
		select DATE_FORMAT(date_add('2020-08-21', interval row DAY),'%Y-%m-%d') daytime from
		 ( 
				SELECT @row := @row + 1 as row FROM 
				(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
				(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
				(SELECT @row:=-1) r
		 ) se where DATE_FORMAT(date_add('2020-08-21', interval row DAY),'%Y-%m-%d') <= DATE_FORMAT('2023-08-22','%Y-%m-%d')
	) c LEFT JOIN (
	SELECT sum( DOWN_COUNT ) downCounts,date_format( create_time, '%Y-%m-%d' ) AS daytime 
	FROM MP_STATISTICS_DOWNLOAD_DAY 
	WHERE 1 = 1 AND date_format ( create_time, '%Y-%m-%d' ) BETWEEN '2020-08-21' AND '2023-08-22' 
	GROUP BY date_format( create_time, '%Y-%m-%d' )) d ON c.daytime = d.daytime 
ORDER BY c.monthTime ASC;
 
 
-- 按月统计
SELECT
	FLOOR(ifnull( d.downCounts, 0)) AS downCounts,
	c.monthTime AS createTime 
FROM
	(
		select DATE_FORMAT(date_add('2020-08-21', interval row MONTH),'%Y-%m') monthTime from
		 ( 
				SELECT @row := @row + 1 as row FROM 
				(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
				(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
				(SELECT @row:=-1) r
		 ) se where DATE_FORMAT(date_add('2020-08-21', interval row MONTH),'%Y-%m') <= DATE_FORMAT('2023-08-22','%Y-%m')
	) c LEFT JOIN (
	SELECT sum( DOWN_COUNT ) downCounts,date_format( create_time, '%Y-%m' ) AS monthTime 
	FROM MP_STATISTICS_DOWNLOAD_DAY 
	WHERE 1 = 1 AND date_format ( create_time, '%Y-%m' ) BETWEEN '2020-08-21' AND '2023-08-22' 
	GROUP BY date_format( create_time, '%Y-%m' )) d ON c.monthTime = d.monthTime 
ORDER BY c.monthTime ASC;
 
 
 
-- 按年统计
SELECT
	FLOOR(ifnull( d.downCounts, 0)) AS downCounts,
	c.yeartime AS createTime 
FROM
	(
		select DATE_FORMAT(date_add('2020-08-21', interval row Year),'%Y') yeartime from
		 ( 
				SELECT @row := @row + 1 as row FROM 
				(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
				(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
				(SELECT @row:=-1) r
		 ) se where DATE_FORMAT(date_add('2020-08-21', interval row Year),'%Y') <= DATE_FORMAT('2023-08-22','%Y')
	) c LEFT JOIN (
	SELECT sum( DOWN_COUNT ) downCounts,date_format( create_time, '%Y' ) AS yeartime 
	FROM MP_STATISTICS_DOWNLOAD_DAY 
	WHERE 1 = 1 AND date_format ( create_time, '%Y' ) BETWEEN '2020-08-21' AND '2023-08-22' 
	GROUP BY date_format( create_time, '%Y' )) d ON c.yeartime = d.yeartime 
ORDER BY c.monthTime ASC;
 
 
oracle相关语句
SELECT DATA_SOURCES, max(DATA_DATE) MAX_DATE FROM (
(
  SELECT '3' DATA_SOURCES, '1970-01-01' DATA_DATE FROM DUAL UNION
  SELECT '4' DATA_SOURCES, '1970-01-01' DATA_DATE FROM DUAL UNION
  SELECT '5' DATA_SOURCES, '1970-01-01' DATA_DATE FROM DUAL
)
union ALL (select DATA_SOURCES,DATA_DATE from ACTIVITY_ISSUE_COUPONS_RECORD))
group by DATA_SOURCES
ORDER BY DATA_SOURCES DESC
posted @   小公羊  阅读(607)  评论(0)  编辑  收藏  举报
相关博文：
·  Redis-firewall使用命令
·  Redis安装（CentOS 8.5 64位）
·  一行SQL语句实现统计未来7天、按月统计数据，无数据填充0
·  SQL SERVER 月、季、年统计与常用查询语句汇总
·  MYsql 7天数据汇总统计 SQL语句 半成品
阅读排行：
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
· 单元测试从入门到精通
· winform 绘制太阳，地球，月球 运作规律
· 上周热点回顾（3.3-3.9）
点击右上角即可分享
微信分享提示
AI IDE Trae