DB2 高级应用

sdchy

　　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