工作，sql

目录

目录：

1,cast函数
2,日期处理
3,中位数函数
4,列的累加累减
5,天维度除以月维度
6,字段周，月的制作
7,sum() over(partition by) 函数的应用
8,行转列代码
9,sql字段类型
10，百分比显示


######################################################
######################################################
######################################################
1,cast函数
CAST(usedcar_create_time AS TIMESTAMP)
CAST(usedcar_create_time AS varchar)
CAST(usedcar_create_time AS int)


2,日期处理
1）存储为整型的字段处理
date_format(from_unixtime(completed_time/1000,'Asia/Shanghai'),'%Y-%m-%d %H:%i:%s') 

2）存储为timestemp的字段处理
date_format(completed_time,'%Y-%m-%d %H:%i:%s') 

3）存储为字符串的字段处理
substr(completed_time,'%Y-%m-%d')

4)时间日期处理

date_format((date_trunc('month',CURRENT_DATE ) - INTERVAL  '1' month), '%Y-%m-%d')
date_format(current_date, '%Y-%m-%d')

date_trunc('month',CURRENT_DATE )
date_format(date_trunc('month',CURRENT_DATE ) , '%Y-%m-%d')
date_format((date_trunc('month',CURRENT_DATE ) + INTERVAL  '7' day), '%Y-%m-%d') 

,date_diff('minute', CAST(ct_order.first_submit_time AS TIMESTAMP), CAST(cacp0.actime AS TIMESTAMP))  "GW提交-抢单时效(min)"
,date_diff('second', CAST(usedcar_create_time AS TIMESTAMP), CAST(buyer_order_pay_time AS TIMESTAMP)) / 60 as  "建单to支付定金"

5）日期处理
date_diff 只能日期格式相减，day second，minute
cast() 可以转换成 date,timestamp,decimal
CAST( substr(jiaoche_time,1,10) AS date) +INTERVAL '7' DAY 日期格式才能相加

3,中位数函数
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 

4,列的累加累减累乘累除
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

5,天维度除以月维度



6,字段周，月的制作
 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


7,sum() over(partition by) 函数的应用


,sum("放款量")  OVER (over BY "进件时间" ) "天维度放款量"  --计算天维度的放款量
这个在每一条数据中按照时间累加放款量
,sum("放款量")  OVER (PARTITION BY "进件时间" ) "天维度放款量"  --计算天维度的放款量
这个在每一条数据总计算出每天的放款量

,sum("首付量")  OVER (PARTITION BY substr("进件时间", 1, 7)  ) "月维度首付量"
,sum("对照组通过") over(  order by check_audit_start_time) "累计对照组通过" --计算天维度的累计值

8,行转列代码
SELECT userid, col
FROM (select userid, split(product,',') as numbers_array from test)
CROSS JOIN UNNEST(numbers_array) as temp_table(col);

9,sql字段类型
整数类型：int,bigint
小数：
1)浮点型：float/double
2）定点型：decimal
字符串：
1）定长型：char
2）变长型：varchar
3）文本字符串：text/blob
4)枚举型：enum
5）集合型：set
时间日期：
1）年：year
2）时间戳：timestamp
3）日期：date
4）日期时间：datetime
5）时间：time（3字节）

10，百分比显示
concat(CAST(TRY((("自有资金资方审批通过数" * 100.00) / ("资方审批通过数"))) AS VARCHAR), '%') "资方终审通过自有资金占比",