Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Advanced Oracle SQL Book ? (was: Reading column into a string variable)

Re: Advanced Oracle SQL Book ? (was: Reading column into a string variable)

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 14 Nov 2006 03:12:41 -0800
Message-ID: <1163502761.473608.172920@b28g2000cwb.googlegroups.com>


hasta_l3_at_hotmail.com wrote:
> Sometimes, I am amazed by the results one can achieve
> with Oracle SQL extensions, such as analytic functions
> and sys_connect_by_path below...
>
> Would you recommand a *good* book on advanced Oracle SQL
> (not PL/SQL) illustrating the use of these extensions to solve
> actual problems ?
>
> The examples in the SQL reference manual are not bad,
> but - understandably - a bit too focused on the statement
> or function being specified.
>
> Thanks
>
> --- Raoul

The SQL Reference Manual, at least for the 10g R2 release is a good reference for the analytical functions, and includes a sample for each function. See chapter 5 (and possibly 6): http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200.pdf

I had previously read a handful of SQL reference manuals, including "Special Edition Using SQL", "Oracle SQL High-Performance Tuning", and "Joe Celko's SQL for Smarties", none of which discussed the Oracle analytical functions. After seeing some of the short and very efficient solutions posted by Michel Cadot, compared to some of the less efficient non-analytical solutions that I generated for the same problems, I took a couple days vacation and went through each function described in chapters 5 and 6 and then created an example using the functions that could be applied to the tables and columns in my databases. It also helped to actively look for problems were the analytical functions can be applied. For instance, after my post yesterday to this thread, I decided to do the same concatenation of rows, but for each employee department. This is what I came up with (the WHERE clause has since been moved) is as follows: SELECT
  DEPARTMENT_ID,
  EMPLOYEE_LIST
FROM
  (SELECT
    DEPARTMENT_ID,
    SUBSTR(SYS_CONNECT_BY_PATH(ID,','),2) EMPLOYEE_LIST,     LEVEL CURRENT_EMP_COUNTER,
    EMPLOYEE_DEPT_COUNT
  FROM
    (SELECT

      ID,
      DEPARTMENT_ID,
      ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY ID)
ROW_POSITION,
      COUNT(ID)  OVER (PARTITION BY DEPARTMENT_ID) EMPLOYEE_DEPT_COUNT
    FROM
      EMPLOYEE
    WHERE
      ACTIVE='Y'
    ORDER BY
      DEPARTMENT_ID,
      ID)

  CONNECT BY PRIOR
    (DEPARTMENT_ID||TO_CHAR(ROW_POSITION)) = (DEPARTMENT_ID||TO_CHAR(ROW_POSITION-1))   START WITH
    ROW_POSITION=1
  )
WHERE
  CURRENT_EMP_COUNTER=EMPLOYEE_DEPT_COUNT; As I was learning to use the analytical functions, one of the difficult tasks was determining how to construct the correct syntax, for example:   ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY ID) The effects of the PARTITION BY syntax is similar in concept to a GROUP BY. If the items are grouped by DEPARTMENT_ID and within the group are ordered by ID, generate a sequence of numbers (the ROW_NUMBER function) starting with 1 for the first ID within the group and end with the last ID in the group being equal to the number of IDs in the group (the COUNT). It is also possible to use syntax such as this (WITHIN GROUP) for many of the analytical functions:
SELECT
  PRODUCT_CODE,
  RANK(1) WITHIN GROUP (ORDER BY UNIT_MATERIAL_COST DESC NULLS LAST) UNIT_PRICE,
  RANK(2) WITHIN GROUP (ORDER BY UNIT_MATERIAL_COST DESC NULLS LAST) UNIT_PRICE,
  RANK(3) WITHIN GROUP (ORDER BY UNIT_MATERIAL_COST DESC NULLS LAST) UNIT_PRICE,
  RANK(4) WITHIN GROUP (ORDER BY UNIT_MATERIAL_COST DESC NULLS LAST) UNIT_PRICE,
  RANK(5) WITHIN GROUP (ORDER BY UNIT_MATERIAL_COST DESC NULLS LAST) UNIT_PRICE
FROM
  PART
GROUP BY
  PRODUCT_CODE
ORDER BY
  PRODUCT_CODE; The above returns the five highest UNIT_MATERIAL_COST values for each of PRODUCT_CODE in the PART table.

The rows to be included in the analytical functions can also be specified, as in the following:
SELECT

  FW.FISCAL_YEAR_WEEK,
  FW.FISCAL_YEAR,
  FW.FISCAL_MONTH,

  TO_NUMBER(SUBSTR(TO_CHAR(SW.FISCAL_YEAR_WEEK),5)) FISCAL_WEEK_NUM,   SW.SALES,
  SUM(SW.SALES) OVER (PARTITION BY FW.FISCAL_YEAR,FW.FISCAL_MONTH ORDER BY FW.FISCAL_YEAR_WEEK ROWS BETWEEN 100 PRECEDING AND 0 FOLLOWING) MTD_SALES,
  SUM(SW.SALES) OVER (PARTITION BY
FW.FISCAL_YEAR,TRUNC(FW.FISCAL_MONTH/3) ORDER BY FW.FISCAL_YEAR_WEEK ROWS BETWEEN 1000 PRECEDING AND 0 FOLLOWING) QTD_SALES,   SUM(SW.SALES) OVER (PARTITION BY FW.FISCAL_YEAR ORDER BY FW.FISCAL_YEAR_WEEK ROWS BETWEEN 1000 PRECEDING AND 0 FOLLOWING) YTD_SALES
FROM
  FISCAL_WEEKS FW,
  SALES_BY_WEEK SW
WHERE
  FW.FISCAL_YEAR_WEEK=SW.FISCAL_YEAR_WEEK If there is interest, I will post other examples of analytical functions.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Nov 14 2006 - 05:12:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US