项目开发里遇到的一些统计分析、超限率等的一些sql

最新推荐文章于 2024-05-20 16:22:31 发布
最新推荐文章于 2024-05-20 16:22:31 发布
阅读量117 收藏
点赞数
分类专栏： sql 文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u011004598/article/details/84529025
版权 


本人本月等超载率的sql:
本日：select t1.countycode,t1.count1 as allnumber,nvl(t2.count2,0) as overnumber,nvl(to_char(t2.count2/t1.count1*100,'990.00'),0)  as overpercent from (select t.countycode as countycode,count(*) as count1 from t_checkinfo t where     ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )   and trunc(t.checkdate)=trunc(sysdate) group by t.countycode) t1 left outer join   (select t.countycode ,count(*) as count2 from t_checkinfo t  where       ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )     and t.ispunished!=0 and trunc(t.checkdate)=trunc(sysdate)  group by t.countycode) t2   on  t1.countycode=t2.countycode
本周：select t1.countycode,t1.count1 as allnumber,nvl(t2.count2,0) as overnumber,nvl(to_char(t2.count2/t1.count1*100,'990.00'),0)  as overpercent from (select t.countycode as countycode,count(*) as count1 from t_checkinfo t  where     ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )   and t.checkdate >=(trunc(sysdate, 'd') + 1) and t.checkdate<=( trunc(sysdate, 'd') + 8) group by t.countycode) t1  left outer join  (select t.countycode ,count(*) as count2 from t_checkinfo t  where       ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )     and t.ispunished!=0 and t.checkdate >=(trunc(sysdate, 'd') + 1) and t.checkdate<=( trunc(sysdate, 'd') + 8) group by t.countycode) t2  on t1.countycode=t2.countycode
本月：select t1.countycode,t1.count1 as allnumber,nvl(t2.count2,0) as overnumber,nvl(to_char(t2.count2/t1.count1*100,'990.00'),0)  as overpercent from (select t.countycode as countycode,count(*) as count1 from t_checkinfo t where     ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )   and t.checkdate >=(trunc(sysdate, 'mm')) and t.checkdate<=last_day(trunc(sysdate))  group by t.countycode) t1   left outer join   (select t.countycode ,count(*) as count2 from t_checkinfo t  where       ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )     and t.ispunished!=0 and t.checkdate >=(trunc(sysdate, 'mm')) and t.checkdate<=last_day(trunc(sysdate)) group by t.countycode) t2   on t1.countycode=t2.countycode
本季：select t1.countycode,t1.count1 as allnumber,nvl(t2.count2,0) as overnumber,nvl(to_char(t2.count2/t1.count1*100,'990.00'),0)  as overpercent    from (select t.countycode as countycode,count(*) as count1 from t_checkinfo t  where     ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )   and t.checkdate >=(trunc(sysdate, 'Q')) and t.checkdate<=(add_months(trunc(sysdate, 'Q'), 3) - 1) group by t.countycode) t1   left outer join   (select t.countycode ,count(*) as count2 from t_checkinfo t   where       ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )     and t.ispunished!=0 and t.checkdate >=(trunc(sysdate, 'Q')) and t.checkdate<=(add_months(trunc(sysdate, 'Q'), 3) - 1) group by t.countycode) t2   on t1.countycode=t2.countycode
本年：select t1.countycode,t1.count1 as allnumber,nvl(t2.count2,0) as overnumber,nvl(to_char(t2.count2/t1.count1*100,'990.00'),0)  as overpercent from (select t.countycode as countycode,count(*) as count1 from t_checkinfo t  where     ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )   and t.checkdate >=trunc(sysdate, 'yyyy')  and t.checkdate<=(add_months(trunc(sysdate, 'yyyy'), 12) - 1) group by t.countycode) t1  left outer join  (select t.countycode ,count(*) as count2 from t_checkinfo t  where       ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )    and t.ispunished!=0 and t.checkdate >=trunc(sysdate, 'yyyy')  and t.checkdate<=(add_months(trunc(sysdate, 'yyyy'), 12) - 1) group by t.countycode) t2   on t1.countycode=t2.countycode

----------------------------------------------------------------------------------
统计分析sql:
1. 天统计:
    所有统计：
