STOCK_ANALYSIS_PROCEDURE.sql

USE [STOCK_ANALYSIS]

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('CREATE_TBL_STOCK_INFO') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE CREATE_TBL_STOCK_INFO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('CREATE_TBL_STOCK_CATE') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE CREATE_TBL_STOCK_CATE
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('CREATE_TBL_STOCK_PRICE') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE CREATE_TBL_STOCK_PRICE
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('CREATE_TBL_FINANCE_INFO') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE CREATE_TBL_FINANCE_INFO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('CREATE_TBL_THEME_INFO') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE CREATE_TBL_THEME_INFO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('UPDATE_STOCK_PRICE') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE UPDATE_STOCK_PRICE
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('UPDATE_FINANCE_INFO') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE UPDATE_FINANCE_INFO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('UPDATE_THEME_INFO') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE UPDATE_THEME_INFO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('UPDATE_STOCK_THEME') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE UPDATE_STOCK_THEME
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('UPDATE_STOCK_PRICE_RATIO') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE UPDATE_STOCK_PRICE_RATIO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('UPDATE_EMBEDDING_WEIGHTS') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE UPDATE_EMBEDDING_WEIGHTS
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('CREATE_SPLIT_STOCK_SET') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE CREATE_SPLIT_STOCK_SET
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('CREATE_DRV_STOCK_THEME') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE CREATE_DRV_STOCK_THEME
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('CREATE_DRV_STOCK_PRICE_WEEK') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE CREATE_DRV_STOCK_PRICE_WEEK
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('CREATE_DRV_STOCK_PRICE_MONTH') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE CREATE_DRV_STOCK_PRICE_MONTH
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('CREATE_DRV_STOCK_EMBEDDING_SET') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE CREATE_DRV_STOCK_EMBEDDING_SET
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('CONV_FINANCE_INFO') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE CONV_FINANCE_INFO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('CREATE_DRV_FINANCE_RATIO') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE CREATE_DRV_FINANCE_RATIO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('CREATE_DRV_WEEK_FINANCE_RATIO') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE CREATE_DRV_WEEK_FINANCE_RATIO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('CONV_STOCK_NAME') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE CONV_STOCK_NAME
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('CREATE_DRV_TABLES') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE CREATE_DRV_TABLES
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('SEL_STOCK_PRICE') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE SEL_STOCK_PRICE
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('SEL_STOCK_TRAINING_DATA') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE SEL_STOCK_TRAINING_DATA
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('SEL_STOCK_TRAINING_DATA_WEEK') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE SEL_STOCK_TRAINING_DATA_WEEK
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('CREATE_TBL_STOCK_EMBEDDING_WEIGHTS') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE CREATE_TBL_STOCK_EMBEDDING_WEIGHTS
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('SEL_STOCK_TEST_DATA') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE SEL_STOCK_TEST_DATA
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('SEL_STOCK_TEST_DATA_WEEK') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE SEL_STOCK_TEST_DATA_WEEK


-- 테이블 생성 쿼리 --
GO
CREATE PROCEDURE CREATE_TBL_STOCK_INFO
AS
BEGIN
	IF EXISTS (SELECT * FROM sysobjects WHERE name='STOCK_INFO' AND xtype='U')
	DROP TABLE STOCK_INFO
	CREATE TABLE STOCK_INFO ( STOCK_CODE char(10), STOCK_NAME nvarchar(50), STOCK_CATE nvarchar(50))
END
GO


GO  
CREATE PROCEDURE CREATE_TBL_STOCK_CATE 
AS  
BEGIN
	IF EXISTS (SELECT * FROM sysobjects WHERE name='STOCK_CATEGORY' AND xtype='U')
	DROP TABLE STOCK_CATEGORY
	SELECT ROW_NUMBER() OVER(ORDER BY STOCK_CATE) CATE_ID, STOCK_CATE
	INTO STOCK_CATEGORY
	FROM (SELECT DISTINCT STOCK_CATE FROM STOCK_INFO) INFO
	ORDER BY STOCK_CATE

END
GO

GO 
CREATE PROCEDURE CREATE_TBL_STOCK_PRICE
AS  
BEGIN
	IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='STOCK_PRICE' AND xtype='U') 
	BEGIN
	CREATE TABLE STOCK_PRICE 
	([STOCK_CODE] int, [START] int, [CLOSE] int, [HIGHEST] int, [LOWEST] int, [VOLUME] int, [DATE] date)
	CREATE UNIQUE CLUSTERED INDEX STOCK_PRICE_INDEX
	ON STOCK_PRICE  (STOCK_CODE DESC,[DATE] DESC)
	END
END
GO

GO
CREATE PROCEDURE CREATE_TBL_FINANCE_INFO
AS
BEGIN
	IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='FINANCE_INFO' AND xtype='U')
	BEGIN
	CREATE TABLE FINANCE_INFO
	([STOCK_CODE] INT, REVENUE FLOAT, PROFIT FLOAT, NET_INCOME FLOAT, ASSETS_SUM FLOAT, DEBT_SUM FLOAT, CAPITAL_SUM FLOAT, 
	OPERATE_CF FLOAT, INVEST_CF FLOAT, FINANCE_CF INT, CAPEX FLOAT, FCF FLOAT, INTEREST_DEPT FLOAT, RESERVE_RATE FLOAT, 
	DIV_RATE FLOAT, TOTAL_STOCK FLOAT, [DATE] date)
	CREATE UNIQUE CLUSTERED INDEX FINANCE_INFO_INDEX
	ON FINANCE_INFO (STOCK_CODE DESC, [DATE] DESC)
	END
END
GO

GO
CREATE PROCEDURE CREATE_TBL_THEME_INFO
AS
BEGIN
	IF EXISTS (SELECT * FROM sysobjects WHERE name='STOCK_THEME' AND xtype='U')
		DROP TABLE STOCK_THEME
	BEGIN
	CREATE TABLE STOCK_THEME
	([STOCK_CODE] INT, [THEME_CODE] INT)
	END
	IF EXISTS (SELECT * FROM sysobjects WHERE name='THEME_INFO' AND xtype='U')
		DROP TABLE THEME_INFO
	BEGIN
	CREATE TABLE THEME_INFO
	([THEME_CODE] INT, [THEME_NAME] NVARCHAR(50), [THEME_INFO] NVARCHAR(MAX))
	CREATE UNIQUE CLUSTERED INDEX THEME_INFO_INDEX
	ON THEME_INFO (THEME_CODE ASC)
	END
