select
if(sys_cd="각인기", "타각기", concat(sys_cd," 비전")) as systemNm,
facl_cd as facilityNm,
date_format(min(gatr_dtm), "%Y-%m-%d %T") startDtm,
CASE WHEN COUNT(*) = COUNT(startDtm) THEN date_format(max(startDtm), "%Y-%m-%d %T") else null END endDtm
from ( select
*,
row_number() over (order by gatr_dtm) -
row_number() over (partition by facl_cd order by gatr_dtm) as groupWithFaclAndTime
from
(SELECT
gatr_dtm,
facl_cd,
sys_cd,
lead(facl_cd) over (order by gatr_dtm) nextFacility,
lead(gatr_dtm) over (order by gatr_dtm) startDtm
FROM welding.qlty_facl_bypass_stts_l
WHERE
jgmt_rslt_cd = "O"
and cast(gatr_dtm as datetime) >= cast(date_format("230712", "%Y-%m-%d 06:00:00") as datetime)
) log order by gatr_dtm d
카테고리 없음
sfa
728x90
반응형