select t1.sitecode as sitecode,t1.sitename as sitename,to_char(t1.dateperiod,'yyyy-mm-dd') as dateperiod ,t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename,trunc(t.checkdate,'dd')  as datePeriod,count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2013-12-27','yyyy-mm-dd') and  to_date('2014-01-03','yyyy-mm-dd')  group by t.sitecode,t.sitename,trunc(t.checkdate,'dd')  ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename,trunc(t.checkdate,'dd')  as datePeriod,count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2013-12-27','yyyy-mm-dd') and  to_date('2014-01-03','yyyy-mm-dd')   and t.ifoverload=2  group by t.sitecode,t.sitename ,trunc(t.checkdate,'dd') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by sitecode  ,dateperiod desc
2.周统计：
   所有的统计：
select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  group by t.sitecode,t.sitename,to_char(t.checkdate,'iw')  ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')   and t.ifoverload=2  group by t.sitecode,t.sitename ,to_char(t.checkdate,'iw') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by sitecode  ,dateperiod desc
    某个区县的统计：
select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.countycode='140602' group by t.sitecode,t.sitename,to_char(t.checkdate,'iw')  ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.countycode='140602'  and t.ifoverload=2  group by t.sitecode,t.sitename ,to_char(t.checkdate,'iw') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by sitecode  ,dateperiod desc
    某个站点的统计：
select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.sitecode='1406021001' group by t.sitecode,t.sitename,to_char(t.checkdate,'iw')  ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.sitecode='1406021001'  and t.ifoverload=2  group by t.sitecode,t.sitename ,to_char(t.checkdate,'iw') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by sitecode  ,dateperiod desc

3. 月统计，所有：
select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'mm')  as datePeriod,count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2013-11-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  group by t.sitecode,t.sitename,to_char(t.checkdate,'mm')  ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'mm')  as datePeriod,count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2013-11-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')   and t.ifoverload=2  group by t.sitecode,t.sitename ,to_char(t.checkdate,'mm') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by sitecode  ,dateperiod desc
4. 季度统计，所有的：
select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'q')  as datePeriod,count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2013-5-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  group by t.sitecode,t.sitename,to_char(t.checkdate,'q')  ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'q')  as datePeriod,count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2013-5-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')   and t.ifoverload=2  group by t.sitecode,t.sitename ,to_char(t.checkdate,'q') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by sitecode  ,dateperiod desc
5. 年统计，，所有的：
select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'yyyy')  as datePeriod,count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2011-5-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  group by t.sitecode,t.sitename,to_char(t.checkdate,'yyyy')  ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'yyyy')  as datePeriod,count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2011-5-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')   and t.ifoverload=2  group by t.sitecode,t.sitename ,to_char(t.checkdate,'yyyy') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by sitecode  ,dateperiod desc

----------------------------------------------------------------------------------

原来：时间年对比：站点数据
select site.ctrl_site_name	as city,to_char(checkdate,'yyyy') as time,  count(t.checkid) as jiancenum,       	 
sum(decode(t.ispunished,'0',0,1)) as chaozainum,   sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight))  
as totalnum,                                               	
 round(sum(decode(t.ispunished,'0',0,1)) / count(t.checkid) ,4)   * 100 as overrun   	 from 
 t_checkinfo t , t_ctrl_site site , t_organization c where 
 t.sitecode = site.ctrl_code  and c.o_orgcode = site.region_name and t.sitecode in ('1406022001') and site.region_name = '140602' and 
 t.ifcertificate='0'  and t.overpercent >= 0.1 and t.weight >= 30.0 and (to_char(t.checkdate, 'yyyy') between '2012' and  '2013') group by 
 site.ctrl_site_name,to_char(checkdate,'yyyy') order by to_char(checkdate,'yyyy');

 原来：区域对比：站点数据 ： sqlsite
 select site.ctrl_site_name	as city,max(to_char(checkdate,'yyyy-mm-dd')) as time,  count(t.checkid) as jiancenum,       	 
 sum(decode(t.ispunished,'0',0,1)) as chaozainum,   sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight))  
 as totalnum,               
