|
|
| (같은 사용자의 중간 판 2개는 보이지 않습니다) |
| 1번째 줄: |
1번째 줄: |
| | == 데이트레이딩 일일적재 SQL == | | == 데이트레이딩 일일적재 SQL == |
| − | <source lang=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()-2, '%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
| |
| | | | |
| − |
| + | [[category:주식]] |
| − | 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 -5 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
| |
| − | ;
| |
| − | </source>
| |