sql 语句中count()条件计数

在count函数里直接对需要计数的变量写条件表达式 但是需要加 or NULL，如下所示：

select count(distinct job_id), count(pay_pv>0 or NULL) from (
select t1.job_id, count(*) as times_pv, count(tmp.pay_time) as pay_pv
    from dac_seven_dev.boss_block_label_l2_ymt as t1
    join dwd_boss_flow.dwd_flow_action_biz_scene_block_di b on cast(t1.job_id as int)= cast(b.actionp as int)
    left join (
    select distinct s.target_id as job_id, s.add_time as pay_time
    from dwd_boss_flow.dwd_flow_action_biz_scene_block_di b 
        join ods_boss_business.ods_boss_block_order bbo on bbo.id = cast(get_json_object(cast (regexp_replace(CommonIdDecodeUDF(b.actionp4), \[|\], ) as string),$.businessId) as int)
        join ods_boss_business.ods_boss_block_sub_order s on s.parent_order_id = bbo.id 
    where b.action=biz-block-click-time
    and coalesce(b.user_source,0)=0 --填补0
    and b.bg=1 --boss
    and cast(get_json_object(cast (regexp_replace(CommonIdDecodeUDF(b.actionp4), \[|\], ) as string),$.businessType) as int) =2
    and b.actionp=2 -- 确认支付页
    and b.ds between 2021-10-15 and 2021-11-11
    and s.target_type=2
    ) tmp on tmp.job_id = cast(b.actionp as int)

如果不加or NULL的话，计数为总的数据条数，达不到目的。

需要加or NULL的原因是：count(任意内容)都会统计出所有记录数，因为count只有在遇见NULL时不计数，即count(null)==0，因此前者单引号内不管输入什么值都会统计出所有记录数。而例子中如果仅仅是count(pay_uv>0)，对于<=0时会返回False，而并不是NULL，所以仍旧会被计数。所以，在使用的时候需要注意，记得加or NULL。

参考资料：

1.