1.over(partition by)开窗函数,与聚合函数的区别是聚合函数返回一行数据,该函数分组返回多组数据。
例如:
SELECT * FROM (select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm,count(*) over(partition by t.CLASS) from T2_TEMP t) ;
注:rank()跳排名 1,1,3,4。
dense_rank连续排序 1,2,3,4。
*row_number()有去重复的功能。排名相同的只显示一个。
count() over(partition by ... order by ...):求分组后的总数。
max() over(partition by ... order by ...):求分组后的最大值。
min() over(partition by ... order by ...):求分组后的最小值。
avg() over(partition by ... order by ...):求分组后的平均值。
lag() over(partition by ... order by ...):取出前n行数据。
lead() over(partition by ... order by ...):取出后n行数据。
2.to_number()字符串强转成数字
3.sys_connect_by_path()
比较使用行转列的函数,注意需要把表转成树的形式才可使用!
例子:
select MAX(substr(sys_connect_by_path(name, ','),2)) from (SELECT name,rn id,LAG(RN) OVER(ORDER BY RN) pid FROM(select temp.name,rownum rn from T2_Temp temp)) start with pid is null connect by prior id=pid
start with 条件 connect by prior 条件,树形式。
此例子是通过lag()把结果集转换成了树结构。很实用。
4.wm_concat()函数
行转列select id,wm_concat(subname) from(...)group by id
5.pivot
列转行
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4]
FROM(SELECT DaysToManufacture, StandardCost FROM Production.Product) AS SourceTable
PIVOT(AVG(StandardCost)FOR DaysToManufacture IN ([0], [1], [2], [3], [4])) AS PivotTable;