DB2 高级应用
Assumes that we have a table named PRICE_CNID
CABLEID
PRODUCTID
PRICE
50000000
ANND1
609
90
1. NEW TABLE, OLD TABLE
a. with update
SELECT * FROM OLD TABLE (
UPDATE PRICE_CNPRICESET 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
http://comedsh.iyunv.com/upload/attachment/112450/1864c2f2-9e8a-318c-9685-4d3f757be56d.jpg
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
页:
[1]