程志伟的博客

关注微信公共号：小程在线关注优快云博客：程志伟的博客ClickHouse server version 21.6.61.向下取整：floor(x[,N])dblab-VirtualBox :) select floor(12.345,2);SELECT floor(12.345, 2)Query id: 38b30540-f1b7-464c-8a03-bf3e18b40131┌─floor(12.345, 2)─┐│ 12.34 │└──...

关注微信公共号：小程在线关注优快云博客：程志伟的博客ClickHouse server version 21.6.61.lower函数将所有字符串转成小写dblab-VirtualBox :) select lower('ABc');SELECT lower('ABc')Query id: f8c4bba3-9296-43fc-a752-cfb7cf3e323c┌─lower('ABc')─┐│ abc │└──────────────...

关注微信公共号：小程在线关注优快云博客：程志伟的博客ClickHouse server version 21.6.61. and返回值0, 如果至少有一个零值参数。dblab-VirtualBox :) select and (0,1,-9);SELECT 0 AND 1 AND -9Query id: 761714ee-2e2c-40cf-9d97-5da9788fdd9d┌─and(0, 1, -9)─┐│ 0 │└...

关注微信公共号：小程在线关注优快云博客：程志伟的博客ClickHouse server version 21.6.61. plus(a, b), a + b operatordblab-VirtualBox :) select plus(1,2);SELECT 1 + 2Query id: 3fe6d016-c351-4ade-a637-d4fd2f93a9e0┌─plus(1, 2)─┐│ 3 │└────────────┘1 row...

关注微信公共号：小程在线关注优快云博客：程志伟的博客ClickHouse server version 21.6.6创建分区表示例一：函数式分区dblab-VirtualBox :) create table visits(userid int,visitdate date,website String) engine=MergeTree() PARTITION BY toYYYYMM(visitdate) order by userid;CREATE TABLE v.

关注微信公共号：小程在线关注优快云博客：程志伟的博客ClickHouse server version 21.6.6表结构：dblab-VirtualBox :) desc columns_transformers format TSV;DESCRIBE TABLE columns_transformersFORMAT TSVQuery id: fad31334-702a-4360-8976-d9b08eeaec68i Int64 ...

关注微信公共号：小程在线关注优快云博客：程志伟的博客ClickHouse server version 21.6.6通过设置union_default_mode等于DISTINCT，相当于union的功能；通过设置union_default_mode等于ALL，相当于union all的功能；dblab-VirtualBox :) SET union_default_mode = 'DISTINCT';SET union_default_mode = 'DISTINC.

关注微信公共号：小程在线关注优快云博客：程志伟的博客ClickHouse server version 21.6.6OFFSET指定开始从查询结果集中返回行之前要跳过的行数。FETCH指定查询结果中可以包含的最大行数。WITH TIES选项用于根据ORDER BY子句返回结果集中最后一个位置的任何附加行。dblab-VirtualBox :)CREATE TABLE test_fetch(a Int, b Int) ENGINE = Memory;CREATE T.

关注微信公共号：小程在线关注优快云博客：程志伟的博客ClickHouse server version 21.6.6创建表dblab-VirtualBox :) CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;CREATE TABLE limit_by( `id` Int, `val` Int)ENGINE = MemoryQuery id: 892b335b-f25f-4aef-...

关注微信公共号：小程在线关注优快云博客：程志伟的博客ClickHouse server version 21.6.61.创建一个表结构dblab-VirtualBox :) create table group_by (class String,sex String,age String) ENGINE = Memory;CREATE TABLE group_by( `class` String, `sex` String, `age` Stri...

关注微信公共号：小程在线关注优快云博客：程志伟的博客ClickHouse server version 21.6.6ClickHouse中没有ROW_NUMBER OVER 和DENSE_RANK OVER函数需要借助特殊函数变相实现1.创建表并插入数据；insert into hanshu values ('aa',18,'nan'),('aa',19,'nan'),('aa',20,'nan'),('aa',19,'nv');2.将分组的字段改为数组..

dblab-VirtualBox :) CREATE TABLE arrays_test:-] (:-] s String,:-] arr Array(UInt8):-] ) ENGINE = Memory;CREATE TABLE arrays_test( `s` String, `arr` Array(UInt8))ENGINE = MemoryQuery id: 6294f6b5-de74-4952-8e9c-40ecfc515190Ok....

关注微信公共号：小程在线关注优快云博客：程志伟的博客EXCEPT函数：指定要从结果中排除的一个或多个列的名称。所有匹配的列名都将从输出中省略。1.启动数据库hadoop@dblab-VirtualBox:~$ clickhouse-client --passwordClickHouse client version 21.6.6.51 (official build).Password for user (default):Connecting to localho.

dblab-VirtualBox :) CREATE TABLE columns_transformers (i Int64, j Int16, k Int64) ENGINE = MergeTree ORDER by (i);CREATE TABLE columns_transformers( `i` Int64, `j` Int16, `k` Int64)ENGINE = MergeTreeORDER BY iQuery id: 05c008a6-ab92-43a...

hadoop@dblab-VirtualBox:~$ clickhouse-clientClickHouse client version 21.6.6.51 (official build).Connecting to localhost:9000 as user default.Code: 516. DB::Exception: Received from localhost:9000. DB::Exception: default: Authentication failed: password

关注微信公共号：小程在线关注优快云博客：程志伟的博客hadoop@dblab-VirtualBox:~$ sudo apt-get install dirmngr[sudo] hadoop 的密码：正在读取软件包列表... 完成正在分析软件包的依赖关系树 正在读取状态信息... 完成 dirmngr 已经是最新版 (2.1.11-6ubuntu2.1)。升级了 0 个软件包，新安装了 0 个软件包，要卸载 0 个软件包，有 853 个软件包...