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

Откуда: Курск
Сообщений: 254
Доброго времени суток, уважаемые.
Может кто сталкивался с подобной проблемой. Суть ее состоит в следующем.
Имеется некий запрос, который возвращает следующий результат:
IN_DATE  ADATE
-------- --------
06.09.04 06.09.04
07.09.04
08.09.04
09.09.04
10.09.04
11.09.04
12.09.04
13.09.04
14.09.04
15.09.04
16.09.04
В столбце ADATE значения возникают очень редко. Как можно исхитриться, чтобы после того, как значение столбца ADATE появилось (или изменилось), каждое последующее значение было не null, а последнее значение (относительно текущей записи) ADATE? Проблема возникает не впервые, и до сих пор более-менее внятного решения найти не удалось. Need help!
21 мар 06, 16:26    [2472572]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54382
dilomakin
Доброго времени суток, уважаемые.
Может кто сталкивался с подобной проблемой. Суть ее состоит в следующем.
Имеется некий запрос, который возвращает следующий результат:
IN_DATE  ADATE
-------- --------
06.09.04 06.09.04
07.09.04
08.09.04
09.09.04
10.09.04
11.09.04
12.09.04
13.09.04
14.09.04
15.09.04
16.09.04
В столбце ADATE значения возникают очень редко. Как можно исхитриться, чтобы после того, как значение столбца ADATE появилось (или изменилось), каждое последующее значение было не null, а последнее значение (относительно текущей записи) ADATE? Проблема возникает не впервые, и до сих пор более-менее внятного решения найти не удалось. Need help!
может так
SELECT id1, id2,
	   NVL(id2, MAX(id2) OVER(ORDER BY id1)) id2_new
FROM(
SELECT 1 id1, 1 id2 FROM dual UNION
SELECT 2 id1, 0+NULL id2 FROM dual UNION
SELECT 3 id1, 0+NULL id2 FROM dual UNION
SELECT 4 id1, 2 id2 FROM dual UNION
SELECT 5 id1, 0+NULL id2 FROM dual
)
ORDER BY id1
21 мар 06, 16:38    [2472633]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
dilomakin
Member

Откуда: Курск
Сообщений: 254
Не, аналитическй max не катит. Он просто берет максимальное значение даты (просто список дат для второго столбца уже сформирован) и ставит. А мне надо именно в порядке появления. Первый столбец - эталонный. Я пробую lag/lead с динамическим смещением. Но проблема вычислить это самое смещение.
21 мар 06, 16:47    [2472677]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
Tolmachov Dmitiry
Member

Откуда: Москва, Пенза
Сообщений: 1520
select in_date,
       MAX(adate) OVER(ORDER BY in_date, adate) + ROW_NUMBER() OVER(ORDER BY in_date, adate)
  from (SELECT sysdate as in_date, to_date('06.09.2004') as adate
          FROM dual
        UNION
        SELECT sysdate + LEVEL, NULL
          FROM DUAL
        CONNECT BY LEVEL < 5
        UNION
        SELECT sysdate + 5 as in_date, to_date('10.10.2004') as adate
          FROM dual
        UNION
        SELECT sysdate + 5 + LEVEL, NULL FROM DUAL CONNECT BY LEVEL < 5)
21 мар 06, 18:30    [2473299]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
Elic
Member

