테이블
id regdate pollu slope
1 1000 10
2 1300 20 여기부터 계산
위의 테이블은 임의로 설정한 것 입니다.
regdate는 시간을 초로 나타낸 것이고 (임의의 값을 넣음)
pollu는 미세먼지 농도를 측정한 값입니다.(또한 임의의 값)
데이터는 5분 주기로 계속 들어올 것입니다.
slope는 기울기를 계산한 값을 넣으려고 합니다.
기울기는 새로 들어올 데이터에서 현재 데이터를 빼려고 합니다.
따라서 id값이 1인 열 하나만 존재할때는 비교할 값이 없어서 1열의 기울기값은 존재하지 않습니다.
두번째 열부터 새로들어올 데이터(세번째 열)과의 값을 비교해서 기울기컬럼에 삽입하고싶습니다.
ex) (3번째열 pollu - 20) / (3번째열 regdate - 1300) = 2열의 slope에 값 삽입
쿼리문을 어떻게 작성해야 할지 감이 오질 않는데 도움을 주시면 감사하겠습니다.
WITH t AS ( SELECT 1 id, 1000 regdate, 10 pollu UNION ALL SELECT 2, 1300, 20 UNION ALL SELECT 3, 1600, 40 ) SELECT id, regdate, pollu , ROUND( (pollu - LAG(pollu ) OVER(ORDER BY regdate)) / (regdate - LAG(regdate) OVER(ORDER BY regdate)) , 3) slope1 , ROUND( (LEAD(pollu ) OVER(ORDER BY regdate) - pollu ) / (LEAD(regdate) OVER(ORDER BY regdate) - regdate) , 3) slope2 FROM t ; -- 이전값을 기준으로 할지 이후값을 기준으로 할지 고민해 보세요.
WITH t AS ( SELECT 1 id, 1000 regdate, 10 pollu UNION ALL SELECT 2, 1300, 20 UNION ALL SELECT 3, 1600, 40 UNION ALL SELECT 4, 1900, 70 ) -- 1. 한번 감싸서 평균 구하기 SELECT id, regdate, pollu , ROUND(slope, 3) slope , ROUND(AVG(slope) OVER(), 3) slope_avg FROM (SELECT id, regdate, pollu , (LEAD(pollu ) OVER(ORDER BY regdate) - pollu ) / (LEAD(regdate) OVER(ORDER BY regdate) - regdate) slope FROM t ) a ORDER BY regdate ;
WITH t AS ( SELECT 1 id, 1000 regdate, 10 pollu UNION ALL SELECT 2, 1300, 20 UNION ALL SELECT 3, 1600, 40 UNION ALL SELECT 4, 1900, 70 ) -- 2. 최고, 최저 기울기 구하면 기울기 평균과 같음.(발상의 전환) SELECT id, regdate, pollu , ROUND( (LEAD(pollu ) OVER(ORDER BY regdate) - pollu ) / (LEAD(regdate) OVER(ORDER BY regdate) - regdate) , 3) slope , ROUND( (MAX(pollu ) OVER() - MIN(pollu ) OVER() ) / (MAX(regdate) OVER() - MIN(regdate) OVER() ) , 3) slope_avg FROM t ORDER BY regdate ;