DB2 高级应用

comedsh

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 successfulSELECT COUNT(*) FROM OLD TABLE(      INSERT INTO PRICE_CN ( CABLEID, PRODUCTID, PRICE ) VALUES ( 'ANND2', 700, 1000))-- Results:-- Count-- 12. 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
WITHBEFORE_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. 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 rownumberfrom emptab;---------- The results belows ----------EMPNUM DEPT SALARY RANK DENSERANK ROWNUMBER------ ---- ------ ---- --------- ---------6 1 78000 1 1 12 1 75000 2 2 27 1 75000 2 2 311 1 53000 4 3 45 1 52000 5 4 51 1 50000 6 5 6--------------------------------------------------9 2 51000 1 1 14 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 digitsfrom emptabwhere dept = 1;EMPNUM SALARY DIGITS----------- ----------- ----------1 50000 00000500002 75000 00000750005 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 sumfrom emptabgroup by dept;DEPT SUM----------- -----------1 3830002 510003 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 maxsalfrom emptab;DEPT SALARY DEPTSUM AVGSAL DEPTCOUNT MAXSAL----- ------- -------- ------- --------- --------1 50000 383000 63833 6 780001 75000 383000 63833 6 780001 52000 383000 63833 6 780001 78000 383000 63833 6 780001 75000 383000 63833 6 780001 53000 383000 63833 6 780002 - 51000 51000 2 510002 51000 51000 51000 2 510003 79000 209000 69666 3 790003 55000 209000 69666 3 790003 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 percentagefrom emptab;EMPNUM DEPT SALARY DEPTSUM PERCENTAGE------ ----- -------- ----------- ----------1 1 50000 383000 0.13052 1 75000 383000 0.19585 1 52000 383000 0.13576 1 78000 383000 0.20367 1 75000 383000 0.195811 1 53000 383000 0.13834 2 - 510009 2 51000 51000 1.00008 3 79000 209000 0.377910 3 55000 209000 0.263112 3 75000 209000 0.35880 - - 840003 - 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 setcountfrom saleswhere year(date) = 2000;DATE SALES CUME_SUM SETCOUNT---------- ------------ ------------ ---------01/01/2000 968871.12 968871.12 102/01/2000 80050.05 1048921.17 203/01/2000 757866.14 1806787.31 304/01/2000 58748.13 1865535.44 405/01/2000 40711.69 1906247.13 506/01/2000 241187.78 2147434.91 607/01/2000 954924.16 3102359.07 708/01/2000 502822.96 3605182.03 809/01/2000 97201.45 3702383.48 910/01/2000 853999.45 4556382.93 1011/01/2000 358775.59 4915158.52 1112/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_sumfrom saleswhere year(date) >= 2000;DATE SALES CUME_SUM---------- ------------ -----------01/01/2000 968871.12 968871.1202/01/2000 80050.05 1048921.1703/01/2000 757866.14 1806787.3104/01/2000 58748.13 1865535.4405/01/2000 40711.69 1906247.1306/01/2000 241187.78 2147434.9107/01/2000 954924.16 3102359.0708/01/2000 502822.96 3605182.0309/01/2000 97201.45 3702383.4810/01/2000 853999.45 4556382.9311/01/2000 358775.59 4915158.5212/01/2000 437513.35 5352671.8701/01/2001 476851.71 476851.7102/01/2001 593768.12 1070619.8303/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_sumfrom saleswhere year(date) >= 1995group by year(date);YEAR SUM CUME_SUM----------- ------------- ------------1995 7731162.39 7731162.391996 4127017.98 11858180.371997 7211584.76 19069765.131998 4149296.50 23219061.631999 6278023.54 29497085.172000 5352671.87 34849757.042001 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 rankfrom salesgroup by year(date))select year, sum, rankfrom ranked_yearswhere rank <= 3;YEAR SUM RANK----------- ------------- -------1995 7731162.39 11997 7211584.76 21999 6278023.54 3