Hive count(*) count(1) count(c1) 区别

太过爱你忘了你带给我的痛 2021-09-25 02:34 559阅读 0赞

**1 count(c1) 不统计c1=NULL的记录，但统计c1='' 和 c1=' ' 空格的记录！**

**2 count(\*) = count(1) = count(10000) = count(c1) + count(\*) where c1 is null **

3 count(数字)，这个数字可以设置任意的正整数，不影响结果。

select count(1)
                from (
                
                select 1 as c1 
                
                union all
                select '' as c1
                     
                union all
                select null as c1
                ) t1  -- 3 行
    
    
                select count(1)
                from (
                
                select 1 as c1 
                
                union all
                select '' as c1
                           
                union all
                select ' ' as c1 -- 空格
                
                union all
                select null as c1
                ) t1  -- 4 行
    
    --------------
    
    
                select count(*)
                from (
                
                select 1 as c1 
                
                union all
                select '' as c1
                     
                union all
                select null as c1
                ) t1  -- 3 行
    
    
                select count(*)
                from (
                
                select 1 as c1 
                
                union all
                select '' as c1
                           
                union all
                select ' ' as c1 -- 空格
                
                union all
                select null as c1
                ) t1  -- 4 行
    
    --------------
    
          select count(c1)
                from (
                
                select 1 as c1 
                
                union all
                select '' as c1
                     
                union all
                select null as c1
                ) t1  -- 2行
    
    
                select count(c1)
                from (
                
                select 1 as c1 
                
                union all
                select '' as c1
                           
                union all
                select ' ' as c1 -- 空格
                
                union all
                select null as c1
                ) t1  -- 3行
    
    --------------
    
     
    
    Hive2.0.0 版本