版本2.5.12
建表语句
CREATE TABLE duk_bdm_yn_user_operations (
cld_month varchar (50) NULL COMMENT “”,
xw_code varchar (51) NULL COMMENT “”,
xw_name varchar (52) NULL COMMENT “”,
wgxw_code varchar (53) NULL COMMENT “”,
wgxw_name varchar (54) NULL COMMENT “”,
indstry_code varchar (50) NULL COMMENT “”,
indstry_name varchar (50) NULL COMMENT “”,
mainchnl_code varchar (55) NULL COMMENT “”,
mainchnl_name varchar (56) NULL COMMENT “”,
subchnl_code varchar (57) NULL COMMENT “”,
subchnl_name varchar (58) NULL COMMENT “”,
mngcst_code varchar (50) NULL COMMENT “”,
mngcst_name varchar (260) NULL COMMENT “”,
shop_code varchar (50) NULL COMMENT “”,
shop_name varchar (260) NULL COMMENT “”,
khxz varchar (61) NULL COMMENT “”,
sf_xc varchar (62) NULL COMMENT “”,
hdid varchar (63) NULL COMMENT “”,
hdxs varchar (64) NULL COMMENT “”,
hdbc varchar (65) NULL COMMENT “”,
hdlsmb decimal64(15, 2) NULL COMMENT “”,
hdlssj decimal64(15, 2) NULL COMMENT “”,
hkmbcc decimal64(15, 2) NULL COMMENT “”,
hkdbcc decimal64(15, 2) NULL COMMENT “”,
hkmb decimal64(15, 2) NULL COMMENT “”,
hksj decimal64(15, 2) NULL COMMENT “”,
dl_etl_date varchar (50) NULL COMMENT “”,
doris_pt date NULL COMMENT “”,
brand_code varchar (65533) NULL COMMENT “”,
brand_name varchar (65533) NULL COMMENT “”,
khxz_code varchar (20) NULL COMMENT “”
) ENGINE=OLAP
DUPLICATE KEY (cld_month, xw_code, xw_name, wgxw_code, wgxw_name, indstry_code, indstry_name, mainchnl_code, mainchnl_name, subchnl_code, subchnl_name)
COMMENT “云脑活动运营”
PARTITION BY RANGE (doris_pt)
( PARTITION p202101 VALUES [(“1997-01-01”), (“2022-01-01”)),
PARTITION p202201 VALUES [(“2022-01-01”), (“2022-02-01”)),
PARTITION p202202 VALUES [(“2022-02-01”), (“2022-03-01”)),
PARTITION p202203 VALUES [(“2022-03-01”), (“2022-04-01”)),
PARTITION p202204 VALUES [(“2022-04-01”), (“2022-05-01”)),
PARTITION p202205 VALUES [(“2022-05-01”), (“2022-06-01”)),
PARTITION p202206 VALUES [(“2022-06-01”), (“2022-07-01”)),
PARTITION p202207 VALUES [(“2022-07-01”), (“2022-08-01”)),
PARTITION p202208 VALUES [(“2022-08-01”), (“2022-09-01”)),
PARTITION p202209 VALUES [(“2022-09-01”), (“2022-10-01”)),
PARTITION p202210 VALUES [(“2022-10-01”), (“2022-11-01”)),
PARTITION p202211 VALUES [(“2022-11-01”), (“2022-12-01”)),
PARTITION p202212 VALUES [(“2022-12-01”), (“2023-01-01”)),
PARTITION p202301 VALUES [(“2023-01-01”), (“2023-02-01”)),
PARTITION p202302 VALUES [(“2023-02-01”), (“2023-03-01”)),
PARTITION p202303 VALUES [(“2023-03-01”), (“2023-04-01”)),
PARTITION p202304 VALUES [(“2023-04-01”), (“2023-05-01”)),
PARTITION p202305 VALUES [(“2023-05-01”), (“2023-06-01”)),
PARTITION p202306 VALUES [(“2023-06-01”), (“2023-07-01”)),
PARTITION p202307 VALUES [(“2023-07-01”), (“2023-08-01”)),
PARTITION p202308 VALUES [(“2023-08-01”), (“2023-09-01”)),
PARTITION p202309 VALUES [(“2023-09-01”), (“2023-10-01”)),
PARTITION p202310 VALUES [(“2023-10-01”), (“2023-11-01”)),
PARTITION p202311 VALUES [(“2023-11-01”), (“2023-12-01”)))
DISTRIBUTED BY HASH(shop_code) BUCKETS 8
PROPERTIES (
“replication_num” = “3”,
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “MONTH”,
“dynamic_partition.time_zone” = “Asia/Shanghai”,
“dynamic_partition.start” = “-2147483648”,
“dynamic_partition.end” = “2”,
“dynamic_partition.prefix” = “p”,
“dynamic_partition.buckets” = “3”,
“dynamic_partition.history_partition_num” = “0”,
“dynamic_partition.start_day_of_month” = “1”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”,
“compression” = “LZ4”,
“storage_medium” = “SSD”
);
查询语句报错
SELECT a.cld_month, sum(DISTINCT a.hdid ) AS hdidCount FROM hdop.duk_bdm_yn_user_operations a where 1=1 AND a.cld_month = ‘202308’ AND a.xw_code IN(‘12001’) GROUP by a.cld_month
