建表语句:
CREATE TABLE
example_tbl_by_default
(timestamp
DATETIME NOT NULL COMMENT '日志时间',type
INT NOT NULL COMMENT '日志类型',error_code
INT NULL COMMENT '错误码',error_msg
VARCHAR(1024) NULL COMMENT '错误详细信息',op_id
BIGINT NULL COMMENT '负责人id',op_time
DATETIME NULL COMMENT '处理时间' ) ENGINE=OLAP DUPLICATE KEY(timestamp
,type
,error_code
) COMMENT 'OLAP' DISTRIBUTED BY HASH(type
) BUCKETS 1 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "min_load_replica_num" = "-1", "is_being_synced" = "false", "storage_medium" = "hdd", "storage_format" = "V2", "inverted_index_storage_format" = "V1", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false", "group_commit_interval_ms" = "10000", "group_commit_data_bytes" = "134217728" );
执行SQL:
SELECT MAKEDATE(YEAR(timestamp), 1) + INTERVAL QUARTER(timestamp) QUARTER - INTERVAL 1 QUARTER AS __timestamp,
error_code AS error_code,
COUNT(1) AS count
FROM test.example_tbl_by_default
GROUP BY error_code,
MAKEDATE(YEAR(timestamp), 1) + INTERVAL QUARTER(timestamp) QUARTER - INTERVAL 1 QUARTER
ORDER BY count DESC
LIMIT 10000;