chengben/Scripts/指标.sql

--删除分区
--先解除父表与分区表绑定关系
alter table sqmdb_bak.sdrflow detach partition sqmdb_bak.sdrflow_20220429;
--再删除分区表
drop table sqmdb_bak.sdrflow_20220429

select pg_relation_filepath('sqmdb_bak.sdrflow_20220429');

--查看数据库大小
--select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='o2p' order by pg_relation_size(relid) desc

select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='sqmdb_bak' and relname like '%sdr%' order by pg_relation_size(relid) desc

--select pg_database_size('sqmmt')/1024/1024/1024 as gb;

select sdate,count(*) from o2p.sdr_0_4g_flow_cell_day group by sdate 

SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC ;

   select pg_database.datname, pg_database_size(pg_database.datname)/1024/1024/1024/1024::numeric AS size_tb from pg_database; 


select (regexp_split_to_array('127.134555.17','\.'))[2]::numeric 

select case 
when to_number((regexp_split_to_array('127.134555.17','\.'))[2],'000000') between 134554 and 134559 then '1' else '0' end

--北10：'北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南'


select date_trunc('day',sdate)  from sqmdb_cost.kuandai_ftth kf 

--到期合同
select * from hetong2 where date_part('day',edate::timestamp-now()::timestamp) <= 0;

select *,
extract(year from age(edate+'1 day',bdate))*12+
extract(month from age(edate+'1 day',bdate))+
extract(day from age(edate+'1 day',bdate))/30 as nmonth
from sqmdb_cost.hetong

select datname,pg_encoding_to_char(encoding) as encoding from pg_database;

with amount_type as (
select distinct report_id,smonth,period,period_type,
segment1,segment1_name,prov_code,prov_name,city_code,
city_name,grid_code,grid_name,a.sort_id,column_tag,amount,
b.type1,b.type2,b.type3,b.leixing1,b.leixing2 
from sqmdb_cost.jituanchengben_e a 
left join sqmdb_cost.dict_report49 b on b.sort_id=a.sort_id
where a.smonth='202111' and a.report_id='49' 
and ds_cn='归集+横向分摊+调差'  and prov_name='河北' 
)
select report_id,"period",smonth,city_name,leixing2,sum(amount) from amount_type
where leixing2 in ('修理费','铁塔租赁费' ) and "period"='2021-11Y' 
group by report_id,"period",smonth,city_name,leixing2 

select * from sqmdb_cost.station_hetong where station_sid =14900001104874 --有重复


--月份区间
select date_trunc('month',current_date - interval '1' month)
union
select date_trunc('month',current_date)


select t.*,dtc.cbzx from
(select sdate,city_operator,area_operator,site_addr_id,sum(total_cost) as total_cost from 
sqmdb_cost.tower2 where sdate='202203' group by sdate,city_operator,area_operator,site_addr_id) t
left join sqmdb_cost.dict_tower_cbzx dtc on t.site_addr_id=dtc.tower_code 


--bbu
select * from
(select * from sqmdb_cost.equip e) a
 join
(select * from sqmdb_cost.nenghao_bbu nb) b
on a.eqp_name=b.sitename

select date_trunc('month',to_date('202204','YYYYMM'))

select sdate,count(*) from sqmdb_cost.kuandai_ftth group by sdate order by sdate desc

select date_trunc('month',to_date('202203','yyyymm'))

select date_trunc('month',to_date('202203','yyyymm') + interval '1' month)

select * from sqmdb_cost.rpt_dport_cost_grid rdcg where smonth='202204' and grid_name like '%深泽%'

select to_char((to_date('202203','yyyymm') - interval '1 years'),'yyyy12')

select to_char((now() - interval '1 month'),'yyyymm')

select sdate,count(*) from sqmdb_cost.tower3 t group by sdate order by sdate desc

select sdate,count(*) from sqmdb_cost.kuandai_ftth kf group by sdate order by sdate desc

select max(cbzx_code) as mycode from sqmdb_cost.dict_grid_code where cbzx_code>9013040000

