Initial EMA: average N day's closing price,
EMA: (after today's closing price - yesterday EMA) * (2.0)/(N + 1) + yesterday EMA
The initial database SQL:
CREATE TABLE "STOCK_H (
""ID" VARCHAR2 (36 BYTE),
"CODE" VARCHAR2 (2000 BYTE),
"TYPE" VARCHAR2 (2000 BYTE),
"SCODE" VARCHAR2 (2000 BYTE),
"NAME" VARCHAR2 (2000 BYTE),
"C" NUMBER (30, 8),
"H" NUMBER (30, 8),
"L" NUMBER (30, 8),
"O" NUMBER (30, 8),
"LC" NUMBER (30, 8),
"PV" NUMBER (30, 8),
"PA" NUMBER (30, 8),
"VOL" NUMBER (30, 8),
"The AMOUNT" NUMBER (30, 8),
"PE" NUMBER (30, 8),
"TR" NUMBER (30, 8),
"PER" NUMBER (30, 8),
"VR" VARCHAR2 (2000 BYTE),
"TMV" NUMBER (30, 8),
"CMV" NUMBER (30, 8),
"PBV" NUMBER (30, 8),
"PV_L" NUMBER (30, 8),
"PV_5" NUMBER (30, 8),
"PV_60" NUMBER (30, 8),
"PV_Y" NUMBER (30, 8),
"CR NUMBER" (30, 8),
"IV" NUMBER (30, 8),
"IR" NUMBER (30, 8),
"SLIV" NUMBER (30, 8),
"SLIR" NUMBER (30, 8),
"LIV" NUMBER (30, 8),
"LIR" NUMBER (30, 8),
"MIV" NUMBER (30, 8),
"MIR" NUMBER (30, 8),
"Sivs" NUMBER (30, 8),
"SIR" NUMBER (30, 8),
"The UN NUMBER,"
"DN" NUMBER,
"UCODE" VARCHAR2 (2000 BYTE),
"UNAME" VARCHAR2 (2000 BYTE),
"UPV" NUMBER (30, 8),
"DCODE" VARCHAR2 (2000 BYTE),
"DNAME" VARCHAR2 (2000 BYTE),
"DPV" NUMBER (30, 8),
"TTM" NUMBER,
"MT" NUMBER
);
COMMENT ON the COLUMN "STOCK_H". "ID" IS 'primary key';
COMMENT ON the COLUMN "STOCK_H". "CODE" IS 'stock CODE;
COMMENT ON the COLUMN "STOCK_H". "TYPE" IS the 'TYPE';
COMMENT ON the COLUMN "STOCK_H". "SCODE" IS 'stocks';
COMMENT ON the COLUMN "STOCK_H". "NAME" IS 'stock NAME;
COMMENT ON the COLUMN "STOCK_H." "C" IS 'latest price;
COMMENT ON the COLUMN "STOCK_H". "H" IS 'high'.
COMMENT ON the COLUMN "STOCK_H." "L" IS 'low';
COMMENT ON the COLUMN "STOCK_H". "O" IS 'open';
COMMENT ON the COLUMN "STOCK_H". "LC" IS 'yesterday closing price;
COMMENT ON the COLUMN "STOCK_H". "PV" IS 'size;
COMMENT ON the COLUMN "STOCK_H." "PA" IS 'rise and fall the forehead;
COMMENT ON the COLUMN "STOCK_H." "VOL" IS 'volume';
COMMENT ON the COLUMN "STOCK_H". "the AMOUNT" IS 'turnover;
COMMENT ON the COLUMN "STOCK_H." "PE" IS 'amplitude;
COMMENT ON the COLUMN "STOCK_H". "TR" IS 'turnover rate;
COMMENT ON the COLUMN "STOCK_H". "PER" IS 'p/e ratio;
COMMENT ON the COLUMN "STOCK_H". "VR" IS 'than';
COMMENT ON the COLUMN "STOCK_H". "TMV" IS 'total market value;
COMMENT ON the COLUMN "STOCK_H". "CMV IS 'current market;
COMMENT ON the COLUMN "STOCK_H". "PBV IS ', '";
COMMENT ON the COLUMN "STOCK_H". "PV_L" IS 'dish tail pace;
COMMENT ON the COLUMN "STOCK_H". "PV_5" IS five minutes' or ';
COMMENT ON the COLUMN "STOCK_H". "PV_60 IS '60 day price";
COMMENT ON the COLUMN "STOCK_H". "PV_Y" IS 'year-to-date price;
COMMENT ON the COLUMN "STOCK_H". "CR" IS 'committee than;
COMMENT ON the COLUMN "STOCK_H". "IV" IS 'main net;
COMMENT ON the COLUMN "STOCK_H". "IR" IS 'main net inflows of';
COMMENT ON the COLUMN "STOCK_H". "SLIV" IS 'super single net;
COMMENT ON the COLUMN "STOCK_H". "SLIR" IS 'super single net inflows of';
COMMENT ON the COLUMN "STOCK_H". "LIV IS 'big single net";
COMMENT ON the COLUMN "STOCK_H". "LIR" IS 'big single net inflows of';
COMMENT ON the COLUMN "STOCK_H". "MIV" IS 'in a single net;
COMMENT ON the COLUMN "STOCK_H". "MIR" IS single net inflows of ' ';
COMMENT ON the COLUMN "STOCK_H". "sivs" IS 'small single net;
COMMENT ON the COLUMN "STOCK_H". "SIR" IS 'small single net inflows of';
COMMENT ON the COLUMN "STOCK_H". "UN" IS 'rising stock quantity;
COMMENT ON the COLUMN "STOCK_H". "DN" IS 'falling stock quantity;
COMMENT ON the COLUMN "STOCK_H". "UCODE" IS 'led code;
COMMENT ON the COLUMN "STOCK_H". "UNAME" IS 'led a rally in stock;
COMMENT ON the COLUMN "STOCK_H". "UPV" IS 'collar or degrees;
COMMENT ON the COLUMN "STOCK_H". "DCODE" IS 'led by code;
COMMENT ON the COLUMN "STOCK_H". "DNAME" IS 'shares led declines;
COMMENT ON the COLUMN "STOCK_H". "DPV" IS 'led by amplitude;
COMMENT ON the COLUMN "STOCK_H". "TTM" IS 'time to market;
COMMENT ON the COLUMN "STOCK_H". "MT" IS 'ON time';
COMMENT ON the TABLE "STOCK_H" IS 'stocks daily data;
SQL implementation EMA formula:
WITH STOCK_TEMP (XH, CODE, C, EMA5 EMA10, EMA20, EMA60, EMA120, MT) AS (
- initialize the first day
SELECT 1 AS XH, CODE, 0 C, AVG (case when XH & lt; C=5 then end) EMA5, AVG (case when XH & lt; C=10 then end) EMA10, AVG (case when XH & lt; 20 then C=end) EMA20, AVG (case when XH & lt;=60 then end C) EMA60, AVG (case when XH & lt;=120 EMA120 then C end), MIN (MT) MT FROM (
The SELECT row_number () over (partition by CODE order by MT ASC) XH, CODE, C, MT from STOCK_H
) GROUP BY CODE
UNION ALL
- in the future every day
The SELECT T.X H, tc ODE, tc, (tc - S.E MA5) * (2.0/6) + S.E MA5, (tc - S.E MA10) * (2.0/11) + S.E MA10, (tc - S.E MA20) * (2.0/21) + S.E MA20, (tc - S.E MA60) * (2.0/61) + S.E MA60, (tc - S.E MA120) * (2.0/121) + S.E MA120, T.M T the FROM (
The SELECT row_number () over (partition by CODE order by MT ASC) XH, CODE, C, MT from STOCK_H
) T, STOCK_TEMP S WHERE S.C ODE=tc ODE AND T.X H=S.X H + 1
)
The SELECT T.X H, tc ODE, tc, T.E MA5, T.E MA10, T.E MA20, T.E MA60, T.E MA120, T.M T FROM STOCK_TEMP T WHERE T.M T=to_char (sysdate, 'yyyymmdd') ORDER BY CODE
Implementation effect screenshots:
