做abtest 评估必会的python代码、sql代码

1、sql计算指标显著性

--评估显著性


raw as (
select pid, exp_group_name, 
nvl(bubble_cnt,0) as bubble_cnt
from pas_tag 
left join bubble
on pas_tag.pid=bubble.passenger_id
)


select
    metric,
     round(metric_t, 4) as metric_t,
     round(metric_c, 4) as metric_c,
     N_t,
     N_c,
     round(mean_difference, 4) as mean_difference,
     concat(round(lift * 100, 2), '%') as lift,
     round(upper_bound, 4) as upper_bound,
      round(lower_bound, 4) as lower_bound,
      case
          when upper_bound * lower_bound > 0 then '显著'
          else '不显著'
          
      end as siginificance
  from(
          select
              metric,
              metric_t,
              metric_c,
              N_t,
              N_c,
              sum_ss_t,
              sum_ss_c,
              metric_t - metric_c as mean_difference,
              case
                  when metric_c <> 0 then (metric_t - metric_c) / metric_c
              e