round(sum(decode(t.ispunished,'0',0,1)) / count(t.checkid) ,4)   * 100 as overrun   	 from 
t_checkinfo t , t_ctrl_site site , t_organization c where 
t.sitecode = site.ctrl_code and c.o_orgcode = site.region_name and t.sitecode in ('1406021001') and site.region_name = '140602' and 
t.ifcertificate='0'  and t.weight >= 10.0 and (to_char(t.chkIntimestamp, 'yyyy-mm-dd') between '2014-01-05' and  '2014-01-07' 
or to_char(t.chkouttimestamp, 'yyyy-mm-dd') between '2014-01-05' and  '2014-01-07') group by site.ctrl_site_name

 原来：时间年对比：检测车数据
 select site.ctrl_site_name	as city,to_char(checkdate,'yyyy') as time,  count(t.checkid) as jiancenum,       	 
 sum(decode(t.ispunished,'0',0,1)) as chaozainum,   sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight))  
 as totalnum,                                          
 round(sum(decode(t.ispunished,'0',0,1)) / count(t.checkid) ,4)   * 100 as overrun   	 from 
 t_checkinfo t , t_ctrl_site site , t_organization c where 
 t.sitecode = site.ctrl_code  and c.o_orgcode = site.region_name and t.sitecode in ('1406022001') and site.region_name = '140602' and 
