【执行语句】DELETE FROM rpt_crm_shop_efficiency_inc_d PARTITION p20221127 WHERE shop_code = ‘CN43193434’ and report_qty = 0 ;
【报错信息】[42000][1064] Column[report_qty] is not key column or storage model is not duplicate or column type is float or double.
【StarRocks版本】2.3.7 43fbd55
【建表语句】
CREATE TABLE rpt_crm_shop_efficiency_inc_d (
dayno date NULL COMMENT “xxx”,
shop_code varchar(255) NULL COMMENT “xxx”,
sku_code varchar(255) NULL COMMENT “xxx”,
shop_id varchar(255) NULL COMMENT “xxx”,
sales_org_code varchar(255) NULL COMMENT “xxx”,
exclusive_org_code varchar(255) NULL COMMENT “xxx”,
shop_name varchar(255) NULL COMMENT “xxx”,
shop_type int(11) NULL COMMENT “xxx”,
shop_type_desc varchar(255) NULL COMMENT “xxx”,
shop_level int(11) NULL COMMENT “xxx”,
shop_level_desc varchar(255) NULL COMMENT “xxx”,
shop_operate_status int(11) NULL COMMENT “xxx”,
shop_operate_status_desc varchar(255) NULL COMMENT “xxx”,
start_cooper_date varchar(255) NULL COMMENT “xxx”,
end_cooper_date varchar(255) NULL COMMENT “xxx”,
is_have_guide int(11) NULL COMMENT “xxx”,
is_sale_service_shop int(11) NULL COMMENT “xxx”,
location_attr int(11) NULL COMMENT “xxx”,
location_attr_desc varchar(255) NULL COMMENT “xxx”,
biz_area_attr int(11) NULL COMMENT “xxx”,
biz_area_attr_desc varchar(255) NULL COMMENT “xxx”,
renovation_level int(11) NULL COMMENT “xxx”,
renovation_level_desc varchar(255) NULL COMMENT “xxx”,
cust_id bigint(20) NULL COMMENT “xxx”,
cust_code varchar(255) NULL COMMENT “xxx”,
cust_name varchar(255) NULL COMMENT “xxx”,
cust_level varchar(255) NULL COMMENT “xxx”,
cust_level_desc varchar(255) NULL COMMENT “xxx”,
area_code varchar(255) NULL COMMENT “xxx”,
area_name varchar(255) NULL COMMENT “xxx”,
city_code varchar(255) NULL COMMENT “xxx”,
city_name varchar(255) NULL COMMENT “xxx”,
city_level varchar(255) NULL COMMENT “xxx”,
city_level_desc varchar(255) NULL COMMENT “xxx”,
image_version varchar(255) NULL COMMENT “xxx”,
image_version_desc varchar(255) NULL COMMENT “xxx”,
report_qty_cnt float NULL COMMENT “xxx”,
report_qty float NULL COMMENT “xxx”,
act_recv_amt float NULL COMMENT “xxx”,
op_flag varchar(255) NULL COMMENT “xxx”,
etl_time datetime NULL COMMENT “xxx”,
cust_group_code varchar(255) NULL COMMENT “xxx”,
cust_group_name varchar(255) NULL COMMENT “xxx”,
stat_period date NULL COMMENT “xxx”
) ENGINE=OLAP
DUPLICATE KEY(dayno, shop_code, sku_code)
COMMENT “xxxx”
PARTITION BY RANGE(dayno)
(
START (“2022-01-01”) END (“2023-12-31”) EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(`shop_code`, `sku_code`) BUCKETS 16
PROPERTIES (
“replication_num” = “3”,
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “DAY”,
“dynamic_partition.time_zone” = “Asia/Chongqing”,
“dynamic_partition.start” = “-2147483648”,
“dynamic_partition.end” = “24”,
“dynamic_partition.prefix” = “p”,
“dynamic_partition.buckets” = “16”,
“in_memory” = “false”,
“storage_format” = “V2”,
“enable_persistent_index” = “false”
);