# Count(Distinct)
假如一个表数据分布如下。
| **ds（分区）** | **cnt（记录数）** |
|:---|:---|
| 20220416   | 73025514    |
| 20220415 | 2292806      |
| 20220417  | 2319160   |
   
使用下面的语句就容易发生数据倾斜：
```
SELECT
    ds,
    COUNT(DISTINCT shop_id) AS cnt
FROM
    demo_data0
GROUP BY
    ds;
```
解决方案如下：
| **序号** | **方案**    | **说明**                                          |
|:---|:---|:---|
| 方案一    | 通用两阶段聚合 | 在partition字段值拼接随机数。                             |
| 方案二    | 类似两阶段聚合 | 先通过Group By两分组字段(ds+shop_id)再使用Count(Distinct)。 |
   
方案一：通用两阶段聚合。
若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;
```