t.ifcertificate='0'  and t.overpercent >= 0.1 and t.weight >= 30.0 and (to_char(t.checkdate, 'yyyy') between '2012' and  '2013') group by 
site.ctrl_site_name,to_char(checkdate,'yyyy') order by to_char(checkdate,'yyyy') 

 原来：区域天对比：站点数据
 select site.ctrl_site_name	as city,max(to_char(checkdate,'yyyy-mm-dd')) as time,  count(t.checkid) as jiancenum,       	
 sum(decode(t.ispunished,'0',0,1)) as chaozainum,   sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight))  
 as totalnum,                                               	
 round(sum(decode(t.ispunished,'0',0,1)) / count(t.checkid) ,4)   * 100 as overrun   	 from 
 t_checkinfo t , t_ctrl_site site , t_organization c where 
 t.sitecode = site.ctrl_code and c.o_orgcode = site.region_name and t.sitecode in ('1406021001') and site.region_name = '140602' and 
 t.ifcertificate='0'  and t.weight >= 10.0 and 
 (to_char(t.chkIntimestamp, 'yyyy-mm-dd') between '2014-01-05' and  '2014-01-07' or to_char(t.chkouttimestamp, 'yyyy-mm-dd') between '2014-01-05' and  '2014-01-07') 
 group by site.ctrl_site_name

 天统计，所有统计(鲁最新SQL语句)
 select t1.sitecode as sitecode,t1.sitename as sitename,to_char(t1.dateperiod,'yyyy-mm-dd') as dateperiod ,t1.allnum as allnumber,
 nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  
 (select t.sitecode as sitecode,t.sitename as sitename,trunc(t.checkdate,'dd')  as datePeriod,count(*) as allnum from t_checkinfo t  where   
 t.checkdate between to_date('2013-12-27','yyyy-mm-dd') and  to_date('2014-01-03','yyyy-mm-dd')  group by 
 t.sitecode,t.sitename,trunc(t.checkdate,'dd')  ) t1  
 left outer join  
 (select t.sitecode as sitecode,t.sitename as sitename,trunc(t.checkdate,'dd')  as datePeriod,count(*) as overnum from t_checkinfo t   where   
 t.checkdate between to_date('2013-12-27','yyyy-mm-dd') and  to_date('2014-01-03','yyyy-mm-dd')   and t.ifoverload=2  group by 
 t.sitecode,t.sitename ,trunc(t.checkdate,'dd') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by 
 sitecode  ,dateperiod desc

 鲁，周，所有
 select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,
 nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  
 (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as allnum from t_checkinfo t  where   
 t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  group by 
 t.sitecode,t.sitename,to_char(t.checkdate,'iw')  ) t1 
 left outer join  
 (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as overnum from t_checkinfo t   where  
 t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')   and t.ifoverload=2  group by 
 t.sitecode,t.sitename ,to_char(t.checkdate,'iw') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by 
 sitecode  ,dateperiod desc

鲁，周，某区县
 select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,
 nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  
 (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as allnum from t_checkinfo t  where   
 t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.countycode='140602' group by 
 t.sitecode,t.sitename,to_char(t.checkdate,'iw')  ) t1  
 left outer join  
 (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as overnum from t_checkinfo t   where   
 t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.countycode='140602'  and t.ifoverload=2  group by 
 t.sitecode,t.sitename ,to_char(t.checkdate,'iw') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by 
 sitecode  ,dateperiod desc

 鲁，周，某站点
 select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,
 nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  
 (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as allnum from t_checkinfo t  where  
 t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.sitecode='1406021001' group by 
 t.sitecode,t.sitename,to_char(t.checkdate,'iw')  ) t1  
 left outer join  
 (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as overnum from t_checkinfo t   where   
 t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.sitecode='1406021001'  and t.ifoverload=2  group by 
 t.sitecode,t.sitename ,to_char(t.checkdate,'iw') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by 
 sitecode  ,dateperiod desc

 鲁，季度，所有
 select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,
 nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  
 (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'q')  as datePeriod,count(*) as allnum from t_checkinfo t  where   
 t.checkdate between to_date('2013-5-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  group by 
 t.sitecode,t.sitename,to_char(t.checkdate,'q')  ) t1  
 left outer join
 (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'q')  as datePeriod,count(*) as overnum from t_checkinfo t   where   
 t.checkdate between to_date('2013-5-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')   and t.ifoverload=2  group by 
 t.sitecode,t.sitename ,to_char(t.checkdate,'q') ) t2 on 
 t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by sitecode  ,dateperiod desc



 //按区域查询代码示例，现在有问题，稍后需要重新检查错误（这次对了）
  select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,
 nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  
 (select t.sitecode as sitecode,t.sitename as sitename,max(to_char(checkdate,'yyyy-mm-dd')) as datePeriod,count(*) as allnum from t_checkinfo t  where  
 t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.sitecode='1406021001' group by 
 t.sitecode,t.sitename  ) t1  
 left outer join  
 (select t.sitecode as sitecode,t.sitename as sitename,max(to_char(checkdate,'yyyy-mm-dd')) as datePeriod,count(*) as overnum from t_checkinfo t   where   
 t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.sitecode='1406021001'  and t.ifoverload=2  group by 
 t.sitecode,t.sitename ) t2 
on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename;



 测试程序里生成的sql语句
 select t1.sitename as sitename,t1.dateperiod as dateperiod ,  t1.allnum as allnumber,  nvl(t2.overnum,0) as overnumber,  
t1.alloverweightnum as alloverweight,  nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0) as overpercent,  t1.sitecode as sitecode  from 
(select t.sitecode as sitecode,t.sitename as sitename, to_char(t.checkdate,'yyyy')  as datePeriod, count(*) as allnum, 
sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight)) as alloverweightnum from 
t_checkinfo t  where 1=1  and t.sitecode in ('1406021001') and to_char(t.checkdate, 'yyyy') between '2012' and  '2013'  
group by t.sitecode,t.sitename, to_char(t.checkdate,'yyyy')   ) t1 
 left outer join  
 (select t.sitecode as sitecode,t.sitename as sitename, to_char(t.checkdate,'yyyy')   as datePeriod,
 count(*) as overnum, sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight)) as
  alloverweightnum from t_checkinfo t   where 1=1  and t.sitecode in ('1406021001')  and  t.ifoverload=2  and 
  to_char(t.checkdate, 'yyyy') between '2012' and  '2013'  
  group by t.sitecode,t.sitename ,  to_char(t.checkdate,'yyyy')  ) t2 on 
  t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by  sitecode  ,dateperiod desc


to_char(t.checkdate, 'yyyy') between '2012' and  '2013';

select t.overweight,t.checkdate from t_checkinfo t where t.overweight is not null and t.overweight!=0 and t.ifoverload=0 order by t.checkdate;

---------------------------------------------------------------------------------

select t.*, t.rowid from t_checkinfo t
对比类型compareType  按什么时间对比compareNum  开始时间结束时间  市区city  站点类型stationType  站点stations  分析内容compareContent
总吨数weight  超载率outOverPercent  生成图形imgType