END
GO

GO
CREATE PROCEDURE CREATE_TBL_STOCK_EMBEDDING_WEIGHTS
AS
BEGIN
	IF EXISTS (SELECT * FROM sysobjects WHERE name='STOCK_EMBEDDING_WEIGHTS' AND xtype='U')
		DROP TABLE STOCK_EMBEDDING_WEIGHTS
	CREATE TABLE STOCK_EMBEDDING_WEIGHTS
	([STOCK_CODE] INT, [W1] FLOAT, [W2] FLOAT, [W3] FLOAT)
	CREATE UNIQUE CLUSTERED INDEX STOCK_EMBEDDING_WEIGHTS_INDEX
	ON STOCK_EMBEDDING_WEIGHTS (STOCK_CODE DESC)
END
GO

-- 테이블 업데이트 쿼리 --
GO  
CREATE PROCEDURE UPDATE_STOCK_PRICE @STOCK_CODE INT, @START INT, @CLOSE INT, @HIGHEST INT, @LOWEST INT, @VOLUME INT, @DATE DATE
AS  
BEGIN
	IF NOT EXISTS (SELECT * FROM STOCK_PRICE WHERE STOCK_CODE = @STOCK_CODE AND [DATE] = @DATE)
	BEGIN
		INSERT INTO STOCK_PRICE ([STOCK_CODE], [START], [CLOSE], [HIGHEST], [LOWEST], [VOLUME], [DATE]) 
		VALUES (@STOCK_CODE, @START, @CLOSE, @HIGHEST, @LOWEST, @VOLUME, @DATE)
		RETURN 0
	END
	ELSE
	BEGIN
		RETURN -1
	END
END
GO

GO  
CREATE PROCEDURE UPDATE_FINANCE_INFO 
@STOCK_CODE FLOAT, @REVENUE FLOAT, @PROFIT FLOAT, @NET_INCOME FLOAT, @ASSETS_SUM FLOAT, @DEBT_SUM FLOAT, 
@CAPITAL_SUM FLOAT, @OPERATE_CF FLOAT, @INVEST_CF FLOAT, @FINANCE_CF FLOAT, @CAPEX FLOAT, @FCF FLOAT, 
@INTEREST_DEPT FLOAT, @RESERVE_RATE FLOAT, @DIV_RATE FLOAT, @TOTAL_STOCK FLOAT, @DATE DATE
AS  
BEGIN
	IF NOT EXISTS (SELECT * FROM FINANCE_INFO WHERE STOCK_CODE = @STOCK_CODE AND [DATE] = @DATE)
	BEGIN
		INSERT INTO FINANCE_INFO ([STOCK_CODE], REVENUE, PROFIT, NET_INCOME, ASSETS_SUM, DEBT_SUM, CAPITAL_SUM, OPERATE_CF, INVEST_CF, FINANCE_CF, 
			CAPEX, FCF, INTEREST_DEPT, RESERVE_RATE, DIV_RATE, TOTAL_STOCK, [DATE])
		VALUES (@STOCK_CODE, @REVENUE, @PROFIT, @NET_INCOME, @ASSETS_SUM, @DEBT_SUM, @CAPITAL_SUM, @OPERATE_CF, @INVEST_CF, @FINANCE_CF, 
			@CAPEX, @FCF, @INTEREST_DEPT, @RESERVE_RATE, @DIV_RATE, @TOTAL_STOCK, @DATE)
		RETURN 0
	END
	ELSE
	BEGIN
		RETURN -1
	END
END
GO

GO  
CREATE PROCEDURE UPDATE_THEME_INFO @THEME_CODE INT, @THEME_NAME NVARCHAR(50), @THEME_INFO NVARCHAR(MAX) 
AS  
BEGIN
	IF NOT EXISTS (SELECT * FROM THEME_INFO WHERE THEME_CODE = @THEME_CODE)
	BEGIN
		INSERT INTO THEME_INFO (THEME_CODE, THEME_NAME, THEME_INFO)
		VALUES (@THEME_CODE, @THEME_NAME, @THEME_INFO)
		RETURN 0
	END
	ELSE
	BEGIN
		RETURN -1
	END
END
GO

GO
CREATE PROCEDURE UPDATE_STOCK_THEME @STOCK_CODE INT, @THEME_CODE INT
AS
BEGIN
	IF NOT EXISTS (SELECT * FROM STOCK_THEME WHERE STOCK_CODE = @STOCK_CODE AND THEME_CODE = @THEME_CODE)
	BEGIN
		INSERT INTO STOCK_THEME (STOCK_CODE, THEME_CODE)
		VALUES (@STOCK_CODE, @THEME_CODE)
		RETURN 0
	END
	ELSE
	BEGIN
		RETURN -1
	END
END
GO

GO
CREATE PROCEDURE UPDATE_EMBEDDING_WEIGHTS @STOCK_CODE INT, @W1 FLOAT, @W2 FLOAT, @W3 FLOAT
AS
BEGIN
	IF NOT EXISTS (SELECT * FROM STOCK_EMBEDDING_WEIGHTS WHERE STOCK_CODE = @STOCK_CODE)
	BEGIN
		INSERT INTO STOCK_EMBEDDING_WEIGHTS (STOCK_CODE, W1, W2, W3)
		VALUES (@STOCK_CODE, @W1, @W2, @W3)
		RETURN 0
	END
	ELSE
	BEGIN
		RETURN -1
	END
END
GO

