Count(Distinct)

假如一个表数据分布如下。

使用下面的语句就容易发生数据倾斜：

SELECT
    ds,
    COUNT(DISTINCT shop_id) AS cnt
FROM
    demo_data0
GROUP BY
    ds;

解决方案如下：

方案一：通用两阶段聚合。

若shop_id字段数据分布均匀，可先按ds、shop_id两字段分组，再对ds做 Count(Distinct)；若shop_id字段数据不均匀（存在热点值），简单的两阶段聚合仍会倾斜，需在分区字段值中拼接随机数打散热点，具体见以下方式1、方式2。

-- 方式1：拼接随机数 CONCAT(ROUND(rand(),1)*10,'_', ds) AS rand_ds
SELECT  split_part(rand_ds, '_', 2) AS ds
       ,COUNT(*) AS id_cnt
FROM (
    SELECT  rand_ds, shop_id
    FROM    demo_data0
    GROUP BY rand_ds, shop_id
)
GROUP BY split_part(rand_ds, '_', 2);

-- 方式2：新增随机数字段 round(rand()*10) AS randint10
SELECT  ds
       ,COUNT(*) AS id_cnt
FROM (
    SELECT  ds, randint10, shop_id
    FROM    demo_data0
    GROUP BY ds, randint10, shop_id
)
GROUP BY ds;

方案二：类似两阶段聚合。

如果GroupBy与Distinct的字段数据都均匀，则可以采用如下方式优化，先Group By两分组字段（ds和shop_id）再使用Count(Distinct)命令。

SELECT  ds
       ,COUNT(*) AS cnt
FROM (
    SELECT  ds, shop_id
    FROM    demo_data0
    GROUP BY ds, shop_id
)
GROUP BY ds;