select site.ctrl_site_name	as city,to_char(checkdate,'yyyy') as time,  count(t.checkid) as jiancenum,       	 sum(decode(t.ispunished,'0',0,1)) as chaozainum,   sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight))  as totalnum,                                               	 round(sum(decode(t.ispunished,'0',0,1)) / count(t.checkid) ,4)   * 100 as overrun   	 from t_checkinfo t , t_ctrl_site site , t_organization c where t.sitecode = site.ctrl_code  and c.o_orgcode = site.region_name and t.sitecode in ('1406022001') and site.region_name = '140602' and t.ifcertificate='0'  and t.overpercent >= 0.1 and t.weight >= 30.0 and (to_char(t.checkdate, 'yyyy') between '2012' and  '2013') group by site.ctrl_site_name,to_char(checkdate,'yyyy') order by to_char(checkdate,'yyyy') 


select site.ctrl_site_name	as city,to_char(checkdate,'yyyy') as time,  count(t.checkid) as jiancenum,       	 
sum(decode(t.ispunished,'0',0,1)) as chaozainum,   sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight))  
as totalnum,                                               	
 round(sum(decode(t.ispunished,'0',0,1)) / count(t.checkid) ,4)   * 100 as overrun   	 from 
 t_checkinfo t , t_ctrl_site site , t_organization c where 
 t.sitecode = site.ctrl_code  and c.o_orgcode = site.region_name and t.sitecode in ('1406022001') and site.region_name = '140602' and 
 t.ifcertificate='0'  and t.overpercent >= 0.1 and t.weight >= 30.0 and (to_char(t.checkdate, 'yyyy') between '2012' and  '2013') group by 
 site.ctrl_site_name,to_char(checkdate,'yyyy') order by to_char(checkdate,'yyyy') 

t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent

select t1.sitename as sitename,t1.dateperiod as dateperiod ,  t1.allnum as allnumber,  nvl(t2.overnum,0) as overnumber,  
t1.alloverweightnum as alloverweight,  nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0) as overpercent,  t1.sitecode as sitecode  from 
(select t.sitecode as sitecode,t.sitename as sitename, to_char(t.checkdate,'yyyy')  as datePeriod, count(*) as allnum, 
sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight)) as alloverweightnum from 
t_checkinfo t  where 1=1  and t.sitecode in ('1406021001') and t.checkdate between to_date('2012','yyyy-mm-dd') and  to_date('2013','yyyy-mm-dd')  
group by t.sitecode,t.sitename, to_char(t.checkdate,'yyyy')   ) t1 
 left outer join  
 (select t.sitecode as sitecode,t.sitename as sitename, to_char(t.checkdate,'yyyy')   as datePeriod,
 count(*) as overnum, sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight)) as
  alloverweightnum from t_checkinfo t   where 1=1  and t.sitecode in ('1406021001')  and  t.ifoverload=2  and 
  t.checkdate between to_date('2012','yyyy-mm-dd') and  to_date('2013','yyyy-mm-dd')  
  group by t.sitecode,t.sitename ,  to_char(t.checkdate,'yyyy')  ) t2 on 
  t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by  sitecode  ,dateperiod desc 


-------------------------------------------------------------------------------

