Analytical Functions: A Brief Introduction

最新推荐文章于 2024-11-18 18:36:50 发布
最新推荐文章于 2024-11-18 18:36:50 发布
阅读量562 收藏
点赞数
分类专栏： Oracle 文章标签： date calendar oracle function sql join

I have been using SQL since 1985, and in my humble but biased opinion, Oracle's extensions to standard ANSI SQL are superior to those of its competitors. Oracle 9i added a complete suite of analytical functions to provide some impressive reporting and calculation capabilities.

Just about every major system I have worked on has at least one historical table, i.e. a table that stores multiple historical entries to track changes to a particular data domain over time. A good example of an historical table is the JOB_HISTORY table in the HR demo schema, which contains multiple rows for each employee detailing that employee's job assignments over time. A typical reporting requirement usually required against historical tables is the retrieval of either the least or most recent entry- for example, the least recent job assignment for a specific employee based on the employee's start date in that position.

If I were using another DBMS, I would probably have little choice but to construct the following SQL query and utilize a subquery to find the minimum starting date, and then use the result from that subquery to isolate the remainder of the employee's job history information:

SQL> TTITLE 'Earliest Employee Job History Entry (Traditional Method)'
SQL> COL employee_id     FORMAT 9999     HEADING 'EmpID'
SQL> COL full_name       FORMAT A24      HEADING 'Employee Name'
SQL> COL job_id          FORMAT A10      HEADING 'JobID'
SQL> COL start_date      FORMAT A10      HEADING 'Start Date'
SQL> COL end_date        FORMAT A10      HEADING 'End Date'
SQL>
SQL> SELECT
  2      E.employee_id
  3     ,E.last_name || ', ' || E.first_name full_name
  4     ,JH.job_id
  5     ,TO_CHAR(JH.start_date, 'mm-dd-yyyy') start_date
  6     ,TO_CHAR(JH.end_date, 'mm-dd-yyyy') end_date
  7    FROM
  8      hr.employees E
  9     ,hr.job_history JH
 10   WHERE E.employee_id =  JH.employee_id
 11     AND JH.start_date = (
 12      SELECT MIN(start_date)
 13        FROM hr.job_history
 14       WHERE employee_id = 114)
 15     AND JH.employee_id = 114;

            Earliest Employee Job History Entry (Traditional Method)

EmpID Employee Name            JobID      Start Date End Date
----- ------------------------ ---------- ---------- ----------
  114 Raphaely, Den            ST_CLERK   03-24-1998 12-31-1999

As of Oracle 9i, however, I have an alternative to this method: I can use an Oracle analytical function to return just the first entry in the list of job history entries for the specific employee. Briefly, here is how analytical functions perform their tasks:

Execution Order. A SQL statement that uses an analytical function first executes all joins and then processes all WHERE, GROUP BY, and HAVING statements. Oracle then hands off the result set to the analytical function for calculation and processing, and then finally any query-level ORDER BY statements are processed.

Partitioning of the Result Set. If the analytical function's interior query has any GROUP BY statements, Oracle will logically divide the result set into partitions (not to be confused with partitioned tables or indexes) based on the columns specified. A single partition can be as large as all the rows in the result set, or the partitions can be relatively tiny, depending on the chosen columns. An analytical function can operate against aggregated values in each result set partition.

The Current Row and "Windowing." As the analytical function processes each row in the result set partition, that row is marked as the current row. The current row becomes a reference point for any windowing operations. Windowing operations occur whenever an analytical function defines a sliding window to limit the selection of rows within a result set partition. This means that analytical processing can be limited to a range of rows based on a displacement of a specific number of rows from the current row in the result set partition (e.g., plus or minus 3 rows from the current row). Analytical processing can also be based on specified inclusion criteria.

Analytical functions are typically used heavily in decision-support and data warehousing reporting because they are extremely powerful for calculating rankings and percentiles within a result set, as well as calculating moving and cumulative aggregations based on a displacement from a specific row in the result set. It is also possible to calculate lagging and leading values within a partition, or first and last values within a partition. Here is an example of how to return the same results using Oracle 9i's FIRST_VALUE analytical function:

SQL> TTITLE 'Earliest Employee Job History Entry (Analytic Function)'
SQL> COL eid             FORMAT 9999     HEADING 'EmpID'
SQL> COL name            FORMAT A24      HEADING 'Employee Name'
SQL> COL jobid           FORMAT A10      HEADING 'JobID'
SQL> COL begin_date      FORMAT A10      HEADING 'Start Date'
SQL> COL stop_date       FORMAT A10      HEADING 'End Date'
SQL> 
SQL> SELECT
  2       eid
  3      ,NAME
  4      ,jobid
  5      ,begin_date
  6      ,stop_date
  7    FROM (
  8      SELECT
  9          JH.employee_id eid
 10         ,E.last_name || ', ' || E.first_name  NAME
 11         ,JH.job_id jobid
 12         ,JH.START_DATE begin_date
 13         ,JH.end_date stop_date
 14         ,FIRST_VALUE(JH.start_date) OVER (
 15              ORDER BY JH.employee_id, JH.start_date
 16              ROWS BETWEEN UNBOUNDED PRECEDING
                      AND UNBOUNDED FOLLOWING
 17              ) recent_history
 18        FROM
 19          hr.employees E
 20         ,hr.job_history JH
 21       WHERE E.employee_id =  JH.employee_id
 22         AND JH.employee_id IN (114)
 23   )
 24   WHERE begin_date = recent_history
 25   ORDER BY eid;

            Earliest Employee Job History Entry (Analytic Function)

EmpID Employee Name            JobID      Start Date End Date
----- ------------------------ ---------- ---------- ----------
  114 Raphaely, Den            ST_CLERK   03/24/1998 12/31/1999

In this example, note that the FIRST_VALUE analytical function indicates the retrieval of a single result set partition, sorted within Employee ID and Start Date based on its ORDER BY clause. The ROWS directive tells Oracle to use all entries in each result set partition to determine which entry has the first (and therefore earliest) start date in each partition.

Listing 2.1 shows the DML statements I issued to add rows into the original JOB_HISTORY table to demonstrate these queries and the original queries themselves. An expanded query that shows how the PARTITION BY clause could be used within an analytical query to return the first job history entry for each employee is shown in Listing 2.2.

 

 

 

 

listing 2.1

/*
|| Oracle 10g SQL Enhancements - Listing 2
||
|| Contains examples of new Oracle 10g SQL features, including:
||
|| - Analytical Functions
|| - Partitioned Outer Joins
|| - Data Densification
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g's
|| new SQL features and should be carefully proofread before executing it
|| against any existing Oracle database to insure that no potential damage
|| can occur.
||
*/

----- 
-- Listing 2.1: Using Oracle 9i Analytical Functions
-----

-----
-- Add new entries into the HR.JOB_HISTORY table for Employees #114 and #122
-- for illustration of new Oracle 10g features
-----
INSERT INTO hr.job_history(employee_id, start_date, end_date, job_id, department_id)
VALUES(114, '12/31/1999', '09/30/2000', 'ST_MAN', 50);
INSERT INTO hr.job_history(employee_id, start_date, end_date, job_id, department_id)
VALUES(114, '09/30/2000', '07/31/2001', 'PU_MAN', 30);
INSERT INTO hr.job_history(employee_id, start_date, end_date, job_id, department_id)
VALUES(114, '07/31/2001', '12/31/2036', 'SA_REP', 80);
INSERT INTO hr.job_history(employee_id, start_date, end_date, job_id, department_id)
VALUES(122, '12/31/1999', '09/30/2000', 'ST_MAN', 50);
INSERT INTO hr.job_history(employee_id, start_date, end_date, job_id, department_id)
VALUES(122, '09/30/2000', '06/30/2002', 'SA_REP', 80);
INSERT INTO hr.job_history(employee_id, start_date, end_date, job_id, department_id)
VALUES(122, '06/30/2002', '12/31/2036', 'SA_MAN', 80);

COMMIT;

-----
-- Find the earliest Job History entry for an Employee using traditional
-- SQL query methods
-----
TTITLE 'Earliest Employee Job History Entry (Traditional Method)'
COL employee_id     FORMAT 9999     HEADING 'EmpID'
COL full_name       FORMAT A24      HEADING 'Employee Name'
COL job_id          FORMAT A10      HEADING 'JobID'
COL start_date      FORMAT A10      HEADING 'Start Date'
COL end_date        FORMAT A10      HEADING 'End Date'

