Hive count(*) 会统计NULL，count(字段名)不统计NULL

布满荆棘的人生 2021-09-25 11:00 661阅读 0赞

select count(*) 
    from 
    (
    select 1  as c1
    union all
    select null as c1
    ) -- 2  , count(*) 包含null值
    
    
    select count(c1) 
    from 
    (
    select 1  as c1
    union all
    select null as c1
    ) -- 1  , count(c1) 不包含null值
    
    
    select count(case when 1=1 then 1 else 0 end) ; -- 1
    
    select count(case when 1<>1 then 1 else null end) -- 0 
    
    select count(0)  -- 1
    
    select count(null)  -- 0

select
    type,
    count(case when status = 3 then id else null end) as cnt
    from (
    select 
    1 as id ,
    2 as status,
    1 as type
    union ALL
    select 
    3 as id ,
    4 as status,
    1 as type
    union ALL
    select 
    2 as id ,
    3 as status,
    2 as type
    union ALL
    select 
    4 as id ,
    3 as status,
    2 as type
    ) t1
    group by type
    order by type ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70][]

\----------

select
    type,
    count(case when status = 3 then id else 0 end) as cnt
    from (
    select 
    1 as id ,
    2 as status,
    1 as type
    union ALL
    select 
    3 as id ,
    4 as status,
    1 as type
    union ALL
    select 
    2 as id ,
    3 as status,
    2 as type
    union ALL
    select 
    4 as id ,
    3 as status,
    2 as type
    ) t1
    group by type
    order by type ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 1][] end

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70]: /images/20210923/48a4c6f2b01f43bf97fd0809b9068e51.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 1]: /images/20210923/5898bedf9fe24762ac3e9521ffae7c64.png