汇总报表sql:
---按照站点，一段时间内的站点总统计
select t1.sitecode as sitecode,t1.sitename as sitename 
,t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent 
 from  (select t.sitecode as sitecode,t.sitename as sitename ,count(*) as allnum from t_checkinfo t  
 where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-07','yyyy-mm-dd')  
 and t.countycode='140602' group by t.sitecode,t.sitename  ) t1  
 left outer join 
  (select t.sitecode as sitecode,t.sitename as sitename ,count(*) as overnum from t_checkinfo t   
  where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-07','yyyy-mm-dd')  and t.countycode='140602'  
  and t.ifoverload=2  group by t.sitecode,t.sitename   ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename    order by sitecode   desc
---站点统计
不选择区县，统计一段时间内所有的站点：select t1.sitecode as sitecode,t1.sitename as sitename, t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename, count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')  group by t.sitecode,t.sitename ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename, count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')   and t.ifoverload=2  group by t.sitecode,t.sitename  ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename   order by sitecode  desc
选择区县，统计一段时间内容此区县下的站点：select t1.sitecode as sitecode,t1.sitename as sitename, t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename, count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')  and t.countycode='140622' group by t.sitecode,t.sitename ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename, count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')  and t.countycode='140622'  and t.ifoverload=2  group by t.sitecode,t.sitename  ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename   order by sitecode  desc
---区县统计：
统计所有区县：select t1.sitecode as sitecode,t1.sitename as sitename, t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename, count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')  and t.countycode='140622' group by t.sitecode,t.sitename ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename, count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')  and t.countycode='140622'  and t.ifoverload=2  group by t.sitecode,t.sitename  ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename   order by sitecode  desc
统计具体区县：select t1.countycode as countycode,t1.countyname as countyname, t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select countycode as countycode,t.countyname as countyname, count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')  and t.countycode='140624' group by t.countycode,t.countyname) t1  left outer join  (select countycode as countycode,t.countyname as countyname, count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')  and t.countycode='140624'  and t.ifoverload=2  group by t.countycode,t.countyname  ) t2 on t1.countycode=t2.countycode and t1.countyname=t2.countyname   order by countycode  desc
统计具体区县，带站点编号：select t1.countycode as countycode,t1.countyname as countyname, t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select countycode as countycode,t.countyname as countyname, count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')  and t.countycode='140624' and t.sitecode='1406242020' group by t.countycode,t.countyname) t1  left outer join  (select countycode as countycode,t.countyname as countyname, count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')  and t.countycode='140624' and t.sitecode='1406242020'  and t.ifoverload=2  group by t.countycode,t.countyname  ) t2 on t1.countycode=t2.countycode and t1.countyname=t2.countyname   order by countycode  desc


