Existing a power outage information table, a CIRCUIT_NAME power lines, START_TIME outage start time, END_TIME blackouts over time, ORGNAME power substation, SPORGNAME city state company, PORGNAME area county, EQNM device name, DEV_POWER_CUT power equipment and other fields, line outage three times to query two months now and more than three times the power outage information, how to write the SQL? Teach bosses, table specific fields is as follows:

CodePudding user response:

Is from the specified point in time, 2 months or 2 months interval greater than 2 times?

CodePudding user response:

Any two months, this two months is dynamic, is not specified,

CodePudding user response:

I wrote a SQL, but left the conditions to join in any two months,
Select a t1. *, t2. Cs
The from (select porgname, sporgname circuit_name, eqnm, start_time, end_time
The from t1 t_pw_tdxx)
Left the join (select eqnm, count (*) cs
The from t_pw_tdxx
The where (circuit_name like '% line or circuit_name like' % line or
Rod breaker circuit_name like '%')
Line or (eqnm like '%' or eqnm like '% line or eqnm like' % rod breaker ')
And ceil (to_number (SUBSTR (to_char ((end_time - start_time) * 24 * 60),
2,
10))) & gt;=0
Group by eqnm) t2
On t1. Eqnm=t2. Eqnm
Where t2. Cs & gt;=3

CodePudding user response:

 with tab1 as (
Select the 'a' id, the date '2020-01-02' dat the from dual union all 
Select the 'a' id, the date '2020-01-03' the from dual union all 
Select the 'a' id, the date '2020-01-04' the from dual union all 
Select the 'a' id, the date '2020-01-05' the from dual union all 
Select 'b' id, the date '2020-01-02' the from dual union all 
Select 'b' id, the date '2020-04-02' the from dual 
) 
Tab2 as (
Select a t1. *, 
Count (1) over (partition by t1. The id order by t1. Dat range between interval '0' the month preceding the and interval '2' in the month following) cot 
The from tab1 t1 
) 
Select a t1. Id from tab2 t1 
Group by t1. Id 
Having a count (case when t1. Cot & gt; 2 then one else null end) & gt; 0