Откуда:
Сообщений: 29980
dilomakin
Не, аналитическй max не катит. Он просто берет максимальное значение даты (просто список дат для второго столбца уже сформирован) и ставит. А мне надо именно в порядке появления.
Если при возрастании IN_DATE также возрастает и ADATE, то вариант andreymx-а само то. BTW, nvl там лишний.
21 мар 06, 18:31    [2473305]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
WITH S AS (SELECT 1 ID1, NULL ID2 FROM DUAL UNION ALL
           SELECT 2 ID1, 5 ID2 FROM DUAL UNION ALL
           SELECT 3 ID1, NULL ID2 FROM DUAL UNION ALL
           SELECT 4 ID1, NULL ID2 FROM DUAL UNION ALL
           SELECT 5 ID1, 2 ID2 FROM DUAL UNION ALL
           SELECT 6 ID1, NULL ID2 FROM DUAL UNION ALL
           SELECT 7 ID1, 9 ID2 FROM DUAL UNION ALL
           SELECT 8 ID1, 8 ID2 FROM DUAL UNION ALL
           SELECT 9 ID1, NULL ID2 FROM DUAL
)
SELECT ID1, ID2,FIRST_VALUE(ID2) OVER (PARTITION BY ID1_RANG)--, ID1_RANG
FROM (
  SELECT ID2, ID1, MAX(NVL2(ID2, ID1, 0)) OVER(PARTITION BY NULL ORDER BY ID1) ID1_RANG  
  FROM S)
ORDER BY ID1
21 мар 06, 18:33    [2473311]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
Tolmachov Dmitiry
Member

Откуда: Москва, Пенза
Сообщений: 1520
Мой DML работает только для частного случая :(
21 мар 06, 18:36    [2473323]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
только так, конечно, логичнее ;)
Jannny
SELECT ID2, ID1, MAX(NVL2(ID2, ID1, NULL)) OVER

не 0, а null
21 мар 06, 18:37    [2473329]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54382
вспомнил, кто-то где-то когда-то показывал следующий вариант группировки:
SUM(DECODE(id2, NULL, 0, 1)) OVER(ORDER BY id1) ovr
SELECT id1,
       id2,
       first_value(id2) OVER(PARTITION BY ovr ORDER BY id1) fv 
FROM
(
    SELECT id1,
           id2,
           SUM(DECODE(id2, NULL, 0, 1)) OVER(ORDER BY id1) ovr
    FROM
    (
    SELECT  1 id1, 2      id2 FROM dual UNION
    SELECT  2 id1, 0+NULL id2 FROM dual UNION
    SELECT  3 id1, 0+NULL id2 FROM dual UNION
    SELECT  5 id1, 0      id2 FROM dual UNION
    SELECT  6 id1, 0+NULL id2 FROM dual UNION
    SELECT  7 id1, 0+NULL id2 FROM dual UNION
    SELECT  8 id1, 3      id2 FROM dual UNION
    SELECT  9 id1, 0+NULL id2 FROM dual UNION
    SELECT 10 id1, 0+NULL id2 FROM dual UNION
    SELECT 11 id1, 1      id2 FROM dual UNION
    SELECT 12 id1, 0+NULL id2 FROM dual UNION
    SELECT 13 id1, 0+NULL id2 FROM dual
    )
)
ORDER BY id1
тут можно работать с id1/id2 разных типов
22 мар 06, 08:40    [2474437]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
dilomakin
Member

Откуда: Курск
Сообщений: 254
andreymx
вспомнил, кто-то где-то когда-то показывал следующий вариант группировки:
SUM(DECODE(id2, NULL, 0, 1)) OVER(ORDER BY id1) ovr
SELECT id1,
       id2,
       first_value(id2) OVER(PARTITION BY ovr ORDER BY id1) fv 
FROM
(
    SELECT id1,
           id2,
           SUM(DECODE(id2, NULL, 0, 1)) OVER(ORDER BY id1) ovr
    FROM
    (
    SELECT  1 id1, 2      id2 FROM dual UNION
    SELECT  2 id1, 0+NULL id2 FROM dual UNION
    SELECT  3 id1, 0+NULL id2 FROM dual UNION
    SELECT  5 id1, 0      id2 FROM dual UNION
    SELECT  6 id1, 0+NULL id2 FROM dual UNION
    SELECT  7 id1, 0+NULL id2 FROM dual UNION
    SELECT  8 id1, 3      id2 FROM dual UNION
    SELECT  9 id1, 0+NULL id2 FROM dual UNION
    SELECT 10 id1, 0+NULL id2 FROM dual UNION
    SELECT 11 id1, 1      id2 FROM dual UNION
    SELECT 12 id1, 0+NULL id2 FROM dual UNION
    SELECT 13 id1, 0+NULL id2 FROM dual
    )
)
ORDER BY id1
тут можно работать с id1/id2 разных типов


