常用SQL分析语句

最近在基于SQL做营销分析，随手记录几个以前用的不太熟的SQL脚本：

 

 1、查询结果格式化程百分比

select a.wh,a.age,a.count1,round((a.count1/b.count2),2)*100||'%'
from
(select wh ,avg(age) age , count(wh) count1 from employee group by wh) a,
(select count(*) count2 from employee) b

 

2、提取各个分类下的Top N

select * from (
 select id,name,counts,createDate,row_number() over(partition by name order by createDate 
desc) rn
 from testTable
) t where t.rn <=N