Analytical Functions: A Brief Introduction

最新推荐文章于 2024-07-25 15:52:35 发布
最新推荐文章于 2024-07-25 15:52:35 发布
阅读量524 收藏
点赞数
分类专栏： 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
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
sql语句--分析函数
weixin_50383598的博客
06-05 287
Oracle 数据库中内置了非常多的的分析函数，可以用来进行复杂的计算
StructuredStreaming: A Declarative API for Real-Time
weixin_38440581的博客
05-10 1564
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 257
接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 305
文章目录标准sql里的窗口函数和分析函数 标准sql里的窗口函数和分析函数
SQL Analytic Function
weixin_34233618的博客
01-16 103
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 109
应用场景 主要使用在需要分组计算的场景中，根据所需的计算值可以分为两类： 1，排序类：如排序号，相邻记录等 2，聚合类：如平均值，累加求和，最大值，最末值等 语法 分析函数的语法在各大数据库中基本类似，以基于PostgreSQL的Netezza为例： Func( value_expression) OVER ( [<partition_by_clause>] [<...
ORACLE SQL*Plus:An Introduction and Tutorial
11-03 1774
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 1673
多重匹配（）查询，顾名思义就是跨多个字段搜索查询。例如，如果我们想在 title、synopsis 和 tags 三个字段中搜索 Java 一词，那么 multi_match query 就是答案。另外，很多开发者还不是很清楚 multi-match 及的区别和联系。在今天的文章中，我将详述这两个查询。
LLMs：《PaLM: Scaling Language Modeling with Pathways》翻译与解读
近期请国内外头部出版社可尽快私信博主！——心比天高，仗剑走天涯，保持热爱，奔赴向梦想！低调，谦虚，自律，反思，成长，还算是比较正能量的博主，公益免费传播……内心特别想在AI界做出一些可以推进历史进程影响力的东西(兴趣使然，有点小情怀，也有点使命感呀)…
06-27 4764
​ 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
Analytical Center:收集和分析数据以进行笔测试-开源
05-27
我们中的许多人在研究大量Web服务器时都面临着存储和处理信息的问题。 在进行这样的工作时，它会积累很​​多，同时，此信息需要经常补充和更改。 我们决定编写一个Web应用程序，该应用程序不仅可以存储和构造研究...
Communication Networking : An Analytical Approach
06-21
Communication Networking : An Analytical Approach
Data Analytics with Hadoop: An Introduction for Data Scientists
03-12
"Data Analytics with Hadoop: An Introduction for Data Scientists" ISBN: 1491913703 | 2016 | PDF | 288 pages | 7 MB Ready to use statistical and machine-learning techniques across large data sets? ...
Analytical_tool:社交网络分析工具
06-01
在"Analytical_tool-master"这个项目中，用户可以期待找到实现上述功能的相关代码和文档。通过阅读源代码，学习者可以深入了解如何在Java环境中构建这样的工具，同时也可以根据自己的需求进行定制和扩展。对于研究...
django-analytical：针对Django项目的分析服务
02-05
`django-analytical` 是一个针对 Django 框架的强大扩展，它使得集成各种网站分析服务变得简单易行。这个库的核心目标是帮助开发者轻松地在 Django 项目中收集和处理用户行为数据，以便更好地理解网站的流量、用户...
布尔盲注——多种方式实现及利用burpsuite爆破
m0_74312676的博客
07-25 833
当我们改变前端页面传输给后台sql参数时，页面没有显示相应内容也没有显示报错信息时，页面呈现出两种状态，正常或者不正常。根据这两种状态可以判断我们输入的语句是否查询成功。不能使用联合查询注入和报错注入，这时我们可以考虑是否为基于布尔的盲注。
oracle 宽表设计
最新发布
weixin_41203765的博客
07-25 460
宽表，从字面意义上讲就是字段（列）比较多的数据库表。它通常是指业务主题相关的指标、维度、属性关联在一起的一张数据库表。宽表的设计广泛应用于数据挖掘模型训练前的数据准备，通过将相关字段放在同一张表中，实现业务实体不同维度属性信息的统一存储。Oracle宽表设计通过增加数据库表或视图的字段数量，提升了分析查询的效率，减少了数据冗余，并便于分析建模和结果展示。然而，在设计宽表时，也需要注意列数限制、性能考虑、数据冗余与一致性以及兼容性等问题。
MySQL数据库
m0_73949379的博客
07-25 947
Structured Query Language的缩写，及结构化查询语言关系型数据库的标准语言用于维护管理数据库包括数据查询，数据更新，访问控制，对象管理等功能。
崖山异构数据库迁移利器YMP初体验-Oracle迁移YashanDB
cod0410的博客
07-25 990
崖山迁移平台（YashanDB Migration Platform，YMP）是YashanDB提供的数据库迁移产品，支持异构RDBMS与YashanDB之间进行迁移评估、离线迁移、数据校验的能力。YMP提供可视化服务，用户只需通过简单的界面操作，即可完成从评估到迁移整个流程的执行与监控，实现低门槛、低成本、高效率的异构数据库迁移。
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、付费专栏及课程。

余额充值