SuperHong123
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
煤矿瓦斯超限事故树分析
06-19
本文针对神华集团乌达矿业有限责任公司五虎山煤矿开采现状,在不考虑设备故障的情况下,找出导致瓦斯超限的基本事件,运用事故树分析方法,评价各个基本事件的结构重要度,并且根据结构重要度的不同分别采取预防措施,防止...
一些可以参考的文档集合14
xuejianxinokok的专栏
07-06 406
20230706IntersectionObserver支持两个参数：callback是当被监听元素的可见性变化时，触发的回调函数options是一个配置参数，可选，有默认的属性值。
基于 Flink 构建关联分析引擎的挑战和实践
http://www.54tianzhisheng.cn/
12-17 973
点击上方“zhisheng”，选择“设为星标”公众号（zhisheng）内回复：ffa可以获取到所有PPT 和视频随着云计算、大数据等新一代IT技术在各行业的深入应用，政企机构IT规...
谈谈阿里与谷歌的Java开发规范
hebeind100的博客
01-09 1910
无规矩不成方圆，编码规范就如同协议，有了Http、TCP等各种协议，计算机之间才能有效地通信，同样的，有了一致的编码规范，程序员之间才能有效地合作。道理大家都懂，可现实中的我们，经常一边吐槽别人的代码，一边写着被吐槽的代码，究其根本，就是缺乏遵从编码规范的意识！多年前，Google发布Google Java Style来定义Java编码时应遵循的规范；今年年初阿里则发布阿里巴巴Java 开发手册...
深入理解MySQL原理之一--如何提升查询SQL的性能
恰恰虎的博客
08-20 885
一、前言
超限高层抗震专项审查分析报告书.doc
06-23
超限高层抗震专项审查分析报告书
基于数理统计的余吾煤矿瓦斯超限事故发生规律及对策研究-论文
07-08
为分析瓦斯超限事故发生规律,以余吾煤矿2018年1月至2019年8月期间的瓦斯超限（预警）事故为基础,运用数理统计的方法,从事故发生班次、发生地点及预警时长等方面进行研究。结果表明:在0点班中的01:00—02:00和06:00—...
运用midas Building进行超限分析基本流程指导书
03-27
运用midas Building进行超限分析基本流程指导书
矿井瓦斯超限的原因分析及防范对策
06-25
针对郁山煤业有限公司33061回风巷掘进工作面掘进期间的一次瓦斯超限事故,分析了事故发生的原因,得出了事故的直接原因与间接原因,提出了针对性的防范对策,实现了安全掘进,确保了矿井的安全生产。
Docker笔记-搭建达梦Python环境（dmPython + SQLAlchemy）
IT1995的博客
05-20 275
达梦提供的C接口，dpi和java的jar包已经很好用了，想不到，来了一个用python的同事，这里就只能适应下他了，在不影响其他环境下搭建一个python的达梦环境。最后发现，python对进行达梦增删改查，还是比较简单的开发效率大于C的dpi，甚至感觉效率也不再java之下。仅仅是搞业务这块还是比较方便的，但搭建和部署，难度比C和Java复杂了很多，反正就是各有优劣吧。
Oracle数据库之PL/SQL基本语法（八）
pursue_mony的博客
05-14 112
PL/SQL 是 Oracle 数据库中使用的过程化 SQL 语言扩展，它允许你在 SQL 语句中嵌入控制结构、变量声明、异常处理等。用于在 PL/SQL 块中输出调试信息。为了看到这些信息，你需要在 SQL*Plus 或其他客户端中启用它（例如，使用。PL/SQL 代码通常被组织在块（block）中。一个块包含三个部分：声明部分、执行部分和异常处理部分。包是 PL/SQL 中一种将逻辑、变量、常量、游标、类型、子程序、异常等组合在一起的数据库对象。在 PL/SQL 中，你可以在。部分来处理运行时错误。
sql去除表中某个字段的空格
HelloWorld的专栏
05-20 51
比如表中名字字段，名字之间有空格，需要把空格去除，应该如何写SQL语句实现？函数来去除字符串两端的空格，如果需要去除字段中的所有空格，可以使用。以下是一个示例，假设我们有一个名为。的表，并且我们想要去除。在SQL中，可以使用。
SQL——SERVER的建表的基础知识
m0_74012211的博客
05-15 1315
SQL——SERVER的基本建表操作
byzer sql 详细参考
最新发布
gaohongfeng1的博客
05-20 92
数据加载 load excel load excel.`tmp/upload/exceltest.xls` where header="true" as hello_world; load csv load csv.`tmp/upload/taxi_tour_table_train_simple.csv` where delimiter="," and header = "true" as taxi_tour_table_train_simple;
写SQL的心得
weixin_64842400的博客
05-11 608
接着，系统按照 GROUP BY 子句中的指定字段分组，并对每个分组进行计算，生成虚拟的分组结果表。执行顺序 ：from > on > where > group by > having > select > distinct > order by > top。4、聚合函数本质：对指定的列进行聚合，如果我们用了group by，我们可以对每个分组应用内聚合函数。在分组内部，聚合函数会自动处理所有重复的行。具体来说，在执行 SELECT 子句时，系统先计算 SELECT 中的列表达式和函数等，然后。
sql实践
m0_56366541的博客
05-14 246
3.在select中使用if/case语句（from、where中也可以）在excel导入数据时要先在数据库中创建对应的数据库表。之后点击开始等待一会就结束了。选择文件，选择需要导入的表。2.尝试简单查询/复杂查询。1.从excel导入数据。6.存储过程中的本地变量。
SQL 中 HAVING 与 WHERE 的关系详解
良月柒
05-20 530
WHERE 子句用于在 SQL 查询中过滤记录，它在 SELECT、UPDATE、DELETE 等语句中使用，用于指定返回或操作的数据条件。WHERE 子句直接作用于表中的行数据。示例在此示例中，WHERE 子句过滤出部门为 "Sales" 的员工记录。理解和合理使用 WHERE 和 HAVING 子句对于编写高效的 SQL 查询至关重要。WHERE 子句用于在分组之前过滤数据，而 HAVING 子句用于在分组之后过滤聚合结果。
MySQL基础：SQL分类DDL、DML、DQL、DCL；函数、约束、多表查询、事务、并发事务四大问题、事务隔离级别——脏写、脏读、不可重复读、幻读
qq_44700578的博客
05-16 340
MySQL基础：SQL分类DDL、DML、DQL、DCL；函数、约束、多表查询、事务、并发事务四大问题、事务隔离级别——脏写、脏读、不可重复读、幻读
时间超限 AC:0%
12-25
4. 分析测试数据：对于时间超限的问题，可以分析测试数据的特点，找出导致程序运行时间过长的原因。可以通过调试和打印程序运行时间的方式来定位问题所在。 5. 并行计算：对于一些计算密集型的问题，可以考虑使用...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值