SELECT
    E.employee_id
   ,E.last_name || ', ' || E.first_name full_name
   ,JH.job_id
   ,TO_CHAR(JH.start_date, 'mm-dd-yyyy') start_date
   ,TO_CHAR(JH.end_date, 'mm-dd-yyyy') end_date
  FROM
    hr.employees E
   ,hr.job_history JH
 WHERE E.employee_id =  JH.employee_id
   AND JH.start_date = (
    SELECT MIN(start_date)
      FROM hr.job_history
     WHERE employee_id = 114)
   AND JH.employee_id = 114;

-----
-- Use Oracle 9i Analytic Functions to find the earliest Job History entry 
-- for an Employee
-----
TTITLE 'Earliest Employee Job History Entry (Analytic Function)'
COL eid             FORMAT 9999     HEADING 'EmpID'
COL name            FORMAT A24      HEADING 'Employee Name'
COL jobid           FORMAT A10      HEADING 'JobID'
COL begin_date      FORMAT A10      HEADING 'Start Date'
COL stop_date       FORMAT A10      HEADING 'End Date'

SELECT
     eid
    ,NAME
    ,jobid
    ,begin_date
    ,stop_date
  FROM (
    SELECT
        JH.employee_id eid
       ,E.last_name || ', ' || E.first_name  NAME
       ,JH.job_id jobid
       ,JH.START_DATE begin_date
       ,JH.end_date stop_date
       ,FIRST_VALUE(JH.start_date) OVER (
            ORDER BY JH.employee_id, JH.start_date
            ROWS BETWEEN UNBOUNDED PRECEDING 
                     AND UNBOUNDED FOLLOWING
            ) recent_history
      FROM
        hr.employees E
       ,hr.job_history JH
     WHERE E.employee_id =  JH.employee_id
       AND JH.employee_id IN (114)
 )
 WHERE begin_date = recent_history
 ORDER BY eid;

----- 
-- Listing 2.2: Show the least recent Job History entry for each Employee. 
-- Note the use of the PARTITION BY clause to change the size of the result 
-- set partition based on the changing value of each Employee ID
-----
TTITLE 'Earliest Employee Job History Entries (Analytic Function)'
COL eid             FORMAT 9999     HEADING 'EmpID'
COL name            FORMAT A24      HEADING 'Employee Name'
COL jobid           FORMAT A10      HEADING 'JobID'
COL begin_date      FORMAT A10      HEADING 'Start Date'
COL stop_date       FORMAT A10      HEADING 'End Date'
SELECT
     eid
    ,name
    ,jobid
    ,begin_date
    ,stop_date
  FROM (
    SELECT
        JH.employee_id eid
       ,E.last_name || ', ' || E.first_name  NAME
       ,JH.job_id jobid
       ,TO_CHAR(JH.start_date, 'mm-dd-yyyy') begin_date
       ,TO_CHAR(JH.end_date, 'mm-dd-yyyy') stop_date
       ,FIRST_VALUE(JH.start_date) OVER (
            PARTITION BY JH.employee_id
            ORDER BY JH.employee_id, JH.start_date
            ROWS BETWEEN UNBOUNDED PRECEDING 
                     AND UNBOUNDED FOLLOWING
            ) recent_history
      FROM
        hr.employees E
       ,hr.job_history JH
     WHERE E.employee_id =  JH.employee_id)
 WHERE begin_date = recent_history
 ORDER BY eid;

>>> Results:

Thu Aug 04                                                            page    1
           Earliest Employee Job History Entries (Analytic Function)

EmpID Employee Name            JobID      Start Date End Date
----- ------------------------ ---------- ---------- ----------
  101 Kochhar, Neena           AC_ACCOUNT 09-21-1989 10-27-1993
  102 De Haan, Lex             IT_PROG    01-13-1993 07-24-1998
  114 Raphaely, Den            ST_CLERK   03-24-1998 12-31-1999
  122 Kaufling, Payam          ST_CLERK   01-01-1999 12-31-1999
  176 Taylor, Jonathon         SA_REP     03-24-1998 12-31-1998
  200 Whalen, Jennifer         AD_ASST    09-17-1987 06-17-1993
  201 Hartstein, Michael       MK_REP     02-17-1996 12-19-1999

7 rows selected.

----- 
-- Listing 2.3: Using Partitioned Outer Joins in Oracle 10g
-----

-----
-- Create a view that contains all possible Calendar Years
-----
CREATE OR REPLACE VIEW sh.calendar_years AS
    SELECT
        DISTINCT calendar_year
      FROM sh.times
    UNION
    SELECT 2002 calendar_year
      FROM dual
    UNION
    SELECT 2003 calendar_year
      FROM dual
    UNION
    SELECT 2004 calendar_year
      FROM dual
    UNION
    SELECT 2005 calendar_year
      FROM dual
    ORDER BY calendar_year;

-----
-- Create a view that shows Sales for selected Products only for
-- selected U.S. Midwestern States
-----
CREATE OR REPLACE VIEW sh.sales_midwest_only AS
    SELECT
        CT.cust_state_province state
       ,P.prod_name prod
       ,P.prod_category ctgy
       ,T.calendar_year year
       ,SUM(S.amount_sold) sale
       ,COUNT(S.amount_sold) cnt
      FROM
         sh.sales S
        ,sh.times T
        ,sh.customers CT
        ,sh.products P
     WHERE S.time_id = T.time_id
       AND S.prod_id = P.prod_id
       AND S.cust_id = CT.cust_id
       AND CT.country_id = 'US'
       AND CT.cust_state_province IN ('IL','IN','MI','MN','WI')
       AND P.prod_id BETWEEN 5 AND 35
     GROUP BY
         CT.cust_state_province
        ,P.prod_category
        ,P.prod_name
        ,T.calendar_year;

-----
-- Populate all products across all available states via a partitioned outer
-- join. Note that for the states other than the five specified in the first
-- query, NULL values will be returned (which are converted to zeroes via the
-- NVL function)
-----
SELECT
     SLS.prod
    ,RGNS.state
    ,SLS.year
    ,NVL(SLS.Sales,0) dense_sales
FROM
    (SELECT
         MWO.state
        ,MWO.prod
        ,MWO.year
        ,SUM(MWO.sale) sales
      FROM
          sh.sales_midwest_only MWO
     GROUP BY
         MWO.state
        ,MWO.prod
        ,MWO.YEAR
    ) SLS
    PARTITION BY (SLS.prod)
    RIGHT OUTER JOIN
    (SELECT
        DISTINCT cust_state_province state
       FROM sh.customers
      WHERE cust_state_province IN ('IA', 'IL','IN','MI','OH','WI')
    ) RGNS
    ON (RGNS.state = SLS.state)
 ORDER BY SLS.prod, SLS.state, SLS.year;

>>> Results:

SQL> TTITLE 'Partitioned Outer Join Example'
SQL> COL prod            FORMAT A32              HEADING 'Product'
SQL> COL state           FORMAT A4               HEADING 'State'
SQL> COL year            FORMAT 9999             HEADING 'JobID'
SQL> COL tot_sales       FORMAT 999,999,999.99   HEADING 'Total Sales'
SQL>
SQL> SELECT
  2       SLS.prod
  3      ,RGNS.state
  4      ,SLS.year
  5      ,NVL(SLS.sales,0) tot_sales
  6  FROM
  7      (SELECT
  8           MWO.state
  9          ,MWO.prod
 10          ,MWO.year
 11          ,SUM(MWO.sale) sales
 12        FROM
 13            sh.sales_midwest_only MWO
 14       GROUP BY
 15           MWO.state
 16          ,MWO.prod
 17          ,MWO.YEAR
 18      ) SLS
 19      PARTITION BY (SLS.prod)
 20      RIGHT OUTER JOIN
 21      (SELECT
 22          DISTINCT cust_state_province state
 23         FROM sh.customers
 24        WHERE cust_state_province IN ('IA', 'IL','IN','MI','OH','WI')
 25      ) RGNS
 26      ON (RGNS.state = SLS.state)
 27   ORDER BY SLS.prod, SLS.state, SLS.year;

Thu Aug 04                                                            page    1
                         Partitioned Outer Join Example

