create database test_db;
use test_db;

show databases ;
-- select .. sum() from a group by ....
CREATE TABLE IF NOT EXISTS test_db.example_site_visit
(
    `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 10
properties(
"replication_num"="1"
)

show tables;

insert into test_db.example_site_visit values
                                           (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);

select sum(cost) from example_site_visit;

CREATE TABLE IF NOT EXISTS test_db.user
(
    `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 10
properties(
"replication_num"="1"
)

show tables;

insert into test_db.user values
                             (10000,'wuyanzu','北京',18,0,12345678910,'北京朝阳区','2017-10-01 07:00:00'),
                             (10000,'wuyanzu','北京',19,0,12345678910,'北京朝阳区','2017-10-01 0:00:00'),
                             (10000,'zhangsan','北京',20,0,12345678910,'北京海淀区','2017-11-15 06:10:20');


CREATE TABLE IF NOT EXISTS test_db.example_log
(
    `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(`timestamp`) BUCKETS 10;

insert into test_db.example_log values
                                    ('2017-10-01 08:00:05',1,404,'not found page', 101, '2017-10-01 08:00:05'),
                                    ('2017-10-01 08:00:05',1,404,'not found page', 101, '2017-10-01 08:00:05'),
                                    ('2017-10-01 08:00:05',2,404,'not found page', 101, '2017-10-01 08:00:06'),
                                    ('2017-10-01 08:00:06',2,404,'not found page', 101, '2017-10-01 08:00:07');

HELP CREATE TABLE;