1,列的累加累减累乘累除
select check_audit_start_time as "进件时间"
,"总进件量","对照组单量","测试组资质审批拒绝量","对照组单量占比","测试组单量占比","对照组通过","对照组拒绝","对照组其他"
,"累计对照组单量"
-- ,sum(decode(rn,1,"对照组单量",-"对照组单量")) over(order by check_audit_start_time)
,sum( "对照组单量" ) over(order by check_audit_start_time) as "累加"
,sum( if(rn=1,"对照组单量",-"对照组单量") ) over(order by check_audit_start_time) as "累减"
,round( exp( sum(ln("对照组单量")) over(order by check_audit_start_time) ) ) as "累乘"
,round( exp( sum(if( rn=1,ln("对照组单量"),-ln("对照组单量") ) )over(order by check_audit_start_time) ),2 ) as "累除"
,if( "累计对照组单量" = 0 or "累计对照组单量" is null ,'',concat(CAST(TRY( 100.00 * "累计对照组通过" / "累计对照组单量") AS VARCHAR), '%') ) "累计对照组通过比例"
from end1
order by check_audit_start_time desc
2,中位数函数应用
SELECT
"周" "日期"
,approx_percentile("GW提交-抢单时效(min)",0.5) as "GW提交-抢单时效(min)"
FROM ct_details
where substr("GW首次提交时间",1,10) >= date_format(date_trunc('month',CURRENT_DATE ) , '%Y-%m-%d')
GROUP by 1
ORDER by 1
3,字段周,月的制作
select *
,(case when "GW首次提交时间" between date_format(date_trunc('month',CURRENT_DATE ) , '%Y-%m-%d') and date_format((date_trunc('month',CURRENT_DATE ) + INTERVAL '7' day), '%Y-%m-%d')
then 'h1第一周(1-7)'
when "GW首次提交时间" between date_format((date_trunc('month',CURRENT_DATE ) + INTERVAL '7' day), '%Y-%m-%d') and date_format((date_trunc('month',CURRENT_DATE ) + INTERVAL '14' day), '%Y-%m-%d')
then 'h2第二周(8-14)'
when "GW首次提交时间" between date_format((date_trunc('month',CURRENT_DATE ) + INTERVAL '14' day), '%Y-%m-%d') and date_format((date_trunc('month',CURRENT_DATE ) + INTERVAL '21' day), '%Y-%m-%d')
then 'h3第三周(15-21)'
when "GW首次提交时间" >= date_format((date_trunc('month',CURRENT_DATE ) + INTERVAL '21' day), '%Y-%m-%d')
then 'h4第四周(22-31)'
else '' end) "周"
,substr("GW首次提交时间",1,10) "天"
,concat( 'm',substr("GW首次提交时间",1,7) ) "月"
from ct_details_tmp
4,日期加减
date_diff('minute', CAST(ct_order.first_submit_time AS TIMESTAMP), CAST(cacp0.actime AS TIMESTAMP)) "GW提交-抢单时效(min)",