select
facl_nm,
date_format(min(gatr_dtm), "%Y-%m-%d %T") startDtm,
CASE WHEN COUNT(*) = COUNT(dt) THEN date_format(max(dt), "%Y-%m-%d %T") else null END endDtm
from
(
select
*,
row_number() over(order by facl_nm, gatr_dtm) -
row_number() over(partition by facl_nm order by gatr_dtm) + if(jgmt_rslt_cd="O" and nextStatus="X",
row_number() over(partition by facl_nm, jgmt_rslt_cd order by jgmt_rslt_cd, gatr_dtm),0) grp
from (
select
gatr_dtm,
facl_nm,
jgmt_rslt_cd,
lead(jgmt_rslt_cd) over(partition by facl_nm order by gatr_dtm) nextStatus,
lead(gatr_dtm) over(partition by facl_nm order by gatr_dtm) dt
from welding.qlty_facl_bypass_stts_l where cast(gatr_dtm as datetime) >= cast(date_format("230712", "%Y-%m-%d 16:00:00") as datetime)
order by gatr_dtm
) A
where jgmt_rslt_cd = "O"
order by facl_nm, gatr_dtm) A group by grp
카테고리 없음
오늘의 쿼리
728x90
반응형