KLine生成(定时) - Xiaotian0617/Center GitHub Wiki

创建CONTINUOUS QUERY，连续查询。只取kline(1m分钟)中足够计算最近两根分片时间的KLine数据计算，更新另一张表

CREATE CONTINUOUS QUERY cq_kline_3m ON TopCoinDB
RESAMPLE EVERY 15s FOR 6m
BEGIN
  SELECT LAST("close") AS "close", MAX("high") AS "high", MIN("low") AS "low", FIRST("open") AS "open", SUM("volume") AS "volume", FIRST("onlyKey") AS "onlyKey" 
  INTO "TopCoinDB"."autogen"."kline_3m"
  FROM "TopCoinDB"."autogen"."kline" 
  GROUP BY *,time(3m)
END;

CREATE CONTINUOUS QUERY cq_kline_5m ON TopCoinDB
RESAMPLE EVERY 15s FOR 10m
BEGIN
  SELECT LAST("close") AS "close", MAX("high") AS "high", MIN("low") AS "low", FIRST("open") AS "open", SUM("volume") AS "volume", FIRST("onlyKey") AS "onlyKey" 
  INTO "TopCoinDB"."autogen"."kline_5m" 
  FROM "TopCoinDB"."autogen"."kline" 
  GROUP BY *,time(5m)
END;

CREATE CONTINUOUS QUERY cq_kline_10m ON TopCoinDB
RESAMPLE EVERY 15s FOR 20m
BEGIN
  SELECT LAST("close") AS "close", MAX("high") AS "high", MIN("low") AS "low", FIRST("open") AS "open", SUM("volume") AS "volume", FIRST("onlyKey") AS "onlyKey" 
  INTO "TopCoinDB"."autogen"."kline_10m" 
  FROM "TopCoinDB"."autogen"."kline_5m" 
  GROUP BY *,time(10m)
END;

CREATE CONTINUOUS QUERY cq_kline_15m ON TopCoinDB
RESAMPLE EVERY 15s FOR 30m
BEGIN
  SELECT LAST("close") AS "close", MAX("high") AS "high", MIN("low") AS "low", FIRST("open") AS "open", SUM("volume") AS "volume", FIRST("onlyKey") AS "onlyKey" 
  INTO "TopCoinDB"."autogen"."kline_15m" 
  FROM "TopCoinDB"."autogen"."kline_5m" 
  GROUP BY *,time(15m)
END;

CREATE CONTINUOUS QUERY cq_kline_30m ON TopCoinDB
RESAMPLE EVERY 15s FOR 1h
BEGIN
  SELECT LAST("close") AS "close", MAX("high") AS "high", MIN("low") AS "low", FIRST("open") AS "open", SUM("volume") AS "volume", FIRST("onlyKey") AS "onlyKey" 
  INTO "TopCoinDB"."autogen"."kline_30m" 
  FROM "TopCoinDB"."autogen"."kline_15m" 
  GROUP BY *,time(30m)
END;

CREATE CONTINUOUS QUERY cq_kline_1h ON TopCoinDB
RESAMPLE EVERY 15s FOR 2h
BEGIN
  SELECT LAST("close") AS "close", MAX("high") AS "high", MIN("low") AS "low", FIRST("open") AS "open", SUM("volume") AS "volume", FIRST("onlyKey") AS "onlyKey" 
  INTO "TopCoinDB"."autogen"."kline_1h" 
  FROM "TopCoinDB"."autogen"."kline_30m" 
  GROUP BY *,time(1h)
END;

CREATE CONTINUOUS QUERY cq_kline_2h ON TopCoinDB
RESAMPLE EVERY 15s FOR 4h
BEGIN
  SELECT LAST("close") AS "close", MAX("high") AS "high", MIN("low") AS "low", FIRST("open") AS "open", SUM("volume") AS "volume", FIRST("onlyKey") AS "onlyKey" 
  INTO "TopCoinDB"."autogen"."kline_2h" 
  FROM "TopCoinDB"."autogen"."kline_1h" 
  GROUP BY *,time(2h)
END;

CREATE CONTINUOUS QUERY cq_kline_4h ON TopCoinDB
RESAMPLE EVERY 15s FOR 8h
BEGIN
  SELECT LAST("close") AS "close", MAX("high") AS "high", MIN("low") AS "low", FIRST("open") AS "open", SUM("volume") AS "volume", FIRST("onlyKey") AS "onlyKey" 
  INTO "TopCoinDB"."autogen"."kline_4h" 
  FROM "TopCoinDB"."autogen"."kline_2h" 
  GROUP BY *,time(4h)
END;

CREATE CONTINUOUS QUERY cq_kline_6h ON TopCoinDB
RESAMPLE EVERY 15s FOR 12h
BEGIN
  SELECT LAST("close") AS "close", MAX("high") AS "high", MIN("low") AS "low", FIRST("open") AS "open", SUM("volume") AS "volume", FIRST("onlyKey") AS "onlyKey" 
  INTO "TopCoinDB"."autogen"."kline_6h" 
  FROM "TopCoinDB"."autogen"."kline_2h" 
  GROUP BY *,time(6h)
END;

CREATE CONTINUOUS QUERY cq_kline_12h ON TopCoinDB
RESAMPLE EVERY 15s FOR 24h
BEGIN
  SELECT LAST("close") AS "close", MAX("high") AS "high", MIN("low") AS "low", FIRST("open") AS "open", SUM("volume") AS "volume", FIRST("onlyKey") AS "onlyKey" 
  INTO "TopCoinDB"."autogen"."kline_12h" 
  FROM "TopCoinDB"."autogen"."kline_6h" 
  GROUP BY *,time(12h)
END;

CREATE CONTINUOUS QUERY cq_kline_1d ON TopCoinDB
RESAMPLE EVERY 15s FOR 2d
BEGIN
  SELECT LAST("close") AS "close", MAX("high") AS "high", MIN("low") AS "low", FIRST("open") AS "open", SUM("volume") AS "volume", FIRST("onlyKey") AS "onlyKey" 
  INTO "TopCoinDB"."autogen"."kline_1d" 
  FROM "TopCoinDB"."autogen"."kline_12h" 
  GROUP BY *,time(1d)
END;

CREATE CONTINUOUS QUERY cq_kline_3d ON TopCoinDB
RESAMPLE EVERY 15s FOR 6d
BEGIN
  SELECT LAST("close") AS "close", MAX("high") AS "high", MIN("low") AS "low", FIRST("open") AS "open", SUM("volume") AS "volume", FIRST("onlyKey") AS "onlyKey" 
  INTO "TopCoinDB"."autogen"."kline_3D"
  FROM "TopCoinDB"."autogen"."kline_1D" 
  GROUP BY *,time(3d)
END;

CREATE CONTINUOUS QUERY cq_kline_1w ON TopCoinDB
RESAMPLE EVERY 15s FOR 2w
BEGIN
  SELECT LAST("close") AS "close", MAX("high") AS "high", MIN("low") AS "low", FIRST("open") AS "open", SUM("volume") AS "volume", FIRST("onlyKey") AS "onlyKey" 
  INTO "TopCoinDB"."autogen"."kline_1W" 
  FROM "TopCoinDB"."autogen"."kline_1D" 
  GROUP BY *,time(1w)
END;