-- 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 ;
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、周统计查询填补
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 ;
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;
-- 3、月统计查询填补
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 ;
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
Original: https://www.cnblogs.com/aadzj/p/16284729.html
Author: 小公羊
Title: SQL查询语句–统计
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/612349/
转载文章受原作者版权保护。转载请注明原作者出处！