ORACLE with使用

with 

per as (select sum(PERSON_NUM) as SUM_PERSON_NUM from PERSON_NUM  WHERE APP_ORG_ID='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-01', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-30', 'yyyy-mm-dd') ),

a as (select sum("广东"*p.person_num) as sh from region ph,PERSON_NUM p where ph.app_org_id=p.app_org_id and ph.start_date=p.start_date and  ph.app_org_id='memedai' AND To_date(p.START_DATE,'yyyy-mm-dd')>=To_date('2016-06-01', 'yyyy-mm-dd') AND to_date(p.END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-30', 'yyyy-mm-dd') )

 select a.sh/per.SUM_PERSON_NUM from per,a;

 

with 

datep as (select min(START_DATE) mindate,max(END_DATE) as maxdate from BLACK_NUM ),

a as (select ORG_ACCOUNT,to_char(To_date(START_DATE,'yyyy-mm-dd'),'yyyy') as yearsep ,

                        to_char(To_date(START_DATE,'yyyy-mm-dd'),'mm') as monthsep,

              avg(LOGIN_FAIL_RATE) as LOGIN_FAIL_RATE  from LOGIN_FAIL_RATE 

      WHERE ORG_ACCOUNT='memedai' 

      GROUP BY ORG_ACCOUNT,

      to_char(To_date(START_DATE,'yyyy-mm-dd'),'yyyy'),

      to_char(To_date(START_DATE,'yyyy-mm-dd'),'mm')

      )

select a.* ,To_date(a.yearsep || '-' || a.monthsep ||'-01','yyyy-mm-dd') as cdate

from a,datep

where To_date(a.yearsep || '-' || a.monthsep ||'-01','yyyy-mm-dd') between To_date(mindate,'yyyy-mm-dd') and To_date(maxdate,'yyyy-mm-dd')

order by a.ORG_ACCOUNT,a.yearsep desc ,a.monthsep desc;

 

 

 

with 

a as (select avg(LOGIN_FAIL_RATE) as LOGIN_FAIL_RATE  from LOGIN_FAIL_RATE WHERE ORG_ACCOUNT='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-13', 'yyyy-mm-dd') ),

b as (select avg(RESET_FAIL_RATE) as RESET_FAIL_RATE  from RESET_FAIL_RATE  WHERE ORG_ACCOUNT='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-13', 'yyyy-mm-dd') ),

c as (select avg(CONSUME_FAIL_RATE) as CONSUME_FAIL_RATE from CONSUME_FAIL_RATE  WHERE APP_ORG_ID='memedai' AND To_date(CDATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(CDATE, 'yyyy-mm-dd')<=to_date('2016-06-13', 'yyyy-mm-dd') group by APP_ORG_ID ),

d as (select avg(BLACK_NUM) as BLACK_NUM from BLACK_NUM  WHERE ORG_ACCOUNT='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-13', 'yyyy-mm-dd') ),

e as (select avg(BLACK_RATE) as BLACK_RATE  from BLACK_RATE  WHERE ORG_ACCOUNT='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-13', 'yyyy-mm-dd') ),

f as (select avg(IDCARD_NUM) as IDCARD_NUM from IDCARD_NUM  WHERE ORG_ACCOUNT='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-13', 'yyyy-mm-dd') ),

g as (select avg(NEW_USER) as NEW_USER from NEW_USER  WHERE ORG_ACCOUNT='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-13', 'yyyy-mm-dd') ),

h as (select avg(NEW_PHONE) as NEW_PHONE  from NEW_PHONE  WHERE ORG_ACCOUNT='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-13', 'yyyy-mm-dd') ),

i as (select avg(TRAVEL_TIMES) as TRAVEL_TIMES from TRAVEL_TIMES  WHERE ORG_ACCOUNT='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-13', 'yyyy-mm-dd') ),

j as (select avg(TRAVEL_DURING) as TRAVEL_DURING from TRAVEL_DURING  WHERE ORG_ACCOUNT='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-13', 'yyyy-mm-dd') ),

k as (select avg(SAME_IDCARD_RATE) as SAME_IDCARD_RATE from SAME_IDCARD_RATE  WHERE ORG_ACCOUNT='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-13', 'yyyy-mm-dd') ),

