tricky Oracle analytic function question

Question

Hi all,

I'm new to Oracle analytic functions and I'm trying to find the best way to write my query.

The following is a simplified version of a table I'm working with...

CREATE TABLE my_table
(
    pid          NUMBER  NOT NULL,
    my_value     NUMBER,
    value_date   DATE    NOT NULL,

    CONSTRAINT pk_my_table PRIMARY KEY (pid, value_date)
);

Note that "value_date" is part of the primary key.

For each "pid", I'd like the query to display 5 columns:
1. pid
2. the max value of "my_value" for the most recent 7 days of data
3. the corresponding day (value_date) for the 7 day max
4. the max value of "my_value" for the most recent 30 days of data
5. the corresponding day (value_date) for the 30 day max

Is it possible to do this without a join? What is the best way to accomplish this?

Thanks in advance for any help,

Kal

Answer 1

I think this is correct solution for you. I have just typed it in browser and haven't tested. I think this should help you... At least tried to give you an idea ...

SELECT table_7_day_info.pid
       ,table_7_day_info.max_val_7_day
       ,table_7_day_info.max_day_7_day
       ,table_30_day_info.max_val_30_day
       ,table_30_day_info.max_day_30_day
FROM
    (
       SELECT t1.pid, t1.my_value max_val_7_day, t1.value_date max_day_7_day
       FROM my_table t1
       WHERE (t1.pid,t.my_value) in (
                                     SELECT t.pid, max(t.my_value)
                                     FROM my_table t
                                     WHERE t.value_date >= sysdate - 7
                                     GROUP BY t.pid
                                   )
     ) table_7_day_info,
    (
       SELECT t2.pid, t2.my_value max_val_30_day, t2.value_date max_day_30_day
       FROM my_table t2
       WHERE (t2.pid,t2.my_value) in (
                                     SELECT t3.pid, max(t3.my_value)
                                     FROM my_table t3
                                     WHERE t3.value_date >= sysdate - 30
                                     GROUP BY t3.pid
                                   )
     ) table_30_day_info,
WHERE table_7_day_info.pid = table_7_day_info.pid

Answer 2

You can get information in one pass within the same time interval. I.e. information for the most recent 7 days, and information for the most recent 30 days. But to get these data together, you'll need a join:

select nvl(week.pid,mon.pid)
     , week.week_val
     , week.week_date
     , mon.month_val
     , mon.month_date
  from (
         select pid
              , max(my_value) month_val
              , max(value_date) keep (dense_rank last order by my_value) month_date
           from my_table
          where value_date >= sysdate - 30
          group by pid
       ) mon
       left outer join
       (
         select pid
              , max(my_value) week_val
              , max(value_date) keep (dense_rank last order by my_value) week_date
           from my_table
          where value_date >= sysdate - 7
          group by pid
       ) week
       on (mon.pid = week.pid)
;

Here I used left outer join because it's possible that you have no data within a week but have some data within a month.

Answer 3

I wasn't able to get the answer I was looking for here so I posted the question on the Oracle forums. The answer I got was exactly what I was looking for. The following query gets the answer with only one pass through the table and without any joins...

  SELECT   pid,
           MAX(CASE
                  WHEN value_date > TRUNC (SYSDATE) - 6 THEN my_value
                  ELSE 0
               END)
              max_7_days,
           MAX(value_date)
              KEEP (DENSE_RANK FIRST ORDER BY
                                        (CASE
                                            WHEN value_date >
                                                    TRUNC (SYSDATE) - 6
                                            THEN
                                               my_value
                                            ELSE
                                               0
                                         END) DESC)
              day_7_days,
           MAX(CASE
                  WHEN value_date > TRUNC (SYSDATE) - 29 THEN my_value
                  ELSE 0
               END)
              max_30_days,
           MAX(value_date)
              KEEP (DENSE_RANK FIRST ORDER BY
                                        CASE
                                           WHEN value_date >
                                                   TRUNC (SYSDATE) - 29
                                           THEN
                                              my_value
                                           ELSE
                                              0
                                        END DESC)
              day_30_days
    FROM   my_table
GROUP BY   pid;

Incase anyone would like to test it, here is some sample data.

INSERT INTO my_table (pid, my_value, value_date) VALUES (1,    300, '18-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,    200, '17-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,   4500, '16-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,    800, '15-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  12000, '14-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  21000, '13-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,   5600, '12-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  18400, '11-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,   7200, '10-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  24000, '09-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,   8800, '08-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,   6000, '07-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  44200, '06-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,   2800, '05-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  58500, '04-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,   8000, '03-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  30600, '02-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,   7200, '01-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  62700, '31-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  48000, '30-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  16800, '29-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  83600, '28-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  18400, '27-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  16800, '26-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  40000, '25-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  23400, '24-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  99900, '23-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  67200, '22-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,   5800, '21-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  57000, '20-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  96100, '19-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  64000, '18-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  52800, '17-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  71400, '16-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  14000, '15-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,   7200, '14-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1, 111000, '13-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  64600, '12-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1, 113100, '11-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (1,  84000, '10-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,   6000, '18-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,   7800, '17-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,  13500, '16-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,  25600, '15-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2, 276000, '14-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2, 210000, '13-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2, 162400, '12-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,  36800, '11-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,  86400, '10-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2, 480000, '09-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,   8800, '08-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2, 102000, '07-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,1237600, '06-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,  61600, '05-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,1287000, '04-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2, 144000, '03-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2, 275400, '02-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,  93600, '01-FEB-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,1630200, '31-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,1248000, '30-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2, 235200, '29-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,3176800, '28-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,  73600, '27-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2, 403200, '26-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2, 200000, '25-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2, 280800, '24-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2, 599400, '23-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,1612800, '22-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2, 110200, '21-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,1653000, '20-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,2498600, '19-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,2368000, '18-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2, 211200, '17-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,2570400, '16-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2, 224000, '15-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2, 223200, '14-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2, 999000, '13-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,2325600, '12-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,1017900, '11-JAN-10');
INSERT INTO my_table (pid, my_value, value_date) VALUES (2,2352000, '10-JAN-10');

Thank you all for your efforts in answering this question!

Kal