하루y

Oralce 및 기타 여러 DB에서 중앙값 함수를 제공하지 않아서 로직 샘플 짬.

WITH GOODS AS (
          SELECT 'A' AS GOOD_NM, 'SELLER_1' AS SELLER, 110 AS PRICE_MIN, 310 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'A' AS GOOD_NM, 'SELLER_2' AS SELLER, 120 AS PRICE_MIN, 330 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'A' AS GOOD_NM, 'SELLER_3' AS SELLER, 130 AS PRICE_MIN, 320 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'A' AS GOOD_NM, 'SELLER_4' AS SELLER, 140 AS PRICE_MIN, 340 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'A' AS GOOD_NM, 'SELLER_5' AS SELLER, 150 AS PRICE_MIN, 350 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'B' AS GOOD_NM, 'SELLER_1' AS SELLER, 210 AS PRICE_MIN, 460 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'B' AS GOOD_NM, 'SELLER_2' AS SELLER, 220 AS PRICE_MIN, 410 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'B' AS GOOD_NM, 'SELLER_3' AS SELLER, 230 AS PRICE_MIN, 420 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'B' AS GOOD_NM, 'SELLER_4' AS SELLER, 240 AS PRICE_MIN, 450 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'B' AS GOOD_NM, 'SELLER_5' AS SELLER, 250 AS PRICE_MIN, 430 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'B' AS GOOD_NM, 'SELLER_6' AS SELLER, 260 AS PRICE_MIN, 440 AS PRICE_MAX FROM DUAL
) 
SELECT GOOD_NM
     , SUM(CASE WHEN MOD(GROUP_CNT, 2) <> 0 AND PRICE_MIN_SEQ = (TRUNC(GROUP_CNT/2) + 1)                THEN PRICE_MIN -- 홀수계산. 중앙값 계산
                WHEN MOD(GROUP_CNT, 2) =  0 AND PRICE_MIN_SEQ BETWEEN GROUP_CNT/2 AND (GROUP_CNT/2 + 1) THEN PRICE_MIN -- 짝수계산
               END) 
     / SUM(CASE WHEN MOD(GROUP_CNT, 2) <> 0 AND PRICE_MIN_SEQ = (TRUNC(GROUP_CNT/2) + 1)                THEN 1
                WHEN MOD(GROUP_CNT, 2) =  0 AND PRICE_MIN_SEQ BETWEEN GROUP_CNT/2 AND (GROUP_CNT/2 + 1) THEN 1
                END) AS PRICE_MIN_MEAN
     , SUM(CASE WHEN MOD(GROUP_CNT, 2) <> 0 AND PRICE_MAX_SEQ = (TRUNC(GROUP_CNT/2) + 1)                THEN PRICE_MAX -- 홀수계산. 중앙값 계산
                WHEN MOD(GROUP_CNT, 2) =  0 AND PRICE_MAX_SEQ BETWEEN GROUP_CNT/2 AND (GROUP_CNT/2 + 1) THEN PRICE_MAX -- 짝수계산
               END) 
     / SUM(CASE WHEN MOD(GROUP_CNT, 2) <> 0 AND PRICE_MAX_SEQ = (TRUNC(GROUP_CNT/2) + 1)                THEN 1
                WHEN MOD(GROUP_CNT, 2) =  0 AND PRICE_MAX_SEQ BETWEEN GROUP_CNT/2 AND (GROUP_CNT/2 + 1) THEN 1
                END) AS PRICE_MAX_MEAN
  FROM (
      SELECT A.*
           , ROW_NUMBER() OVER(PARTITION BY GOOD_NM ORDER BY PRICE_MIN) AS PRICE_MIN_SEQ
           , ROW_NUMBER() OVER(PARTITION BY GOOD_NM ORDER BY PRICE_MAX) AS PRICE_MAX_SEQ
           , COUNT(*)     OVER(PARTITION BY GOOD_NM)                    AS GROUP_CNT
        FROM GOODS A
       ) A
GROUP BY GOOD_NM

| 결과

가격이동평균은 일정기간 주간의 평균 가격을 뜻함.

5일 단순이동평균 = 오늘종가 + 1일전종가 ~ 4일전종가 / 5(데이터 수)

1) 소수점은 버린다.

2) 10, 20 이동평균선도 같은 방식으로 계산함.

3) 만약에 데이터가 부족하면, 부족한 데이터만으로 계산한다.

 - 5일 단순이동평균(2일만 있다면) = 오늘종가 + 1일전종가 / 2(데이터 수)

import math

move_avg_5 = math.floor((19300 + 20050 + 18650 + 18550 + 18400) / 5)
print(move_avg_5)

end.