l as (select avg(SAME_PHONE_RATE) as SAME_PHONE_RATE from SAME_PHONE_RATE  WHERE ORG_ACCOUNT='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-13', 'yyyy-mm-dd') ),

m as (select avg(QIANXUN_ORG_NUM) as QIANXUN_ORG_NUM from QIANXUN_ORG_NUM  WHERE ORG_ACCOUNT='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-13', 'yyyy-mm-dd') ),

n as (select avg(NO_REAL_NAME_NUM) as NO_REAL_NAME_NUM from NO_REAL_NAME_NUM WHERE ORG_ACCOUNT='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-13', 'yyyy-mm-dd') ),

o as (select avg(PHONE_USE_TIME) as PHONE_USE_TIME from PHONE_USE_TIME  WHERE ORG_ACCOUNT='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-13', 'yyyy-mm-dd') ),

p as (select avg(PHONE_MONTH_BILL) as PHONE_MONTH_BILL from PHONE_MONTH_BILL WHERE ORG_ACCOUNT='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-13', 'yyyy-mm-dd') )

select a.*,b.*,c.*,d.*,e.*,f.*,g.*,h.*,i.*,j.*,k.*,l.*,m.*,n.*,o.*,p.* 

from a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p

 

 

 

with 

a as (select ROUND(avg("18-22"),2) as age18_22 from AGE WHERE app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-05-14', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-05-21', 'yyyy-mm-dd') group by app_org_id ),

b as (select ROUND(avg("23-26"),2) as age23_26 from AGE WHERE app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-05-14', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-05-21', 'yyyy-mm-dd') group by app_org_id ),

c as (select ROUND(avg("27-30"),2) as age27_30 from AGE WHERE app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-05-14', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-05-21', 'yyyy-mm-dd') group by app_org_id ),

d as (select ROUND(avg("31-34"),2) as age31_34 from AGE WHERE app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-05-14', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-05-21', 'yyyy-mm-dd') group by app_org_id ),

e as (select ROUND(avg("35-38"),2) as age35_38 from AGE WHERE app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-05-14', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-05-21', 'yyyy-mm-dd') group by app_org_id ),

f as (select ROUND(avg("39-42"),2) as age39_42 from AGE WHERE app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-05-14', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-05-21', 'yyyy-mm-dd') group by app_org_id ),

g as (select ROUND(avg("43-46"),2) as age43_46 from AGE WHERE app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-05-14', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-05-21', 'yyyy-mm-dd') group by app_org_id ),

h as (select ROUND(avg("46"),2) as age46 from AGE WHERE app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-05-14', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-05-21', 'yyyy-mm-dd') group by app_org_id )

select a.age18_22,b.age23_26,c.age27_30,d.age31_34,e.age35_38,f.age39_42,g.age43_46,h.age46 from a,b,c,d,e,f,g,h ;

 

with 

