Use the DB2 OLAP function (turn)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Speaking of DB2 online analytical processing, can be very good very powerful to describe. This feature is especially applicable to a variety of statistical query, the query by the usual SQL is very difficult to achieve, or there is no implementation. First of all, we start with a simple example, a step by step opened its mysterious veil, please see the following SQL:

  1. SELECT  
  2.     ROW_NUMBER() OVER(ORDER BY SALARY)AS number,  
  3. Making making NAME AS name,  
  4. Making making DEPT AS Department,  
  5. Making making SALARY AS wages making
  6. FROM  
  7. (  
  8. Making making -- name making making department making wages making
  9.     VALUES  
  10.     ('A three ',' marketing department',4000),  
  11.     ('Zhao red ',' Technology Department',2000),  
  12.     ('Lee four ',' marketing department',5000),  
  13.     ('Li ',' Technology Department',5000),  
  14.     ('Wang five ',' marketing department',NULL),  
  15.     ('Wang blue ',' Technology Department',4000)  
  16. ) AS EMPLOY(NAME,DEPT,SALARY);  
  17.    
  18. The query results are as follows:   
  19.    
  20. The number making making making name making making making department making making making wages making
  21. 1 making making Zhao Hong making making making technology making making 2000 making
  22. 2 making making three making making making market making making 4000 making
  23. 3 making making King blue making making making technology making making 4000 making
  24. 4 making making Li four making making making market making making 5000 making
  25. 5 making making Li Bai making making making technology making making 5000 making
  26. 6 making making king five making making making market making making(null)  

 

 

See the above ROW_NUMBER (OVER) (the)? Many people do not understand, how the two functions can do? Some people even suspect that the SQL statement above is not really able to perform. In fact, ROW_NUMBER is a function right, its role from its name can be seen, is to give the query result set number. However, OVER is not a function, but rather an expression, its function is to define a scope (or can be said to be a result set), function defined by OVER OVER in front of the only result set function. How, do not understand? Never mind, we will introduce.

 

From the above we can see that the SQL DB2, online typical analysis format includes two parts: Function partAndOVERPart of the expression. Then, the function part can have what function? The following:

  1. ROW_NUMBER  
  2. RANK  
  3. DENSE_RANK  
  4. FIRST_VALUE  
  5. LAST_VALUE  
  6. LAG  
  7. LEAD  
  8. COUNT  
  9. MIN  
  10. MAX  
  11. AVG  
  12. SUM  

 

 

The above function, I'll be in the back gradually to introduce, we can according to the function name guess function.

 

Suppose I want to without changing the above sentence query results, additional average wages for staff and staff's average wage query, how to do? By the usual SQL difficult to query, but the use of the OLAP function is very simple, as shown in the SQL below:

  1. SELECT  
  2.     ROW_NUMBER() OVER()AS number,  
  3.     ROW_NUMBER() OVER(PARTITION BY DEPT ORDER BY SALARY)AS department number,  
  4. Making making NAME AS name,  
  5. Making making DEPT AS Department,  
  6. Making making SALARY AS wage,  
  7.     AVG(SALARY) OVER(PARTITION BY DEPT)The average wage AS Department,  
  8.     AVG(SALARY) OVER()AS staff average wages making
  9. FROM  
  10. (  
  11. Making making -- name making making department making wages making
  12.     VALUES  
  13.     ('A three ',' marketing department',4000),  
  14.     ('Zhao red ',' Technology Department',2000),  
  15.     ('Lee four ',' marketing department',5000),  
  16.     ('Li ',' Technology Department',5000),  
  17.     ('Wang five ',' marketing department',NULL),  
  18.     ('Wang blue ',' Technology Department',4000)  
  19. ) AS EMPLOY(NAME,DEPT,SALARY);  
  20.    
  21.    
  22. The query results are as follows:   
  23.    
  24. The number making making making sector number making making making name making making making department making making making wage making making making sector average wage making making making staff average wages making
  25. 1 making making making making making making 1 making making making making making three making making making market making making 4000 making making making 4500 making making making making making making making making making making 4000 making
  26. 2 making making making making making making 2 making making making making making Li four making making making market making making 5000 making making making 4500 making making making making making making making making making making 4000 making
  27. 3 making making making making making making 3 making making making making making king five making making making market making making(null)     4500                     4000  
  28. 4 making making making making making making 1 making making making making making Zhao Hong making making making technology making making 2000 making making making 3666 making making making making making making making making making making 4000 making
  29. 5 making making making making making making 2 making making making making making King blue making making making technology making making 4000 making making making 3666 making making making making making making making making making making 4000 making
  30. 6 making making making making making making 3 making making making making making Li Bai making making making technology making making 5000 making making making 3666 making making making making making making making making making making 4000 making

 

 

Please pay attention to the difference between the number and the number of the Department, when we query the sector number, in a OVER expression more than two clauses, respectivelyPARTITION BYAndORDER BY. What is their role? Before the introduction of their role, we begin with a review of the role of the OVER, do you remember?

 

OVER is an expression, its function is to define a scope (or can be said to be a result set), function defined by OVER OVER in front of the only result set function.

 

ORDER BYThe role we should be very familiar with, used to sort the result set. PARTITION BYThe effect is also very simple, andGROUP BYThe same effect, are used to group the result set.

 

Making so far, we should OLAP function routines have certain knowledge and experience. We look at the top SQL result set, Wang found that the wage is five null, when we press the wages ranking, null was at the end, we want to put null in front of the how to do? UseNULLS FIRSTKeyword, the default isNULLS LAST, Please see the following SQL:

  1. SELECT  
  2.     ROW_NUMBER() OVER(ORDER BY SALARY desc NULLS FIRST) AS RN,  
  3.     RANK() OVER(ORDER BY SALARY desc NULLS FIRST) AS RK,  
  4.     DENSE_RANK() OVER(ORDER BY SALARY desc NULLS FIRST) AS D_RK,  
  5. Making making NAME AS name,  
  6. Making making DEPT AS Department,  
  7. Making making SALARY AS wages making
  8. FROM  
  9. (  
  10. Making making -- name making making department making wages making
  11.     VALUES  
  12.     ('A three ',' marketing department',4000),  
  13.     ('Zhao red ',' Technology Department',2000),  
  14.     ('Lee four ',' marketing department',5000),  
  15.     ('Li ',' Technology Department',5000),  
  16.     ('Wang five ',' marketing department',NULL),  
  17.     ('Wang blue ',' Technology Department',4000)  
  18. ) AS EMPLOY(NAME,DEPT,SALARY);  
  19.    
  20. The query results are as follows:   
  21.    
  22. RN making RK making D_RK making making name making making making department making making making wages making
  23. 1 making making 1 making making 1 making making king five making making making City Department making making(null)  
  24. 2 making making 2 making making 2 making making Li four making making making City Department making making 5000 making
  25. 3 making making 2 making making 2 making making Li Bai making making making technology making making 5000 making
  26. 4 making making 4 making making 3 making making three making making making City Department making making 4000 making
  27. 5 making making 4 making making 3 making making King blue making making making technology making making 4000 making
  28. 6 making making 6 making making 4 making making Zhao Hong making making making technology making making 2000 making

 

 

Please pay attention to the difference between ROW_NUMBER and RANK, RANK is rank, ranking meaning, Li Si and Li Bai's salary is 5000, they tied for second place. Zhang San and Wang Lan's salary is 4000, how the RANK function is ranked fourth, while the DENSE_RANK ranking is third? This is the difference between these two functions. Because there are two to second, so the default RANK function no third.

 

