doris表语句

DDL

show databases;

use usertag;

show tables;

CREATE TABLE student
(
id INT,
name VARCHAR(50),
age INT,
count BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY(id, name, age)
DISTRIBUTED BY HASH(id) buckets 10
PROPERTIES("replication_num" = "1");

---

表模型

AGGREGATE KEY （聚合表）

[注] AGGREGATE KEY字段必须全都一样才能聚合pv字段

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;

insert into site_visit values(1, 1, 'zs', 10)

UNIQUE KEY （去重表）

CREATE TABLE sale_order
(
orderid BIGINT,
status TINYINT,
username VARCHAR(32),
amount BIGINT  DEFAULT '0'
)
UNIQUE KEY(orderid)
DISTRIBUTED BY HASH(orderid) buckets 10;

DUPLICATE KEY

只指定排序列，相同的行不会合并，适用于数据无需提前聚合的分析业务，存储明细用

CREATE TABLE session_data
(
visitorid SMALLINT,
sessionid BIGINT,
city CHAR(20),
ip VARCHAR(32)
)
DUPLICATE KEY(visitorid, sessionid)
DISTRIBUTED BY HASH(visitorid, sessionid) buckets 10;