建表语句:
CREATE TABLE
example_tbl_by_default(timestampDATETIME NOT NULL COMMENT '日志时间',typeINT NOT NULL COMMENT '日志类型',error_codeINT NULL COMMENT '错误码',error_msgVARCHAR(1024) NULL COMMENT '错误详细信息',op_idBIGINT NULL COMMENT '负责人id',op_timeDATETIME 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;