chengben/Scripts/工参治理业务量.sql

--drop FOREIGN TABLE sqmdb_bak.sdrflow_daily_e
CREATE FOREIGN TABLE sqmdb_bak.sdrflow_daily_e (
	starttime numeric NULL,
	ran_ne_id varchar NULL,
	cgisai varchar NULL,
	rat numeric NULL,
	l4_ul_throughput numeric NULL,
	l4_dl_throughput numeric NULL,
	layer2id numeric NULL,
	layer3id numeric NULL,
	od varchar NULL
)
SERVER pg_file_server
OPTIONS (filename '/data/jyc/SDR_FLOW_CELL/sdrforput.txt', format 'text', header 'false', delimiter '|', null '');

CREATE TABLE sqmdb_bak.sdrflow_daily (
	starttime numeric NULL,
	ran_ne_id varchar NULL,
	cgisai varchar NULL,
	rat numeric NULL,
	l4_ul_throughput numeric NULL,
	l4_dl_throughput numeric NULL,
	layer2id numeric NULL,
	layer3id numeric NULL,
	od varchar NULL,
	sdate timestamp NULL
)


select sum(l4_dl_throughput +l4_ul_throughput)/1024/1024/1024/1024 as flow_tb from sqmdb_bak.sdrflow_check  where rat=9 and sdate between '2022-04-14' and '2022-04-20';

select date_trunc('day',sdate) as sdate,sum(l4_dl_throughput +l4_ul_throughput)/1024/1024/1024/1024 as flow_tb from sqmdb_bak.sdrflow_check  where rat=9 group by date_trunc('day',sdate);



select sdate,rat,sum(l4_dl_throughput +l4_ul_throughput)/1024/1024/1024/1024 as flow_tb from sqmdb_bak.sdrflow_check group by sdate,rat;



select date_trunc('day',sdate) as sdate,sum(l4_dl_throughput +l4_ul_throughput)/1024/1024/1024/1024 as flow_tb from sqmdb_bak.sdrflow  where rat=9 and sdate between '2022-05-05' and '2022-05-05' group by date_trunc('day',sdate);
     

select sum(THROUGHPUT)/1024/1024/1024/1024 as flow_tb 
from 
(select distinct city_name, cell_id, gnbid, THROUGHPUT
from rpt_1_5g_flow_cell_7d_day
where sdate = (select max(sdate) from rpt_1_5g_flow_cell_7d_day)) a
                   
                   
select 
sum(ul_throughput_m+dl_throughput_m)/1024/1024/1024/1024 as flow_tb
from o2p.sdr_1_5g_flow_base_day sgfbd 
where sdate between '2022-04-14' and '2022-04-20'

--truncate table sqmdb_bak.sdrflow_check

select sum(l4_dl_throughput +l4_ul_throughput)/1024/1024/1024/1024 as flow_tb 
from sqmdb_bak.sdrflow  where sdate between '2022-04-14' and '2022-04-20' and rat=9;


select to_char(date_trunc('day',sdate),'yyyymmdd'),cast(cast(('x'||substring(cgisai,6,5)) as bit(20)) as int) as enbid,cast(cast(('x'||substring(cgisai,11,2)) as bit(8)) as int) as cellid,rat,layer2id,'' as region_name,layer3id,'' as county_name,sum(l4_dl_throughput) as l4_dl_throughput,sum(l4_ul_throughput) as l4_ul_throughput,sum(l4_ul_throughput+l4_dl_throughput) as throughput from sqmdb_bak.sdrflow_daily where rat=6 and length(cgisai)=12 and sdate>=date_trunc('day',now())::timestamp + '-2 day' and sdate<date_trunc('day',now())::timestamp + '-1 day' group by date_trunc('day',sdate),cgisai,layer2id,layer3id,rat


select enbid,
         cell_id,
         max(city_name) city_name,
         sum(throughput) throughput
    from sdr_0_4g_flow_cell_day k
    left join (select distinct city_code,city_name from cfg_0_city_conf  where city_code is not null) t
      on (k.layer2id = t.city_code)
   where k.sdate between '20220718'  and '20220718' and k.enbid>0 and city_name is not null
   group by enbid, cell_id