Product                          Stat JobID     Total Sales
-------------------------------- ---- ----- ---------------
And 2 Crosscourt Tee Kids        IL    1998          826.00
And 2 Crosscourt Tee Kids        IL    1999        1,187.90
And 2 Crosscourt Tee Kids        IL    2000        2,619.40
And 2 Crosscourt Tee Kids        IN    1998          266.00
And 2 Crosscourt Tee Kids        IN    2000        2,086.00
And 2 Crosscourt Tee Kids        MI    1999           53.20
And 2 Crosscourt Tee Kids        MI    2000        1,209.60
And 2 Crosscourt Tee Kids        WI    1998          686.00
And 2 Crosscourt Tee Kids        WI    1999          504.00
And 2 Crosscourt Tee Kids        WI    2000          378.00
And 2 Crosscourt Tee Kids        IA                     .00
And 2 Crosscourt Tee Kids        OH                     .00
Coin Pocket Twill Cargo Trousers MI    1999          234.00
Coin Pocket Twill Cargo Trousers MI    2000           78.00
Coin Pocket Twill Cargo Trousers WI    1999          722.15
Coin Pocket Twill Cargo Trousers WI    2000          390.00
Coin Pocket Twill Cargo Trousers IA                     .00
Coin Pocket Twill Cargo Trousers IL                     .00
Coin Pocket Twill Cargo Trousers OH                     .00
Coin Pocket Twill Cargo Trousers IN                     .00
Gurfield& Murks Pleated Trousers IL    1998        9,100.00
Gurfield& Murks Pleated Trousers IL    1999       13,825.00
Gurfield& Murks Pleated Trousers IL    2000        2,100.00
Gurfield& Murks Pleated Trousers IN    1999        7,525.00
Gurfield& Murks Pleated Trousers MI    1998       26,040.00
Gurfield& Murks Pleated Trousers MI    1999       12,600.00
Gurfield& Murks Pleated Trousers MI    2000       19,425.00
Gurfield& Murks Pleated Trousers WI    1998          175.00
Gurfield& Murks Pleated Trousers IA                     .00
Gurfield& Murks Pleated Trousers OH                     .00
Kahala Pleated Chino Short       IL    1998          504.00
Kahala Pleated Chino Short       IL    1999        3,738.00
Kahala Pleated Chino Short       IA                     .00
Kahala Pleated Chino Short       IN                     .00
Kahala Pleated Chino Short       MI                     .00
Kahala Pleated Chino Short       OH                     .00
Kahala Pleated Chino Short       WI                     .00

37 rows selected.

(Note: The "missing" results populated via the partitioned outer join are in bold.)

----- 
-- Listing 2.4: Oracle 10g Data Densification Examples. Note the use of the
-- WITH clause to organize the component queries
-----
TTITLE 'Partitioned Outer Join Densifying Across Two Dimensions'
COL prod            FORMAT A32              HEADING 'Product'
COL state           FORMAT A4               HEADING 'State'
COL year            FORMAT 9999             HEADING 'JobID'
COL tot_sales       FORMAT 999,999,999.99   HEADING 'Total Sales'

WITH
    -- Gather all Midwest-only sales data
    Q1 AS (
        SELECT
             prod
            ,state
            ,year
            ,SUM(sale) sale
          FROM
             sh.sales_midwest_only
         GROUP BY state, prod, year
    ),
    -- Gather a list of distinct states
    Q2 AS (
        SELECT DISTINCT cust_state_province state
       FROM sh.customers
      WHERE cust_state_province IN ('IA', 'IL','IN','MI','OH','WI')
    ),
    -- Gather a list of distinct calendar years
    Q3 AS (
        SELECT calendar_year year
          FROM sh.calendar_years
    )
SELECT
     Q4.prod
    ,Q4.state
    ,Q3.year
    ,NVL(Q4.sale, 0) tot_sales
  FROM
    (SELECT
         Q1.prod
        ,Q2.state
        ,Q1.year
        ,Q1.sale
      FROM Q1
        PARTITION BY (prod)
        RIGHT OUTER JOIN Q2 -- This partitioned outer join "densifies" on state
        ON (Q1.state = Q2.state)
    ) Q4
        PARTITION BY (prod,state)
        RIGHT OUTER JOIN Q3 -- This partioned outer join "densifies" on calendar year
        ON (Q4.YEAR = Q3.year)
 ORDER BY 1, 2, 3;

>>> Results:

Thu Aug 04                                                             page    1
            Partitioned Outer Join Densifying Across Two Dimensions

