首页 > 其他分享 >clickhouse创建2个表

clickhouse创建2个表

时间：2023-03-31 18:46:32浏览次数：46  
标签：Nullable DEFAULT 创建 Float64 NUMBER PCT STK 个表 clickhouse

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

 

标签：Nullable,DEFAULT,创建,Float64,NUMBER,PCT,STK,个表,clickhouse
From： https://www.cnblogs.com/music-liang/p/17277177.html

相关文章

  • vue3创建工程
    创建Vue3项目的步骤如下：安装Node.jsVue3需要依赖Node.js环境，因此需要先安装Node.js。可以从官网下载Node.js的安装包并安装，也可以使用包管理器安装，例如在Ubuntu上可以使用以下命令安装：sudoapt-getupdatesudoapt-getinstallnodejssudoapt-getinstallnpm......
  • Visual Studio创建.net 6的WinForm一直提示"正在加载设计器"
    用VS创建.net6的WinForm，一直停留在"正在加载设计器"的界面*其实不止.net6，也能解决其它非.netframework的WinForm状况 原因1.删除了本地的安装缓存（VS安装时会把安装文件先下载到本地）2.关闭了或没打开NuGet 解决方法1.VS菜单->工具->选项->NuGet包管理器->程......
  • 01011 IDEA创建新项目
    创建新项目​ new->project创建新模块​ projectstructure->+->newmodule->改名字不改路径模块下创建包new->package->com.lxc.->作用会生成对应文件夹包下创建类​ new->class->name......
  • SHELL创建钉钉群接入机器人推送报警信息（text消息类型）
    步骤一：获取自定义机器人Webhook选择需要添加机器人的群聊，然后依次单击群设置 > 智能群助手。在机器人管理页面选择自定义机器人，输入机器人名字并选择要发送消息的群，同时可以为机器人设置机器人头像。 完成必要的安全设置，勾选我已阅读并同意《自定义机器人服务......
  • 2023-03-21-将指针所在地址传入函数来创建链表的一种写法
    如下，通过将指针所在的地址传入函数中即**p的形式，来保证直接对地址进行运算，而不需要再返回一个链表//双链表#include<stdio.h>#include<stdbool.h>#include<malloc.h>typedefstructDNode{intdata;structDNode*prior,*next;//prior指向上一个结点,next指......
  • Java：如何在PowerPoint幻灯片中创建散点图
    散点图是通过两组数据构成多个坐标点，考察坐标点的分布，判断两变量之间是否存在某种关联或总结坐标点的分布模式。散点图将序列显示为一组点，值由点在图表中的位置表示，类别由图表中的不同标记表示，通常用于比较跨类别的聚合数据。本文将为您介如何通过Java代码在PowerPoint幻灯片中创......
  • SQL-根据已有表结构创建表、修改表名
    stringsql_Rename_MainTable=string.Empty;//改表名(主表->历史表)的sqlstringsql_Create_MainTable=string.Empty;//通过历史表的表结构创建主表的sqlswitch(dbType){caseDbType.MySql:sql_Rename_MainTable="altertable'{0}'renameto'......
  • Linux系统中创建文件常用的方法！
    众所周知，在Windows系统中可以直接右键新建文件，而在Linux系统中，想要创建文件并非易事，需要通过执行命令来完成，那么Linux系统中创建文件常用的方法有哪些?本文为大家介绍一下Linux系统下创建文件的8种方法，快来了解一下吧。1、重定向符号>通常重定向符号可以创建一个0kb的......
  • MySQL数据库怎么创建表？MySQL数据库基础知识
    MySQL是一个关系型数据库管理系统，由瑞典MySQLAB公司开发，目前属于Oracle旗下产品。MySQL最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(RelationalDatabaseManagementSystem，关系数据库管理系统)应用软件之一。MySQL是一种关联数据库管理系统，关联数据库......
  • nsq topic创建流程
    一、topic结构体： 折叠源码typeTopic struct {    //64bitatomicvarsneedtobefirstforproperalignmenton32bitplatforms    messag......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99