DDL语句如下:
CREATE TABLE ods_working_procedure_check_detail
(
dim_id
bigint NOT NULL,
uid
bigint NOT NULL,
check_date
datetime NOT NULL,
proj_code
int NULL,
check_value
float NULL
) ENGINE=OLAP
UNIQUE KEY(dim_id
, uid
, check_date
)
DISTRIBUTED BY HASH(dim_id
) BUCKETS 48;
普通的计数可以正常返回结果:
SELECT year(check_date) as cyear, month(check_date) as cmonth, count(1) as num
FROM ods_working_procedure_check_detail
where check_date >= '2023-12-01 00:00:00' and check_date < '2024-01-01 00:00:00'
group by 1,2;
一旦加了去重统计,就会报错:
SELECT year(check_date) as cyear, month(check_date) as cmonth, count(1) as num, count(distinct uid) as num1
FROM ods_working_procedure_check_detail
where check_date >= '2023-12-01 00:00:00' and check_date < '2024-01-01 00:00:00'
group by 1,2;