일 주 월 날짜 구하기

select
item_id
, item_name
, deal_date
, price_open
, price_high
, price_low
, price_close
, floor(volume) volume
, compared1
, compared2
, compared3
, price_avg5
, ROUND(((price_close-compared2) / compared2 *100),2) as fluctuation
FROM(select
i.item_id
, i.item_name
, p.deal_date
, p.price_open
, p.price_high
, p.price_low
, p.price_close
, volume
, price_avg5
, lag(p.deal_date,1) over(partition by i.item_id order by p.deal_date) as compared1
, lag(p.price_close,1) over(partition by i.item_id order by p.deal_date) as compared2
, p.price_close - lag(p.price_close,1) over(partition by i.item_id order by p.deal_date) as compared3
from
item as i
JOIN item_price as p
on i.item_id = p.item_id
) t1
WHERE 1=1

전일대비 등락률

주식 구하기

select
item_id
, deal_date
, month
, week
, aa
, bb
from( 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
, RANK() OVER (PARTITION BY month ORDER BY date(deal_date) desc ) as aa
, RANK() OVER (PARTITION BY week ORDER BY date(deal_date) desc ) as bb
from item_price
where item_id = 032500
AND date_format(deal_date,'%Y%m%d')
BETWEEN date_format(DATE_ADD('20200131',INTERVAL -3 MONTH), '%Y%m%d')
AND date_format('20200131','%Y%m%d')
) t1
where
aa = 1
-- bb = 1
order by date(deal_date) desc;