Kosta 클라우드 네이티브 애플리케이션 개발 (CNA) 교육 8일차

-------------------------------------

-- 기간내 종목 정보 출력

desc kospi;

-------------------------------------
-- 기간 내 종목 당 거래량
-- 거래량 높은 순 출력

select tname,sum(vol) 
from kospi
group by tname
order by sum(vol) desc;

-------------------------------------
-- 투자기간(일)
-- 2022-01-31 ~ 2023-06-30

select tname, ticker,max(rdate) - min(rdate) as 기간
, tname
from kospi
group by tname,ticker;

-------------------------------------------------

-- 각 종목별 투자기간(일)

select tname,
        max(cprice) as 고가, 
        min(cprice) as 저가,
        max(cprice) - min(cprice) as 차이
from kospi
group by tname;

-------------------------------------------------

-- 각 종목별 최대/최소   종가
-- 등락(차액)이 큰 순 출력

select tname, 고가, 저가, 고가 - 저가 as 차이 
from(
    select tname,
        max(cprice) as 고가, 
        min(cprice) as 저가
    from kospi
    group by tname)
order by 차이;

-------------------------------------------------

-- 각 종목별 최근   종가

select s.tname, s.rdate, k.cprice
from kospi k, 
            (select tname, max(rdate) as rdate 
                from kospi 
                group by tname) s                       
where s.tname = k.tname and s.rdate = k.rdate
;

--
select ticker, tname, cprice, rdate
from (
        select ticker, tname, cprice, rdate
               --, RANK() OVER (PARTITION BY ticker ORDER BY rdate DESC) RANK 
               , ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY rdate DESC) RANK 
        from kospi
    ) 
where rank = 1    
;

-------------------------------------------------
-- 각 종목별 상승, 하락 횟수
-- 전일 기준으로 해야하나 편의상 상승/하락은 아래 기준으로 한다.
-- 상승 : 시가 <= 종가
-- 하락 : 시가 > 종가

select tname, oprice - cprice as ud
from kospi
group by tname, rdate, oprice - cprice;

select ticker, tname ,
        sum(case when ud > 0 then 1 else 0 end) as 상승 ,
        sum(case when ud <= 0 then 1 else 0 end) as 하락

from(
        select ticker, tname, oprice - cprice as ud
        from kospi
        group by ticker, tname, rdate, oprice - cprice 
        )
group by ticker, tname;

-----------------------------------------------------------------

(
    select ticker,  tname,   '상승' as gubun,  count(1) as cnt 
    from kospi
    where oprice <= cprice
    group by ticker, tname 
)
         UNION
(
    select ticker,  tname,   '하락' as gubun,  count(1) as cnt 
    from kospi
    where oprice > cprice
    group by ticker, tname 
)
order by ticker asc;

-------------

SELECT ticker, tname, ud, COUNT(*) as gubun
FROM (
    SELECT ticker, tname, 
           CASE WHEN (oprice - cprice) > 0 THEN '상승' ELSE '하락' END AS ud
    FROM kospi
)
GROUP BY ticker, tname, ud
ORDER BY ticker DESC;

-------------------------------------------------

select tname as 종목명
        , to_char(rdate, 'YYYY-MM') as 날짜
        , to_char(vol, 'fm999,999,999,999') as 거래량
        , cprice as 종가
from kospi
where ticker = '035720'
--group by tname, to_char(rdate, 'YYYY-MM'), vol
order by 날짜;

-------------------------------------------------

select tname as 종목
        ,to_char(k.rdate, 'YYYY-MM') as 날짜
        , to_char(s.mv, 'fm999,999,999,999') as 최대거래량
from kospi k
        , (select max(vol) as mv
            from kospi 
            where ticker = '035720') s
where k.vol = mv;

-------------------------------------------------

-- 카카오 월별 종가
-- M1  M2  M3  M4
  --- --- ---  ---

select 
     (select cprice from kospi where ticker = '035720' and to_char(rdate,'YYYY-MM') = '2022-01') as "2022-01"
    ,(select cprice from kospi where ticker = '035720' and to_char(rdate,'YYYY-MM') = '2022-02') as "2022-02"
    ,(select cprice from kospi where ticker = '035720' and to_char(rdate,'YYYY-MM') = '2022-03') as "2022-03"
    ,(select cprice from kospi where ticker = '035720' and to_char(rdate,'YYYY-MM') = '2022-04') as "2022-04"
    ,(select cprice from kospi where ticker = '035720' and to_char(rdate,'YYYY-MM') = '2022-05') as "2022-05"
    ,(select cprice from kospi where ticker = '035720' and to_char(rdate,'YYYY-MM') = '2022-06') as "2022-06"
    ,(select cprice from kospi where ticker = '035720' and to_char(rdate,'YYYY-MM') = '2022-07') as "2022-07"
    ,(select cprice from kospi where ticker = '035720' and to_char(rdate,'YYYY-MM') = '2022-08') as "2022-08"
    ,(select cprice from kospi where ticker = '035720' and to_char(rdate,'YYYY-MM') = '2022-09') as "2022-09"
    ,(select cprice from kospi where ticker = '035720' and to_char(rdate,'YYYY-MM') = '2022-10') as "2022-10"
    ,(select cprice from kospi where ticker = '035720' and to_char(rdate,'YYYY-MM') = '2022-11') as "2022-11"
    ,(select cprice from kospi where ticker = '035720' and to_char(rdate,'YYYY-MM') = '2022-12') as "2022-12"
    ,(select cprice from kospi where ticker = '035720' and to_char(rdate,'YYYY-MM') = '2023-01') as "2023-01"
    ,(select cprice from kospi where ticker = '035720' and to_char(rdate,'YYYY-MM') = '2023-02') as "2023-02"
    ,(select cprice from kospi where ticker = '035720' and to_char(rdate,'YYYY-MM') = '2023-03') as "2023-03"
    ,(select cprice from kospi where ticker = '035720' and to_char(rdate,'YYYY-MM') = '2023-04') as "2023-04"
    ,(select cprice from kospi where ticker = '035720' and to_char(rdate,'YYYY-MM') = '2022-05') as "2023-05"
    ,(select cprice from kospi where ticker = '035720' and to_char(rdate,'YYYY-MM') = '2022-06') as "2023-06"
from dual;

-------------------------------------------------
-- 카카오 월별  (거래량, 종가) 추이

-- MM VOL CPRICE
select tname, to_char(rdate, 'YYYY-MM')as mm, round((cprice - oprice) / oprice * 100, 1)as RATE
from kospi
where ticker = '035720';

-------------------------------------------------