Product                          Stat JobID     Total Sales
-------------------------------- ---- ----- ---------------
And 2 Crosscourt Tee Kids        IA    1998             .00
And 2 Crosscourt Tee Kids        IA    1999             .00
And 2 Crosscourt Tee Kids        IA    2000             .00
And 2 Crosscourt Tee Kids        IA    2001             .00
And 2 Crosscourt Tee Kids        IA    2002             .00
And 2 Crosscourt Tee Kids        IA    2003             .00
And 2 Crosscourt Tee Kids        IA    2004             .00
And 2 Crosscourt Tee Kids        IA    2005             .00
And 2 Crosscourt Tee Kids        IL    1998          826.00
And 2 Crosscourt Tee Kids        IL    1999        1,187.90
And 2 Crosscourt Tee Kids        IL    2000        2,619.40
And 2 Crosscourt Tee Kids        IL    2001             .00
And 2 Crosscourt Tee Kids        IL    2002             .00
And 2 Crosscourt Tee Kids        IL    2003             .00
And 2 Crosscourt Tee Kids        IL    2004             .00
And 2 Crosscourt Tee Kids        IL    2005             .00
And 2 Crosscourt Tee Kids        IN    1998          266.00
And 2 Crosscourt Tee Kids        IN    1999             .00
And 2 Crosscourt Tee Kids        IN    2000        2,086.00
And 2 Crosscourt Tee Kids        IN    2001             .00
And 2 Crosscourt Tee Kids        IN    2002             .00
And 2 Crosscourt Tee Kids        IN    2003             .00
And 2 Crosscourt Tee Kids        IN    2004             .00
And 2 Crosscourt Tee Kids        IN    2005             .00
And 2 Crosscourt Tee Kids        MI    1998             .00
And 2 Crosscourt Tee Kids        MI    1999           53.20
And 2 Crosscourt Tee Kids        MI    2000        1,209.60
And 2 Crosscourt Tee Kids        MI    2001             .00
And 2 Crosscourt Tee Kids        MI    2002             .00
And 2 Crosscourt Tee Kids        MI    2003             .00
And 2 Crosscourt Tee Kids        MI    2004             .00
And 2 Crosscourt Tee Kids        MI    2005             .00
And 2 Crosscourt Tee Kids        OH    1998             .00
And 2 Crosscourt Tee Kids        OH    1999             .00
And 2 Crosscourt Tee Kids        OH    2000             .00
And 2 Crosscourt Tee Kids        OH    2001             .00
And 2 Crosscourt Tee Kids        OH    2002             .00
And 2 Crosscourt Tee Kids        OH    2003             .00
And 2 Crosscourt Tee Kids        OH    2004             .00
And 2 Crosscourt Tee Kids        OH    2005             .00
And 2 Crosscourt Tee Kids        WI    1998          686.00
And 2 Crosscourt Tee Kids        WI    1999          504.00
And 2 Crosscourt Tee Kids        WI    2000          378.00
And 2 Crosscourt Tee Kids        WI    2001             .00
And 2 Crosscourt Tee Kids        WI    2002             .00
And 2 Crosscourt Tee Kids        WI    2003             .00
And 2 Crosscourt Tee Kids        WI    2004             .00
And 2 Crosscourt Tee Kids        WI    2005             .00
Coin Pocket Twill Cargo Trousers IA    1998             .00
Coin Pocket Twill Cargo Trousers IA    1999             .00
Coin Pocket Twill Cargo Trousers IA    2000             .00
Coin Pocket Twill Cargo Trousers IA    2001             .00
Coin Pocket Twill Cargo Trousers IA    2002             .00
Coin Pocket Twill Cargo Trousers IA    2003             .00
Coin Pocket Twill Cargo Trousers IA    2004             .00
Coin Pocket Twill Cargo Trousers IA    2005             .00
Coin Pocket Twill Cargo Trousers IL    1998             .00
Coin Pocket Twill Cargo Trousers IL    1999             .00
Coin Pocket Twill Cargo Trousers IL    2000             .00
Coin Pocket Twill Cargo Trousers IL    2001             .00
Coin Pocket Twill Cargo Trousers IL    2002             .00
Coin Pocket Twill Cargo Trousers IL    2003             .00
Coin Pocket Twill Cargo Trousers IL    2004             .00
Coin Pocket Twill Cargo Trousers IL    2005             .00
Coin Pocket Twill Cargo Trousers IN    1998             .00
Coin Pocket Twill Cargo Trousers IN    1999             .00
Coin Pocket Twill Cargo Trousers IN    2000             .00
Coin Pocket Twill Cargo Trousers IN    2001             .00
Coin Pocket Twill Cargo Trousers IN    2002             .00
Coin Pocket Twill Cargo Trousers IN    2003             .00
Coin Pocket Twill Cargo Trousers IN    2004             .00
Coin Pocket Twill Cargo Trousers IN    2005             .00
Coin Pocket Twill Cargo Trousers MI    1998             .00
Coin Pocket Twill Cargo Trousers MI    1999          234.00
Coin Pocket Twill Cargo Trousers MI    2000           78.00
Coin Pocket Twill Cargo Trousers MI    2001             .00
Coin Pocket Twill Cargo Trousers MI    2002             .00
Coin Pocket Twill Cargo Trousers MI    2003             .00
Coin Pocket Twill Cargo Trousers MI    2004             .00
Coin Pocket Twill Cargo Trousers MI    2005             .00
Coin Pocket Twill Cargo Trousers OH    1998             .00
Coin Pocket Twill Cargo Trousers OH    1999             .00
Coin Pocket Twill Cargo Trousers OH    2000             .00
Coin Pocket Twill Cargo Trousers OH    2001             .00
Coin Pocket Twill Cargo Trousers OH    2002             .00
Coin Pocket Twill Cargo Trousers OH    2003             .00
Coin Pocket Twill Cargo Trousers OH    2004             .00
Coin Pocket Twill Cargo Trousers OH    2005             .00
Coin Pocket Twill Cargo Trousers WI    1998             .00
Coin Pocket Twill Cargo Trousers WI    1999          722.15
Coin Pocket Twill Cargo Trousers WI    2000          390.00
Coin Pocket Twill Cargo Trousers WI    2001             .00
Coin Pocket Twill Cargo Trousers WI    2002             .00
Coin Pocket Twill Cargo Trousers WI    2003             .00
Coin Pocket Twill Cargo Trousers WI    2004             .00
Coin Pocket Twill Cargo Trousers WI    2005             .00
Gurfield& Murks Pleated Trousers IA    1998             .00
Gurfield& Murks Pleated Trousers IA    1999             .00
Gurfield& Murks Pleated Trousers IA    2000             .00
Gurfield& Murks Pleated Trousers IA    2001             .00
Gurfield& Murks Pleated Trousers IA    2002             .00
Gurfield& Murks Pleated Trousers IA    2003             .00
Gurfield& Murks Pleated Trousers IA    2004             .00
Gurfield& Murks Pleated Trousers IA    2005             .00
Gurfield& Murks Pleated Trousers IL    1998        9,100.00
Gurfield& Murks Pleated Trousers IL    1999       13,825.00
Gurfield& Murks Pleated Trousers IL    2000        2,100.00
Gurfield& Murks Pleated Trousers IL    2001             .00
Gurfield& Murks Pleated Trousers IL    2002             .00
Gurfield& Murks Pleated Trousers IL    2003             .00
Gurfield& Murks Pleated Trousers IL    2004             .00
Gurfield& Murks Pleated Trousers IL    2005             .00
Gurfield& Murks Pleated Trousers IN    1998             .00
Gurfield& Murks Pleated Trousers IN    1999        7,525.00
Gurfield& Murks Pleated Trousers IN    2000             .00
Gurfield& Murks Pleated Trousers IN    2001             .00
Gurfield& Murks Pleated Trousers IN    2002             .00
Gurfield& Murks Pleated Trousers IN    2003             .00
Gurfield& Murks Pleated Trousers IN    2004             .00
Gurfield& Murks Pleated Trousers IN    2005             .00
Gurfield& Murks Pleated Trousers MI    1998       26,040.00
Gurfield& Murks Pleated Trousers MI    1999       12,600.00
Gurfield& Murks Pleated Trousers MI    2000       19,425.00
Gurfield& Murks Pleated Trousers MI    2001             .00
Gurfield& Murks Pleated Trousers MI    2002             .00
Gurfield& Murks Pleated Trousers MI    2003             .00
Gurfield& Murks Pleated Trousers MI    2004             .00
Gurfield& Murks Pleated Trousers MI    2005             .00
Gurfield& Murks Pleated Trousers OH    1998             .00
Gurfield& Murks Pleated Trousers OH    1999             .00
Gurfield& Murks Pleated Trousers OH    2000             .00
Gurfield& Murks Pleated Trousers OH    2001             .00
Gurfield& Murks Pleated Trousers OH    2002             .00
Gurfield& Murks Pleated Trousers OH    2003             .00
Gurfield& Murks Pleated Trousers OH    2004             .00
Gurfield& Murks Pleated Trousers OH    2005             .00
Gurfield& Murks Pleated Trousers WI    1998          175.00
Gurfield& Murks Pleated Trousers WI    1999             .00
Gurfield& Murks Pleated Trousers WI    2000             .00
Gurfield& Murks Pleated Trousers WI    2001             .00
Gurfield& Murks Pleated Trousers WI    2002             .00
Gurfield& Murks Pleated Trousers WI    2003             .00
Gurfield& Murks Pleated Trousers WI    2004             .00
Gurfield& Murks Pleated Trousers WI    2005             .00
Kahala Pleated Chino Short       IA    1998             .00
Kahala Pleated Chino Short       IA    1999             .00
Kahala Pleated Chino Short       IA    2000             .00
Kahala Pleated Chino Short       IA    2001             .00
Kahala Pleated Chino Short       IA    2002             .00
Kahala Pleated Chino Short       IA    2003             .00
Kahala Pleated Chino Short       IA    2004             .00
Kahala Pleated Chino Short       IA    2005             .00
Kahala Pleated Chino Short       IL    1998          504.00
Kahala Pleated Chino Short       IL    1999        3,738.00
Kahala Pleated Chino Short       IL    2000             .00
Kahala Pleated Chino Short       IL    2001             .00
Kahala Pleated Chino Short       IL    2002             .00
Kahala Pleated Chino Short       IL    2003             .00
Kahala Pleated Chino Short       IL    2004             .00
Kahala Pleated Chino Short       IL    2005             .00
Kahala Pleated Chino Short       IN    1998             .00
Kahala Pleated Chino Short       IN    1999             .00
Kahala Pleated Chino Short       IN    2000             .00
Kahala Pleated Chino Short       IN    2001             .00
Kahala Pleated Chino Short       IN    2002             .00
Kahala Pleated Chino Short       IN    2003             .00
Kahala Pleated Chino Short       IN    2004             .00
Kahala Pleated Chino Short       IN    2005             .00
Kahala Pleated Chino Short       MI    1998             .00
Kahala Pleated Chino Short       MI    1999             .00
Kahala Pleated Chino Short       MI    2000             .00
Kahala Pleated Chino Short       MI    2001             .00
Kahala Pleated Chino Short       MI    2002             .00
Kahala Pleated Chino Short       MI    2003             .00
Kahala Pleated Chino Short       MI    2004             .00
Kahala Pleated Chino Short       MI    2005             .00
Kahala Pleated Chino Short       OH    1998             .00
Kahala Pleated Chino Short       OH    1999             .00
Kahala Pleated Chino Short       OH    2000             .00
Kahala Pleated Chino Short       OH    2001             .00
Kahala Pleated Chino Short       OH    2002             .00
Kahala Pleated Chino Short       OH    2003             .00
Kahala Pleated Chino Short       OH    2004             .00
Kahala Pleated Chino Short       OH    2005             .00
Kahala Pleated Chino Short       WI    1998             .00
Kahala Pleated Chino Short       WI    1999             .00
Kahala Pleated Chino Short       WI    2000             .00
Kahala Pleated Chino Short       WI    2001             .00
Kahala Pleated Chino Short       WI    2002             .00
Kahala Pleated Chino Short       WI    2003             .00
Kahala Pleated Chino Short       WI    2004             .00
Kahala Pleated Chino Short       WI    2005             .00

192 rows selected.

 
 
listing 2.2
/*
|| Oracle 10g SQL Enhancements - Listing 2
||
|| Contains examples of new Oracle 10g SQL features, including:
||
|| - Analytical Functions
|| - Partitioned Outer Joins
|| - Data Densification
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g's
|| new SQL features and should be carefully proofread before executing it
|| against any existing Oracle database to insure that no potential damage
|| can occur.
||
*/