a as (select ROUND(avg("新疆"),16) as "新疆" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

b as (select ROUND(avg("西藏"),16) as "西藏" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

c as (select ROUND(avg("内蒙古"),16) as "内蒙古" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

d as (select ROUND(avg("青海"),16) as "青海" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

e as (select ROUND(avg("四川"),16) as "四川" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

f as (select ROUND(avg("黑龙江"),16) as "黑龙江" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

g as (select ROUND(avg("甘肃"),16) as "甘肃" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

h as (select ROUND(avg("云南"),16) as "云南" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

i as (select ROUND(avg("广西"),16) as "广西" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

j as (select ROUND(avg("湖南"),16) as "湖南" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

k as (select ROUND(avg("陕西"),16) as "陕西" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

l as (select ROUND(avg("河北"),16) as "河北" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

m as (select ROUND(avg("湖北"),16) as "湖北" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

n as (select ROUND(avg("吉林"),16) as "吉林" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

o as (select ROUND(avg("广东"),16) as "广东" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

p as (select ROUND(avg("贵州"),16) as "贵州" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

q as (select ROUND(avg("河南"),16) as "河南" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

r as (select ROUND(avg("江西"),16) as "江西" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

s as (select ROUND(avg("山西"),16) as "山西" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

t as (select ROUND(avg("山东"),16) as "山东" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

u as (select ROUND(avg("辽宁"),16) as "辽宁" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

v as (select ROUND(avg("安徽"),16) as "安徽" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

w as (select ROUND(avg("福建"),16) as "福建" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

x as (select ROUND(avg("浙江"),16) as "浙江" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

y as (select ROUND(avg("江苏"),16) as "江苏" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

z as (select ROUND(avg("重庆"),16) as "重庆" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

aa as (select ROUND(avg("宁夏"),16) as "宁夏" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

bb as (select ROUND(avg("台湾"),16) as "台湾" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

cc as (select ROUND(avg("海南"),16) as "海南" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

dd as (select ROUND(avg("北京"),16) as "北京" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

ee as (select ROUND(avg("天津"),16) as "天津" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

ff as (select ROUND(avg("上海"),16) as "上海" from region where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id )

select a.*,b.*,c.*,d.*,e.*,f.*,g.*,h.*,i.*,j.*,k.*,l.*,m.*,n.*,o.*,p.*,q.*,r.*,s.*,t.*,u.*,v.*,w.*,x.*,y.*,z.*,aa.*,bb.*,cc.*,dd.*,ee.*,ff.* from a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,aa,bb,cc,dd,ee,ff

 

with 

a as (select avg("男") as 男 from sexuality where org_account='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-07-01', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-07-31', 'yyyy-mm-dd')),

b as (select avg("女") as 女 from sexuality where org_account='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-07-01', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-07-31', 'yyyy-mm-dd'))

select a.*,b.* from a,b

 

 

with 

a as (select ROUND(avg("新疆"),16) as "新疆" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

b as (select ROUND(avg("西藏"),16) as "西藏" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

c as (select ROUND(avg("内蒙古"),16) as "内蒙古" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

d as (select ROUND(avg("青海"),16) as "青海" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

e as (select ROUND(avg("四川"),16) as "四川" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

f as (select ROUND(avg("黑龙江"),16) as "黑龙江" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

g as (select ROUND(avg("甘肃"),16) as "甘肃" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

h as (select ROUND(avg("云南"),16) as "云南" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

i as (select ROUND(avg("广西"),16) as "广西" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

j as (select ROUND(avg("湖南"),16) as "湖南" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

k as (select ROUND(avg("陕西"),16) as "陕西" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

l as (select ROUND(avg("河北"),16) as "河北" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

m as (select ROUND(avg("湖北"),16) as "湖北" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

n as (select ROUND(avg("吉林"),16) as "吉林" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

o as (select ROUND(avg("广东"),16) as "广东" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

p as (select ROUND(avg("贵州"),16) as "贵州" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

q as (select ROUND(avg("河南"),16) as "河南" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

r as (select ROUND(avg("江西"),16) as "江西" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

s as (select ROUND(avg("山西"),16) as "山西" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

t as (select ROUND(avg("山东"),16) as "山东" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

u as (select ROUND(avg("辽宁"),16) as "辽宁" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

v as (select ROUND(avg("安徽"),16) as "安徽" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

w as (select ROUND(avg("福建"),16) as "福建" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

x as (select ROUND(avg("浙江"),16) as "浙江" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

y as (select ROUND(avg("江苏"),16) as "江苏" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

z as (select ROUND(avg("重庆"),16) as "重庆" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

aa as (select ROUND(avg("宁夏"),16) as "宁夏" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

bb as (select ROUND(avg("台湾"),16) as "台湾" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

cc as (select ROUND(avg("海南"),16) as "海南" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

dd as (select ROUND(avg("北京"),16) as "北京" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

ee as (select ROUND(avg("天津"),16) as "天津" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id ),

ff as (select ROUND(avg("上海"),16) as "上海" from phLocation where app_org_id='memedai' AND To_date(START_DATE,'yyyy-mm-dd')>=To_date('2016-06-13', 'yyyy-mm-dd') AND to_date(END_DATE, 'yyyy-mm-dd')<=to_date('2016-06-15', 'yyyy-mm-dd') group by app_org_id )

select a.*,b.*,c.*,d.*,e.*,f.*,g.*,h.*,i.*,j.*,k.*,l.*,m.*,n.*,o.*,p.*,q.*,r.*,s.*,t.*,u.*,v.*,w.*,x.*,y.*,z.*,aa.*,bb.*,cc.*,dd.*,ee.*,ff.* from a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,aa,bb,cc,dd,ee,ff