工作，常用sql函数及其操作

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)",