----- 
-- Listing 2.1: Using Oracle 9i Analytical Functions
-----

-----
-- Add new entries into the HR.JOB_HISTORY table for Employees #114 and #122
-- for illustration of new Oracle 10g features
-----
INSERT INTO hr.job_history(employee_id, start_date, end_date, job_id, department_id)
VALUES(114, '12/31/1999', '09/30/2000', 'ST_MAN', 50);
INSERT INTO hr.job_history(employee_id, start_date, end_date, job_id, department_id)
VALUES(114, '09/30/2000', '07/31/2001', 'PU_MAN', 30);
INSERT INTO hr.job_history(employee_id, start_date, end_date, job_id, department_id)
VALUES(114, '07/31/2001', '12/31/2036', 'SA_REP', 80);
INSERT INTO hr.job_history(employee_id, start_date, end_date, job_id, department_id)
VALUES(122, '12/31/1999', '09/30/2000', 'ST_MAN', 50);
INSERT INTO hr.job_history(employee_id, start_date, end_date, job_id, department_id)
VALUES(122, '09/30/2000', '06/30/2002', 'SA_REP', 80);
INSERT INTO hr.job_history(employee_id, start_date, end_date, job_id, department_id)
VALUES(122, '06/30/2002', '12/31/2036', 'SA_MAN', 80);

COMMIT;

-----
-- Find the earliest Job History entry for an Employee using traditional
-- SQL query methods
-----
TTITLE 'Earliest Employee Job History Entry (Traditional Method)'
COL employee_id     FORMAT 9999     HEADING 'EmpID'
COL full_name       FORMAT A24      HEADING 'Employee Name'
COL job_id          FORMAT A10      HEADING 'JobID'
COL start_date      FORMAT A10      HEADING 'Start Date'
COL end_date        FORMAT A10      HEADING 'End Date'

SELECT
    E.employee_id
   ,E.last_name || ', ' || E.first_name full_name
   ,JH.job_id
   ,TO_CHAR(JH.start_date, 'mm-dd-yyyy') start_date
   ,TO_CHAR(JH.end_date, 'mm-dd-yyyy') end_date
  FROM
    hr.employees E
   ,hr.job_history JH
 WHERE E.employee_id =  JH.employee_id
   AND JH.start_date = (
    SELECT MIN(start_date)
      FROM hr.job_history
     WHERE employee_id = 114)
   AND JH.employee_id = 114;

-----
-- Use Oracle 9i Analytic Functions to find the earliest Job History entry 
-- for an Employee
-----
TTITLE 'Earliest Employee Job History Entry (Analytic Function)'
COL eid             FORMAT 9999     HEADING 'EmpID'
COL name            FORMAT A24      HEADING 'Employee Name'
COL jobid           FORMAT A10      HEADING 'JobID'
COL begin_date      FORMAT A10      HEADING 'Start Date'
COL stop_date       FORMAT A10      HEADING 'End Date'

SELECT
     eid
    ,NAME
    ,jobid
    ,begin_date
    ,stop_date
  FROM (
    SELECT
        JH.employee_id eid
       ,E.last_name || ', ' || E.first_name  NAME
       ,JH.job_id jobid
       ,JH.START_DATE begin_date
       ,JH.end_date stop_date
       ,FIRST_VALUE(JH.start_date) OVER (
            ORDER BY JH.employee_id, JH.start_date
            ROWS BETWEEN UNBOUNDED PRECEDING 
                     AND UNBOUNDED FOLLOWING
            ) recent_history
      FROM
        hr.employees E
       ,hr.job_history JH
     WHERE E.employee_id =  JH.employee_id
       AND JH.employee_id IN (114)
 )
 WHERE begin_date = recent_history
 ORDER BY eid;

----- 
-- Listing 2.2: Show the least recent Job History entry for each Employee. 
-- Note the use of the PARTITION BY clause to change the size of the result 
-- set partition based on the changing value of each Employee ID
-----
TTITLE 'Earliest Employee Job History Entries (Analytic Function)'
COL eid             FORMAT 9999     HEADING 'EmpID'
COL name            FORMAT A24      HEADING 'Employee Name'
COL jobid           FORMAT A10      HEADING 'JobID'
COL begin_date      FORMAT A10      HEADING 'Start Date'
COL stop_date       FORMAT A10      HEADING 'End Date'
SELECT
     eid
    ,name
    ,jobid
    ,begin_date
    ,stop_date
  FROM (
    SELECT
        JH.employee_id eid
       ,E.last_name || ', ' || E.first_name  NAME
       ,JH.job_id jobid
       ,TO_CHAR(JH.start_date, 'mm-dd-yyyy') begin_date
       ,TO_CHAR(JH.end_date, 'mm-dd-yyyy') stop_date
       ,FIRST_VALUE(JH.start_date) OVER (
            PARTITION BY JH.employee_id
            ORDER BY JH.employee_id, JH.start_date
            ROWS BETWEEN UNBOUNDED PRECEDING 
                     AND UNBOUNDED FOLLOWING
            ) recent_history
      FROM
        hr.employees E
       ,hr.job_history JH
     WHERE E.employee_id =  JH.employee_id)
 WHERE begin_date = recent_history
 ORDER BY eid;

>>> Results:

Thu Aug 04                                                            page    1
           Earliest Employee Job History Entries (Analytic Function)

EmpID Employee Name            JobID      Start Date End Date
----- ------------------------ ---------- ---------- ----------
  101 Kochhar, Neena           AC_ACCOUNT 09-21-1989 10-27-1993
  102 De Haan, Lex             IT_PROG    01-13-1993 07-24-1998
  114 Raphaely, Den            ST_CLERK   03-24-1998 12-31-1999
  122 Kaufling, Payam          ST_CLERK   01-01-1999 12-31-1999
  176 Taylor, Jonathon         SA_REP     03-24-1998 12-31-1998
  200 Whalen, Jennifer         AD_ASST    09-17-1987 06-17-1993
  201 Hartstein, Michael       MK_REP     02-17-1996 12-19-1999

7 rows selected.

----- 
-- Listing 2.3: Using Partitioned Outer Joins in Oracle 10g
-----

-----
-- Create a view that contains all possible Calendar Years
-----
CREATE OR REPLACE VIEW sh.calendar_years AS
    SELECT
        DISTINCT calendar_year
      FROM sh.times
    UNION
    SELECT 2002 calendar_year
      FROM dual
    UNION
    SELECT 2003 calendar_year
      FROM dual
    UNION
    SELECT 2004 calendar_year
      FROM dual
    UNION
    SELECT 2005 calendar_year
      FROM dual
    ORDER BY calendar_year;

-----
-- Create a view that shows Sales for selected Products only for
-- selected U.S. Midwestern States
-----
CREATE OR REPLACE VIEW sh.sales_midwest_only AS
    SELECT
        CT.cust_state_province state
       ,P.prod_name prod
       ,P.prod_category ctgy
       ,T.calendar_year year
       ,SUM(S.amount_sold) sale
       ,COUNT(S.amount_sold) cnt
      FROM
         sh.sales S
        ,sh.times T
        ,sh.customers CT
        ,sh.products P
     WHERE S.time_id = T.time_id
       AND S.prod_id = P.prod_id
       AND S.cust_id = CT.cust_id
       AND CT.country_id = 'US'
       AND CT.cust_state_province IN ('IL','IN','MI','MN','WI')
       AND P.prod_id BETWEEN 5 AND 35
     GROUP BY
         CT.cust_state_province
        ,P.prod_category
        ,P.prod_name
        ,T.calendar_year;

-----
-- Populate all products across all available states via a partitioned outer
-- join. Note that for the states other than the five specified in the first
-- query, NULL values will be returned (which are converted to zeroes via the
-- NVL function)
-----
SELECT
     SLS.prod
    ,RGNS.state
    ,SLS.year
    ,NVL(SLS.Sales,0) dense_sales
FROM
    (SELECT
         MWO.state
        ,MWO.prod
        ,MWO.year
        ,SUM(MWO.sale) sales
      FROM
          sh.sales_midwest_only MWO
     GROUP BY
         MWO.state
        ,MWO.prod
        ,MWO.YEAR
    ) SLS
    PARTITION BY (SLS.prod)
    RIGHT OUTER JOIN
    (SELECT
        DISTINCT cust_state_province state
       FROM sh.customers
      WHERE cust_state_province IN ('IA', 'IL','IN','MI','OH','WI')
    ) RGNS
    ON (RGNS.state = SLS.state)
 ORDER BY SLS.prod, SLS.state, SLS.year;