-- 파생 테이블 생성 쿼리 --
GO
CREATE PROCEDURE UPDATE_STOCK_PRICE_RATIO
AS
BEGIN
	IF EXISTS (SELECT * FROM sysobjects WHERE name='STOCK_PRICE' AND xtype='U')
	BEGIN
		IF EXISTS (SELECT * FROM sysobjects WHERE name='STOCK_PRICE_RATIO' AND xtype='U')
			DROP TABLE STOCK_PRICE_RATIO

		SELECT *, ([START] + [CLOSE])/2 AVERAGE 
		INTO #STOCK_PRICE_TEMP 
		FROM STOCK_PRICE A
		ORDER BY A.STOCK_CODE, A.[DATE] DESC

		SELECT STOCK_CODE, AVERAGE, HIGHEST, LOWEST, VOLUME, [DATE],
			LEAD(STOCK_CODE) OVER (ORDER BY A.STOCK_CODE, [DATE] DESC) BEF_CODE,
			LEAD(AVERAGE) OVER (ORDER BY A.STOCK_CODE, [DATE] DESC) BEF_AVERAGE
		INTO #STOCK_PRICE_RATIO 
		FROM #STOCK_PRICE_TEMP A 
		ORDER BY A.STOCK_CODE, A.[DATE] DESC

		
		UPDATE #STOCK_PRICE_RATIO SET BEF_AVERAGE = NULL WHERE BEF_CODE <> STOCK_CODE

		-- 종목별 주가 테이블 생성
		SELECT A.STOCK_CODE, A.AVERAGE, A.HIGHEST, A.LOWEST, A.VOLUME, A.[DATE], 
		CONVERT(FLOAT,A.AVERAGE) / NULLIF(A.BEF_AVERAGE, 0) CHANGE_RATIO
		INTO STOCK_PRICE_RATIO
		FROM #STOCK_PRICE_RATIO A

		-- 거래정지 종목 데이터 제거
		DELETE FROM STOCK_PRICE_RATIO WHERE HIGHEST = 0 OR LOWEST = 0
		-- 이상 변동률 데이터 제거
		DELETE FROM STOCK_PRICE_RATIO WHERE CHANGE_RATIO IS NULL
		DELETE FROM STOCK_PRICE_RATIO WHERE CHANGE_RATIO > 1.3 OR CHANGE_RATIO < 0.7

		DROP TABLE #STOCK_PRICE_RATIO
		DROP TABLE #STOCK_PRICE_TEMP
	END
END
GO

GO
CREATE PROCEDURE CREATE_SPLIT_STOCK_SET
AS
BEGIN
	IF EXISTS (SELECT * FROM sysobjects WHERE name='SPLIT_STOCK_SET' AND xtype='U')
		DROP TABLE SPLIT_STOCK_SET

	-- 액면분할 종목에 대해 리스트 생성
	SELECT DISTINCT STOCK_CODE
	INTO SPLIT_STOCK_SET
	FROM STOCK_PRICE_RATIO
	WHERE CHANGE_RATIO < 0.6
END
GO

GO  
CREATE PROCEDURE CREATE_DRV_STOCK_THEME 
AS  
BEGIN
	IF EXISTS (SELECT * FROM sysobjects WHERE name='DRV_STOCK_THEME' AND xtype='U')
		DROP TABLE DRV_STOCK_THEME
	
	DECLARE @QUERY VARCHAR(MAX)
	SET @QUERY = '';

	SELECT @QUERY = @QUERY + '[' + LTRIM(STR(THEME_CODE))  + '],'
	FROM (SELECT DISTINCT THEME_CODE FROM STOCK_THEME) A
	ORDER BY THEME_CODE ASC

	SET @QUERY = LEFT(@QUERY, LEN(@QUERY)-1)
	EXEC('SELECT * INTO DRV_STOCK_THEME_ FROM STOCK_THEME PIVOT(COUNT(THEME_CODE) FOR THEME_CODE IN (' + @QUERY + ')) AS PVT')

	DECLARE @COL VARCHAR(MAX)
	DECLARE @COLCNT INT
	DECLARE @IT INT
	DECLARE @SPLIT VARCHAR(1)

	SELECT TOP 1 @COLCNT = (SELECT COUNT(*) FROM dbo.syscolumns where id=OBJECT_ID('DRV_STOCK_THEME_')) - 1
	SET @COL = 'LTRIM(STR([0]))'
	SET @IT = 1
	SET @SPLIT = ','

	WHILE (@IT < @COLCNT)
	BEGIN
		SET @COL = @COL + ' + '','' + LTRIM(STR([' + LTRIM(STR(@IT)) + ']))'
		SET @IT = @IT + 1
	END

	EXEC('SELECT STOCK_CODE, ' + @COL + 'AS THEME_STRING INTO DRV_STOCK_THEME FROM DRV_STOCK_THEME_')
	DROP TABLE DRV_STOCK_THEME_
END
GO

GO
CREATE PROCEDURE CREATE_DRV_STOCK_PRICE_WEEK 
AS  
BEGIN
	IF EXISTS (SELECT * FROM sysobjects WHERE name='DRV_STOCK_PRICE_WEEK' AND xtype='U')
		DROP TABLE DRV_STOCK_PRICE_WEEK

	SELECT A.STOCK_CODE, 
		AVG(A.AVERAGE) AVERAGE, 
		MAX(A.HIGHEST) HIGHEST, 
		MIN(A.LOWEST) LOWEST, 
		YEAR([DATE]) [YEAR], 
		DATEPART(WEEK, [DATE]) [WEEK],
		SUM(A.VOLUME) [VOLUME],
		MIN([DATE]) [DATE]
	INTO #STOCK_PRICE_WEEK
	FROM STOCK_PRICE_RATIO A
	GROUP BY STOCK_CODE, DATEPART(WEEK, [DATE]), YEAR([DATE])
	ORDER BY STOCK_CODE, [DATE] DESC

	SELECT *, 
		LEAD([DATE]) OVER (ORDER BY A.STOCK_CODE, [YEAR] DESC, [WEEK] DESC) AS BEF_DATE,
		CONVERT(FLOAT, A.[AVERAGE]) / LEAD([AVERAGE]) OVER (ORDER BY A.STOCK_CODE, [YEAR] DESC, [WEEK] DESC) AS CHANGE_RATIO
	INTO DRV_STOCK_PRICE_WEEK
	FROM #STOCK_PRICE_WEEK A
	ORDER BY A.STOCK_CODE, A.[DATE] DESC


	
	-- 예외 데이터의 경우 제거 필요
	DELETE FROM DRV_STOCK_PRICE_WEEK WHERE BEF_DATE > [DATE]
	DELETE FROM DRV_STOCK_PRICE_WEEK WHERE CHANGE_RATIO IS NULL
	DELETE FROM DRV_STOCK_PRICE_WEEK WHERE DATEDIFF(WEEK, [BEF_DATE], [DATE]) > 1

	DROP TABLE #STOCK_PRICE_WEEK
END
GO