--update sqmdb_cost.dict_grid
--set sort_id=dcg.sort_id 
--from sqmdb_cost.dict_code_grid dcg 
--where sqmdb_cost.dict_grid.city_code=dcg.city_code

--update sqmdb_cost.rpt_baobiao_3_dzzl 
--set sort_id_city=dcg.sort_id 
--from sqmdb_cost.dict_code_grid dcg 
--where sqmdb_cost.rpt_baobiao_3_dzzl.city_code=dcg.city_code*100

select 
dcg.grid_code,
dcg.city_name,
dz.area_id,
dz.area_name,
dz.p_id
from sqmdb_cost.dict_code_grid dcg 
join sqmdb_cost.dict_zzjg dz on dcg.area_code=dz.area_id 

select distinct cname,dname from sqmdb_cost.hetong_new hn 

select date_trunc('month',now())

select * from
(select distinct area_code,grid_id,grid_name from sqmdb_cost.room) a
left join sqmdb_cost.dict_grid_code dgc on trim(a.grid_name)=trim(dgc.grid_name)
where dgc.grid_name is null

select station_sid,station_sname,tower_code from sqmdb_cost.station3 s where station_sid='14900001518654'


--合同--输出报表
select 
city_name as 地市名称,
sid as 合同编码,
sname as 合同名称,
cbzx as 合同成本中心,
idx_station_sid as 索引表局站ID,
idx_hetong_sid as 索引表合同编码,
stype as 合同校验
from sqmdb_cost.err_has_ht_no_station err
where not exists (select hetong_sid from sqmdb_cost.hetong_white hw where err.sid=hw.hetong_sid)
order by sort_id_city

select 
smonth,grid_code,
count(*) as total_num,
sum(case when stype='有合同有局站' then 1 else 0 end) as has_num,
sum(case when stype!='有合同有局站' then 1 else 0 end) as no_num
from sqmdb_cost.err_has_ht_no_station
group by smonth,grid_code


select dictid,count(*) from sqmdb_cost.dict group by dictid having count(*)>1

select * from sqmdb_cost.dict

--insert into sqmdb_cost.chengben_shengnei_m 
select 
smonth,
stype,
sname,
income,
cost_total,
cost_fix,
cost_liao
from sqmdb_cost.chengben_shengnei where smonth='202201'


--insert into sqmdb_cost.chengben_shengnei_m 
select cm.smonth,cm.stype,cm.sname,
cm.income-lm.income as income,
cm.cost_total-lm.cost_total as cost_total,
cm.cost_fix-lm.cost_fix as cost_fix,
cm.cost_liao-lm.cost_liao as cost_liao
from
(select * from sqmdb_cost.chengben_shengnei where smonth='202202') cm
join 
(select * from sqmdb_cost.chengben_shengnei where smonth='202201') lm
on cm.stype=lm.stype and cm.sname=lm.sname

select * from sqmdb_cost.station s where station_sid ='14900001060000'

select avg(nh_kwh) from sqmdb_cost.nenghao_rru nr 
where net_type='4G' and changjia='中兴' and smonth='202201'


--truncate table sqmdb_cost.equip 
--insert into sqmdb_cost.equip 
--select * from sqmdb_cost.equip_e 


SELECT 
gusuan_3g.cell_id,
room.station_sid,
61.6681 AS nh_kwh
FROM gusuan_3g,
room,
roomid
WHERE gusuan_3g.room_id = roomid.room_id AND room.room_sid = roomid.room_sid


select smonth,changjia,net_type,count(*) as cell_num from sqmdb_cost.nenghao_rru 
where changjia='中兴' group by smonth,changjia,net_type order by smonth desc


select * from sqmdb_cost.station_hetong sh where station_sid ='14900001078418'

select stype,count(*) from 
(select * from sqmdb_cost.tower3 t 
where sdate='202206' and city_operator='邯郸市' and area_operator like '%成安%') a
group by stype 


SELECT distinct station_sid,city_code*100 as city_code,area_code FROM sqmdb_cost.station s 

select sdate,count(*) from sqmdb_cost.kuandai_ftth group by sdate order by sdate 

select * from sqmdb_cost.kuandai_ftth kf where sdate=to_date('20220809','yyyymmdd')