>>> Results:

SQL> TTITLE 'Partitioned Outer Join Example'
SQL> COL prod            FORMAT A32              HEADING 'Product'
SQL> COL state           FORMAT A4               HEADING 'State'
SQL> COL year            FORMAT 9999             HEADING 'JobID'
SQL> COL tot_sales       FORMAT 999,999,999.99   HEADING 'Total Sales'
SQL>
SQL> SELECT
  2       SLS.prod
  3      ,RGNS.state
  4      ,SLS.year
  5      ,NVL(SLS.sales,0) tot_sales
  6  FROM
  7      (SELECT
  8           MWO.state
  9          ,MWO.prod
 10          ,MWO.year
 11          ,SUM(MWO.sale) sales
 12        FROM
 13            sh.sales_midwest_only MWO
 14       GROUP BY
 15           MWO.state
 16          ,MWO.prod
 17          ,MWO.YEAR
 18      ) SLS
 19      PARTITION BY (SLS.prod)
 20      RIGHT OUTER JOIN
 21      (SELECT
 22          DISTINCT cust_state_province state
 23         FROM sh.customers
 24        WHERE cust_state_province IN ('IA', 'IL','IN','MI','OH','WI')
 25      ) RGNS
 26      ON (RGNS.state = SLS.state)
 27   ORDER BY SLS.prod, SLS.state, SLS.year;

Thu Aug 04                                                            page    1
                         Partitioned Outer Join Example

Product                          Stat JobID     Total Sales
-------------------------------- ---- ----- ---------------
And 2 Crosscourt Tee Kids        IL    1998          826.00
And 2 Crosscourt Tee Kids        IL    1999        1,187.90
And 2 Crosscourt Tee Kids        IL    2000        2,619.40
And 2 Crosscourt Tee Kids        IN    1998          266.00
And 2 Crosscourt Tee Kids        IN    2000        2,086.00
And 2 Crosscourt Tee Kids        MI    1999           53.20
And 2 Crosscourt Tee Kids        MI    2000        1,209.60
And 2 Crosscourt Tee Kids        WI    1998          686.00
And 2 Crosscourt Tee Kids        WI    1999          504.00
And 2 Crosscourt Tee Kids        WI    2000          378.00
And 2 Crosscourt Tee Kids        IA                     .00
And 2 Crosscourt Tee Kids        OH                     .00
Coin Pocket Twill Cargo Trousers MI    1999          234.00
Coin Pocket Twill Cargo Trousers MI    2000           78.00
Coin Pocket Twill Cargo Trousers WI    1999          722.15
Coin Pocket Twill Cargo Trousers WI    2000          390.00
Coin Pocket Twill Cargo Trousers IA                     .00
Coin Pocket Twill Cargo Trousers IL                     .00
Coin Pocket Twill Cargo Trousers OH                     .00
Coin Pocket Twill Cargo Trousers IN                     .00
Gurfield& Murks Pleated Trousers IL    1998        9,100.00
Gurfield& Murks Pleated Trousers IL    1999       13,825.00
Gurfield& Murks Pleated Trousers IL    2000        2,100.00
Gurfield& Murks Pleated Trousers IN    1999        7,525.00
Gurfield& Murks Pleated Trousers MI    1998       26,040.00
Gurfield& Murks Pleated Trousers MI    1999       12,600.00
Gurfield& Murks Pleated Trousers MI    2000       19,425.00
Gurfield& Murks Pleated Trousers WI    1998          175.00
Gurfield& Murks Pleated Trousers IA                     .00
Gurfield& Murks Pleated Trousers OH                     .00
Kahala Pleated Chino Short       IL    1998          504.00
Kahala Pleated Chino Short       IL    1999        3,738.00
Kahala Pleated Chino Short       IA                     .00
Kahala Pleated Chino Short       IN                     .00
Kahala Pleated Chino Short       MI                     .00
Kahala Pleated Chino Short       OH                     .00
Kahala Pleated Chino Short       WI                     .00

37 rows selected.

(Note: The "missing" results populated via the partitioned outer join are in bold.)

----- 
-- Listing 2.4: Oracle 10g Data Densification Examples. Note the use of the
-- WITH clause to organize the component queries
-----
TTITLE 'Partitioned Outer Join Densifying Across Two Dimensions'
COL prod            FORMAT A32              HEADING 'Product'
COL state           FORMAT A4               HEADING 'State'
COL year            FORMAT 9999             HEADING 'JobID'
COL tot_sales       FORMAT 999,999,999.99   HEADING 'Total Sales'

WITH
    -- Gather all Midwest-only sales data
    Q1 AS (
        SELECT
             prod
            ,state
            ,year
            ,SUM(sale) sale
          FROM
             sh.sales_midwest_only
         GROUP BY state, prod, year
    ),
    -- Gather a list of distinct states
    Q2 AS (
        SELECT DISTINCT cust_state_province state
       FROM sh.customers
      WHERE cust_state_province IN ('IA', 'IL','IN','MI','OH','WI')
    ),
    -- Gather a list of distinct calendar years
    Q3 AS (
        SELECT calendar_year year
          FROM sh.calendar_years
    )
SELECT
     Q4.prod
    ,Q4.state
    ,Q3.year
    ,NVL(Q4.sale, 0) tot_sales
  FROM
    (SELECT
         Q1.prod
        ,Q2.state
        ,Q1.year
        ,Q1.sale
      FROM Q1
        PARTITION BY (prod)
        RIGHT OUTER JOIN Q2 -- This partitioned outer join "densifies" on state
        ON (Q1.state = Q2.state)
    ) Q4
        PARTITION BY (prod,state)
        RIGHT OUTER JOIN Q3 -- This partioned outer join "densifies" on calendar year
        ON (Q4.YEAR = Q3.year)
 ORDER BY 1, 2, 3;

>>> Results:

Thu Aug 04                                                             page    1
            Partitioned Outer Join Densifying Across Two Dimensions