GO
CREATE PROCEDURE CREATE_DRV_STOCK_PRICE_MONTH
AS
BEGIN
	IF EXISTS (SELECT * FROM sysobjects WHERE name='DRV_STOCK_PRICE_MONTH' AND xtype='U')
		DROP TABLE DRV_STOCK_PRICE_MONTH

	SELECT A.STOCK_CODE, 
		AVG((A.[CLOSE] + A.[START])/2) AVERAGE, 
		MAX(A.HIGHEST) HIGHEST, 
		MIN(A.LOWEST) LOWEST, 
		YEAR([DATE]) [YEAR], 
		DATEPART(MONTH, [DATE]) [MONTH]
	INTO  #STOCK_PRICE_MONTH
	FROM STOCK_PRICE A
	GROUP BY STOCK_CODE, DATEPART(MONTH, [DATE]), YEAR([DATE])
	ORDER BY STOCK_CODE, [YEAR] DESC, [MONTH] DESC 

	SELECT *, 
		LEAD([YEAR]) OVER (ORDER BY A.STOCK_CODE, [YEAR] DESC, [MONTH] DESC) AS BEF_YEAR,
		LEAD([MONTH]) OVER (ORDER BY A.STOCK_CODE, [YEAR] DESC, [MONTH] DESC) AS BEF_MONTH,
		A.[AVERAGE] / LEAD([AVERAGE]) OVER (ORDER BY A.STOCK_CODE, [YEAR] DESC, [MONTH] DESC) AS CHANGE_RATE
	INTO DRV_STOCK_PRICE_MONTH
	FROM #STOCK_PRICE_MONTH A
	ORDER BY A.STOCK_CODE, A.[YEAR] DESC, A.[MONTH] DESC
	
	DELETE FROM DRV_STOCK_PRICE_MONTH WHERE BEF_YEAR > [YEAR]
	DROP TABLE #STOCK_PRICE_MONTH
END
GO

GO
CREATE PROCEDURE CREATE_DRV_STOCK_EMBEDDING_SET
AS
BEGIN
	IF EXISTS (SELECT * FROM sysobjects WHERE name='STOCK_EMBEDDING_SET' AND xtype='U')
		DROP TABLE STOCK_EMBEDDING_SET
	IF EXISTS (SELECT * FROM sysobjects WHERE name='STOCK_FALSE_SET' AND xtype='U')
		DROP TABLE STOCK_FALSE_SET
	IF EXISTS (SELECT * FROM sysobjects WHERE name='STOCK_EMBEDDING_LIST' AND xtype='U')
		DROP TABLE STOCK_EMBEDDING_LIST

	-- 주가 상승이 30퍼센트 이상인 경우만 남김
	SELECT *
	INTO #DATE_THEME_AVERAGE
	FROM STOCK_TRAINING_SET_WEEK A
	WHERE CHANGE_RATIO > 1.3

	SELECT B.STOCK_NAME, A.* 
	FROM #DATE_THEME_AVERAGE A
	JOIN STOCK_INFO B ON A.STOCK_CODE = B.STOCK_CODE

	-- 리인덱싱 테이블 생성
	SELECT DISTINCT STOCK_CODE
	INTO #STOCK_EMBEDDING_LIST
	FROM #DATE_THEME_AVERAGE
	SELECT STOCK_CODE, ROW_NUMBER() OVER(ORDER BY STOCK_CODE ASC) - 1 AS NUM
	INTO STOCK_EMBEDDING_LIST
	FROM #STOCK_EMBEDDING_LIST
	
	-- 해당 주식들 중 다음주 상승한 주식과 매칭
	SELECT CONVERT(INT, C.NUM) BEF_STOCK, CONVERT(INT, D.NUM) AFT_STOCK, A.CHANGE_RATIO BEF_CH, B.CHANGE_RATIO AFT_CH
	INTO STOCK_EMBEDDING_SET
	FROM #DATE_THEME_AVERAGE A
	JOIN #DATE_THEME_AVERAGE B ON A.[DATE] = DATEADD(WEEK, -1, B.[DATE]) AND A.STOCK_CODE <> B.STOCK_CODE
	JOIN STOCK_EMBEDDING_LIST C ON A.STOCK_CODE = C.STOCK_CODE
	JOIN STOCK_EMBEDDING_LIST D ON B.STOCK_CODE = D.STOCK_CODE

	-- 학습을 위해 False Set 생성
	SELECT CONVERT(INT, A.NUM) BEF_STOCK, CONVERT(INT, B.NUM) AFT_STOCK
	INTO STOCK_FALSE_SET
	FROM STOCK_EMBEDDING_LIST A
	CROSS JOIN STOCK_EMBEDDING_LIST B
	
	DELETE A
	FROM STOCK_FALSE_SET A
	INNER JOIN STOCK_EMBEDDING_SET B 
	ON A.BEF_STOCK = B.BEF_STOCK AND A.AFT_STOCK = B.AFT_STOCK
	
	DROP TABLE #STOCK_EMBEDDING_LIST
	DROP TABLE #DATE_THEME_AVERAGE
END
GO

GO
CREATE PROCEDURE CONV_FINANCE_INFO
AS
BEGIN
	-- 일부 데이터 하드코딩 필요
	UPDATE FINANCE_INFO SET TOTAL_STOCK = 840000 WHERE STOCK_CODE = 306620 
	UPDATE FINANCE_INFO SET TOTAL_STOCK = 84000000 WHERE STOCK_CODE = 041190 AND [DATE] = '2019-09-01'
	UPDATE FINANCE_INFO SET TOTAL_STOCK = 33873125 WHERE STOCK_CODE = 303030 AND [DATE] = '2019-09-01'
	UPDATE FINANCE_INFO SET TOTAL_STOCK = 22353873 WHERE STOCK_CODE = 042370 AND [DATE] = '2019-09-01'
	UPDATE FINANCE_INFO SET TOTAL_STOCK = 55725992 WHERE STOCK_CODE = 071050 
	UPDATE FINANCE_INFO SET TOTAL_STOCK = 31154798 WHERE STOCK_CODE = 093240 
	UPDATE FINANCE_INFO SET TOTAL_STOCK = 140224314 WHERE STOCK_CODE = 138040 

	-- 추가 오류사항 발생 시 데이터 전처리 코드 작성 필요
	DELETE FROM FINANCE_INFO 
	WHERE STOCK_CODE IN (
		SELECT STOCK_CODE 
		FROM FINANCE_INFO A 
		GROUP BY STOCK_CODE 
		HAVING COUNT(*) < 5 )
	
	DELETE FROM FINANCE_INFO
	WHERE STOCK_CODE IN (
		SELECT STOCK_CODE
		FROM FINANCE_INFO
		WHERE ASSETS_SUM = 0 AND DEBT_SUM = 0 AND CAPITAL_SUM = 0
		GROUP BY STOCK_CODE)

