6 Replies Latest reply: Jul 11, 2013 2:43 PM by 949210 RSS

    range windows in analytics

    949210

      CREATE TABLE employee (
      employee_id NUMBER,
      first_name VARCHAR2(30),
      last_name VARCHAR2(30),
      hire_date DATE,
      salary NUMBER(9,2),
      manager NUMBER,
      department_id NUMBER
      );

      CREATE TABLE department (
      department_id NUMBER,
      name VARCHAR2(30),
      location VARCHAR2(30)
      );

      CREATE SEQUENCE employee_seq;

      CREATE SEQUENCE department_seq;

      REM These sequences will be used eventually. For now, in order for your output to match that shown in the series articles'' examples,
      REM please insert literal number values into the ID columns of the tables as shown below.

      REM 8) Insert records into the employee table.

      INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
        VALUES (28, 'Emily', 'Eckhardt', to_date('07-JUL-2004', 'DD-MON-YYYY'), 100000, NULL, 10);

      INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
        VALUES (37, 'Frances', 'Newton', to_date('14-SEP-2005', 'DD-MON-YYYY'), 75000, NULL, NULL);

      INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
        VALUES (1234, 'Donald', 'Newton', to_date('24-SEP-2006', 'DD-MON-YYYY'), 80000, 28, 10);

      INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
        VALUES (7895, 'Matthew', 'Michaels', to_date('16-MAY-2007', 'DD-MON-YYYY'), 70000, 28, 10);

      INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
        VALUES (6567, 'Roger', 'Friedli', to_date('16-MAY-2007', 'DD-MON-YYYY'), 60000, 28, 10);

      INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
        VALUES (6568, 'Betsy', 'James', to_date('16-MAY-2007', 'DD-MON-YYYY'), 60000, 28, 10);

      INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
        VALUES (6569, 'michael', 'peterson', to_date('03-NOV-2008', 'DD-MON-YYYY'), 90000, NULL, 20);

      INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
        VALUES (6570, 'mark', 'leblanc', to_date('06-MAR-2009', 'DD-MON-YYYY'), 65000, 6569, 20);

      INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
        VALUES (6571, 'Thomas', 'Jeffrey', to_date('27-FEB-2010', 'DD-MON-YYYY'), 300000, null, 30);

      INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
        VALUES (6572, 'Theresa', 'Wong', to_date('27-FEB-2010 9:02:45', 'DD-MON-YYYY HH24:MI:SS'), 70000, 6571, 30);

      INSERT INTO employee (employee_id, first_name, last_name, hire_date, salary, manager, department_id)
        VALUES (6573, 'Lori', 'Dovichi', to_date('07-JUL-2011 8:31:57', 'DD-MON-YYYY HH24:MI:SS'), null, 28, 10);

      REM 9) Insert records into the department table.


      INSERT INTO department (department_id, name, location)
        VALUES (10, 'Accounting', 'LOS ANGELES');

      INSERT INTO department (department_id, name, location)
        VALUES (20, 'Payroll', 'NEW YORK');

      INSERT INTO department (department_id, name, location)
        VALUES (30, 'IT', 'WASHINGTON DC');

      REM 10) Save your newly created records to the database.

      Commit;
      
      

       

       

      i  am referring the below url

      SQL 101: A Window into the World of Analytic Functions

       

      i am unable to understand the below code


      {code}

       

       

      select last_name, first_name, department_id, hire_date, salary,

      SUM (salary)

      OVER (PARTITION BY department_id ORDER BY hire_date

      RANGE 90 PRECEDING) department_total

      from employee

      order by department_id, hire_date;

       

      {code}

      yes there is an explanation provided for 'Also note that within Department 30, two employees'..........but i am missing something here since 5 -6 hours
      i have already read the two paragraphs beside the big"Next Steps" box many times(at least 15)

       

       

      kindly explain  the difference between 190000 in department_id 10 that shows as expected(3 times) and this department_id =20 which does not display the sum of  salaries just like department 10

       

       

       

       

      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

      PL/SQL Release 10.2.0.1.0 - Production

      "CORE 10.2.0.1.0 Production"

      TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

      NLSRTL Version 10.2.0.1.0 - Production

       

      thank you

        • 1. Re: range windows in analytics
          rp0428

          >

          kindly explain  the difference between 190000 in department_id 10 that shows as expected(3 times) and this department_id =20 which does not display the sum of  salaries just like department 10

          >

          The paragraph above the one you quoted already explains it:

          >

          RANGE 90 PRECEDING     

           

          Next Steps

           READ SQL 101, Parts 1–9

           

          READ more about
          relational database design and concepts
           Oracle Database Concepts 11g Release 2 (11.2)
           Oracle Database SQL Language Reference 11g Release 1 (11.1)
           Oracle Database Data Warehousing Guide 11g Release 2 (11.2)
           Oracle SQL Developer User’s Guide Release 3.1

           

           DOWNLOAD the sample script for this article

          This line means, “Provide a summary of the current row’s salary value together with the salary values of all previous rows whose HIRE_DATE value falls within 90 days preceding the HIRE_DATE value of the current row.” Note that within Department 10, only three rows have a DEPARTMENT_TOTAL value different from their SALARY value. The employees listed in these rows were all hired on the same date and therefore fall within the range of date values required for salary summation.

          >

          The windowing clause specifies '90' which means, as that quote above says, 'within 90 days preceding the HIRE_DATE value of the current row'.

           

          The two dept 20 hire dates are 03-NOV-08 and 06-MAR-09.

           

          So ask yourself: is 03-NOV-08 within 90 days of 06-MAR-09?

          • 2. Re: range windows in analytics
            949210

            i mean department_id=30 not department_id=20
            (sorry about the typing mistake)

            both the hiredates are 27 feb 2010

            i could not understand that

            both rows in department_id=30 do not show 370000

            why is that?

            apologize again for the typo

            • 3. Re: range windows in analytics
              949210

              someone please reply

              the output is

               

               

              100000
              80000
              190000
              190000
              190000
              
              90000
              65000
              300000
              370000
              75000
              

               

              why is it like that

              atleast what do i do to get a

              100000
              80000
              190000
              190000
              190000
              
              90000
              65000
              3700000
              3700000
              75000
              

              thanks

              • 4. Re: range windows in analytics
                Greg Spall

                That's because of the time stamp ... change your query to this - and you can "See" what Oracle is doing:

                 

                select employee_id, last_name, first_name, department_id, hire_date, salary,
                      SUM (salary)
                         OVER (PARTITION BY department_id ORDER BY (hire_date)
                            RANGE 90 PRECEDING) department_total,
                      FIRST_VALUE(employee_id)
                         OVER (PARTITION BY department_id ORDER BY (hire_date)
                            RANGE 90 PRECEDING) first_emp,
                      LAST_VALUE(employee_id)
                         OVER (PARTITION BY department_id ORDER BY (hire_date)
                            RANGE 90 PRECEDING) last_emp
                from employee
                order by department_id, hire_date;
                
                

                 

                Header 1

                EMPLOYEE_ID LAST_NAME                      FIRST_NAME                     DEPARTMENT_ID HIRE_DATE     SALARY DEPARTMENT_TOTAL  FIRST_EMP   LAST_EMP

                ----------- ------------------------------ ------------------------------ ------------- --------- ---------- ---------------- ---------- ----------

                         28 Eckhardt                       Emily                                     10 07-JUL-04     100000           100000         28         28

                       1234 Newton                         Donald                                    10 24-SEP-06      80000            80000       1234       1234

                       6567 Friedli                        Roger                                     10 16-MAY-07      60000           190000       6567       7895

                       6568 James                          Betsy                                     10 16-MAY-07      60000           190000       6567       7895

                       7895 Michaels                       Matthew                                   10 16-MAY-07      70000           190000       6567       7895

                       6573 Dovichi                        Lori                                      10 07-JUL-11                                   6573       6573

                       6569 peterson                       michael                                   20 03-NOV-08      90000            90000       6569       6569

                       6570 leblanc                        mark                                      20 06-MAR-09      65000            65000       6570       6570

                       6571 Jeffrey                        Thomas                                    30 27-FEB-10     300000           300000       6571       6571

                       6572 Wong                           Theresa                                   30 27-FEB-10      70000           370000       6571       6572

                         37 Newton                         Frances                                      14-SEP-05      75000            75000         37         37

                 

                11 rows selected.

                 

                trunc the timestamp off .. and it works as you want:

                 

                select employee_id, last_name, first_name, department_id, hire_date, salary,
                      SUM (salary)
                         OVER (PARTITION BY department_id ORDER BY trunc(hire_date)
                            RANGE 90 PRECEDING) department_total,
                      FIRST_VALUE(employee_id)
                         OVER (PARTITION BY department_id ORDER BY trunc(hire_date)
                            RANGE 90 PRECEDING) first_emp,
                      LAST_VALUE(employee_id)
                         OVER (PARTITION BY department_id ORDER BY trunc(hire_date)
                            RANGE 90 PRECEDING) last_emp
                from employee
                order by department_id, hire_date;
                
                

                 

                Header 1

                EMPLOYEE_ID LAST_NAME                      FIRST_NAME                     DEPARTMENT_ID HIRE_DATE     SALARY DEPARTMENT_TOTAL  FIRST_EMP   LAST_EMP

                ----------- ------------------------------ ------------------------------ ------------- --------- ---------- ---------------- ---------- ----------

                         28 Eckhardt                       Emily                                     10 07-JUL-04     100000           100000         28         28

                       1234 Newton                         Donald                                    10 24-SEP-06      80000            80000       1234       1234

                       6567 Friedli                        Roger                                     10 16-MAY-07      60000           190000       6567       7895

                       6568 James                          Betsy                                     10 16-MAY-07      60000           190000       6567       7895

                       7895 Michaels                       Matthew                                   10 16-MAY-07      70000           190000       6567       7895

                       6573 Dovichi                        Lori                                      10 07-JUL-11                                   6573       6573

                       6569 peterson                       michael                                   20 03-NOV-08      90000            90000       6569       6569

                       6570 leblanc                        mark                                      20 06-MAR-09      65000            65000       6570       6570

                       6571 Jeffrey                        Thomas                                    30 27-FEB-10     300000           300000       6571       6571

                       6572 Wong                           Theresa                                   30 27-FEB-10      70000           370000       6571       6572

                         37 Newton                         Frances                                      14-SEP-05      75000            75000         37         37

                 

                11 rows selected.

                • 5. Re: range windows in analytics
                  Etbin

                  try NOT TESTED ! No Database at hand

                   

                  select last_name, first_name, department_id, hire_date, salary,

                         sum (salary) over (partition by department_id order by trunc(hire_date) range 90 preceding) department_total

                    from employee

                  order by department_id, hire_date

                   

                  Regards

                   

                  Etbin

                   

                  Oops ! Should have checked before posting.

                   

                  Message was edited by: Etbin

                  • 6. Re: range windows in analytics
                    949210

                    well, i have an old

                    alter session set nls_date_format = 'DD:FMMONTH:YYYY HH24:MI';

                    select * from employee;

                    27:FEBRUARY:2010 0:0
                    27:FEBRUARY:2010 9:2

                    they are not the same.

                    this clears my doubt about the working of sum(aggregates) with analytics and windowing

                    thank you very much greg, sql 101.