Product                          Stat JobID     Total Sales
-------------------------------- ---- ----- ---------------
And 2 Crosscourt Tee Kids        IA    1998             .00
And 2 Crosscourt Tee Kids        IA    1999             .00
And 2 Crosscourt Tee Kids        IA    2000             .00
And 2 Crosscourt Tee Kids        IA    2001             .00
And 2 Crosscourt Tee Kids        IA    2002             .00
And 2 Crosscourt Tee Kids        IA    2003             .00
And 2 Crosscourt Tee Kids        IA    2004             .00
And 2 Crosscourt Tee Kids        IA    2005             .00
And 2 Crosscourt Tee Kids        IL    1998          826.00
And 2 Crosscourt Tee Kids        IL    1999        1,187.90
And 2 Crosscourt Tee Kids        IL    2000        2,619.40
And 2 Crosscourt Tee Kids        IL    2001             .00
And 2 Crosscourt Tee Kids        IL    2002             .00
And 2 Crosscourt Tee Kids        IL    2003             .00
And 2 Crosscourt Tee Kids        IL    2004             .00
And 2 Crosscourt Tee Kids        IL    2005             .00
And 2 Crosscourt Tee Kids        IN    1998          266.00
And 2 Crosscourt Tee Kids        IN    1999             .00
And 2 Crosscourt Tee Kids        IN    2000        2,086.00
And 2 Crosscourt Tee Kids        IN    2001             .00
And 2 Crosscourt Tee Kids        IN    2002             .00
And 2 Crosscourt Tee Kids        IN    2003             .00
And 2 Crosscourt Tee Kids        IN    2004             .00
And 2 Crosscourt Tee Kids        IN    2005             .00
And 2 Crosscourt Tee Kids        MI    1998             .00
And 2 Crosscourt Tee Kids        MI    1999           53.20
And 2 Crosscourt Tee Kids        MI    2000        1,209.60
And 2 Crosscourt Tee Kids        MI    2001             .00
And 2 Crosscourt Tee Kids        MI    2002             .00
And 2 Crosscourt Tee Kids        MI    2003             .00
And 2 Crosscourt Tee Kids        MI    2004             .00
And 2 Crosscourt Tee Kids        MI    2005             .00
And 2 Crosscourt Tee Kids        OH    1998             .00
And 2 Crosscourt Tee Kids        OH    1999             .00
And 2 Crosscourt Tee Kids        OH    2000             .00
And 2 Crosscourt Tee Kids        OH    2001             .00
And 2 Crosscourt Tee Kids        OH    2002             .00
And 2 Crosscourt Tee Kids        OH    2003             .00
And 2 Crosscourt Tee Kids        OH    2004             .00
And 2 Crosscourt Tee Kids        OH    2005             .00
And 2 Crosscourt Tee Kids        WI    1998          686.00
And 2 Crosscourt Tee Kids        WI    1999          504.00
And 2 Crosscourt Tee Kids        WI    2000          378.00
And 2 Crosscourt Tee Kids        WI    2001             .00
And 2 Crosscourt Tee Kids        WI    2002             .00
And 2 Crosscourt Tee Kids        WI    2003             .00
And 2 Crosscourt Tee Kids        WI    2004             .00
And 2 Crosscourt Tee Kids        WI    2005             .00
Coin Pocket Twill Cargo Trousers IA    1998             .00
Coin Pocket Twill Cargo Trousers IA    1999             .00
Coin Pocket Twill Cargo Trousers IA    2000             .00
Coin Pocket Twill Cargo Trousers IA    2001             .00
Coin Pocket Twill Cargo Trousers IA    2002             .00
Coin Pocket Twill Cargo Trousers IA    2003             .00
Coin Pocket Twill Cargo Trousers IA    2004             .00
Coin Pocket Twill Cargo Trousers IA    2005             .00
Coin Pocket Twill Cargo Trousers IL    1998             .00
Coin Pocket Twill Cargo Trousers IL    1999             .00
Coin Pocket Twill Cargo Trousers IL    2000             .00
Coin Pocket Twill Cargo Trousers IL    2001             .00
Coin Pocket Twill Cargo Trousers IL    2002             .00
Coin Pocket Twill Cargo Trousers IL    2003             .00
Coin Pocket Twill Cargo Trousers IL    2004             .00
Coin Pocket Twill Cargo Trousers IL    2005             .00
Coin Pocket Twill Cargo Trousers IN    1998             .00
Coin Pocket Twill Cargo Trousers IN    1999             .00
Coin Pocket Twill Cargo Trousers IN    2000             .00
Coin Pocket Twill Cargo Trousers IN    2001             .00
Coin Pocket Twill Cargo Trousers IN    2002             .00
Coin Pocket Twill Cargo Trousers IN    2003             .00
Coin Pocket Twill Cargo Trousers IN    2004             .00
Coin Pocket Twill Cargo Trousers IN    2005             .00
Coin Pocket Twill Cargo Trousers MI    1998             .00
Coin Pocket Twill Cargo Trousers MI    1999          234.00
Coin Pocket Twill Cargo Trousers MI    2000           78.00
Coin Pocket Twill Cargo Trousers MI    2001             .00
Coin Pocket Twill Cargo Trousers MI    2002             .00
Coin Pocket Twill Cargo Trousers MI    2003             .00
Coin Pocket Twill Cargo Trousers MI    2004             .00
Coin Pocket Twill Cargo Trousers MI    2005             .00
Coin Pocket Twill Cargo Trousers OH    1998             .00
Coin Pocket Twill Cargo Trousers OH    1999             .00
Coin Pocket Twill Cargo Trousers OH    2000             .00
Coin Pocket Twill Cargo Trousers OH    2001             .00
Coin Pocket Twill Cargo Trousers OH    2002             .00
Coin Pocket Twill Cargo Trousers OH    2003             .00
Coin Pocket Twill Cargo Trousers OH    2004             .00
Coin Pocket Twill Cargo Trousers OH    2005             .00
Coin Pocket Twill Cargo Trousers WI    1998             .00
Coin Pocket Twill Cargo Trousers WI    1999          722.15
Coin Pocket Twill Cargo Trousers WI    2000          390.00
Coin Pocket Twill Cargo Trousers WI    2001             .00
Coin Pocket Twill Cargo Trousers WI    2002             .00
Coin Pocket Twill Cargo Trousers WI    2003             .00
Coin Pocket Twill Cargo Trousers WI    2004             .00
Coin Pocket Twill Cargo Trousers WI    2005             .00
Gurfield& Murks Pleated Trousers IA    1998             .00
Gurfield& Murks Pleated Trousers IA    1999             .00
Gurfield& Murks Pleated Trousers IA    2000             .00
Gurfield& Murks Pleated Trousers IA    2001             .00
Gurfield& Murks Pleated Trousers IA    2002             .00
Gurfield& Murks Pleated Trousers IA    2003             .00
Gurfield& Murks Pleated Trousers IA    2004             .00
Gurfield& Murks Pleated Trousers IA    2005             .00
Gurfield& Murks Pleated Trousers IL    1998        9,100.00
Gurfield& Murks Pleated Trousers IL    1999       13,825.00
Gurfield& Murks Pleated Trousers IL    2000        2,100.00
Gurfield& Murks Pleated Trousers IL    2001             .00
Gurfield& Murks Pleated Trousers IL    2002             .00
Gurfield& Murks Pleated Trousers IL    2003             .00
Gurfield& Murks Pleated Trousers IL    2004             .00
Gurfield& Murks Pleated Trousers IL    2005             .00
Gurfield& Murks Pleated Trousers IN    1998             .00
Gurfield& Murks Pleated Trousers IN    1999        7,525.00
Gurfield& Murks Pleated Trousers IN    2000             .00
Gurfield& Murks Pleated Trousers IN    2001             .00
Gurfield& Murks Pleated Trousers IN    2002             .00
Gurfield& Murks Pleated Trousers IN    2003             .00
Gurfield& Murks Pleated Trousers IN    2004             .00
Gurfield& Murks Pleated Trousers IN    2005             .00
Gurfield& Murks Pleated Trousers MI    1998       26,040.00
Gurfield& Murks Pleated Trousers MI    1999       12,600.00
Gurfield& Murks Pleated Trousers MI    2000       19,425.00
Gurfield& Murks Pleated Trousers MI    2001             .00
Gurfield& Murks Pleated Trousers MI    2002             .00
Gurfield& Murks Pleated Trousers MI    2003             .00
Gurfield& Murks Pleated Trousers MI    2004             .00
Gurfield& Murks Pleated Trousers MI    2005             .00
Gurfield& Murks Pleated Trousers OH    1998             .00
Gurfield& Murks Pleated Trousers OH    1999             .00
Gurfield& Murks Pleated Trousers OH    2000             .00
Gurfield& Murks Pleated Trousers OH    2001             .00
Gurfield& Murks Pleated Trousers OH    2002             .00
Gurfield& Murks Pleated Trousers OH    2003             .00
Gurfield& Murks Pleated Trousers OH    2004             .00
Gurfield& Murks Pleated Trousers OH    2005             .00
Gurfield& Murks Pleated Trousers WI    1998          175.00
Gurfield& Murks Pleated Trousers WI    1999             .00
Gurfield& Murks Pleated Trousers WI    2000             .00
Gurfield& Murks Pleated Trousers WI    2001             .00
Gurfield& Murks Pleated Trousers WI    2002             .00
Gurfield& Murks Pleated Trousers WI    2003             .00
Gurfield& Murks Pleated Trousers WI    2004             .00
Gurfield& Murks Pleated Trousers WI    2005             .00
Kahala Pleated Chino Short       IA    1998             .00
Kahala Pleated Chino Short       IA    1999             .00
Kahala Pleated Chino Short       IA    2000             .00
Kahala Pleated Chino Short       IA    2001             .00
Kahala Pleated Chino Short       IA    2002             .00
Kahala Pleated Chino Short       IA    2003             .00
Kahala Pleated Chino Short       IA    2004             .00
Kahala Pleated Chino Short       IA    2005             .00
Kahala Pleated Chino Short       IL    1998          504.00
Kahala Pleated Chino Short       IL    1999        3,738.00
Kahala Pleated Chino Short       IL    2000             .00
Kahala Pleated Chino Short       IL    2001             .00
Kahala Pleated Chino Short       IL    2002             .00
Kahala Pleated Chino Short       IL    2003             .00
Kahala Pleated Chino Short       IL    2004             .00
Kahala Pleated Chino Short       IL    2005             .00
Kahala Pleated Chino Short       IN    1998             .00
Kahala Pleated Chino Short       IN    1999             .00
Kahala Pleated Chino Short       IN    2000             .00
Kahala Pleated Chino Short       IN    2001             .00
Kahala Pleated Chino Short       IN    2002             .00
Kahala Pleated Chino Short       IN    2003             .00
Kahala Pleated Chino Short       IN    2004             .00
Kahala Pleated Chino Short       IN    2005             .00
Kahala Pleated Chino Short       MI    1998             .00
Kahala Pleated Chino Short       MI    1999             .00
Kahala Pleated Chino Short       MI    2000             .00
Kahala Pleated Chino Short       MI    2001             .00
Kahala Pleated Chino Short       MI    2002             .00
Kahala Pleated Chino Short       MI    2003             .00
Kahala Pleated Chino Short       MI    2004             .00
Kahala Pleated Chino Short       MI    2005             .00
Kahala Pleated Chino Short       OH    1998             .00
Kahala Pleated Chino Short       OH    1999             .00
Kahala Pleated Chino Short       OH    2000             .00
Kahala Pleated Chino Short       OH    2001             .00
Kahala Pleated Chino Short       OH    2002             .00
Kahala Pleated Chino Short       OH    2003             .00
Kahala Pleated Chino Short       OH    2004             .00
Kahala Pleated Chino Short       OH    2005             .00
Kahala Pleated Chino Short       WI    1998             .00
Kahala Pleated Chino Short       WI    1999             .00
Kahala Pleated Chino Short       WI    2000             .00
Kahala Pleated Chino Short       WI    2001             .00
Kahala Pleated Chino Short       WI    2002             .00
Kahala Pleated Chino Short       WI    2003             .00
Kahala Pleated Chino Short       WI    2004             .00
Kahala Pleated Chino Short       WI    2005             .00