END
GO

GO
CREATE PROCEDURE CREATE_DRV_FINANCE_RATIO
AS
BEGIN
	IF EXISTS (SELECT * FROM sysobjects WHERE name='STOCK_TRAINING_SET' AND xtype='U')
		DROP TABLE STOCK_TRAINING_SET
		
	SELECT *, DATEADD(MONTH, 3, [DATE]) NXT_QUARTER
	INTO #FINANCE_TEMP
	FROM FINANCE_INFO A 

	SELECT STOCK_CODE, MAX(NXT_QUARTER) NXT_QUARTER
	INTO #FINANCE_NEW_DATE
	FROM #FINANCE_TEMP
	GROUP BY STOCK_CODE

	DECLARE @NEWEST_DATE [DATE]
	SET @NEWEST_DATE = (SELECT TOP 1 MAX(DATE) FROM STOCK_PRICE_RATIO)

	-- 재무제표가 없는 최신 데이터는 지난 재무제표에 매칭
	UPDATE #FINANCE_TEMP 
	SET NXT_QUARTER = @NEWEST_DATE
	FROM #FINANCE_TEMP A
	JOIN #FINANCE_NEW_DATE B ON A.STOCK_CODE = B.STOCK_CODE AND A.NXT_QUARTER = B.NXT_QUARTER

	SELECT B.STOCK_CODE, B.AVERAGE, B.HIGHEST, B.LOWEST, B.VOLUME, B.CHANGE_RATIO,
		B.AVERAGE * A.TOTAL_STOCK MARKET_CAP, 
		B.AVERAGE * CONVERT(FLOAT, B.VOLUME) TRADING_VAL,  
		A.ASSETS_SUM, A.CAPITAL_SUM, A.DEBT_SUM,
		A.OPERATE_CF, A.INVEST_CF, A.FINANCE_CF,
		A.CAPEX, A.FCF, 
		A.INTEREST_DEPT, A.RESERVE_RATE,
		A.REVENUE, A.NET_INCOME, A.PROFIT, 
		A.DIV_RATE, A.TOTAL_STOCK, 
		B.[DATE]
	INTO #FINANCE_TEMP2
	FROM #FINANCE_TEMP A 
	JOIN STOCK_PRICE_RATIO B ON A.STOCK_CODE = B.STOCK_CODE AND B.[DATE] BETWEEN A.[DATE] AND DATEADD(DAY, -1, A.NXT_QUARTER)
	ORDER BY B.STOCK_CODE ASC, B.[DATE] DESC  

	SELECT A.STOCK_CODE,
		A.AVERAGE,
		A.HIGHEST,
		A.LOWEST,
		A.VOLUME,
		A.CHANGE_RATIO,
		A.MARKET_CAP,
		A.MARKET_CAP / NULLIF(A.ASSETS_SUM, 0) PBR, 
		A.MARKET_CAP / NULLIF(A.CAPITAL_SUM, 0) PBR2,
		A.MARKET_CAP / NULLIF(A.DEBT_SUM, 0) PDR,
		A.MARKET_CAP / NULLIF(A.PROFIT , 0) PER, 
		A.MARKET_CAP / NULLIF(A.OPERATE_CF, 0) PCR_OP, 
		A.MARKET_CAP / NULLIF(A.INVEST_CF,0) PCR_IV,
		A.MARKET_CAP / NULLIF(A.FINANCE_CF,0) PCR_FI,
		A.MARKET_CAP / NULLIF(A.REVENUE , 0) PSR,
		A.MARKET_CAP / NULLIF(A.FCF, 0) PFR,
		A.MARKET_CAP / NULLIF(A.CAPEX, 0) PXR,
		A.PROFIT / NULLIF(A.CAPITAL_SUM, 0) ROE,
		A.PROFIT / NULLIF(A.ASSETS_SUM, 0) ROA,
		RANK() OVER (PARTITION BY A.[DATE] ORDER BY MARKET_CAP DESC) MARKET_RANK,
		A.[DATE]
	INTO STOCK_TRAINING_SET
	FROM #FINANCE_TEMP2 A
	ORDER BY A.STOCK_CODE, A.[DATE] DESC

	DELETE FROM STOCK_TRAINING_SET WHERE CHANGE_RATIO > 1.3 OR CHANGE_RATIO < 0.7
	DELETE FROM STOCK_TRAINING_SET WHERE STOCK_CODE IN (SELECT STOCK_CODE FROM SPLIT_STOCK_SET)

	UPDATE STOCK_TRAINING_SET SET PBR = (SELECT MAX(PBR) FROM STOCK_TRAINING_SET) WHERE PBR IS NULL
	UPDATE STOCK_TRAINING_SET SET PBR2 = (SELECT MAX(PBR2) FROM STOCK_TRAINING_SET)WHERE PBR2 IS NULL
	UPDATE STOCK_TRAINING_SET SET PDR = (SELECT MAX(PDR) FROM STOCK_TRAINING_SET) WHERE PDR IS NULL
	UPDATE STOCK_TRAINING_SET SET PER = (SELECT MAX(PER) FROM STOCK_TRAINING_SET) WHERE PER IS NULL
	UPDATE STOCK_TRAINING_SET SET PCR_OP = (SELECT MAX(PCR_OP) FROM STOCK_TRAINING_SET) WHERE PCR_OP IS NULL
	UPDATE STOCK_TRAINING_SET SET PCR_IV = (SELECT MAX(PCR_IV) FROM STOCK_TRAINING_SET) WHERE PCR_IV IS NULL
	UPDATE STOCK_TRAINING_SET SET PCR_FI = (SELECT MAX(PCR_FI) FROM STOCK_TRAINING_SET) WHERE PCR_FI IS NULL
	UPDATE STOCK_TRAINING_SET SET PSR = (SELECT MAX(PSR) FROM STOCK_TRAINING_SET) WHERE PSR IS NULL
	UPDATE STOCK_TRAINING_SET SET PFR = (SELECT MAX(PFR) FROM STOCK_TRAINING_SET) WHERE PFR IS NULL
	UPDATE STOCK_TRAINING_SET SET PXR = (SELECT MAX(PXR) FROM STOCK_TRAINING_SET) WHERE PXR IS NULL
	UPDATE STOCK_TRAINING_SET SET ROE = (SELECT MAX(ROE) FROM STOCK_TRAINING_SET) WHERE ROE IS NULL
	UPDATE STOCK_TRAINING_SET SET ROA = (SELECT MAX(ROA) FROM STOCK_TRAINING_SET) WHERE ROA IS NULL

	CREATE UNIQUE CLUSTERED INDEX STOCK_TRAINING_SET_INDEX
	ON STOCK_TRAINING_SET (STOCK_CODE ASC, [DATE] DESC)

	DROP TABLE #FINANCE_NEW_DATE
	DROP TABLE #FINANCE_TEMP
	DROP TABLE #FINANCE_TEMP2
