SQL查询连续两个时间段，以Hadoop为例

1、原理：

要实现连续两个时间段的数据查询，网上有很多，我也转载了别人写得比较好的文章。但是最简便的方法是用最原始的方法，将相同数据查询两遍，关联后将两个时间段的数据放在一行。

select a.*,b.列1，b.列2，b.列3
(select
time,id,列1,列2，列3
from 表
)a,
(select
time,id,列1,列2，列3
from 表
)b
where a.time = b.time+1(1个时间段)
and a.id=b.id

2、SQL实例（以Hadoop为例）

select * from(
 select 
 start_time as time,
 province,
 city,
 cgi,
 userlabel,
 vendor,
 nettype as 小区制式,
 RRC_CONNMAX  RRC最大连接数,
 round(UL_PRB_UTILRADIO,4) as 上行PRB利用率,
 round(DL_PRB_UTILRADIO,4) as 下行PRB利用率,
 round(eu0103,4) as 无线接通率,
 round(EU0223,4) as 无线掉线率,
 round(ERAB_NBRMEANESTAB_QCI1,4) as VoLTE语音话务量,
 round(EU0113,4) as "无线接通率(QCI=1)",
 round(EU0204,4) as "E-RAB掉线率(QCI=1)（小区级）",
 concat(is_gaofuhe,is_dijietong,is_gaodianxian,is_volte)  差小区类型
 from(
 select 
 T1.*,
 case when  (T1.nettype ='TDD' and ((T1.UL_PRB_UTILRADIO>0.5 or T1.DL_PRB_UTILRADIO>0.5) and T1.RRC_CONNMAX>300) ) then '高负荷' 
 when (t1.nettype ='FDD' and ((t1.UL_PRB_UTILRADIO>0.7 or t1.DL_PRB_UTILRADIO>0.7) and t1.RRC_CONNMAX>400) ) then '高负荷' 
 when (t1.nettype ='3D-MIMO' and ((t1.UL_PRB_UTILRADIO>0.8 or t1.DL_PRB_UTILRADIO>0.8) and t1.RRC_CONNMAX>500 )) then '高负荷' 
 else ''  end as is_gaofuhe,
 case when  ((t1.eu0103>0 and t1.eu0103<0.98) and t1.RRC_CONNMAX>100) and ( (t2.eu0103>0 and t2.eu0103<0.98) and t2.RRC_CONNMAX>100) then  '低接通' else '' end as  is_dijietong,
 case when ((t1.eu0223>0.02 and t1.eu0223<1) and t1.RRC_CONNMAX>100) and ((t2.eu0223>0.02 and t2.eu0223<1) and t2.RRC_CONNMAX>100 ) then  '高掉线' else '' end as  is_gaodianxian,
 case when ((t1.eu0113<0.95  or  t1.eu0204>0.05) and t1.ERAB_NBRMEANESTAB_QCI1>1) and  ((t2.eu0113<0.95  or  t2.eu0204>0.05) and t2.ERAB_NBRMEANESTAB_QCI1>1)  then  'volte' else '' end as  is_volte
from (select 
 b.start_time,a.province,a.city,a.cgi2 as cgi,b.user_label userlabel,a.vendor,case when  a.is4G3DMIMO ='是'  then '3D-MIMO'  else  nettype end as nettype,
 b.RRC_CONNMAX,
 decode(b.RRU_PuschPrbTot,0,0,b.RRU_PuschPrbAssn/b.RRU_PuschPrbTot) as UL_PRB_UTILRADIO,
 decode(b.RRU_PdschPrbTot,0,0,b.RRU_PdschPrbAssn/b.RRU_PdschPrbTot) as DL_PRB_UTILRADIO,
 decode(ERAB_NBRATTESTAB*RRC_ATTCONNESTAB,null,1,0,1,ERAB_NBRSUCCESTAB/ERAB_NBRATTESTAB*RRC_SUCCCONNESTAB/RRC_ATTCONNESTAB) as  eu0103,
 decode((CONTEXT_SUCCINITALSETUP+CONTEXT_NBRLEFT+HO_SUCCEXECINC+RRC_SUCCCONNREESTAB_NONSRCCELL),null,0,0,0,(CONTEXT_ATTRELENB-CONTEXT_ATTRELENB_NORMAL)/(CONTEXT_SUCCINITALSETUP+CONTEXT_NBRLEFT+HO_SUCCEXECINC+RRC_SUCCCONNREESTAB_NONSRCCELL)) as  EU0223,
 nvl(ERAB_NBRMEANESTAB_QCI1,0) as ERAB_NBRMEANESTAB_QCI1,
 decode(ERAB_NBRATTESTAB_QCI1*RRC_ATTCONNESTAB,null,1,0,1,ERAB_NBRSUCCESTAB_QCI1/ERAB_NBRATTESTAB_QCI1*RRC_SUCCCONNESTAB/RRC_ATTCONNESTAB) as EU0113,
 decode((ERAB_NBRLEFT_QCI1+ERAB_NBRSUCCESTAB_QCI1+ERAB_NBRHOINC_QCI1),null,0,0,0,(ERAB_NBRREQRELENB_QCI1-ERAB_NBRREQRELENB_NORMAL_QCI1+ERAB_HOFAIL_QCI1)/(ERAB_NBRLEFT_QCI1+ERAB_NBRSUCCESTAB_QCI1+ERAB_NBRHOINC_QCI1)) as EU0204
from chunjiebaozhang_2024_lte a left join
 ( select b.*,m.cgi,m.user_label from wxwy.f_l_c_eutrancelltdd_q  b left join wxwy.f_l_c_eutrancelltdd_tmp m
   on(b.eutrancelltdd_uk = m.unique_key)
	  where 1=1
				 and b.partitionday="$[:{day}]" 
				and b.start_time>='$[:{开始刻钟}]'         
                and b.start_time<='$[:{结束刻钟}]'
	 )b
  on(a.cgi2= b.cgi)
  )T1,(select 
 b.start_time,a.province,a.city,a.cgi2 as cgi,b.user_label userlabel,a.vendor,case when  a.is4G3DMIMO ='是'  then '3D-MIMO'  else  nettype end as nettype,
 b.RRC_CONNMAX,
 decode(b.RRU_PuschPrbTot,0,0,b.RRU_PuschPrbAssn/b.RRU_PuschPrbTot) as UL_PRB_UTILRADIO,
 decode(b.RRU_PdschPrbTot,0,0,b.RRU_PdschPrbAssn/b.RRU_PdschPrbTot) as DL_PRB_UTILRADIO,
 decode(ERAB_NBRATTESTAB*RRC_ATTCONNESTAB,null,1,0,1,ERAB_NBRSUCCESTAB/ERAB_NBRATTESTAB*RRC_SUCCCONNESTAB/RRC_ATTCONNESTAB) as  eu0103,
 decode((CONTEXT_SUCCINITALSETUP+CONTEXT_NBRLEFT+HO_SUCCEXECINC+RRC_SUCCCONNREESTAB_NONSRCCELL),null,0,0,0,(CONTEXT_ATTRELENB-CONTEXT_ATTRELENB_NORMAL)/(CONTEXT_SUCCINITALSETUP+CONTEXT_NBRLEFT+HO_SUCCEXECINC+RRC_SUCCCONNREESTAB_NONSRCCELL)) as  EU0223,
 nvl(ERAB_NBRMEANESTAB_QCI1,0) as ERAB_NBRMEANESTAB_QCI1,
 decode(ERAB_NBRATTESTAB_QCI1*RRC_ATTCONNESTAB,null,1,0,1,ERAB_NBRSUCCESTAB_QCI1/ERAB_NBRATTESTAB_QCI1*RRC_SUCCCONNESTAB/RRC_ATTCONNESTAB) as EU0113,
 decode((ERAB_NBRLEFT_QCI1+ERAB_NBRSUCCESTAB_QCI1+ERAB_NBRHOINC_QCI1),null,0,0,0,(ERAB_NBRREQRELENB_QCI1-ERAB_NBRREQRELENB_NORMAL_QCI1+ERAB_HOFAIL_QCI1)/(ERAB_NBRLEFT_QCI1+ERAB_NBRSUCCESTAB_QCI1+ERAB_NBRHOINC_QCI1)) as EU0204
from chunjiebaozhang_2024_lte a left join
 ( select b.*,m.cgi,m.user_label from wxwy.f_l_c_eutrancelltdd_q  b left join wxwy.f_l_c_eutrancelltdd_tmp m
   on(b.eutrancelltdd_uk = m.unique_key)
	  where 1=1
				 and b.partitionday="$[:{day}]" 
				and b.start_time>=substr(from_unixtime(unix_timestamp(concat(substr('$[:{开始刻钟}]',1,4),'-',substr('$[:{开始刻钟}]',5,2),'-',substr('$[:{开始刻钟}]',7,2),' ',substr('$[:{开始刻钟}]',9,2),':',substr('$[:{开始刻钟}]',11,2),':','00'))-15*60,'yyyyMMddHHmmss'),1)            
                and b.start_time<='$[:{结束刻钟}]'
	 )b
  on(a.cgi2= b.cgi)
  )T2
  where T2.start_time =substr(from_unixtime(unix_timestamp(concat(substr(T1.start_time,1,4),'-',substr(T1.start_time,5,2),'-',substr(T1.start_time,7,2),' ',substr(T1.start_time,9,2),':',substr(T1.start_time,11,2),':','00'))-15*60,'yyyyMMddHHmmss'),1)  
  and T1.cgi = T2.cgi
 )TT
   )M where  差小区类型 is not null and 差小区类型 !=''

这是我在实际工作中用到的sql，有点长，作用是判断连续两个刻钟的数据，如果两个刻钟都是差小区，则认为它是差小区。