Все сложилось. Премного благодарен человеку по имени andreymx, как раз то что нужно для моих целей.
22 мар 06, 09:03    [2474516]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
Jannny
FIRST_VALUE(ID2) OVER (PARTITION BY ID1_RANG)
...
  MAX(NVL2(ID2, ID1, 0)) OVER(PARTITION BY NULL ORDER BY ID1) ID1_RANG  

andreymx
       first_value(id2) OVER(PARTITION BY ovr ORDER BY id1) fv 
...
           SUM(DECODE(id2, NULL, 0, 1)) OVER(ORDER BY id1) ovr

Называется - найдите отличия в подходе ;)
22 мар 06, 09:53    [2474733]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
dilomakin
Member

Откуда: Курск
Сообщений: 254
Jannny, тебе я тоже благодарен. Ты совершенно прав. Просто я зашел на форум только сегодня утром и, как обычно, стал просматривать посты с конца. Решение andreymx подошло идеально, так что все остальное я просмотрел мельком. Сорри...
22 мар 06, 10:06    [2474801]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54382
Jannny
Jannny
FIRST_VALUE(ID2) OVER (PARTITION BY ID1_RANG)
...
  MAX(NVL2(ID2, ID1, 0)) OVER(PARTITION BY NULL ORDER BY ID1) ID1_RANG  

andreymx
       first_value(id2) OVER(PARTITION BY ovr ORDER BY id1) fv 
...
           SUM(DECODE(id2, NULL, 0, 1)) OVER(ORDER BY id1) ovr

Называется - найдите отличия в подходе ;)
например, для случая id1 <= 0
А вообще-то я не гений, всем спасибо за подсказки в моих ошибках
22 мар 06, 10:18    [2474886]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
andreymx
например, для случая id1 <= 0

на самом деле на этой случай я поправилась сразу ;)
Jannny
только так, конечно, логичнее ;)
Jannny
SELECT ID2, ID1, MAX(NVL2(ID2, ID1, NULL)) OVER

не 0, а null
22 мар 06, 10:22    [2474919]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54382
Jannny
andreymx
например, для случая id1 <= 0

на самом деле на этой случай я поправилась сразу ;)
Jannny
только так, конечно, логичнее ;)
Jannny
SELECT ID2, ID1, MAX(NVL2(ID2, ID1, NULL)) OVER

не 0, а null
согласен, я не умнее тебя
22 мар 06, 10:26    [2474941]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54382
кстати, а если более широкий случай - сортировка не по одному полю id1, а по нескольким?
22 мар 06, 10:30    [2474964]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
andreymx
кстати, а если более широкий случай - сортировка не по одному полю id1, а по нескольким?

Ну тогда, конечно, вариант с суммой.

andreymx
согласен, я не умнее тебя

