【SQL】论count 和 distinct

DROP table if exists tmp_test_insert_none_yc_table;
create table tmp_test_insert_none_yc_table(
    user_id   string comment '记录首次时间'
   ,start_date      string comment '记录首次时间'
   ,end_date        string comment '记录最后一次登录时间'
) comment 'user_id粒度的汇总表'
PARTITIONED BY
(
    dt       STRING COMMENT '分区日期'
)
lifecycle 2;



insert overwrite table tttttttt partition(dt = '20200915') VALUES
("a", 1, 1),
("b", 1, 1),
("b", 1, 1),
("c", 1, 1),
(null, 1, 1);

SELECT 
    DISTINCT user_id  --- 3,算上了null
FROM 
    tttttttt;

SELECT 
    count(1)  ----- 5
    , count(DISTINCT user_id) --- 2，不算null
    , count(CASE WHEN user_id = "b" THEN 1 end)  --- 2
    , count(distinct CASE WHEN user_id = "b" THEN 1 end)  ---- 1
    , count(distinct CASE WHEN user_id = "b" THEN user_id end)   ---- 1，和上条一样，有没有id都无所谓
    , count(CASE WHEN user_id = "a" THEN 1 end) ---- 1
    , count(CASE WHEN user_id is not null THEN 1 end)  ---- 4
    , count(CASE WHEN user_id != null THEN 1 end)  ---- 0
    
FROM 
    tttttttt;