Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 использование аналитической функции-> некорректный рез-т запроса - почему?  [new]
Beretta
Member

Откуда: Киев
Сообщений: 319
Версия Oracle 8.1.7.4.0.

Привожу здесь упрощенный вариант проблемы. Есть некая таблица, содержащая сведения о действиях посетителей сайта, где
app_id - код приложения, которое выбирает посетитель
date_id - дата действия в формате YYYYMDD (тип NUMBER)
time_stamp - таймстэмп действия
activity - вид действия

create table test_cli
(app_id  number,
 date_id number,
 time_stamp date,
 activity number)
/
INSERT INTO test_cli (APP_ID,DATE_ID,TIME_STAMP,ACTIVITY)
VALUES ('413812',20060128,to_date('1/28/2006 02:26:14','MM/DD/RRRR HH24:MI:SS'), 1)
/
INSERT INTO test_cli (APP_ID,DATE_ID,TIME_STAMP,ACTIVITY)
VALUES ('413812',20060128,to_date('1/28/2006 12:26:14','MM/DD/RRRR HH24:MI:SS'), 4)
/
INSERT INTO test_cli (APP_ID,DATE_ID,TIME_STAMP,ACTIVITY)
VALUES ('413812',20060129,to_date('1/29/2006 22:40:23','MM/DD/RRRR HH24:MI:SS'), 10)
/
INSERT INTO test_cli (APP_ID,DATE_ID,TIME_STAMP,ACTIVITY)
VALUES ('8272234',20060129,to_date('1/29/2006 20:24:22','MM/DD/RRRR HH24:MI:SS'), 1)
/
INSERT INTO test_cli (APP_ID,DATE_ID,TIME_STAMP,ACTIVITY)
VALUES ('8272234',20060129,to_date('1/29/2006 21:31:13','MM/DD/RRRR HH24:MI:SS'), 5)
/
alter session set nls_date_format= "MM.DD.YYYY HH24:MI:SS";
SELECT *  FROM   test_cli;

    APP_ID    DATE_ID TIME_STAMP            ACTIVITY                            
---------- ---------- ------------------- ----------                            
    413812   20060128 01.28.2006 02:26:14          1                            
    413812   20060128 01.28.2006 12:26:14          4                            
    413812   20060129 01.29.2006 22:40:23         10                            
   8272234   20060129 01.29.2006 20:24:22          1                            
   8272234   20060129 01.29.2006 21:31:13          5   


Необходимо для каждого app_id, по которому имеется какая-то деятельность на дату 20060129, показать два последних действия, т.е. самое последнее и предпоследнее. Предпоследнее могло иметь место как в тот же день, так и год назад.
Т.е. необходимо получить такой результат:

    APP_ID LAST_TIME_STAMP     LAST_ACTIVITY PREV_TIME_STAMP     PREV_ACTIVITY  
---------- ------------------- ------------- ------------------- -------------  
    413812 01.29.2006 22:40:23            10 01.28.2006 12:26:14             4  
   8272234 01.29.2006 21:31:13             5 01.29.2006 20:24:22             1

Выбираю для каждого app_id два последних по времени действия:
SELECT app_id,
       rn,
       activity,
       time_stamp
FROM   (SELECT app_id,
               ROW_NUMBER () OVER (PARTITION BY app_id ORDER BY time_stamp DESC) rn,
               activity,
               time_stamp
        FROM   test_cli
        WHERE  date_id <= 20060129
        AND    app_id IN (SELECT app_id
                          FROM   test_cli
                          WHERE  date_id = 20060129))
WHERE  rn < 3;

    APP_ID         RN   ACTIVITY TIME_STAMP                                     
---------- ---------- ---------- -------------------                            
    413812          1         10 01.29.2006 22:40:23                            
    413812          2          4 01.28.2006 12:26:14                            
   8272234          1          5 01.29.2006 21:31:13                            
   8272234          2          1 01.29.2006 20:24:22 

Выполняю pivot. Получаю для каждого app_id последнее действие (last_time_stamp и last_activity) и предпоследнее действие (prev_time_stamp и prev_activity)

SELECT   
         app_id,
         MAX (DECODE (rn, 1, time_stamp)) last_time_stamp,
         MAX (DECODE (rn, 1, activity)) last_activity,
         MAX (DECODE (rn, 2, time_stamp)) prev_time_stamp,
         MAX (DECODE (rn, 2, activity)) prev_activity
