starrocks聚合表使用

 1、创建聚合表

CREATE TABLE IF NOT EXISTS site_access_log (
    `access_date` DATE NOT NULL COMMENT "访问日期",
    `ip` VARCHAR(200) COMMENT "ip",
    `cnt` BIGINT SUM DEFAULT "0" COMMENT "访问数"
)
AGGREGATE KEY(access_date, ip)
PARTITION BY RANGE(`access_date`) ( )
DISTRIBUTED BY HASH(`access_date`)
PROPERTIES (
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-180",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "sal",
    "dynamic_partition.buckets" = "10"
);

如果要初始化之前的数据，需要执行以下脚本

ALTER TABLE site_access_log SET("dynamic_partition.enable"="false");
ALTER TABLE site_access_log ADD PARTITIONS START ("2023-11-17") END ("2023-11-30") EVERY (INTERVAL 1 DAY);
ALTER TABLE site_access_log SET("dynamic_partition.enable"="true");

2、直接执行insert语句

INSERT INTO site_access_log VALUES('2023-11-18','1.1.1.1',1);

这样就能自动统计数量