在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。
参考资料: