ColumnName | Type | AggregationType | Comment |
user_id | LARGEINT | - | 用户 ID |
date | DATE | - | 数据导入日期 |
city | VARCHAR(20) | - | 用户所在城市 |
age | SMALLINT | - | 用户年龄 |
sex | TINYINT | - | 用户性别 |
last_visit_date | DATETIME | REPLACE | 用户最后一次访问时间 |
cost | BIGINT | SUM | 用户总消费 |
max_dwell_time | INT | MAX | 用户最大停留时间 |
min_dwell_time | INT | MIN | 用户最小停留时间 |
CREATE TABLE IF NOT EXISTS example_db.expamle_tbl(`user_id` LARGEINT NOT NULL COMMENT "用户id",`date` DATE NOT NULL COMMENT "数据导入日期时间",`city` VARCHAR(20) COMMENT "用户所在城市",`age` SMALLINT COMMENT "用户年龄",`sex` TINYINT COMMENT "用户性别",`last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",`cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",`max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",`min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间")AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)DISTRIBUTED BY HASH(`user_id`) BUCKETS 1PROPERTIES ("replication_allocation" = "tag.location.default: 1");
AggregationType
,分为 Key (维度列) 和 Value(指标列)。没有设置 AggregationType
的,如 user_id
、date
、age
、sex
称为 Key,而设置了 AggregationType
的称为 Value。
当我们导入数据时,对于 Key 列相同的行会聚合成一行,而 Value 列会按照设置的 AggregationType
进行聚合。 AggregationType
目前有以下四种聚合方式:user_id | date | city | age | sex | last_visit_date | cost | max_dwell_time | min_dwell_time |
10000 | 2017-10-01 | 北京 | 20 | 0 | 2017-10-01 06:00:00 | 20 | 10 | 10 |
10000 | 2017-10-01 | 北京 | 20 | 0 | 2017-10-01 07:00:00 | 15 | 2 | 2 |
10001 | 2017-10-01 | 北京 | 30 | 1 | 2017-10-01 17:05:45 | 2 | 22 | 22 |
10002 | 2017-10-02 | 上海 | 20 | 1 | 2017-10-02 12:59:12 | 200 | 5 | 5 |
10003 | 2017-10-02 | 广州 | 32 | 0 | 2017-10-02 11:20:00 | 30 | 11 | 11 |
10004 | 2017-10-01 | 深圳 | 35 | 0 | 2017-10-01 10:00:15 | 100 | 3 | 3 |
10004 | 2017-10-03 | 深圳 | 35 | 0 | 2017-10-03 10:20:22 | 11 | 6 | 6 |
数据 | 说明 |
10000 | 用户 ID,每个用户唯一识别 ID |
2017-10-01 | 数据入库时间,精确到日期 |
北京 | 用户所在城市 |
20 | 用户年龄 |
0 | 性别男(1 代表女性) |
2017-10-01 06:00:00 | 用户本次访问该页面的时间,精确到秒 |
20 | 用户本次访问产生的消费 |
10 | 用户本次访问,驻留该页面的时间 |
10 | 用户本次访问,驻留该页面的时间(冗余) |
user_id | date | city | age | sex | last_visit_date | cost | max_dwell_time | min_dwell_time |
10000 | 2017-10-01 | 北京 | 20 | 0 | 2017-10-01 07:00:00 | 35 | 10 | 2 |
10001 | 2017-10-01 | 北京 | 30 | 1 | 2017-10-01 17:05:45 | 2 | 22 | 22 |
10002 | 2017-10-02 | 上海 | 20 | 1 | 2017-10-02 12:59:12 | 200 | 5 | 5 |
10003 | 2017-10-02 | 广州 | 32 | 0 | 2017-10-02 11:20:00 | 30 | 11 | 11 |
10004 | 2017-10-01 | 深圳 | 35 | 0 | 2017-10-01 10:00:15 | 100 | 3 | 3 |
10004 | 2017-10-03 | 深圳 | 35 | 0 | 2017-10-03 10:20:22 | 11 | 6 | 6 |
last_visit_date
开始:2017-10-01 07:00:00
:因为 last_visit_date
列的聚合方式为 REPLACE,所以 2017-10-01 07:00:00
替换了 2017-10-01 06:00:00
保存了下来。2017-10-01 06:00:00
。而对于不同导入批次中的数据,可以保证,后一批次的数据会替换前一批次。35
:因为 cost
列的聚合类型为 SUM,所以由 20 + 15 累加获得 35。10
:因为 max_dwell_time
列的聚合类型为 MAX,所以 10 和 2 取最大值,获得 10。2
:因为 min_dwell_time
列的聚合类型为 MIN,所以 10 和 2 取最小值,获得 2。ColumnName | Type | AggregationType | Comment |
user_id | LARGEINT | - | 用户 ID |
date | DATE | - | 数据导入日期 |
timestamp | DATETIME | - | 数据导入时间,精确到秒 |
city | VARCHAR(20) | - | 用户所在城市 |
age | SMALLINT | - | 用户年龄 |
sex | TINYINT | - | 用户性别 |
last_visit_date | DATETIME | REPLACE | 用户最后一次访问时间 |
cost | BIGINT | SUM | 用户总消费 |
max_dwell_time | INT | MAX | 用户最大停留时间 |
min_dwell_time | INT | MIN | 用户最小停留时间 |
timestamp
,记录精确到秒的数据导入时间。
同时,将AGGREGATE KEY
设置为AGGREGATE KEY(user_id, date, timestamp, city, age, sex)
user_id | date | timestamp | city | age | sex | last_visit_date | cost | max_dwell_time | min_dwell_time |
10000 | 2017-10-01 | 2017-10-01 08:00:05 | 北京 | 20 | 0 | 2017-10-01 06:00:00 | 20 | 10 | 10 |
10000 | 2017-10-01 | 2017-10-01 09:00:05 | 北京 | 20 | 0 | 2017-10-01 07:00:00 | 15 | 2 | 2 |
10001 | 2017-10-01 | 2017-10-01 18:12:10 | 北京 | 30 | 1 | 2017-10-01 17:05:45 | 2 | 22 | 22 |
10002 | 2017-10-02 | 2017-10-02 13:10:00 | 上海 | 20 | 1 | 2017-10-02 12:59:12 | 200 | 5 | 5 |
10003 | 2017-10-02 | 2017-10-02 13:15:00 | 广州 | 32 | 0 | 2017-10-02 11:20:00 | 30 | 11 | 11 |
10004 | 2017-10-01 | 2017-10-01 12:12:48 | 深圳 | 35 | 0 | 2017-10-01 10:00:15 | 100 | 3 | 3 |
10004 | 2017-10-03 | 2017-10-03 12:38:20 | 深圳 | 35 | 0 | 2017-10-03 10:20:22 | 11 | 6 | 6 |
user_id | date | timestamp | city | age | sex | last_visit_date | cost | max_dwell_time | min_dwell_time |
10000 | 2017-10-01 | 2017-10-01 08:00:05 | 北京 | 20 | 0 | 2017-10-01 06:00:00 | 20 | 10 | 10 |
10000 | 2017-10-01 | 2017-10-01 09:00:05 | 北京 | 20 | 0 | 2017-10-01 07:00:00 | 15 | 2 | 2 |
10001 | 2017-10-01 | 2017-10-01 18:12:10 | 北京 | 30 | 1 | 2017-10-01 17:05:45 | 2 | 22 | 22 |
10002 | 2017-10-02 | 2017-10-02 13:10:00 | 上海 | 20 | 1 | 2017-10-02 12:59:12 | 200 | 5 | 5 |
10003 | 2017-10-02 | 2017-10-02 13:15:00 | 广州 | 32 | 0 | 2017-10-02 11:20:00 | 30 | 11 | 11 |
10004 | 2017-10-01 | 2017-10-01 12:12:48 | 深圳 | 35 | 0 | 2017-10-01 10:00:15 | 100 | 3 | 3 |
10004 | 2017-10-03 | 2017-10-03 12:38:20 | 深圳 | 35 | 0 | 2017-10-03 10:20:22 | 11 | 6 | 6 |
timestamp
列,所有行的 Key 都不完全相同。也就是说,只要保证导入的数据中,每一行的 Key 都不完全相同,那么即使在聚合模型下,Doris 也可以保存完整的明细数据。user_id | date | city | age | sex | last_visit_date | cost | max_dwell_time | min_dwell_time |
10000 | 2017-10-01 | 北京 | 20 | 0 | 2017-10-01 07:00:00 | 35 | 10 | 2 |
10001 | 2017-10-01 | 北京 | 30 | 1 | 2017-10-01 17:05:45 | 2 | 22 | 22 |
10002 | 2017-10-02 | 上海 | 20 | 1 | 2017-10-02 12:59:12 | 200 | 5 | 5 |
10003 | 2017-10-02 | 广州 | 32 | 0 | 2017-10-02 11:20:00 | 30 | 11 | 11 |
10004 | 2017-10-01 | 深圳 | 35 | 0 | 2017-10-01 10:00:15 | 100 | 3 | 3 |
10004 | 2017-10-03 | 深圳 | 35 | 0 | 2017-10-03 10:20:22 | 11 | 6 | 6 |
user_id | date | city | age | sex | last_visit_date | cost | max_dwell_time | min_dwell_time |
10004 | 2017-10-03 | 深圳 | 35 | 0 | 2017-10-03 11:22:00 | 44 | 19 | 19 |
10005 | 2017-10-03 | 长沙 | 29 | 1 | 2017-10-03 18:11:02 | 3 | 1 | 1 |
user_id | date | city | age | sex | last_visit_date | cost | max_dwell_time | min_dwell_time |
10000 | 2017-10-01 | 北京 | 20 | 0 | 2017-10-01 07:00:00 | 35 | 10 | 2 |
10001 | 2017-10-01 | 北京 | 30 | 1 | 2017-10-01 17:05:45 | 2 | 22 | 22 |
10002 | 2017-10-02 | 上海 | 20 | 1 | 2017-10-02 12:59:12 | 200 | 5 | 5 |
10003 | 2017-10-02 | 广州 | 32 | 0 | 2017-10-02 11:20:00 | 30 | 11 | 11 |
10004 | 2017-10-01 | 深圳 | 35 | 0 | 2017-10-01 10:00:15 | 100 | 3 | 3 |
10004 | 2017-10-03 | 深圳 | 35 | 0 | 2017-10-03 11:22:00 | 55 | 19 | 6 |
10005 | 2017-10-03 | 长沙 | 29 | 1 | 2017-10-03 18:11:02 | 3 | 1 | 1 |
ColumnName | Type | IsKey | Comment |
user_id | BIGINT | Yes | 用户 ID |
username | VARCHAR(50) | Yes | 用户昵称 |
city | VARCHAR(20) | No | 用户所在城市 |
age | SMALLINT | No | 用户年龄 |
sex | TINYINT | No | 用户性别 |
phone | LARGEINT | No | 用户电话 |
address | VARCHAR(500) | No | 用户住址 |
register_time | DATETIME | No | 用户注册时间 |
CREATE TABLE IF NOT EXISTS example_db.expamle_tbl(`user_id` LARGEINT NOT NULL COMMENT "用户id",`username` VARCHAR(50) NOT NULL COMMENT "用户昵称",`city` VARCHAR(20) COMMENT "用户所在城市",`age` SMALLINT COMMENT "用户年龄",`sex` TINYINT COMMENT "用户性别",`phone` LARGEINT COMMENT "用户电话",`address` VARCHAR(500) COMMENT "用户地址",`register_time` DATETIME COMMENT "用户注册时间")UNIQUE KEY(`user_id`, `username`)DISTRIBUTED BY HASH(`user_id`) BUCKETS 1PROPERTIES ("replication_allocation" = "tag.location.default: 1");
ColumnName | Type | AggregationType | Comment |
user_id | BIGINT | - | 用户 ID |
username | VARCHAR(50) | - | 用户昵称 |
city | VARCHAR(20) | REPLACE | 用户所在城市 |
age | SMALLINT | REPLACE | 用户年龄 |
sex | TINYINT | REPLACE | 用户性别 |
phone | LARGEINT | REPLACE | 用户电话 |
address | VARCHAR(500) | REPLACE | 用户住址 |
register_time | DATETIME | REPLACE | 用户注册时间 |
CREATE TABLE IF NOT EXISTS example_db.expamle_tbl(`user_id` LARGEINT NOT NULL COMMENT "用户id",`username` VARCHAR(50) NOT NULL COMMENT "用户昵称",`city` VARCHAR(20) REPLACE COMMENT "用户所在城市",`age` SMALLINT REPLACE COMMENT "用户年龄",`sex` TINYINT REPLACE COMMENT "用户性别",`phone` LARGEINT REPLACE COMMENT "用户电话",`address` VARCHAR(500) REPLACE COMMENT "用户地址",`register_time` DATETIME REPLACE COMMENT "用户注册时间")AGGREGATE KEY(`user_id`, `username`)DISTRIBUTED BY HASH(`user_id`) BUCKETS 1PROPERTIES ("replication_allocation" = "tag.location.default: 1");
ColumnName | Type | SortKey | Comment |
timestamp | DATETIME | Yes | 日志时间 |
type | INT | Yes | 日志类型 |
error_code | INT | Yes | 错误码 |
error_msg | VARCHAR(1024) | No | 错误详细信息 |
op_id | BIGINT | No | 负责人 ID |
op_time | DATETIME | No | 处理时间 |
CREATE TABLE IF NOT EXISTS example_db.expamle_tbl(`timestamp` DATETIME NOT NULL COMMENT "日志时间",`type` INT NOT NULL COMMENT "日志类型",`error_code` INT COMMENT "错误码",`error_msg` VARCHAR(1024) COMMENT "错误详细信息",`op_id` BIGINT COMMENT "负责人id",`op_time` DATETIME COMMENT "处理时间")DUPLICATE KEY(`timestamp`, `type`)DISTRIBUTED BY HASH(`type`) BUCKETS 1PROPERTIES ("replication_allocation" = "tag.location.default: 1");
排序
。在 DUPLICATE KEY 的选择上,我们建议适当的选择前 2-4 列就可以。这种数据模型适用于既没有聚合需求,又没有主键唯一性约束的原始数据的存储。ColumnName | Type | AggregationType | Comment |
user_id | LARGEINT | - | 用户 ID |
date | DATE | - | 数据导入日期 |
cost | BIGINT | SUM | 用户总消费 |
user_id | date | cost |
10001 | 2017-11-20 | 50 |
10002 | 2017-11-21 | 39 |
user_id | date | cost |
10001 | 2017-11-20 | 1 |
10001 | 2017-11-21 | 5 |
10003 | 2017-11-22 | 22 |
user_id | date | cost |
10001 | 2017-11-20 | 51 |
10001 | 2017-11-21 | 5 |
10002 | 2017-11-21 | 39 |
10003 | 2017-11-22 | 22 |
SELECT MIN(cost) FROM table;
SELECT COUNT(*) FROM table;
user_id | date | cost |
10001 | 2017-11-20 | 50 |
10002 | 2017-11-21 | 39 |
user_id | date | cost |
10001 | 2017-11-20 | 1 |
10001 | 2017-11-21 | 5 |
10003 | 2017-11-22 | 22 |
user_id | date | cost |
10001 | 2017-11-20 | 51 |
10001 | 2017-11-21 | 5 |
10002 | 2017-11-21 | 39 |
10003 | 2017-11-22 | 22 |
select count(*) from table;
的正确结果应该为 4。但如果我们只扫描 user_id
这一列,如果加上查询时聚合,最终得到的结果是 3(10001, 10002, 10003)。而如果不加查询时聚合,则得到的结果是 5(两批次一共5行数据)。可见这两个结果都是不对的。user_id
和 date
这两列的数据,再加上查询时聚合,才能返回 4 这个正确的结果。也就是说,在 count( ) 查询中,Doris 必须扫描所有的 AGGREGATE KEY 列(这里就是 user_id
和 date
),并且聚合后,才能得到语意正确的结果。当聚合列非常多时,count( ) 查询需要扫描大量的数据。ColumnName | Type | AggregateType | Comment |
user_id | BIGINT | - | 用户 ID |
date | DATE | - | 数据导入日期 |
cost | BIGINT | SUM | 用户总消费 |
count | BIGINT | SUM | 用于计算 count |
select count(*) from table;
的结果等价于 select sum(count) from table;
。而后者的查询效率将远高于前者。不过这种方式也有使用限制,就是用户需要自行保证,不会重复导入 AGGREGATE KEY 列都相同的行。否则,select sum(count) from table;
只能表述原始导入的行数,而不是 select count(*) from table;
的语义,前者值会错误的增大。count
列的聚合类型改为 REPLACE,且依然值恒为 1。那么 select sum(count) from table;
和 select count(*) from table;
的结果将是一致的。并且这种方式,没有导入重复行的限制。CREATE TABLE site_visit(siteid INT,city SMALLINT,username VARCHAR(32),pv BIGINT SUM DEFAULT '0')AGGREGATE KEY(siteid, city, username)DISTRIBUTED BY HASH(siteid) BUCKETS 10;
CREATE TABLE sales_order(orderid BIGINT,status TINYINT,username VARCHAR(32),amount BIGINT DEFAULT '0')UNIQUE KEY(orderid)DISTRIBUTED BY HASH(orderid) BUCKETS 10;
CREATE TABLE session_data(visitorid SMALLINT,sessionid BIGINT,visittime DATETIME,city CHAR(20),province CHAR(20),ip varchar(32),brower CHAR(20),url VARCHAR(1024))DUPLICATE KEY(visitorid, sessionid)DISTRIBUTED BY HASH(sessionid, visitorid) BUCKETS 10;
本页内容是否解决了您的问题?