이동평균 이평선

SELECT
			date(deal_date) deal_date
			,price_close
			, ROUND((CASE
			WHEN
			5 = COUNT(*)
			OVER (ORDER BY deal_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
			THEN
			AVG(SUM(price_close))
			OVER (ORDER BY deal_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
			END), 2)
			AS price_avg5
			, ROUND((CASE
			WHEN
			10 = COUNT(*)
			OVER (ORDER BY deal_date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)
			THEN
			AVG(SUM(price_close))
			OVER (ORDER BY deal_date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)
			END), 2)
			AS price_avg10
			, ROUND((CASE
			WHEN
			20 = COUNT(*)
			OVER (ORDER BY deal_date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
			THEN
			AVG(SUM(price_close))
			OVER (ORDER BY deal_date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
			END), 2)
			AS price_avg20
			, ROUND((CASE
			WHEN
			60 = COUNT(*)
			OVER (ORDER BY deal_date ROWS BETWEEN 59 PRECEDING AND CURRENT ROW)
			THEN
			AVG(SUM(price_close))
			OVER (ORDER BY deal_date ROWS BETWEEN 59 PRECEDING AND CURRENT ROW)
			END), 2)
			AS price_avg60
			,volume
			, ROUND((CASE
			WHEN
			5 = COUNT(*)
			OVER (ORDER BY deal_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
			THEN
			AVG(SUM(volume))
			OVER (ORDER BY deal_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
			END), 2)
			AS volume_avg5
			, ROUND((CASE
			WHEN
			20 = COUNT(*)
			OVER (ORDER BY deal_date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
			THEN
			AVG(SUM(volume))
			OVER (ORDER BY deal_date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)
			END), 2)
			AS volume_avg20
			, ROUND((CASE
			WHEN
			60 = COUNT(*)
			OVER (ORDER BY deal_date ROWS BETWEEN 59 PRECEDING AND CURRENT ROW)
			THEN
			AVG(SUM(volume))
			OVER (ORDER BY deal_date ROWS BETWEEN 59 PRECEDING AND CURRENT ROW)
			END), 2)
			AS volume_avg60
		
			FROM item_price
			where item_id = #{itemId}
				AND date_format(deal_date,'%Y%m%d')
					BETWEEN date_format(DATE_ADD(#{dealDate},INTERVAL -3 year), '%Y%m%d')
				AND date_format(#{dealDate},'%Y%m%d')
			GROUP BY deal_date, price_close, volume
			ORDER BY deal_date DESC
			limit 10

================================================

select
     item_id
	, date(deal_date) deal_date
	, date_format(deal_date,'%m') as month
	, date_format(deal_date,'%u') as week
	, day(deal_date) day
	, price_close
   , avg(price_close) over(ORDER BY deal_date desc ROWS BETWEEN 0 PRECEDING AND 4 FOLLOWING) price_avg5
   , avg(price_close) over(ORDER BY deal_date desc ROWS BETWEEN 0 PRECEDING AND 9 FOLLOWING) price_avg10
   , avg(price_close) over(ORDER BY deal_date desc ROWS BETWEEN 19 PRECEDING AND 19 FOLLOWING) price_avg20
   , avg(price_close) over(ORDER BY deal_date desc ROWS BETWEEN 59 PRECEDING AND 59 FOLLOWING) price_avg60
   from
   item_price
   where
   item_id = 028300
   order by deal_date desc

주식 쿼리 구할 때 사용함.