"대신증권 일일적재"의 두 판 사이의 차이

데이트레이딩 일일적재 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     
 ;