mysql计算拼接率

拼接率计算公式：key关联条数/非空key条数

实现：通过将两个表进行left join关联，分别计算count(左表key)即为非空条数not_null_count，count(右表key)即为关联条数join_count，则左表key拼接率为join_count/not_null_count*100%。

好处：通过left join可以一条sql完成拼接率分子、分母计算，而不用分别计算分子、分母。

实例：计算order_product表外键customer_code的拼接率

select count(customer_code) as not_null_count,count(part_num) as join_count
from 
(
select regexp_replace(customer_code,'\"','') as customer_code
from u_datapipeline.order_product
where dptime=20201019
) as a
left join
(
select distinct part_num
from bigdata.siebel_s_prod_int_new
where dt = '20201019'
) as b
on a.customer_code = b.part_num
;