FROM     (SELECT app_id,
                 ROW_NUMBER () OVER (PARTITION BY app_id ORDER BY time_stamp DESC) rn,
                 activity,
                 time_stamp
          FROM   test_cli
          WHERE  date_id <= 20060129
          AND    app_id IN (SELECT app_id
                            FROM   test_cli
                            WHERE  date_id = 20060129))
WHERE    rn < 3
GROUP BY app_id;
    APP_ID LAST_TIME_STAMP     LAST_ACTIVITY PREV_TIME_STAMP     PREV_ACTIVITY  
---------- ------------------- ------------- ------------------- -------------  
    413812 01.29.2006 22:40:23            10 01.28.2006 12:26:14             4  
   8272234 01.29.2006 21:31:13             5 01.29.2006 21:31:13             5
Для второй строки результат неправильный!
Для первой строки, где предыдущее действие имело место день назад, результат правильный.

Меняю запрос: заменяю MAX на SUM для вычисления last_activity. Такая замена вполне корректна, поскольку в любом случае SUM будет складывать два значения, одно из которых - NULL , а второе - timestamp последнего действия.
SELECT   
         app_id,
         MAX (DECODE (rn, 1, time_stamp)) last_time_stamp,
         SUM (DECODE (rn, 1, activity)) last_activity,
         MAX (DECODE (rn, 2, time_stamp)) prev_time_stamp,
         MAX (DECODE (rn, 2, activity)) prev_activity
FROM     (SELECT app_id,
                 ROW_NUMBER () OVER (PARTITION BY app_id ORDER BY time_stamp DESC) rn,
                 activity,
                 time_stamp
          FROM   test_cli
          WHERE  date_id <= 20060129
          AND    app_id IN (SELECT app_id
                            FROM   test_cli
                            WHERE  date_id = 20060129))
WHERE    rn < 3
GROUP BY app_id;
    APP_ID LAST_TIME_STAMP     LAST_ACTIVITY PREV_TIME_STAMP     PREV_ACTIVITY  
---------- ------------------- ------------- ------------------- -------------  
    413812 01.29.2006 22:40:23            10 01.28.2006 12:26:14             4  
   8272234 01.29.2006 21:31:13             5 01.29.2006 20:24:22             1 

Теперь все ОК!

Если заменить аналитическую функцию на rownum, первоначальный запрос отработывает относительно корректно. В том смысле, что он корректно делает pivot по rn.

Т.е. такую неразбериху вроде как создает сочетание нескольких MAX и аналитической ROW_NUMBER и только для вычислений в пределах одного дня.
Вариант с подставленным SUM вместо MAX теперь не дает мне уверенности, что такой запрос всегда будет отрабатывать стабильно.

На 10-ке эта проблема не воспроизводится.

Как возможно объяснить такое изменчивое поведение Оракла?
8 май 06, 14:42    [2642949]     Ответить | Цитировать Сообщить модератору
 Re: использование аналитической функции-> некорректный рез-т запроса - почему?  [new]
Q u a d r o
Member

Откуда: Canada
Сообщений: 1987
Восьмёрки под рукой нет, но IMHO запрос проще написать без pivot

SQL> select app_id, time_stamp last_time_stamp, activity last_activity, prev_time_stamp, prev_activi
ty
  2  from (
  3  select c.*,
  4   lag(time_stamp) over (partition by app_id order by time_stamp) prev_time_stamp,
  5   lag(activity) over (partition by app_id order by time_stamp) prev_activity,
  6   row_number() over (partition by app_id order by time_stamp desc) rnum
  7  from test_cli c where date_id<=20060129
  8  ) where rnum=1 and date_id=20060129;

    APP_ID LAST_TIME_STAMP     LAST_ACTIVITY PREV_TIME_STAMP     PREV_ACTIVITY
---------- ------------------- ------------- ------------------- -------------
    413812 01.29.2006 22:40:23            10 01.28.2006 12:26:14             4
   8272234 01.29.2006 21:31:13             5 01.29.2006 20:24:22             1
8 май 06, 15:32    [2643033]     Ответить | Цитировать Сообщить модератору
 Re: использование аналитической функции-> некорректный рез-т запроса - почему?  [new]
Beretta
Member

Откуда: Киев
Сообщений: 319
Это да, но на самом деле там намного больше метрик нужно вытягивать. Поэтому я решила попробовать без множества lag-ов и натолкнулась на такую вот "фичу".
8 май 06, 15:42    [2643046]     Ответить | Цитировать Сообщить модератору
 Re: использование аналитической функции-> некорректный рез-т запроса - почему?  [new]
Q u a d r o
Member

