select count(*) from dwd_ess_cell_inc where time>= '2022-01-01' and namespace_code = 'zj-yh'
这个没走前缀索引
select count(*) from dwd_ess_cell_inc where time>= '2024-10-31' and namespace_code = 'zj-yh';
这个走了前缀索引
只是时间不一样就不走了？
CREATE TABLE dwd_ess_cell_inc (
time datetime NOT NULL COMMENT '采集时间戳',
namespace_code varchar(64) NOT NULL COMMENT '项目英文名',
device_instance_property_code varchar(64) NOT NULL COMMENT '属性英文名',
device_instance_code varchar(64) NOT NULL COMMENT '唯一映射key (instanceCode)',
value varchar(64) NULL COMMENT '值',
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=OLAP
UNIQUE KEY(time, namespace_code, device_instance_property_code, device_instance_code)
COMMENT 'DWD|储能数仓|电芯数据明细表'
PARTITION BY RANGE(time)
(PARTITION p202110 VALUES [('2021-10-01 00:00:00'), ('2021-11-01 00:00:00')),
PARTITION p202111 VALUES [('2021-11-01 00:00:00'), ('2021-12-01 00:00:00')),
PARTITION p202112 VALUES [('2021-12-01 00:00:00'), ('2022-01-01 00:00:00')),
PARTITION p202201 VALUES [('2022-01-01 00:00:00'), ('2022-02-01 00:00:00')),
PARTITION p202202 VALUES [('2022-02-01 00:00:00'), ('2022-03-01 00:00:00')),
PARTITION p202203 VALUES [('2022-03-01 00:00:00'), ('2022-04-01 00:00:00')),
PARTITION p202204 VALUES [('2022-04-01 00:00:00'), ('2022-05-01 00:00:00')),
PARTITION p202205 VALUES [('2022-05-01 00:00:00'), ('2022-06-01 00:00:00')),
PARTITION p202206 VALUES [('2022-06-01 00:00:00'), ('2022-07-01 00:00:00')),
PARTITION p202207 VALUES [('2022-07-01 00:00:00'), ('2022-08-01 00:00:00')),
PARTITION p202208 VALUES [('2022-08-01 00:00:00'), ('2022-09-01 00:00:00')),
PARTITION p202209 VALUES [('2022-09-01 00:00:00'), ('2022-10-01 00:00:00')),
PARTITION p202210 VALUES [('2022-10-01 00:00:00'), ('2022-11-01 00:00:00')),
PARTITION p202211 VALUES [('2022-11-01 00:00:00'), ('2022-12-01 00:00:00')),
PARTITION p202212 VALUES [('2022-12-01 00:00:00'), ('2023-01-01 00:00:00')),
PARTITION p202301 VALUES [('2023-01-01 00:00:00'), ('2023-02-01 00:00:00')),
PARTITION p202302 VALUES [('2023-02-01 00:00:00'), ('2023-03-01 00:00:00')),
PARTITION p202303 VALUES [('2023-03-01 00:00:00'), ('2023-04-01 00:00:00')),
PARTITION p202304 VALUES [('2023-04-01 00:00:00'), ('2023-05-01 00:00:00')),
PARTITION p202305 VALUES [('2023-05-01 00:00:00'), ('2023-06-01 00:00:00')),
PARTITION p202306 VALUES [('2023-06-01 00:00:00'), ('2023-07-01 00:00:00')),
PARTITION p202307 VALUES [('2023-07-01 00:00:00'), ('2023-08-01 00:00:00')),
PARTITION p202308 VALUES [('2023-08-01 00:00:00'), ('2023-09-01 00:00:00')),
PARTITION p202309 VALUES [('2023-09-01 00:00:00'), ('2023-10-01 00:00:00')),
PARTITION p202310 VALUES [('2023-10-01 00:00:00'), ('2023-11-01 00:00:00')),
PARTITION p202311 VALUES [('2023-11-01 00:00:00'), ('2023-12-01 00:00:00')),
PARTITION p202312 VALUES [('2023-12-01 00:00:00'), ('2024-01-01 00:00:00')),
PARTITION p202401 VALUES [('2024-01-01 00:00:00'), ('2024-02-01 00:00:00')),
PARTITION p202402 VALUES [('2024-02-01 00:00:00'), ('2024-03-01 00:00:00')),
PARTITION p202403 VALUES [('2024-03-01 00:00:00'), ('2024-04-01 00:00:00')),
PARTITION p202404 VALUES [('2024-04-01 00:00:00'), ('2024-05-01 00:00:00')),
PARTITION p202405 VALUES [('2024-05-01 00:00:00'), ('2024-06-01 00:00:00')),
PARTITION p202406 VALUES [('2024-06-01 00:00:00'), ('2024-07-01 00:00:00')),
PARTITION p202407 VALUES [('2024-07-01 00:00:00'), ('2024-08-01 00:00:00')),
PARTITION p202408 VALUES [('2024-08-01 00:00:00'), ('2024-09-01 00:00:00')),
PARTITION p202409 VALUES [('2024-09-01 00:00:00'), ('2024-10-01 00:00:00')),
PARTITION p202410 VALUES [('2024-10-01 00:00:00'), ('2024-11-01 00:00:00')),
PARTITION p202411 VALUES [('2024-11-01 00:00:00'), ('2024-12-01 00:00:00')),
PARTITION p202412 VALUES [('2024-12-01 00:00:00'), ('2025-01-01 00:00:00')),
PARTITION p202501 VALUES [('2025-01-01 00:00:00'), ('2025-02-01 00:00:00')))
DISTRIBUTED BY HASH(time, namespace_code, device_instance_property_code, device_instance_code) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"bloom_filter_columns" = "namespace_code",
"is_being_synced" = "false",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-36",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.default: 3",
"dynamic_partition.buckets" = "36",
"dynamic_partition.create_history_partition" = "true",
"dynamic_partition.history_partition_num" = "36",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.storage_policy" = "",
"dynamic_partition.start_day_of_month" = "1",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "false",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);