192 rows selected.
palm_civet
关注
专栏目录
sql语句--分析函数
weixin_50383598的博客
06-05 312
Oracle 数据库中内置了非常多的的分析函数，可以用来进行复杂的计算
StructuredStreaming: A Declarative API for Real-Time
weixin_38440581的博客
05-10 1667
AbstractWith the ubiquity of real-time data,organizations need streaming systems that are scalable, easy to use, and easyto integrate into business applications. Structured Streaming is a newhigh-leve...
oracle分析函数结构,ORACLE分析函数实战(Analytical Functions)_PART3
weixin_33277412的博客
04-04 276
接PART2：http://blog.chinaunix.net/uid-7655508-id-5834764.html1.2.2分析函数语法结构要使用分析函数，首先必须了解分析函数的语法结构，基本语法结构如下图所示：从上图可以看出，分析函数是由函数名，参数，over关键字，over后面的括号内指定分析子句，分析子句可能由partition子句，order by子句，window子句按顺序排列组成...
HIVE学习四：Window And Analytical Function
patrick_big_data的博客
03-12 639
文章目录标准sql里的窗口函数和分析函数 标准sql里的窗口函数和分析函数
SQL Analytic Function
weixin_34233618的博客
01-16 118
1) SQL analytic function 1@@@@ basic example @@@ @@@<1> over() for each record, or for each group @@@ SCOTT@ocm> !cat tmp.sql set echo off set feedback off set verify off ...
SQL Analytic Functions 分析函数
weixin_34198762的博客
12-24 128
应用场景 主要使用在需要分组计算的场景中，根据所需的计算值可以分为两类： 1，排序类：如排序号，相邻记录等 2，聚合类：如平均值，累加求和，最大值，最末值等 语法 分析函数的语法在各大数据库中基本类似，以基于PostgreSQL的Netezza为例： Func( value_expression) OVER ( [<partition_by_clause>] [<...
ORACLE SQL*Plus:An Introduction and Tutorial
11-03 1851
Contents1. Introduction 2. Oracle Products: An Overview 2.1 Application Development Tools 2.2 Database Utilities 2.3 Connectivity Products 2.4 Core Database Engine 3. SQL*Plus
Elasticsearch：Multi-match (multi_match) 及 Disjunction max 查询
Elastic 中国社区官方博客
02-08 1955
多重匹配（）查询，顾名思义就是跨多个字段搜索查询。例如，如果我们想在 title、synopsis 和 tags 三个字段中搜索 Java 一词，那么 multi_match query 就是答案。另外，很多开发者还不是很清楚 multi-match 及的区别和联系。在今天的文章中，我将详述这两个查询。
LLMs：《PaLM: Scaling Language Modeling with Pathways》翻译与解读
近期请国内外头部出版社可尽快私信博主！——心比天高，仗剑走天涯，保持热爱，奔赴向梦想！低调，谦虚，自律，反思，成长，还算是比较正能量的博主，公益免费传播……内心特别想在AI界做出一些可以推进历史进程影响力的东西(兴趣使然，有点小情怀，也有点使命感呀)…
06-27 5063
​ LLMs：《PaLM: Scaling Language Modeling with Pathways》翻译与解读 目录 《PaLM: Scaling Language Modeling with Pathways》翻译与解读 Abstract 1、Introduction介绍 2、Model Architecture模型架构 3、Training Dataset训练数据集 4、Training Infrastructure训练基础设施 5、Trai
MATLAB Genetic Algorithm vs. Conventional Methods: A Deep Comparative Study Revealing Advantages
Introduction to Genetic Algorithms and MATLAB Overview Genetic algorithms are heuristic search algorithms that simulate the processes of natural selection and genetics. As a global optimizatio
A Pareto-Efficient Algorithm for Multiple Objective Optimization in E-Commerce Recommendation阅读翻译
u010505915的博客
02-27 3736
ABSTRACT Recommendation with multiple objectives is an important but difficult problem, where the coherent difficulty lies in the possible conflicts between objectives. In this case, multi-objective o...
The right to contest automated decisions under the General Data Protection Regulation: Beyond the so
weixin_42786150的博客
02-28 2483
The right to contest automated decisions under the General Data Protection Regulation: Beyond the so-called “right to explanation”
【文献阅读】ES as a Scalable Alternative to RL（OpenAI 17）
wxmcp3的博客
09-23 1680
Evolution Strategies as a Scalable Alternative to Reinforcement Learning brief 文章链接 该文章是 Open AI 17年发布的，目前有300+的引用量。 Abstract 【开篇明意】We explore the use of Evolution Strategies (ES), a class of black box optimization algorithms, as an alternative to popular
Debezium-MySqlConnectorTask
SlothLu的博客
11-18 310
MySqlConnectorTask用于设置和启动MySQL连接器，确保能够正确地读取和处理MySQL的二进制日志。
【DBA攻坚指南：左右Oracle，右手MySQL-学习总结】
qianxunsiyue的博客
11-13 458
从用户提交会话开始，LGWR进程将redo缓存中的信息写入redo日志文件后，LGWR进程通知用户写操作完成，到用户会话接受到LGWR进程通知为止，这整个过程就是可能出现log file sync等待事件的地方。在LGWR写入日志过程中，还包括了控制文件修改操作，对于RAC节点，可能还存在节点之间的scn同步、节点之间cr块的同步。
如何准备 PostgreSQL 中级认证考试？
cqsizhuang的博客
11-14 304
准备PostgreSQL中级认证考试（PGCE）需要系统的学习和实践。
问：SQL优化，七条实践总结？
技术铺掌柜
11-14 1256
SQL语句优化是提升数据库性能的关键，包括优化Where子句顺序、用EXISTS替代IN、避免在索引列上计算和判断NULL值、建立索引、避免在where子句中对字段进行null值判断和表达式操作等。通过综合应用这些优化方法，如使用JOIN代替子查询、日期范围查询代替函数计算、建立相关索引等，可以显著提升查询性能，特别是在处理大数据时。
MySQL数据库：SQL语言入门 【3】（学习笔记）
2301_81819439的博客
11-14 1240
TCL事务控制语言：事务概念，事务并发问题，事务的隔离等级，jdbc对事务的操作 ，视图，存储过程
MySQL深度剖析-索引原理由浅入深
最新发布
qq_46248151的博客
11-18 762
官方上面说索引是帮助MySQL高效获取数据的数据结构，通俗点的说，数，可以直接根据页码找到对应的内容，目的就是为了加快数据库的查询速度。索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
IT英语词汇2000全收录：从A到Z详解
该书按照字母顺序排列，从"A"到"Z"，每个字母下的词汇都附带了清晰的音标和详尽的解释，便于学习者理解和记忆。书中涵盖了大量的专业术语，包括但不限于： 1. "AAAIMS" (An Analytical Information Management ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值