メモリストページ

メモ：メモ一覧

INSERT INTO EveryMonth4Data
SELECT 
 LEFT(A.YMD, 6) AS YM
,A.BRAND_CODE
,B.START_AMT
,MAX(A.HIGH_AMT) AS HIGH_AMT
,MIN(A.LOW_AMT) AS LOW_AMT
,C.END_AMT
,SUM(A.VOLUME) AS VOLUME
,'' AS TRADING_AMT
FROM EveryDay4Data A
INNER JOIN 
(
	select 
	 Z.BRAND_CODE
	,MAX(Z.YMD) AS LAST_YMD
	,MIN(Z.YMD) AS FAST_YMD

from EveryDay4Data Z
    WHERE Z.YMD LIKE '202003%'

group by Z.BRAND_CODE
) AA ON
    AA.BRAND_CODE = A.BRAND_CODE

INNER JOIN EveryDay4Data B ON
    B.BRAND_CODE = A.BRAND_CODE
AND B.YMD = AA.FAST_YMD
INNER JOIN EveryDay4Data C ON
    C.BRAND_CODE = A.BRAND_CODE
AND C.YMD = AA.LAST_YMD

WHERE
    A.YMD LIKE '202003%'

GROUP BY  LEFT(A.YMD, 6), A.BRAND_CODE,B.START_AMT,C.END_AMT

-- 移動平均
INSERT INTO MonthMoveAverage
SELECT 
A.YM
,A.BRAND_CODE
,( 
	select AVG(Z.END_AMT)
	from EveryMonth4Data Z
	where Z.BRAND_CODE = A.BRAND_CODE
	AND Z.TIME_NUM BETWEEN A.TIME_NUM - 5 AND A.TIME_NUM
) AS move6
,( 
	select AVG(Z.END_AMT)
	from EveryMonth4Data Z
	where Z.BRAND_CODE = A.BRAND_CODE
	AND Z.TIME_NUM BETWEEN A.TIME_NUM - 11 AND A.TIME_NUM
) AS move12
,( 
	select AVG(Z.END_AMT)
	from EveryMonth4Data Z
	where Z.BRAND_CODE = A.BRAND_CODE
	AND Z.TIME_NUM BETWEEN A.TIME_NUM - 23 AND A.TIME_NUM
) AS move24
FROM EveryMonth4Data A
WHERE A.YM > '201805'

GROUP BY A.YM,A.BRAND_CODE

-- ボリンジャーバンド
INSERT INTO EveryMonth4BoriData
SELECT 
  A.YM
 ,A.BRAND_CODE
 ,B.SIGMA
 ,C.move12
FROM EveryMonth4Data A
INNER JOIN MonthMoveAverage C ON
    C.BRAND_CODE = A.BRAND_CODE
AND C.YM = A.YM
LEFT JOIN 
(
	SELECT 
	 Z.YM
	,Z.BRAND_CODE
	,Z.SIGMA
	FROM 
	(
		 SELECT
		   S.YM
		  ,S.BRAND_CODE
		  ,SQRT(SUM((SS.END_AMT - XX.move12) * (SS.END_AMT - XX.move12)) / COUNT(*)) AS SIGMA
		 FROM EveryMonth4Data S
		 INNER JOIN EveryMonth4Data SS ON
		 	 SS.BRAND_CODE = S.BRAND_CODE
		 AND SS.TIME_NUM BETWEEN S.TIME_NUM - 11 AND S.TIME_NUM
		 INNER JOIN MonthMoveAverage XX ON
			XX.YM = S.YM
		AND XX.BRAND_CODE = S.BRAND_CODE
		WHERE 
		     S.YM = '202103'
		GROUP by S.YM ,S.BRAND_CODE
	) Z
) B ON
	B.YM = A.YM
AND B.BRAND_CODE = A.BRAND_CODE
WHERE A.YM = '202103'
  AND A.END_AMT <> 0
  AND C.move12 <> '0'

-- 高値作成
INSERT INTO EveryMonth4HighData
SELECT 
 A.YM
,A.BRAND_CODE
,A.HIGH_AMT
,A.TIME_NUM
FROM EveryMonth4Data A
LEFT JOIN EveryMonth4Data B ON
    A.BRAND_CODE = B.BRAND_CODE
AND A.TIME_NUM BETWEEN B.TIME_NUM - 5 AND B.TIME_NUM + 5
AND B.HIGH_AMT > A.HIGH_AMT

WHERE B.BRAND_CODE IS NULL

-- 低値作成
INSERT INTO EveryMonth4LowData
SELECT 
 A.YM
,A.BRAND_CODE
,A.LOW_AMT
,A.TIME_NUM
FROM EveryMonth4Data A
LEFT JOIN EveryMonth4Data B ON
    A.BRAND_CODE = B.BRAND_CODE
AND A.TIME_NUM BETWEEN B.TIME_NUM - 5 AND B.TIME_NUM + 5
AND B.LOW_AMT < A.LOW_AMT

WHERE B.BRAND_CODE IS NULL

-- 低値番号付け
UPDATE 
EveryMonth4LowData A
INNER JOIN (
    SELECT 
       Z.YM
      ,Z.BRAND_CODE
      ,COUNT(*) AS CNT
    FROM EveryMonth4LowData Z
    INNER JOIN EveryMonth4LowData ZZ ON
	    ZZ.BRAND_CODE = Z.BRAND_CODE
	AND ZZ.YM <= Z.YM

GROUP BY 
	     Z.YM
	    ,Z.BRAND_CODE
 
) B ON
    B.YM = A.YM
AND B.BRAND_CODE = A.BRAND_CODE
SET A.TIME_NUM = B.CNT;