常用sql

备份表

--创建表结构
create table dwd_wind_fact_downtime_20210709 as select * from dwd_wind_fact_downtime where 1=2;
--备份指定数据
insert into dwd_wind_fact_downtime_20210709 select * from dwd_wind_fact_downtime -- Where [条件1,条件2] 

时间线上最大间隔时间(pgsql)

with t1 as(
SELECT
	--r.region_name,
	--wf.site_name,
	--wt.feedline_name,
	--wt.wtg_type,
--	wt.wtg_name,
--	wt.on_grid_date,
	wtsr.start_time,
	wtsr.end_time,
	--wtsr.description,
	COALESCE ( wt.wtg_id_yj, wt.wtg_id ) AS wtg_id,
	--wt.feedline_id,
	wt.brand,
	ROW_NUMBER() OVER (PARTITION BY wt.brand ORDER BY wtsr.start_time)RowNum
FROM
	dim_wind_device wt
	LEFT JOIN (
	SELECT
		e.wtg_id wtg_id,
		e.start_time,
		COALESCE ( e.end_time, now( ) ) end_time,
		dwdr.description 
	FROM
		dwd_wind_fact_downtime e
		LEFT JOIN dim_wind_sc_template dwdr ON e.sc_id = dwdr.sc_id 
		AND e.plc_version = dwdr.plc_version 
	WHERE
		e.start_time >= '2021-01-01 00:00:00' 
		AND e.start_time <= '2021-07-07 00:00:00'
		AND (
			( e.end_time IS NULL AND e.start_time < now( ) - INTERVAL '10 minutes' ) 
			OR e.start_time < e.end_time - INTERVAL '10 minutes' 
		) 
		AND e.system_id IN ( 2 ) 
	ORDER BY
		e.wtg_id,
		e.start_time 
	) wtsr ON wt.wtg_id = wtsr.wtg_id
	LEFT JOIN ( SELECT dp.phase_id, dc.control_type FROM dim_wind_phase dp LEFT JOIN dim_system_company dc ON dp.company_id = dc.company_id ) dt ON wt.phase_id = dt.phase_id
	LEFT JOIN dim_wind_feedline wl ON wt.feedline_id = wl.feedline_id
	LEFT JOIN dim_wind_site wf ON wt.site_id = wf.site_id
	LEFT JOIN dim_wind_region r ON wf.state_name = r.region_name 
WHERE
	wt.wtg_id_yj IS NOT NULL 
	AND dt.control_type <> '3'
	--and wt.brand='联合动力'
	 --and wt.wtg_id='026C7RW1'
	 --ORDER BY wtsr.start_time
	--limit 100
	)
	,t3 as(--之间
	SELECT t1.*,t2.start_time n_start_time,date_part('epoch',t2.start_time-t1.end_time)/3600 diff from t1 JOIN t1 as t2 on t1.rownum+1=t2.rownum and t1.wtg_id=t2.wtg_id)
	,m1 as(--首
	SELECT DISTINCT on(brand)brand,start_time,end_time,date_part('epoch',start_time-'2021-01-01 00:00:00')/3600 diff from t3 ORDER BY brand,start_time asc)
	,m2 as(--尾
	SELECT DISTINCT on(brand)brand,start_time,end_time,date_part('epoch','2021-07-07 00:00:00'-end_time)/3600 diff from t3 ORDER BY brand,start_time desc)
	,t4 as(SELECT brand,start_time,end_time,diff from t3 UNION ALL SELECT * from m1 UNION ALL SELECT * from m2)
	SELECT * from (SELECT brand,max(diff)diff from t4 GROUP BY brand)t ORDER BY diff desc

分类汇总合并行

SELECT array_to_string(array_agg(state_name||'('||round(loss_power/10000,0)||')'),'、')主要限电地区 from t;