Shulou(Shulou.com)06/01 Report--

/ * Directory: 1. Grammar 2. Common functions and usage 2.1 sort function 2.2 wm_concat () can merge different lines with the same field! The production environment should be cautious with 2.3.windowing usage 2.4.The proportion function ratio_to_report () over (partition by) 2.5.The first lag is followed by a lead 2.6.The last last_value 2.7plus 1 multiplicates and subtracts 1 * /-1. Analysis function syntax: function name () over (partition sort sliding window / window)-1.1. The function specifies what to do on the data delineated by over-- you can do common aggregate functions such as sum () and max (), and you can also use powerful analysis functions such as row_number () and first_value ()-- 1.2 partitions define what the dataset of the previous function is, similar to group by. But more powerful than it is, columns that do not aggregate with group by must participate in grouping and cannot be displayed separately. Sort specifies the order in which the function is calculated, such as ranking, such as adding-1.4 windowed partition by to specify the rows that participate in the calculation-- 2. Common functions and uses-2.1sort function row_number () returns only one result 123456 rank () is a jump sort, and when there are two second places, then the fourth place is 122456 dense_rank () is a continuous sort. When there are two second places, it is still followed by the third 122345 [for example, you want to get the ranking of each employee in the department] select row_number () over (partition by deptno order by sal desc) no, ename, sal, deptno from emp NO ENAME SAL DEPTNO-1 KING 5000 10 2 CLARK 2450 10 3 MILLER 1300 10 1 SCOTT 3000 20 2 FORD 3000 20 3 JONES 2975 20 4 ADAMS 1100 20 5 SMITH 800 20 1 BLAKE 2850 30 2 ALLEN 1600 30 3 TURNER 1500 30 4 MARTIN 1250 30 5 WARD 1250 30 6 JAMES 950 30 / * Partition partition by is defined by department as a dataset Thus, a dataset row_number () for each department indicates that sorting is to be done on the dataset. Order by is specified to sort by sal * /-- to specify the first 3 select * from (select t.deptno, t.ename, t.sal) of each department. Row_number () over (partition by deptno order by sal desc) top_no from emp t) a where a.top_no

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.