每日分享工作中比较常用的东西，加强记忆

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;