;( Все, что мне было интересно - это только то, что именно в том, что написано мной, не подошло под решение задачи.
22 мар 06, 10:51    [2475119]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
Колобок
Member

Откуда:
Сообщений: 122
SELECT id1,
       id2,
       MOD(MAX(id1*10000+id2) OVER(ORDER BY id1),10000) ovr
FROM (SELECT  1 id1, 2      id2 FROM dual UNION
      SELECT  2 id1, 0+NULL id2 FROM dual UNION
      SELECT  3 id1, 0+NULL id2 FROM dual UNION
      SELECT  5 id1, 0      id2 FROM dual UNION
      SELECT  6 id1, 0+NULL id2 FROM dual UNION
      SELECT  7 id1, 0+NULL id2 FROM dual UNION
      SELECT  8 id1, 3      id2 FROM dual UNION
      SELECT  9 id1, 0+NULL id2 FROM dual UNION
      SELECT 10 id1, 0+NULL id2 FROM dual UNION
      SELECT 11 id1, 1      id2 FROM dual UNION
      SELECT 12 id1, 0+NULL id2 FROM dual UNION
      SELECT 13 id1, 0+NULL id2 FROM dual)
22 мар 06, 11:20    [2475308]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
Колобок
SELECT id1,
       id2,
       MOD(MAX(id1*10000+id2) OVER(ORDER BY id1),10000) ovr
FROM (SELECT  1 id1, 2      id2 FROM dual UNION
      SELECT  2 id1, 0+NULL id2 FROM dual UNION
      SELECT  3 id1, 0+NULL id2 FROM dual UNION
      SELECT  5 id1, 0      id2 FROM dual UNION
      SELECT  6 id1, 0+NULL id2 FROM dual UNION
      SELECT  7 id1, 0+NULL id2 FROM dual UNION
      SELECT  8 id1, 3      id2 FROM dual UNION
      SELECT  9 id1, 0+NULL id2 FROM dual UNION
      SELECT 10 id1, 0+NULL id2 FROM dual UNION
      SELECT 11 id1, 1      id2 FROM dual UNION
      SELECT 12 id1, 0+NULL id2 FROM dual UNION
      SELECT 13 id1, 0+NULL id2 FROM dual)

Прикольно, но подгонка ;) Если задать id2 более 9999, то все съедет ;)
22 мар 06, 11:26    [2475354]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение промежутков ближайшими значениями  [new]
Колобок
Member

Откуда:
Сообщений: 122
Jannny
Прикольно, но подгонка ;) Если задать id2 более 9999, то все съедет ;)

Тогда меняем 10000 на 100000 :)
И при минусах тоже лажа будет.

На самом деле я преследовал цель не сделать идеально на все случаи жизни, а только высказать идею.
В исходном вопросе вообще даты, с ними как раз проще в отношении допустимых значений.
22 мар 06, 11:42    [2475454]     Ответить | Цитировать Сообщить модератору
 В 10g можно проще сделать  [new]
Sergei.Agalakov
Member

Откуда:
Сообщений: 575
select id1,
       id2,
       LAST_VALUE(id2 IGNORE NULLS) OVER ( ORDER BY id1 ) fv
  from
    (
      SELECT  1 id1, 2      id2 FROM dual UNION ALL
      SELECT  2 id1, 0+NULL id2 FROM dual UNION ALL
      SELECT  3 id1, 0+NULL id2 FROM dual UNION ALL
      SELECT  5 id1, 0      id2 FROM dual UNION ALL
      SELECT  6 id1, 0+NULL id2 FROM dual UNION ALL
      SELECT  7 id1, 0+NULL id2 FROM dual UNION ALL
      SELECT  8 id1, 3      id2 FROM dual UNION ALL
      SELECT  9 id1, 0+NULL id2 FROM dual UNION ALL
      SELECT 10 id1, 0+NULL id2 FROM dual UNION ALL
      SELECT 11 id1, 1      id2 FROM dual UNION ALL
      SELECT 12 id1, 0+NULL id2 FROM dual UNION ALL
      SELECT 13 id1, 0+NULL id2 FROM dual
    )
ORDER BY id1
23 мар 06, 02:17    [2478932]     Ответить | Цитировать Сообщить модератору
 Re: В 10g можно проще сделать  [new]
Elic
Member

Откуда:
Сообщений: 29980
Sergei.Agalakov
       LAST_VALUE(id2 IGNORE NULLS)
10-ка здесь распространена не так, как ты думаешь :) Не забывай указывать версиию для версионно-зависимых решений.
23 мар 06, 08:53    [2479273]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить