DB2 高级应用-优快云博客

Assumes that we have a table named PRICE_CN

ID	CABLEID	PRODUCTID	PRICE
50000000	ANND1	609	90

1. NEW TABLE, OLD TABLE

a. with update

SELECT * FROM OLD TABLE (
         UPDATE PRICE_CN  PRICE  SET PRICE = 80 WHERE RICE.ID = 50000000
)
--Results: The price before updated will be selected out
--         PRICE 
--         90

b. with insert

-- Assumes that we only have 1 record before insert successful
SELECT COUNT(*) FROM OLD TABLE(
      INSERT INTO PRICE_CN ( CABLEID, PRODUCTID, PRICE ) VALUES ( 'ANND2', 700, 1000)
)
-- Results:
-- Count
-- 1

2. INSERT WITH SELECT

-- set the price of the cable-product with the cable id 'ANND2' 
-- and productid 700 to be the price of the new cable product 
-- with cable id 'ANND3' and productid 701
    INSERT INTO PRICE CN ( CABLEID, PRODUCTID, PRICE )
    SELECT 'ANND3', 701, PRICE.PRICE 
                 FROM PRICE_CN AS PRICE 
                 WHERE PRICE.CABLEID='ANND2', PRICE.PRODUCTID=700
-- the results of the sub select will be the values to be insert into PRICE_CN table

3. Temp table using WITH

a. How to compare the OLD Price with the NEW price

WITH
BEFORE_PRICE_UPDATED
    AS(
        SELECT * FROM OLD TABLE (
                UPDATE WWPRT.PRICE_CN PRICE SET PRICE = 80 WHERE PRICE.ID = 50000000
        )
    )
SELECT PRICE.CABLEID, PRICE.PRODUCTID, PRICE.PRICE AS OLDPRICE, 
       BEFOREPRICE.PRICE AS NEWPRICE
       FROM WWPRT.PRICE_CN PRICE
            INNER JOIN BEFORE_PRICE_UPDATED BEFOREPRICE ON BEFOREPRICE.ID = PRICE.ID AND PRICE.ID = 50000000

-- results: AFTER UPDATE, we can compare with the new one 
-- selected out from the OLD table as a tempoary table
-- CABLEID     PRODUCTID     OLDPRICE     NEWPRICE   
-- ----------  ------------  -----------  -----------
-- ANND1        609              90            80

b. Syntax

WITH
    TEMP1(T1COL1,T1COL2,....) AS (
                       SELECT COL1, COL2 FROM TABLE1 WHERE ....
    ),  
    TEMP2(T2COL,T2COL2.....)  AS(
                       values (T2COLValue1, T2COL2Value1 ....),
                                   (T2COLValue2, T2COL2Value2 ....),
                                   (T2COLValue3, T2COL2Value3 ....)
     ) // no comma here
   SELECT * FROM TABLE1, TABLE2, TEMP1, TEMP2 .....

c. Using With to realize the Recursion selection

http://www.ibm.com/developerworks/cn/data/library/techarticles/0203venigalla/0203venigalla.html

4. Import and Export

a. Export

EXPORT TO yourfile.del OF DEL  
SELECT PRICE.* from yourtable

b. Import

 import from yourfile.del of del  
 COMMITCOUNT 100000   
 insert into yourtable;  
 // import with matched columns,  
 // assume that the first column is the PK with increased auto  
 IMPORT FROM yourfile.del OF DEL   
 METHOD P ( 1, 2 )  
 COMMITCOUNT 100000  
 INSERT INTO yourschema.table(  
     column2, column3  
 );

5. MERGE INTO

a. Syntax

MERGE INTO   TARGET_TABLE  
            USING SOURCE_TABLE  
            WHEN MATCHED THEN  
                       UPDATE ()=()  
            WHEN NOT MATCHED THEN  
                       INSERT () VALUES ()

b. Example

6. UPDATE FROM THE QUERY RESULT

Just Exmple below

update ( select * from prdstg.price where country = 'CN' fetch first 1 rows only ) p set p.updatets = current timestamp

7. UPDATE a SET of values

WHEN MATCHED 
THEN UPDATE SET (PRODGRPNAME, OFFERTYPE, OFFERINGNAME, VARIANTTYPE, VARIANT, PRODCATEGORY, PRODHIERARCHY, OFFERINGDESC, DEFAULTPRICETYPE, CHARGEABLE, INTROSYS, CHANGED, AUDITTS, AUDITUSER) = ( RC.PRODGRPNAME, RC.OFFERTYPE, RC.OFFERINGNAME, RC.VARIANTTYPE, RC.VARIANT, RC.PRODCATEGORY, RC.PRODHIERARCHY, RC.DESCRIPTION, RC.DEFAULTPRICETYPE, RC.CHARGEABLE, RC.INTROSYS, 'N', RC.AUDITTS, RC.AUDITUSER )

7 . Raise_Error

.... in progress

8. OLAP Functions

reference link: http://www.ibm.com/developerworks/db2/library/techarticle/lyle/0110lyle.html

a. Ranking function

concept: These ranking functions provide the ability to define a set (using the PARTITION clause), and then rank the elements of the set with respect to an ordering

Example 1

suppose we have an employee table and would like to rank the employees' salaries within each department.

select empnum, dept, salary,
rank() over (partition by dept order by salary desc nulls last) as rank,
dense_rank() over (partition by dept order by salary desc nulls last)as denserank,
row_number() over (partition by dept order by salary desc nulls last)as rownumber
from emptab;
---------- The results belows ----------
EMPNUM DEPT SALARY RANK DENSERANK ROWNUMBER
------ ---- ------ ---- --------- ---------
6 1 78000 1 1 1
2 1 75000 2 2 2
7 1 75000 2 2 3
11 1 53000 4 3 4
5 1 52000 5 4 5
1 1 50000 6 5 6
--------------------------------------------------
9 2 51000 1 1 1
4 2 - 2 2 2

I. Following the PARTITION clause, we have an ORDER BY clause, which defines the ordering within the partition. In this case, we'd like to rank high salaries first, so we define the ordering to be on descending salary.

II. n addition to the descending specification, we also specify NULLS LAST. In SQL, nulls collate high, meaning they appear to be greater than all other non-null values. This introduces a problem for ranking, because we probably don't want null salaries ranked first.

b. scalar-aggregate functions

1) scalar function

concept: Scalar functions are those that operate on values within a single row, and return a single result per row

Example: The query below uses the DIGITS scalar function to format the salary field. The calculation of the result is done per row, and only the salary value within the current row is used in the computation

select empnum, salary,
digits(salary) as digits
from emptab
where dept = 1;

EMPNUM SALARY DIGITS
----------- ----------- ----------
1 50000 0000050000
2 75000 0000075000
5 52000 0000052000
...

2) Aggregate function

Concept: operate on a set of rows, and aggregate (or combine) them into a single row in the output - such as sum()

Example: the following query computes the sum of all of the employees in each department

 select dept, sum(salary) as sum
from emptab
group by dept;

DEPT SUM
----------- -----------
1 383000
2 51000
3 209000
- 84000

3) scalar-aggregate function

Concept: These functions are like scalar functions, because they return a single value per row, but they're also like aggregate functions, because the calculation is performed on values from multiple rows within a set to compute the result

Example 1: scalar-aggregate sum function - it does the same calculation as the aggregate sum function, but returns the results without combining the rows

select dept, salary,
sum(salary) over (partition by dept) as deptsum,
avg(salary) over (partition by dept) as avgsal,
count(*) over (partition by dept) as deptcount,
max(salary) over (partition by dept) as maxsal
from emptab;

DEPT SALARY DEPTSUM AVGSAL DEPTCOUNT MAXSAL
----- ------- -------- ------- --------- --------
1 50000 383000 63833 6 78000
1 75000 383000 63833 6 78000
1 52000 383000 63833 6 78000
1 78000 383000 63833 6 78000
1 75000 383000 63833 6 78000
1 53000 383000 63833 6 78000
2 - 51000 51000 2 51000
2 51000 51000 51000 2 51000
3 79000 209000 69666 3 79000
3 55000 209000 69666 3 79000
3 75000 209000 69666 3 79000
- - 84000 84000 2 84000
- 84000 84000 84000 2 84000

the OVER clause is used to partition the data so that the sum function is computed over rows in the same department, and the sum of all the salaries in each department is returned for each row within the department

Example 2: calculating ratios and percentages - To calculate the percentage of one employee's salary versus the total of the entire department's salaries, simply divide the employee's salary by the reporting sum of the salaries

select empnum, dept, salary,
sum(salary) over (partition by dept) as deptsum,
decimal(salary,10,2) /
sum(salary) over(partition by dept)as percentage
from emptab;