END
GO

GO
CREATE PROCEDURE CREATE_DRV_WEEK_FINANCE_RATIO
AS
BEGIN
	IF EXISTS (SELECT * FROM sysobjects WHERE name='STOCK_TRAINING_SET_WEEK' AND xtype='U')
		DROP TABLE STOCK_TRAINING_SET_WEEK

	SELECT *, DATEADD(MONTH, 3, [DATE]) NXT_QUARTER
	INTO #FINANCE_TEMP
	FROM FINANCE_INFO A 

	SELECT STOCK_CODE, MAX(NXT_QUARTER) NXT_QUARTER
	INTO #FINANCE_NEW_DATE
	FROM #FINANCE_TEMP
	GROUP BY STOCK_CODE

	DECLARE @NEWEST_DATE [DATE]
	SET @NEWEST_DATE = (SELECT TOP 1 MAX(DATE) FROM STOCK_PRICE_RATIO)

	-- 재무제표가 없는 최신 데이터는 지난 재무제표에 매칭
	UPDATE #FINANCE_TEMP 
	SET NXT_QUARTER = @NEWEST_DATE
	FROM #FINANCE_TEMP A
	JOIN #FINANCE_NEW_DATE B ON A.STOCK_CODE = B.STOCK_CODE AND A.NXT_QUARTER = B.NXT_QUARTER

	SELECT B.STOCK_CODE, B.AVERAGE, B.HIGHEST, B.LOWEST, B.VOLUME, B.CHANGE_RATIO,
		B.AVERAGE * A.TOTAL_STOCK MARKET_CAP, 
		B.AVERAGE * CONVERT(FLOAT, B.VOLUME) TRADING_VAL,  
		A.ASSETS_SUM, A.CAPITAL_SUM, A.DEBT_SUM,
		A.OPERATE_CF, A.INVEST_CF, A.FINANCE_CF,
		A.CAPEX, A.FCF, 
		A.INTEREST_DEPT, A.RESERVE_RATE,
		A.REVENUE, A.NET_INCOME, A.PROFIT, 
		A.DIV_RATE, A.TOTAL_STOCK, 
		B.[DATE]
	INTO #FINANCE_TEMP2
	FROM #FINANCE_TEMP A 
	JOIN DRV_STOCK_PRICE_WEEK B ON A.STOCK_CODE = B.STOCK_CODE AND B.[DATE] BETWEEN A.[DATE] AND DATEADD(DAY, -1, A.NXT_QUARTER)
	ORDER BY B.STOCK_CODE ASC, B.[DATE] DESC

	SELECT A.STOCK_CODE,
		A.AVERAGE,
		A.HIGHEST,
		A.LOWEST,
		A.VOLUME,
		A.CHANGE_RATIO,
		A.MARKET_CAP,
		A.MARKET_CAP / NULLIF(A.ASSETS_SUM, 0) PBR, 
		A.MARKET_CAP / NULLIF(A.CAPITAL_SUM, 0) PBR2,
		A.MARKET_CAP / NULLIF(A.DEBT_SUM, 0) PDR,
		A.MARKET_CAP / NULLIF(A.PROFIT , 0) PER, 
		A.MARKET_CAP / NULLIF(A.OPERATE_CF, 0) PCR_OP, 
		A.MARKET_CAP / NULLIF(A.INVEST_CF,0) PCR_IV,
		A.MARKET_CAP / NULLIF(A.FINANCE_CF,0) PCR_FI,
		A.MARKET_CAP / NULLIF(A.REVENUE , 0) PSR,
		A.MARKET_CAP / NULLIF(A.FCF, 0) PFR,
		A.MARKET_CAP / NULLIF(A.CAPEX, 0) PXR,
		A.PROFIT / NULLIF(A.CAPITAL_SUM, 0) ROE,
		A.PROFIT / NULLIF(A.ASSETS_SUM, 0) ROA,
		RANK() OVER (PARTITION BY A.[DATE] ORDER BY MARKET_CAP DESC) MARKET_RANK,
		A.[DATE]
	INTO STOCK_TRAINING_SET_WEEK
	FROM #FINANCE_TEMP2 A
	ORDER BY A.STOCK_CODE, A.[DATE] DESC

	UPDATE STOCK_TRAINING_SET_WEEK SET PBR = (SELECT MAX(PBR) FROM STOCK_TRAINING_SET_WEEK) WHERE PBR IS NULL
	UPDATE STOCK_TRAINING_SET_WEEK SET PBR2 = (SELECT MAX(PBR2) FROM STOCK_TRAINING_SET_WEEK)WHERE PBR2 IS NULL
	UPDATE STOCK_TRAINING_SET_WEEK SET PDR = (SELECT MAX(PDR) FROM STOCK_TRAINING_SET_WEEK) WHERE PDR IS NULL
	UPDATE STOCK_TRAINING_SET_WEEK SET PER = (SELECT MAX(PER) FROM STOCK_TRAINING_SET_WEEK) WHERE PER IS NULL
	UPDATE STOCK_TRAINING_SET_WEEK SET PCR_OP = (SELECT MAX(PCR_OP) FROM STOCK_TRAINING_SET_WEEK) WHERE PCR_OP IS NULL
	UPDATE STOCK_TRAINING_SET_WEEK SET PCR_IV = (SELECT MAX(PCR_IV) FROM STOCK_TRAINING_SET_WEEK) WHERE PCR_IV IS NULL
	UPDATE STOCK_TRAINING_SET_WEEK SET PCR_FI = (SELECT MAX(PCR_FI) FROM STOCK_TRAINING_SET_WEEK) WHERE PCR_FI IS NULL
	UPDATE STOCK_TRAINING_SET_WEEK SET PSR = (SELECT MAX(PSR) FROM STOCK_TRAINING_SET_WEEK) WHERE PSR IS NULL
	UPDATE STOCK_TRAINING_SET_WEEK SET PFR = (SELECT MAX(PFR) FROM STOCK_TRAINING_SET_WEEK) WHERE PFR IS NULL
	UPDATE STOCK_TRAINING_SET_WEEK SET PXR = (SELECT MAX(PXR) FROM STOCK_TRAINING_SET_WEEK) WHERE PXR IS NULL
	UPDATE STOCK_TRAINING_SET_WEEK SET ROE = (SELECT MAX(ROE) FROM STOCK_TRAINING_SET_WEEK) WHERE ROE IS NULL
	UPDATE STOCK_TRAINING_SET_WEEK SET ROA = (SELECT MAX(ROA) FROM STOCK_TRAINING_SET_WEEK) WHERE ROA IS NULL

	DELETE FROM STOCK_TRAINING_SET_WEEK WHERE STOCK_CODE IN (SELECT STOCK_CODE FROM SPLIT_STOCK_SET)

	CREATE UNIQUE CLUSTERED INDEX STOCK_TRAINING_SET_WEEK_INDEX
	ON STOCK_TRAINING_SET_WEEK (STOCK_CODE ASC, [DATE] DESC)

	DROP TABLE #FINANCE_NEW_DATE
	DROP TABLE #FINANCE_TEMP
	DROP TABLE #FINANCE_TEMP2
