"대신증권 일일적재"의 두 판 사이의 차이
DB CAFE
(새 문서: == 데이트레이딩 일일적재 SQL == <source lang=sql> -- 데이트레이딩 일일 게더링 INSERT INTO tb_daily_recmd_stock ( date,recmd_hhmm, recmd_type, stock_...) |
|||
11번째 줄: | 11번째 줄: | ||
, net_change_1, net_change_2, net_change_3, net_change_4, net_change_5 | , net_change_1, net_change_2, net_change_3, net_change_4, net_change_5 | ||
) | ) | ||
− | SELECT DATE_FORMAT(CURDATE() | + | SELECT DATE_FORMAT(CURDATE(), '%Y%m%d') DATE |
, '1500' | , '1500' | ||
, '2' recmd_type /* 1:ris ,2:데이트레이딩 */ | , '2' recmd_type /* 1:ris ,2:데이트레이딩 */ | ||
77번째 줄: | 77번째 줄: | ||
WHERE 1=1 -- | WHERE 1=1 -- | ||
-- and DATE='20200529' | -- and DATE='20200529' | ||
− | AND a.date >= DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL - | + | AND a.date >= DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -7 DAY), '%Y%m%d') |
AND a.date <= DATE_FORMAT(CURDATE(), '%Y%m%d') | AND a.date <= DATE_FORMAT(CURDATE(), '%Y%m%d') | ||
AND a.stock_cd IN (SELECT a1.stock_cd | AND a.stock_cd IN (SELECT a1.stock_cd |
2020년 6월 9일 (화) 11:42 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
데이트레이딩 일일적재 SQL[편집]
-- 데이트레이딩 일일 게더링
INSERT INTO tb_daily_recmd_stock (
date,recmd_hhmm, recmd_type, stock_cd, org_stock_cd, stock_nm
, start_price_1, start_price_2, start_price_3, start_price_4, start_price_5
, cls_price_1, cls_price_2, cls_price_3, cls_price_4, cls_price_5
, date_1, date_2, date_3, date_4, date_5
, net_percent_1, net_percent_2, net_percent_3, net_percent_4, net_percent_5
, volume_1, volume_2, volume_3, volume_4, volume_5
, net_change_1, net_change_2, net_change_3, net_change_4, net_change_5
)
SELECT DATE_FORMAT(CURDATE(), '%Y%m%d') DATE
, '1500'
, '2' recmd_type /* 1:ris ,2:데이트레이딩 */
, xx.stock_cd
, substr(xx.stock_cd from 2) AS org_stock_cd
, CONCAT(b.stock_nm,' (',substr(xx.stock_cd from 2),')') AS stock_nm
, xx.strt_price_1 , xx.strt_price_2 , xx.strt_price_3 , xx.strt_price_4 , xx.strt_price_5
, xx.cls_price_1 , xx.cls_price_2 , xx.cls_price_3 , xx.cls_price_4 , xx.cls_price_5
, xx.date_1 , xx.date_2 , xx.date_3 , xx.date_4 , xx.date_5
, xx.percent_1 , xx.percent_2 , xx.percent_3 , xx.percent_4 , xx.percent_5
, xx.volume_1 , xx.volume_2 , xx.volume_3 , xx.volume_4 , xx.volume_5
, xx.cls_net_change_1 , xx.cls_net_change_2 , xx.cls_net_change_3 , xx.cls_net_change_4 , xx.cls_net_change_5
FROM (
SELECT stock_cd
, MAX(CASE WHEN x.rk=1 THEN x.date END) AS date_1
, MAX(CASE WHEN x.rk=1 THEN x.start_price END) AS strt_price_1
, MAX(CASE WHEN x.rk=1 THEN x.cls_price END) AS cls_price_1
, MAX(CASE WHEN x.rk=1 THEN x.net_change END) AS cls_net_change_1
, MAX(CASE WHEN x.rk=1 THEN x.volume END) AS volume_1
, MAX(CASE WHEN x.rk=1 THEN x.percent END) AS percent_1
, MAX(CASE WHEN x.rk=2 THEN x.date END) AS date_2
, MAX(CASE WHEN x.rk=2 THEN x.start_price END) AS strt_price_2
, MAX(CASE WHEN x.rk=2 THEN x.cls_price END) AS cls_price_2
, MAX(CASE WHEN x.rk=2 THEN x.net_change END) AS cls_net_change_2
, MAX(CASE WHEN x.rk=2 THEN x.volume END) AS volume_2
, MAX(CASE WHEN x.rk=2 THEN x.percent END) AS percent_2
, MAX(CASE WHEN x.rk=3 THEN x.date END) AS date_3
, MAX(CASE WHEN x.rk=3 THEN x.start_price END) AS strt_price_3
, MAX(CASE WHEN x.rk=3 THEN x.cls_price END) AS cls_price_3
, MAX(CASE WHEN x.rk=3 THEN x.net_change END) AS cls_net_change_3
, MAX(CASE WHEN x.rk=3 THEN x.volume END) AS volume_3
, MAX(CASE WHEN x.rk=3 THEN x.percent END) AS percent_3
, MAX(CASE WHEN x.rk=4 THEN x.date END) AS date_4
, MAX(CASE WHEN x.rk=4 THEN x.start_price END) AS strt_price_4
, MAX(CASE WHEN x.rk=4 THEN x.cls_price END) AS cls_price_4
, MAX(CASE WHEN x.rk=4 THEN x.net_change END) AS cls_net_change_4
, MAX(CASE WHEN x.rk=4 THEN x.volume END) AS volume_4
, MAX(CASE WHEN x.rk=4 THEN x.percent END) AS percent_4
, MAX(CASE WHEN x.rk=5 THEN x.date END) AS date_5
, MAX(CASE WHEN x.rk=5 THEN x.start_price END) AS strt_price_5
, MAX(CASE WHEN x.rk=5 THEN x.cls_price END) AS cls_price_5
, MAX(CASE WHEN x.rk=5 THEN x.net_change END) AS cls_net_change_5
, MAX(CASE WHEN x.rk=5 THEN x.volume END) AS volume_5
, MAX(CASE WHEN x.rk=5 THEN x.percent END) AS percent_5
FROM (
SELECT (CASE @vjob WHEN t.stock_cd THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) rk
, (@vjob:=t.stock_cd) vjob
, t.*
FROM (
SELECT a.stock_cd
, a.START_PRICE
, a.CLS_PRICE
, a.net_change
, a.VOLUME
, a.HIGH_PRICE
, a.LOW_PRICE
, ROUND((CLS_PRICE - START_PRICE)/START_PRICE * 100,1) AS percent
, a.DATE
from tb_stock_daily_info a
WHERE 1=1 --
-- and DATE='20200529'
AND a.date >= DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -7 DAY), '%Y%m%d')
AND a.date <= DATE_FORMAT(CURDATE(), '%Y%m%d')
AND a.stock_cd IN (SELECT a1.stock_cd
FROM tb_stock_daily_info a1
JOIN vw_stock_list b1
ON a1.stock_cd = b1.stock_cd
WHERE 1=1
AND a1.DATE= (SELECT MAX(date) from tb_stock_daily_info )
-- DATE_FORMAT(CURDATE()-1, '%Y%m%d')
-- 시가가 종가 보다 높은 경우
AND convert(CLS_PRICE,SIGNED) > convert(START_PRICE,SIGNED)
-- 가격이 2천 ~ 8천 사이
AND convert(CLS_PRICE,signed) BETWEEN 2000 AND 8000
-- 상한가 종목 제외
AND ROUND((CLS_PRICE - START_PRICE)/START_PRICE * 100,1) BETWEEN 0 AND 25
)
) t
JOIN (
SELECT @vjob:='', @rownum:=0
) AS r
ORDER BY t.stock_cd,t.date DESC
) x
group BY x.stock_cd
) xx
JOIN vw_stock_list b
ON xx.stock_cd = b.stock_cd
WHERE xx.volume_1 >= 10000000
AND xx.volume_1 > xx.volume_2
-- AND ( xx.volume2 >= 5000000 OR xx.volume3 >= 5000000 OR xx.volume4 >= 5000000 OR xx.volume5 >= 5000000)
ORDER BY volume_1 desc
;