clickhouse创建2个表

oracle的建表语句：

 

-- UPCENTER.STK_RISE_DROP_INFO definition

CREATE TABLE "UPCENTER"."STK_RISE_DROP_INFO" 
   (    "ISVALID" NUMBER(1,0) DEFAULT 1 NOT NULL ENABLE, 
    "CREATETIME" TIMESTAMP (6) DEFAULT SYSDATE NOT NULL ENABLE, 
    "UPDATETIME" TIMESTAMP (6) DEFAULT SYSDATE NOT NULL ENABLE, 
    "STK_UNI_CODE" NUMBER(10,0) NOT NULL ENABLE, 
    "END_DATE" DATE NOT NULL ENABLE, 
    "STAT_SECT_PAR" NUMBER(4,0) NOT NULL ENABLE, 
    "RISE_DROP_RANG" NUMBER(18,6), 
    "STK_AMP" NUMBER(18,6), 
    "AVG_TURN_RATE" NUMBER(18,6), 
     CONSTRAINT "PK_STK_RISE_DROP_INFO" PRIMARY KEY ("STK_UNI_CODE", "END_DATE", "STAT_SECT_PAR")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 33554432 NEXT 33554432 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "UPCENTER_IDX"  ENABLE, 
     SUPPLEMENTAL LOG GROUP "GGS_106726" ("STK_UNI_CODE", "END_DATE", "STAT_SECT_PAR") ALWAYS, 
     SUPPLEMENTAL LOG GROUP "GGS_94835" ("STK_UNI_CODE", "END_DATE", "STAT_SECT_PAR") ALWAYS
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 33554432 NEXT 33554432 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "UPCENTER" ;

CREATE INDEX "UPCENTER"."IX_STK_RISE_DROP_INFO" ON "UPCENTER"."STK_RISE_DROP_INFO" ("UPDATETIME") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 33554432 NEXT 33554432 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "UPCENTER_IDX" ;
  CREATE INDEX "UPCENTER"."IX_STK_RISE_DROP_INFO_1" ON "UPCENTER"."STK_RISE_DROP_INFO" ("END_DATE") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 33554432 NEXT 33554432 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "UPCENTER_IDX" ;
  CREATE UNIQUE INDEX "UPCENTER"."PK_STK_RISE_DROP_INFO" ON "UPCENTER"."STK_RISE_DROP_INFO" ("STK_UNI_CODE", "END_DATE", "STAT_SECT_PAR") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 33554432 NEXT 33554432 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "UPCENTER_IDX" ;
  CREATE INDEX "UPCENTER"."IX_STK_RISE_DROP_INFO_SYNC" ON "UPCENTER"."STK_RISE_DROP_INFO" ("UPDATETIME", "END_DATE", "STAT_SECT_PAR", "STK_UNI_CODE") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "UPCENTER_IDX" ;

 

 

-- UPCENTER.BSE_STK_ITVL_SHOW definition

CREATE TABLE "UPCENTER"."BSE_STK_ITVL_SHOW" 
   (    "CREATETIME" TIMESTAMP (6) DEFAULT SYSTIMESTAMP NOT NULL ENABLE, 
    "UPDATETIME" TIMESTAMP (6) DEFAULT SYSTIMESTAMP NOT NULL ENABLE, 
    "STK_UNI_CODE" NUMBER(10,0) NOT NULL ENABLE, 
    "END_DATE" DATE NOT NULL ENABLE, 
    "CHAN_PCT_W" NUMBER(18,6), 
    "CHAN_PCT_1M" NUMBER(18,6), 
    "CHAN_PCT_3M" NUMBER(18,6), 
    "CHAN_PCT_6M" NUMBER(18,6), 
    "CHAN_PCT_Y" NUMBER(18,6), 
    "CHAN_PCT_TW" NUMBER(18,6), 
    "CHAN_PCT_TM" NUMBER(18,6), 
    "CHAN_PCT_TY" NUMBER(18,6), 
    "AVG_TURNRATE_W" NUMBER(18,6), 
    "AVG_TURNRATE_1M" NUMBER(18,6), 
    "AVG_TURNRATE_3M" NUMBER(18,6), 
    "AVG_TURNRATE_6M" NUMBER(18,6), 
    "AVG_TURNRATE_Y" NUMBER(18,6), 
    "AVG_TURNRATE_TW" NUMBER(18,6), 
    "AVG_TURNRATE_TM" NUMBER(18,6), 
    "AVG_TURNRATE_TY" NUMBER(18,6), 
    "RANGE_PCT_W" NUMBER(18,6), 
    "RANGE_PCT_1M" NUMBER(18,6), 
    "RANGE_PCT_3M" NUMBER(18,6), 
    "RANGE_PCT_6M" NUMBER(18,6), 
    "RANGE_PCT_Y" NUMBER(18,6), 
    "RANGE_PCT_TW" NUMBER(18,6), 
    "RANGE_PCT_TM" NUMBER(18,6), 
    "RANGE_PCT_TY" NUMBER(18,6), 
    "ISVALID" NUMBER(1,0) NOT NULL ENABLE, 
     CONSTRAINT "PK_BSE_STK_ITVL_SHOW" PRIMARY KEY ("END_DATE", "STK_UNI_CODE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "UPCENTER_IDX"  ENABLE, 
     SUPPLEMENTAL LOG GROUP "GGS_246961" ("STK_UNI_CODE", "END_DATE") ALWAYS
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "UPCENTER" ;

CREATE INDEX "UPCENTER"."IX_BSE_STK_ITVL_SHOW" ON "UPCENTER"."BSE_STK_ITVL_SHOW" ("UPDATETIME" DESC) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "UPCENTER_IDX" ;
  CREATE UNIQUE INDEX "UPCENTER"."PK_BSE_STK_ITVL_SHOW" ON "UPCENTER"."BSE_STK_ITVL_SHOW" ("END_DATE", "STK_UNI_CODE") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "UPCENTER_IDX" ;

 

 

clickhouse的建表语句：

CREATE TABLE IF NOT EXISTS STK_RISE_DROP_INFO (ISVALID Int64,
CREATETIME DateTime64(6),
UPDATETIME DateTime64(6),
STK_UNI_CODE Int64,
END_DATE DateTime,
STAT_SECT_PAR Int64,
RISE_DROP_RANG Nullable(Float64),
STK_AMP Nullable(Float64),
AVG_TURN_RATE Nullable(Float64)
) ENGINE = ReplacingMergeTree 
ORDER BY (END_DATE,STAT_SECT_PAR,STK_UNI_CODE) SETTINGS index_granularity = 8192

 

CREATE TABLE IF NOT EXISTS BSE_STK_ITVL_SHOW (CREATETIME DateTime64(6),
UPDATETIME DateTime64(6),
STK_UNI_CODE Int64,
END_DATE DateTime,
CHAN_PCT_W Nullable(Float64),
CHAN_PCT_1M Nullable(Float64),
CHAN_PCT_3M Nullable(Float64),
CHAN_PCT_6M Nullable(Float64),
CHAN_PCT_Y Nullable(Float64),
CHAN_PCT_TW Nullable(Float64),
CHAN_PCT_TM Nullable(Float64),
CHAN_PCT_TY Nullable(Float64),
AVG_TURNRATE_W Nullable(Float64),
AVG_TURNRATE_1M Nullable(Float64),
AVG_TURNRATE_3M Nullable(Float64),
AVG_TURNRATE_6M Nullable(Float64),
AVG_TURNRATE_Y Nullable(Float64),
AVG_TURNRATE_TW Nullable(Float64),
AVG_TURNRATE_TM Nullable(Float64),
AVG_TURNRATE_TY Nullable(Float64),
RANGE_PCT_W Nullable(Float64),
RANGE_PCT_1M Nullable(Float64),
RANGE_PCT_3M Nullable(Float64),
RANGE_PCT_6M Nullable(Float64),
RANGE_PCT_Y Nullable(Float64),
RANGE_PCT_TW Nullable(Float64),
RANGE_PCT_TM Nullable(Float64),
RANGE_PCT_TY Nullable(Float64),
ISVALID Int64
) ENGINE = ReplacingMergeTree 
ORDER BY (END_DATE,STK_UNI_CODE) SETTINGS index_granularity = 8192