END
GO

GO
CREATE PROCEDURE CONV_STOCK_NAME 
AS
BEGIN
	UPDATE STOCK_INFO SET STOCK_NAME = '현대차' WHERE STOCK_NAME = '현대자동차'
	UPDATE STOCK_INFO SET STOCK_NAME = '기아차' WHERE STOCK_NAME = '기아자동차'
	UPDATE STOCK_INFO SET STOCK_NAME = '쌍용차' WHERE STOCK_NAME = '쌍용자동차'
	UPDATE STOCK_INFO SET STOCK_NAME = '메디앙스' WHERE STOCK_NAME = '보령메디앙스'
	UPDATE STOCK_INFO SET STOCK_NAME = 'TBH글로벌' WHERE STOCK_NAME = '티비에이치글로벌'
	UPDATE STOCK_INFO SET STOCK_NAME = '유나이티드제약' WHERE STOCK_NAME = '유나이티드'
	UPDATE STOCK_INFO SET STOCK_NAME = '한국단자' WHERE STOCK_NAME = '한국단자공업'
	UPDATE STOCK_INFO SET STOCK_NAME = '삼화콘덴서' WHERE STOCK_NAME = '삼화콘덴서공업'
	UPDATE STOCK_INFO SET STOCK_NAME = '한국주철관' WHERE STOCK_NAME = '한국주철관공업'
	UPDATE STOCK_INFO SET STOCK_NAME = 'NHN' WHERE STOCK_NAME = '엔에이치엔'
	UPDATE STOCK_INFO SET STOCK_NAME = 'IHQ' WHERE STOCK_NAME = '아이에이치큐'
	UPDATE STOCK_INFO SET STOCK_NAME = '알이피' WHERE STOCK_NAME = '리켐'
	UPDATE STOCK_INFO SET STOCK_NAME = '삼화전자' WHERE STOCK_NAME = '삼화전자공업'
	UPDATE STOCK_INFO SET STOCK_NAME = '한국전력' WHERE STOCK_NAME = '한국전력공사'
	UPDATE STOCK_INFO SET STOCK_NAME = '천랩' WHERE STOCK_NAME = '신규상장'
	UPDATE STOCK_INFO SET STOCK_NAME = '신테카바이오' WHERE STOCK_NAME = '신규상장'
	UPDATE STOCK_INFO SET STOCK_NAME = '서울가스' WHERE STOCK_NAME = '서울도시가스'
	UPDATE STOCK_INFO SET STOCK_NAME = '부산가스' WHERE STOCK_NAME = '부산도시가스'
	UPDATE STOCK_INFO SET STOCK_NAME = '수출포장' WHERE STOCK_NAME = '한국수출포장공업'
	UPDATE STOCK_INFO SET STOCK_NAME = 'KT' WHERE STOCK_NAME = '케이티'
	UPDATE STOCK_INFO SET STOCK_NAME = '효성ITX' WHERE STOCK_NAME = '효성 ITX'
	UPDATE STOCK_INFO SET STOCK_NAME = '현대상사' WHERE STOCK_NAME = '현대종합상사'
	UPDATE STOCK_INFO SET STOCK_NAME = 'KCTC' WHERE STOCK_NAME = '케이씨티시'
	UPDATE STOCK_INFO SET STOCK_NAME = '롯데칠성' WHERE STOCK_NAME = '롯데칠성음료'
	UPDATE STOCK_INFO SET STOCK_NAME = '신세계 I&C' WHERE STOCK_NAME = '신세계I&C'
	UPDATE STOCK_INFO SET STOCK_NAME = '네이블' WHERE STOCK_NAME = '네이블커뮤니케이션즈'
	UPDATE STOCK_INFO SET STOCK_NAME = '서울식품' WHERE STOCK_NAME = '서울식품공업'
	UPDATE STOCK_INFO SET STOCK_NAME = '코썬바이오' WHERE STOCK_NAME = '현성바이탈'
	UPDATE STOCK_INFO SET STOCK_NAME = '하이소닉' WHERE STOCK_NAME = '지투하이소닉'
	UPDATE STOCK_INFO SET STOCK_NAME = '라이브파이낸셜' WHERE STOCK_NAME = '씨티젠'
	UPDATE STOCK_INFO SET STOCK_NAME = '휴니드' WHERE STOCK_NAME = '휴니드테크놀러지스'
	UPDATE STOCK_INFO SET STOCK_NAME = '코스나인' WHERE STOCK_NAME = '나인컴플렉스'
	UPDATE STOCK_INFO SET STOCK_NAME = '한국석유' WHERE STOCK_NAME = '한국석유공업'
	UPDATE STOCK_INFO SET STOCK_NAME = '코웨이' WHERE STOCK_NAME = '웅진코웨이'
	UPDATE STOCK_INFO SET STOCK_NAME = '계룡건설' WHERE STOCK_NAME = '계룡건설산업'
	UPDATE STOCK_INFO SET STOCK_NAME = '하이트론' WHERE STOCK_NAME = '하이트론씨스템즈'
	UPDATE STOCK_INFO SET STOCK_NAME = '삼성화재' WHERE STOCK_NAME = '삼성화재해상보험'
	UPDATE STOCK_INFO SET STOCK_NAME = '쌍용양회' WHERE STOCK_NAME = '쌍용양회공업'
	UPDATE STOCK_INFO SET STOCK_NAME = 'POSCO' WHERE STOCK_NAME = '포스코'
	UPDATE STOCK_INFO SET STOCK_NAME = '금호석유' WHERE STOCK_NAME = '금호석유화학'
	UPDATE STOCK_INFO SET STOCK_NAME = '메디포럼제약' WHERE STOCK_NAME = '씨트리'
	UPDATE STOCK_INFO SET STOCK_NAME = '유진투자증권' WHERE STOCK_NAME = '유진증권'
	UPDATE STOCK_INFO SET STOCK_NAME = '엘브이엠씨홀딩스' WHERE STOCK_NAME = '엘브이엠씨'
	UPDATE STOCK_INFO SET STOCK_NAME = '스카이라이프' WHERE STOCK_NAME = '케이티스카이라이프'
	UPDATE STOCK_INFO SET STOCK_NAME = '삼화페인트' WHERE STOCK_NAME = '삼화페인트공업'
	UPDATE STOCK_INFO SET STOCK_NAME = 'KCC' WHERE STOCK_NAME = '케이씨씨'
	UPDATE STOCK_INFO SET STOCK_NAME = '동양물산' WHERE STOCK_NAME = '동양물산기업'
