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;
-- 按日统计
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
本文来自博客园，作者：小公羊，转载请注明原文链接：https://www.cnblogs.com/aadzj/p/16284729.html