Откуда: Canada
Сообщений: 1987
Один lag заменяет один MAX (DECODE (rn, .... ) ), так что паритет :-)
8 май 06, 15:47    [2643057]     Ответить | Цитировать Сообщить модератору
 Re: использование аналитической функции-> некорректный рез-т запроса - почему?  [new]
Beretta
Member

Откуда: Киев
Сообщений: 319
Только MAX(DECODE..) в данном случае отрабатывает на куда меньшем множестве - 2 строки.
Нет, я согласна, что вариант с лагами может быть быстрее. Просто меня интересует, почему же так меняются результаты запроса.
8 май 06, 15:58    [2643077]     Ответить | Цитировать Сообщить модератору
 Re: использование аналитической функции-> некорректный рез-т запроса - почему?  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116217
Восьмерки к сожалению уже нет, на девятке все нормально...
Что сказать, поведение явно ненормально.
Все рекомендации по исправлению запроса носит характер "танцев с бубном",
как например замена MAX на SUM.
В том же духе может попробовать выкинуть

WHERE    rn < 3

Оно же функционально вроде не нужно. Может он здесь дуреет.
8 май 06, 16:06    [2643101]     Ответить | Цитировать Сообщить модератору
 Re: использование аналитической функции-> некорректный рез-т запроса - почему?  [new]
Beretta
Member

Откуда: Киев
Сообщений: 319
Отож... действительно танцы с бубном.. скорее всего, я заменю на вариант с лагами, но сам вопрос остается открытым.

"Дуреть" может из-за nested view (SELECT app_id
FROM test_cli
WHERE date_id = 20060129)

Когда я убираю или ROW_NUMBER или этот nested view, запрос возвращает нормальные результаты.
8 май 06, 16:12    [2643119]     Ответить | Цитировать Сообщить модератору
 Re: использование аналитической функции-> некорректный рез-т запроса - почему?  [new]
Q u a d r o
Member

Откуда: Canada
Сообщений: 1987
max(decode(...)) отрабатывают на меньшем множестве, потому что есть group by (вы не получаете эти две строчки бесплатно - вы делаете работу).

У вас уже есть row_number() который отрабатывает на том же окне, что и lag. Cпецификация разбиения и сортировки окна у всех функций одинакова, все они смогут использовать одно окно.
8 май 06, 16:16    [2643136]     Ответить | Цитировать Сообщить модератору
 Re: использование аналитической функции-> некорректный рез-т запроса - почему?  [new]
Q u a d r o
Member

Откуда: Canada
Сообщений: 1987
пардон,

все lag будут отрабатывать на одном окне

row_number() нужна сортировка в обратном порядке
8 май 06, 16:24    [2643154]     Ответить | Цитировать Сообщить модератору
 Re: использование аналитической функции-> некорректный рез-т запроса - почему?  [new]
Beretta
Member

Откуда: Киев
Сообщений: 319
to Quadro: Да ничего страшного, я заметила. Спасибо:-), запрос я таки переделала, правда с использованием lead. Просто была каша в голове по поводу того, как окна конструируются. Посмотрела план выполнения - действительно окно конструируется только один раз и используется для всех функций запроса.

SELECT *
FROM   (SELECT app_name,
               app_id,
               date_id,
               ROW_NUMBER () OVER 
                     (PARTITION BY app_name, app_id ORDER BY time_stamp DESC) rn,
               activity,
               LEAD (activity) OVER 
                     (PARTITION BY app_name, app_id ORDER BY time_stamp DESC) prev_activity,
               time_stamp,
               LEAD (time_stamp) OVER
                     (PARTITION BY app_name, app_id ORDER BY time_stamp DESC) prev_time_stamp,
               step_name,
               LEAD (step_name) OVER 
                     (PARTITION BY app_name, app_id ORDER BY time_stamp DESC) prev_step_name,
               step_number,
               LEAD (step_number) OVER 
                     (PARTITION BY app_name, app_id ORDER BY time_stamp DESC) prev_step_number
        FROM   humana_app_avg
        WHERE  client_id = 90083415
        AND    date_id <= 20060129)
WHERE  rn = 1
AND    date_id = 20060129

Не оставляю надежды, что существуют те, кто с такой фишкой сталкивался и нашел этому объяснение.
8 май 06, 16:43    [2643195]     Ответить | Цитировать Сообщить модератору
 Re: использование аналитической функции-> некорректный рез-т запроса - почему?  [new]
Q u a d r o
Member

Откуда: Canada
Сообщений: 1987
да, lag в "прямой" сортировке будет равен lead в "обратной", т.е. мы можем использовать одно окно вместе с row_number.
8 май 06, 17:26    [2643282]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить