pgsql 쿼리 샘플

windows 함수

lead ,leg 함수  

 

select a.* from

(
select reg_dt,
stock_cd,
 cast(extract(epoch from  (reg_dt - (lag(reg_dt) OVER ( ORDER BY stock_cd,reg_dt)))) as integer) AS diff,
 lag(reg_dt) OVER (ORDER BY stock_cd,reg_dt desc) as diff_dt,
 fid_228

from kw_real_data krd 
where 1=1
-- and stock_cd='007570'
and DATE_TRUNC('day', reg_dt) = to_timestamp('20200918', 'YYYYMMDD')
and reg_dt between  to_timestamp('202009180900', 'YYYYMMDDHH24MI') and to_timestamp('202009181520', 'YYYYMMDDHH24MI')
) a
where diff> 200
and fid_228> 100
order by stock_cd,reg_dt