DECODE of Oracle learning notes and common window functions

OracleDatabaseDECODEImplement row-column conversion andCommon window functionsThe related knowledge is what we will introduce in this article. I hope this introduction will be helpful to you!

Use DECODE to convert rows and columns

The Code is as follows:

 
 

  
  
Select 'payroll 'department,
  
  
Sum (decode (k. deptno, '10', k. g)
  
  
, Sum (decode (k. deptno, '20', k. g) B
  
  
, Sum (decode (k. deptno, '30', k. g) C
  
  
, Sum (decode (k. deptno, '40', k. g) D
  
  
From (select sum (e. sal) g, e. deptno from emp e
  
  
Left join dept d on d. deptno = e. empno
  
  
Group by e. deptno) k;
 
 

Oracle window functions

 
 

  
  
Rank () over (order by emp_age)
  
  
Dense_rank () over (order by emp_age)
  
  
Row_number () over (order by emp_age)
  
  
Avg (age) over (partition by position) avg
  
  
Sum (age) over (order by age rows between 1 preceding and 1 following) -- get the first and last rows of the current record
  
  
Count (1) over (order by age range between 1 preceding and 1 following) c -- the difference is one year old.
 
 

Here is an introduction to Oracle Database DECODE and common window functions. I hope this introduction will be helpful to you!