END
GO

-- 파생 테이블들을 한번에 만들어주는 쿼리
GO
CREATE  PROCEDURE CREATE_DRV_TABLES 
AS
BEGIN
	-- 재무제표 전처리
	EXEC CONV_FINANCE_INFO
	EXEC CONV_STOCK_NAME

	-- 가격 변동률 계산
	EXEC UPDATE_STOCK_PRICE_RATIO
	EXEC CREATE_SPLIT_STOCK_SET

	-- 파생 테이블 생성
	EXEC CREATE_DRV_STOCK_THEME 
	EXEC CREATE_DRV_STOCK_PRICE_WEEK

	-- 트레이닝 셋 생성
	EXEC CREATE_DRV_FINANCE_RATIO
	EXEC CREATE_DRV_WEEK_FINANCE_RATIO
	EXEC CREATE_DRV_STOCK_EMBEDDING_SET
END
GO

-- 테이블에서 데이터 로드 SP --
GO
CREATE PROCEDURE SEL_STOCK_PRICE @STOCK_CODE INT
AS
BEGIN
   SELECT  
      [AVERAGE]
      ,[HIGHEST]
      ,[LOWEST]
      ,[VOLUME]
      ,[DATE]
	  ,[CHANGE_RATIO]
   FROM [STOCK_ANALYSIS].[dbo].[STOCK_PRICE_RATIO]
   WHERE STOCK_CODE = @STOCK_CODE
   ORDER BY DATE
END

GO
CREATE PROCEDURE SEL_STOCK_TRAINING_DATA @STOCK_CODE INT
AS
BEGIN
	SELECT A.STOCK_CODE, A.AVERAGE, A.HIGHEST, A.LOWEST, A.VOLUME, A.CHANGE_RATIO, A.MARKET_CAP,
		A.PBR, A.PBR2, A.PDR, A.PER, A.PCR_OP, A.PCR_IV, A.PCR_FI, A.PSR, A.PFR, A.PXR, A.ROE, A.ROA, A.MARKET_RANK, A.[DATE]
	FROM [STOCK_ANALYSIS].[dbo].[STOCK_TRAINING_SET] A
    WHERE STOCK_CODE = @STOCK_CODE
    ORDER BY DATE
END

GO
CREATE PROCEDURE SEL_STOCK_TRAINING_DATA_WEEK @STOCK_CODE INT
AS
BEGIN
	SELECT A.STOCK_CODE, A.AVERAGE, A.HIGHEST, A.LOWEST, A.VOLUME, A.CHANGE_RATIO, A.MARKET_CAP,
		A.PBR, A.PBR2, A.PDR, A.PER, A.PCR_OP, A.PCR_IV, A.PCR_FI, A.PSR, A.PFR, A.PXR, A.ROE, A.ROA, A.MARKET_RANK, A.[DATE]
	FROM [STOCK_ANALYSIS].[dbo].[STOCK_TRAINING_SET_WEEK] A
    WHERE STOCK_CODE = @STOCK_CODE
    ORDER BY DATE
END

GO
CREATE PROCEDURE SEL_STOCK_TEST_DATA @STOCK_CODE INT, @DATE DATE, @DATE_CNT INT
AS
BEGIN
	SELECT A.STOCK_CODE, A.AVERAGE, A.HIGHEST, A.LOWEST, A.VOLUME, A.CHANGE_RATIO, A.MARKET_CAP,
		A.PBR, A.PBR2, A.PDR, A.PER, A.PCR_OP, A.PCR_IV, A.PCR_FI, A.PSR, A.PFR, A.PXR, A.ROE, A.ROA, A.MARKET_RANK, A.[DATE]
	FROM [STOCK_ANALYSIS].[dbo].[STOCK_TRAINING_SET] A
    WHERE STOCK_CODE = @STOCK_CODE AND [DATE] BETWEEN DATEADD(DAY, -@DATE_CNT+1, @DATE) AND @DATE
    ORDER BY DATE
END

GO	
CREATE PROCEDURE SEL_STOCK_TEST_DATA_WEEK @STOCK_CODE INT, @DATE DATE, @WEEK_CNT INT
AS
BEGIN
	SELECT A.STOCK_CODE, A.AVERAGE, A.HIGHEST, A.LOWEST, A.VOLUME, A.CHANGE_RATIO, A.MARKET_CAP,
		A.PBR, A.PBR2, A.PDR, A.PER, A.PCR_OP, A.PCR_IV, A.PCR_FI, A.PSR, A.PFR, A.PXR, A.ROE, A.ROA, A.MARKET_RANK, A.[DATE]
	FROM [STOCK_ANALYSIS].[dbo].[STOCK_TRAINING_SET_WEEK] A
    WHERE STOCK_CODE = @STOCK_CODE AND [DATE] BETWEEN DATEADD(WEEK, -@WEEK_CNT+1, @DATE) AND @DATE
    ORDER BY DATE
END