EMPNUM DEPT SALARY DEPTSUM PERCENTAGE
------ ----- -------- ----------- ----------
1 1 50000 383000 0.1305
2 1 75000 383000 0.1958
5 1 52000 383000 0.1357
6 1 78000 383000 0.2036
7 1 75000 383000 0.1958
11 1 53000 383000 0.1383
4 2 - 51000
9 2 51000 51000 1.0000
8 3 79000 209000 0.3779
10 3 55000 209000 0.2631
12 3 75000 209000 0.3588
0 - - 84000
3 - 84000 84000 1.0000

Example 2: cumulative function - A cumulative function is a scalar-aggregate function that operates on the current row, and all rows in the set that precede it with respect to the ordering. Suppose we have a table with the monthly sales results for the current calendar year. How do we compute the current year-to-date sales figures for each month?

select date, sales,
sum(sales) over (order by date) as cume_sum,
count(*) over (order by date) as setcount
from sales
where year(date) = 2000;

DATE SALES CUME_SUM SETCOUNT
---------- ------------ ------------ ---------
01/01/2000 968871.12 968871.12 1
02/01/2000 80050.05 1048921.17 2
03/01/2000 757866.14 1806787.31 3
04/01/2000 58748.13 1865535.44 4
05/01/2000 40711.69 1906247.13 5
06/01/2000 241187.78 2147434.91 6
07/01/2000 954924.16 3102359.07 7
08/01/2000 502822.96 3605182.03 8
09/01/2000 97201.45 3702383.48 9
10/01/2000 853999.45 4556382.93 10
11/01/2000 358775.59 4915158.52 11
12/01/2000 437513.35 5352671.87 12

Example 2.1 If we had multiple years worth of data and wanted to compute the cumulative sum by month within each year , we could also use the PARTITION BY clause as follows:

select date, sales,
sum(sales) over (partition by year(date)
order by month(date)) as cume_sum
from sales
where year(date) >= 2000;

DATE SALES CUME_SUM
---------- ------------ -----------
01/01/2000 968871.12 968871.12
02/01/2000 80050.05 1048921.17
03/01/2000 757866.14 1806787.31
04/01/2000 58748.13 1865535.44
05/01/2000 40711.69 1906247.13
06/01/2000 241187.78 2147434.91
07/01/2000 954924.16 3102359.07
08/01/2000 502822.96 3605182.03
09/01/2000 97201.45 3702383.48
10/01/2000 853999.45 4556382.93
11/01/2000 358775.59 4915158.52
12/01/2000 437513.35 5352671.87
01/01/2001 476851.71 476851.71
02/01/2001 593768.12 1070619.83
03/01/2001 818597.97 1889217.80
...

c. When are Ranking function and scalar-aggregate function computed ?

1) the answer

The answer is that these functions are computed at the time the rest of the select list is computed. In general, the order of evaluation of a query is as follows:

I. From Clause

II. Where Clause

III. Group By Clause

IV. Having Clause

V. Select List

VI computing at the Select List

As you can see, the select list is computed after all other parts of the query. This means that if you have predicates (in the WHERE or HAVING clause), or if you have any aggregations as a result of a GROUP BY clause, these will all be applied before the functions are evaluated. as the example belows

select year(date) as year, sum(sales) as sum,
sum(sum(sales)) over (order by year(date)) as cume_sum
from sales
where year(date) >= 1995
group by year(date);

YEAR SUM CUME_SUM
----------- ------------- ------------
1995 7731162.39 7731162.39
1996 4127017.98 11858180.37
1997 7211584.76 19069765.13
1998 4149296.50 23219061.63
1999 6278023.54 29497085.17
2000 5352671.87 34849757.04
2001 5736777.81 40586534.85

The sequence of this case, we access the table (named in the FROM clause) and apply the WHERE clause, then we do the GROUP BY and compute the sum of sales per year. Finally, we compute the select list, including all scalar-aggregate functions.

2) not possible to reference scalar-aggregate/Ranking functions in a predicate

Reason: Because the scalar-aggregate functions are computed after the WHERE clause, it is not possible to reference scalar-aggregate functions in a predicate

Instead: if you wish to do this, you must nest the scalar-aggregate function invocation, either within a common table expression, or within a nested query, as the example belows

with ranked_years (year, sum, rank) as
(select year(date) as year, sum(sales) as sum,
rank() over (order by sum(sales) desc) as rank
from sales
group by year(date)
)
select year, sum, rank
from ranked_years
where rank <= 3;

YEAR SUM RANK
----------- ------------- -------
1995 7731162.39 1
1997 7211584.76 2
1999 6278023.54 3