sql查询求教

[wangyi394527]

现有一张停电信息表，有CIRCUIT_NAME停电线路、START_TIME停电开始时间、END_TIME停电结束时间、ORGNAME供电所、SPORGNAME市州公司、PORGNAME区县公司、EQNM设备名称、DEV_POWER_CUT停电设备等字段，现在要查询两个月内线路停电三次及三次以上的停电信息，这个SQL要怎么写？望大佬们指教。表具体字段如下：

[nayi_224]

with tab1 as(
select 'a' id, date'2020-01-02' dat from dual union all
select 'a' id, date'2020-01-03' from dual union all
select 'a' id, date'2020-01-04' from dual union all
select 'a' id, date'2020-01-05' from dual union all
select 'b' id, date'2020-01-02' from dual union all
select 'b' id, date'2020-04-02' from dual 
)
, tab2 as (
select t1.*,
       count(1) over(partition by t1.id order by t1.dat range between interval '0' month preceding and interval '2' month following) cot
  from tab1 t1
 )
select t1.id from tab2 t1 
 group by t1.id
having count(case when t1.cot > 2 then 1 else null end) > 0

[nayi_224]

是距指定时间点2个月内，还是任意2个月的时间间隔内大于2次的？

[wangyi394527]

任意的两个月，这两个月是动态的，不是指定的。

[wangyi394527]

我写了一个SQL，但是没有把任意两个月的条件加入进去。 select t1.*, t2.cs from (select porgname, sporgname, circuit_name, eqnm, start_time, end_time from t_pw_tdxx) t1 left join (select eqnm, count(*) cs from t_pw_tdxx where (circuit_name like '%线' or circuit_name like '%线路' or circuit_name like '%杆断路器') or (eqnm like '%线' or eqnm like '%线路' or eqnm like '%杆断路器') and ceil(to_number(SUBSTR(to_char((end_time - start_time) * 24 * 60), 2, 10))) >= 0 group by eqnm) t2 on t1.eqnm = t2.eqnm where t2.cs >= 3