123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225 |
- --删除分区
- --先解除父表与分区表绑定关系
- 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')
|