테이블 행의 계산 1 10 663

by 컴린이 [SQL Query] [2021.10.21 13:42:25]


테이블

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에 값 삽입

쿼리문을 어떻게 작성해야 할지 감이 오질 않는데 도움을 주시면 감사하겠습니다.

by jkson [2021.10.21 13:48:21]

음.. 그렇다면 id 1도 id 2의 데이터로 slope 계산이 가능한 거 아닌가요? 사용하시는 DB 종류는요?


by 컴린이 [2021.10.21 13:53:50]

아 네 맞습니다. 잘못 생각했네요 제가. Mysql 입니다


by 컴린이 [2021.10.21 13:54:05]

lead함수를 통해 다음행의 값을 가져오면 될까요?


by jkson [2021.10.21 13:59:14]

정답~


by 컴린이 [2021.10.21 14:06:41]

lead함수를 봤는데 좀 어려운거 같아서 예시를 들어주실 수 있을까요 ㅠㅠ


by 마농 [2021.10.21 14:29:35]
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
;
-- 이전값을 기준으로 할지 이후값을 기준으로 할지 고민해 보세요.

 


by 컴린이 [2021.10.21 14:47:22]

감사합니다. 이후 값을 기준으로 기울기 값이 나오네요 !

혹시 그 값을 데이터베이스에 insert하려면 어떻게 해야하나요:?


by 마농 [2021.10.21 15:03:07]

insert 는 레코드를 입력할 때 하는 거구요.
빈 컬럼에 값을 넣는 것은 update 를 이용해야죠.
일괄 업데이트는 최초 한번만 수행하면 될 것이고
이후에는 insert 가 수행될 때마다 트리거 형태로 동작이 되어야 할 것입니다.
그런데. 이 걸 꼭 해야 하는 건지도 검토가 필요합니다.
select 를 통해 구할 수 있는 항목(추출속성)을 굳이 저장해야 하는지?


by 컴린이 [2021.10.21 19:47:32]

lead함수를 통해 나온 기울기값들의 합을 구해서 평균을 내고 싶습니다... 


by 마농 [2021.10.22 08:17:24]
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
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입