计算前复权价格

[zjp-CN]

-- 计算复权数据
DROP TABLE IF EXISTS rustdx.qfq_x;
CREATE TABLE rustdx.qfq_x (
    -- 前复权系数
    code FixedString(6),
    x    Float64,
    PRIMARY KEY(code)
) ENGINE = MergeTree  AS
WITH
qfq AS (
    SELECT code, LAST_VALUE(close) / LAST_VALUE(factor) AS qfq_multi
    FROM rustdx.factor
    GROUP BY code
    ORDER BY code
)
SELECT * FROM qfq;
--  SELECT * FROM rustdx.qfq_x LIMIT 2;
DROP TABLE IF EXISTS rustdx.qfq;
CREATE TABLE rustdx.qfq (
    -- 前复权价格
    date  Date,
    code  FixedString(6),
    close Float64,
    open  Float64,
    high  Float64,
    low   Float64,
    PRIMARY KEY(date, code)
) ENGINE = MergeTree AS
WITH
qfq_x AS (SELECT * FROM rustdx.qfq_x),
fct AS (
    SELECT date, code, open/close AS open, high/close AS high, low/close AS low, factor
    FROM rustdx.factor
),
raw AS (
    SELECT *
    FROM fct
    LEFT JOIN qfq_x ON qfq_x.code = fct.code
)
SELECT date, code, factor*x AS close, open*close AS open, high*close AS high, low*close AS low
FROM raw
ORDER BY date, code

计算后的数据在 rustdx.qfq 表，并且每次运行此脚本时，将原表删除再重建。