Making now have a new problem, that you inquire about each employee's salary and wage is less than the average wage of all his employees, how to do? How? Don't understand the problem? Never mind, please see the following SQL:

  1. SELECT  
  2. Making making NAME AS name,  
  3. Making making SALARY AS wage,  
  4.     SUM(SALARY) OVER(ORDER BY SALARY NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS less than total wages,  
  5.     SUM(SALARY) OVER(ORDER BY SALARY NULLS FIRST ROWS BETWEEN  CURRENT ROW AND UNBOUNDED FOLLOWING)AS than I the total amount of wages,  
  6.     SUM(SALARY) OVER(ORDER BY SALARY NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)Total wages of AS making 1,  
  7.     SUM(SALARY) OVER()Total wages of AS making 2 making
  8. FROM  
  9. (  
  10. Making making -- name making making department making wages making
  11.     VALUES  
  12.     ('A three ',' marketing department',4000),  
  13.     ('Zhao red ',' Technology Department',2000),  
  14.     ('Lee four ',' marketing department',5000),  
  15.     ('Li ',' Technology Department',5000),  
  16.     ('Wang five ',' marketing department',NULL),  
  17.     ('Wang blue ',' Technology Department',4000)  
  18. ) AS EMPLOY(NAME,DEPT,SALARY);  
  19.    
  20. The query results are as follows:   
  21.    
  22. The name making making making wage making making making less than the salary amount making making more than my salary amount making making total wages 1 making making total wages 2 making
  23. Wang Wu making making making(null)     (null)             20000              20000            20000  
  24. Zhao Hong making making making 2000 making making making 2000 making making making making making making making 20000 making making making making making making making 20000 making making making making making making 20000 making
  25. Zhang San making making making 4000 making making making 6000 making making making making making making making 18000 making making making making making making making 20000 making making making making making making 20000 making
  26. Wang Lan making making making 4000 making making making 10000 making making making making making making making 14000 making making making making making making making 20000 making making making making making making 20000 making
  27. Li Si making making making 5000 making making making 15000 making making making making making making making 10000 making making making making making making making 20000 making making making making making making 20000 making
  28. Li Bai making making making 5000 making making making 20000 making making making making making making making 5000 making making making making making making making 20000 making making making making making making 20000 making

 

 

OVER above SQL appeared in a ROWS clause, we first look at the structure of the ROWS clause:

  1. ROWS BETWEEN <The upper > AND < lower limit;>  
  2.    
  3. The "limit conditions" can be the following keywords:   
  4. UNBOUNDED PRECEDING  
  5. <number>  PRECEDING  
  6. CURRENT ROW  
  7.    
  8. "Off line condition "can be the following keywords:   
  9. CURRENT ROW  
  10. <number> FOLLOWING  
  11. UNBOUNDED FOLLOWING  

 

 

Note, the above keywords are relative to the current row, UNBOUNDED PRECEDINGAll the front of the line, that is no upper limit, <number>  PRECEDINGSaid from the current line to the front of it<number>Behavior, for example, number=2, that is the 2 line of the current row in front of the, CURRENT ROWSaid the current row. As for the other two words, I want to, I do not have to say, you should know. If you do not understand, please carefully analyzing the above SQL results.

 

Making OVER expressions can also have one sentence, that isRANGE, The use and the way it isROWS Very similar, or as like as two peas, also worse than not, but there are some differences, as shown below:

 

RANGE BETWEEN <Limit conditions> AND <The lower limit>

 

One of the<Limit conditions> , <The lower limit>And ROWSAs like as two peas, the difference between the following SQL to demonstrate their:

  1. SELECT  
  2. Making making NAME AS name,  
  3. Making making DEPT AS Department,  
  4. Making making SALARY AS wage,  
  5.     FIRST_VALUE(SALARY, 'IGNORE NULLS') OVER(PARTITION BY DEPT)AS sector minimum wage,  
  6.     LAST_VALUE(SALARY, 'RESPECT NULLS') OVER(PARTITION BY DEPT)The highest wages AS Department,  
  7.     SUM(SALARY) OVER(ORDER BY SALARY ROWS BETWEEN 1 PRECEDING  AND 1 FOLLOWING) AS ROWS,  
  8.     SUM(SALARY) OVER(ORDER BY SALARY RANGE BETWEEN 500 PRECEDING AND 500 FOLLOWING) AS RANGE  
  9. FROM  
  10. (  
  11. Making making -- name making making department making wages making
  12.     VALUES  
  13.     ('A three ',' marketing department',2000),  
  14.     ('Zhao red ',' Technology Department',2400),  
  15.     ('Lee four ',' marketing department',3000),  
  16.     ('Li ',' Technology Department',3200),  
  17.     ('Wang five ',' marketing department',4000),  
  18.     ('Wang blue ',' Technology Department',5000)  
  19. ) AS EMPLOY(NAME,DEPT,SALARY);  
  20.    
  21. The query results are as follows:   
  22.    
  23. The name making making making department making making making wage making making making sector minimum wage making making making Department of the highest wages making making making ROWS making making RANGE making
  24. Zhang San making making making market making making 2000 making making making 2000 making making making making making making making 4000 making making making making making making 4400 making making making 4400 making
  25. Zhao Hong making making making technology making making 2400 making making making 2400 making making making making making making making 5000 making making making making making making 7400 making making making 4400 making
  26. Li Si making making making market making making 3000 making making making 2000 making making making making making making making 4000 making making making making making making 8600 making making making 6200 making
  27. Li Bai making making making technology making making 3200 making making making 2400 making making making making making making making 5000 making making making making making making 10200 making making 6200 making
  28. Wang Wu making making making market making making 4000 making making making 2000 making making making making making making making 4000 making making making making making making 12200 making making 4000 making
  29. Wang Lan making making making technology making making 5000 making making making 2400 making making making making making making making 5000 making making making making making making 9000 making making making 5000 making

 

 

The above SQLRANGEClause is the role of the definition of a salary range, the upper limit of this range is the current line of -500 is the minimum wage, the wage +500. For example: 3000 is Li Si's wages, so the limit is 3000-500=2500, the lower limit is 3000+500=3500, then who pay in the range 2500-3500? Only Li Si and Li Bai, so the value of the RANGE column is 3000 (Li Si) +3200 (Li Bai) =6200. And that's the ROWS and RANGE differences.

 

Making the above SQL usedFIRST_VALUEAnd LAST_VALUEThe two functions, their role is very simple, to the minimum and maximum values for OVER definition set. It is worth noting that the two function parameters, 'IGNORE NULLS' Or'RESPECT NULLS', As their names, to ignore the NULL value and NULL value.

 

Making and two function we have not introduced, LAGAnd LEAD, The two function is very powerful, please see below SQL:

  1. SELECT  
  2. Making making NAME AS name,  
  3. Making making SALARY AS wage,  
  4.     LAG(SALARY,0) OVER(ORDER BY SALARY) AS LAG0,  
  5.     LAG(SALARY) OVER(ORDER BY SALARY) AS LAG1,  
  6.     LAG(SALARY,2) OVER(ORDER BY SALARY) AS LAG2,  
  7.     LAG(SALARY,3,0,'IGNORE NULLS') OVER(ORDER BY SALARY) AS LAG3,  
  8.     LAG(SALARY,4,-1,'RESPECT NULLS') OVER(ORDER BY SALARY) AS LAG4,  
  9.     LEAD(SALARY) OVER(ORDER BY SALARY) AS LEAD  
  10. FROM  
  11. (  
  12. Making making -- name making making department making wages making
  13.     VALUES  
  14.     ('A three ',' marketing department',2000),  
  15.     ('Zhao red ',' Technology Department',2400),  
  16.     ('Lee four ',' marketing department',3000),  
  17.     ('Li ',' Technology Department',3200),  
  18.     ('Wang five ',' marketing department',4000),  
  19.     ('Wang blue ',' Technology Department',5000)  
  20. ) AS EMPLOY(NAME,DEPT,SALARY);  
  21.    
  22. The query results are as follows:   
  23.    
  24. The name making making making wage making making making LAG0 making making making LAG1 making making making LAG2 making making making LAG3 making making making LAG4 making making making LEAD making
  25. Zhang San making making making 2000 making making making 2000 making making making(null)   (null)       0       -1        2400  
  26. Zhao Hong making making making 2400 making making making 2400 making making making 2000 making making(null)       0       -1        3000  
  27. Li Si making making making 3000 making making making 3000 making making making 2400 making making 2000 making making making 0 making making making making -1 making making making making 3200 making
  28. Li Bai making making making 3200 making making making 3200 making making making 3000 making making 2400 making making making 2000 making making -1 making making making making 4000 making
  29. Wang Wu making making making 4000 making making making 4000 making making making 3200 making making 3000 making making making 2400 making making 2000 making making making 5000 making
  30. Wang Lan making making making 5000 making making making 5000 making making making 4000 making making 3200 making making making 3000 making making 2400 making making making(null)  

 

 

Making our first look at LAG and LEAD function declarations, as follows:

 

LAG(Expression or field, Offset, The default value, IGNORE NULLSOr RESPECT NULLS)

 

LAG is a downward shift, LEAD is trying to offset, we look at the top SQL results will stick out a mile.

 

The function OVER (PARTITION ORDER BY BY clause Clause in the ROWS or RANGE clause)

The application of OLAP function

We have introduced the basic method of using OLAP function, here introduces related application of OLAP functions

    SELECT 
        Date AS date, 
        Tax AS tax, 
        sum(tax) OVER(partition BY year(date) order by month(date)) AS was a total tax, 
        sum(tax) OVER() AS all tax
  FROM 
    ( 
        Time of tax 
        VALUES 
        ('01/01/2000',4000), 
        ('02/01/2000',2000), 
        ('03/01/2000',5000), 
        ('04/01/2000',5000), 
        ('01/01/2001',NULL), 
        ('02/01/2001',4000) 
    ) AS EMPLOY(date,tax); 

 

The query results are as follows:  

Date of tax year tax total tax
01/01/2000    4000    4000    20000
02/01/2000    2000    6000    20000
03/01/2000    5000    11000    20000
04/01/2000    5000    16000    20000
01/01/2001    [NULL]    [NULL]    20000
02/01/2001    4000    4000    20000 

 

You can see the introduction of a sort in aggregation set, DB2 will not be processed a report (reporting) function, but with a cumulative (cumulative) function. Cumulative function is a scalar aggregate functions, which the current row and current row in the collection (before the relative ranking methods) all line operation. Here, in the year as the boundary is calculated monthly tax and the sum of. To observe the above data can see, the calculation to the 2011-01-01, the cumulative tax 2011-01-01 tax as a starting point, begin to calculate.
SELECT *
FROM ( select row_Number() over(order by id asc) as row_next,id ,name from staff ) AS A
WHERE ROW_next BETWEEN 3 AND 5; 
From third to fifth lines

 

This method has the following characteristics,
Effect on the transaction and performance 
In this method, developers need to handle affairs to programmatically. If you remove the row_next clause (ROW_NEXT BETWEEN? And?), then will return all matching the selection criteria. Used above SELECT * FROM clause can be seen as a temporary table, there exist a matching the selection criteria of the entire result set, and then return to fall in a given row within the result set from the temporary table. Using rownumber () function has the additional performance impact on the system, because the database must first obtain all matched the selection criteria, then each time you get the next rows from the result set when they have access to the database, until get all behavior in a given range check. Therefore, if at the same time to use this method to sort people by DML operation, there may be multiple conditions.
Code portabilityAlmost all RDBMS systems support from the result set of the beginning or end to obtain any number of rows, but by the use of the SQL syntax is different. Therefore, if we want to change the database vendor, will have to modify the corresponding query.
To provide seamless back button function 
If we design needs to have the browser Back button function, and the user hope every time are able to view the information from a database of recent, so this design can provide a feasible solution. Its disadvantage is that, for each result page, may need to access the database, which greatly affect the performance.
Basically, the search parameters URL queries are embedded, these parameters are used to provide SQL Query. For example, the following is a possible URL query:
http://localhost:8050/ProductSearch?minprize=50&maxprice=100&pagecount=20&resultsPerpage=10 
When users click the browser's back button will be displayed in the browser, in front of a URL is:
http://localhost:8050/ProductSearch?minprize=50&maxprice=100&pagecount=10&resultsPerpage=10 
We assume that, in the session provides the latest page ID. Therefore, can look like the following to track the browser Back button:

if(!session.getAttribute("pageID).equalsIgnoreCase
      (request.getAttribute("pageCount")){
    //Then user has clicked the back button
    //Get possible search parameters, pagecount 
    //and resultsPerPage from URL and formulate 
    //a dynamic URL and post the information back 
    //to the servlet.The URL would be
    http://localhost:8050/ProductSearch?minprize=50&
    maxprice=100&pagecount=10&resultsPerpage=10
    return;
    //Done display the results screen page
}

 
By using this method, the latest information of the user can view the database.

The other method of use of OLAP function can refer to

http://www.searchdatabase.com.cn/ShowContent_9290.htm

 

 

 

Reference to the:
   http://www.ibm.com/developerworks/cn/data/library/techarticles/0110lyle/0110lyle.html
  http://www.ibm.com/developerworks/cn/data/library/techarticles/0307balani/0307balani.html
  http://blog.csdn.net/andyxm/article/details/2795356

 

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Bowen at December 05, 2013 - 8:56 AM