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

Откуда: Москва > Melbourne
Сообщений: 3778
есть таблица:

IDI_POSITIONR_ACT_NAMER_COMPLETE_WITEMR_PERF_ACT_NAMER_PERFORMERS
1-1Подпись1Печатьuser1
1-2Печатьuser2
1-3Подписьuser3
1-4Доработкаuser4
1-5Подписьuser5


+ with
WITH t AS (SELECT 1 AS id,
                  -1 AS i_position,
                  'Подпись' AS r_act_name,
                  1 AS r_complete_witem,
                  'Печать' AS r_perf_act_name,
                  'user1' AS r_performers
             FROM DUAL
           UNION ALL
           SELECT 1 AS id,
                  -2 AS i_position,
                  NULL AS r_act_name,
                  NULL AS r_complete_witem,
                  'Печать' AS r_perf_act_name,
                  'user2' AS r_performers
             FROM DUAL
           UNION ALL
           SELECT 1 AS id,
                  -3 AS i_position,
                  NULL AS r_act_name,
                  NULL AS r_complete_witem,
                  'Подпись' AS r_perf_act_name,
                  'user3' AS r_performers
             FROM DUAL
           UNION ALL
           SELECT 1 AS id,
                  -4 AS i_position,
                  NULL AS r_act_name,
                  NULL AS r_complete_witem,
                  'Доработка' AS r_perf_act_name,
                  'user4' AS r_performers
             FROM DUAL
           UNION ALL
           SELECT 1 AS id,
                  -5 AS i_position,
                  NULL AS r_act_name,
                  NULL AS r_complete_witem,
                  'Подпись' AS r_perf_act_name,
                  'user5' AS r_performers
             FROM DUAL)
SELECT *
  FROM t


нужно выбрать второго (точнее r_complete_witem+1) r_performers по счету (i_position desc), такого, у которого r_perf_act_name = r_act_name из строки с i_position=-1, т.е. что-то в духе:

SELECT r_performers
  FROM (SELECT t2.r_performers,
               ROW_NUMBER () OVER (ORDER BY t2.i_position DESC) rn
          FROM t t1, t t2
         WHERE t1.id = t2.id AND t1.r_act_name = t2.r_perf_act_name)
 WHERE rn = (SELECT r_complete_witem + 1
               FROM t t3
              WHERE t3.r_act_name = 'Подпись')
Но у меня в id и r_act_name нужно значения из соединения протолкнуть, поэтому подзапрос не катит.
база 10.2.0.4
17 дек 10, 13:50    [9955623]     Ответить | Цитировать Сообщить модератору
 Re: как можно написать без подзапроса?  [new]
kinky cat
Member

Откуда: с дивана23
Сообщений: 1238
Андрей Панфилов,

автор
как можно написать без подзапроса?

это новая мода ? в чем соль ?
17 дек 10, 13:53    [9955664]     Ответить | Цитировать Сообщить модератору
 Re: как можно написать без подзапроса?  [new]
Андрей Панфилов
Member

Откуда: Москва > Melbourne
Сообщений: 3778
kinky cat
это новая мода ? в чем соль ?
к сожалению не мода, просто плохо SQL знаю, в реальности должно выглядить примерно так:

with act AS (SELECT 1 AS t_id, 'Подпись' AS t_act, 1 AS r FROM DUAL)
SELECT t_act,
       CASE
          WHEN r = 1
          THEN
             (SELECT r_performers
                FROM (SELECT t2.r_performers,
                             ROW_NUMBER ()
                                OVER (ORDER BY t2.i_position DESC)
                                rn
                        FROM t t1, t t2
                       WHERE     t1.id = t2.id
                             AND t1.r_act_name = t2.r_perf_act_name
                             AND t1.id = act.t_id
                             AND t1.r_act_name = act.t_act)
               WHERE rn =
                        (SELECT r_complete_witem + 1
                           FROM t t3
                          WHERE t3.r_act_name = act.t_act
                                AND t3.id = act.t_id))
       END
  FROM act
17 дек 10, 14:11    [9955864]     Ответить | Цитировать Сообщить модератору
 Re: как можно написать без подзапроса?  [new]
AmKad
Member

Откуда:
Сообщений: 5222
Андрей Панфилов,

Ниче не понятно, где условие i_position=-1 в запросе?
17 дек 10, 14:11    [9955869]     Ответить | Цитировать Сообщить модератору
 Re: как можно написать без подзапроса?  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
Андрей Панфилов,
SELECT
MAX(t2.r_performers) keep(dense_rank FIRST ORDER BY 
decode(t2.i_position, (SELECT r_complete_witem + 1 FROM t t3 WHERE t3.r_act_name = 'Подпись'), 1e10, t2.i_position))
r_performers
FROM t t1, t t2
WHERE t1.id = t2.id AND t1.r_act_name = t2.r_perf_act_name
Есть способ "протолкнуть": table+cast+multiset 9955448, но я такое в продакшене не использую. :)
17 дек 10, 14:15    [9955894]     Ответить | Цитировать Сообщить модератору
 Re: как можно написать без подзапроса?  [new]
Андрей Панфилов
Member

Откуда: Москва > Melbourne
Сообщений: 3778
AmKad,

протупил/ненаписал: база так устроена, что r_act_name непустое только там где i_position=-1
17 дек 10, 14:15    [9955904]     Ответить | Цитировать Сообщить модератору
 Re: как можно написать без подзапроса?  [new]
Андрей Панфилов
Member

Откуда: Москва > Melbourne
Сообщений: 3778
dbms_photoshop,

на таком наборе не работает :(

+
WITH t AS (SELECT 1 AS id,
                  -1 AS i_position,
                  'Подпись' AS r_act_name,
                  1 AS r_complete_witem,
                  'Печать' AS r_perf_act_name,
                  'user1' AS r_performers
             FROM DUAL
           UNION ALL
           SELECT 1 AS id,
                  -2 AS i_position,
                  NULL AS r_act_name,
                  NULL AS r_complete_witem,
                  'Печать' AS r_perf_act_name,
                  'user2' AS r_performers
             FROM DUAL
           UNION ALL
           SELECT 1 AS id,
                  -3 AS i_position,
                  NULL AS r_act_name,
                  NULL AS r_complete_witem,
                  'Подпись' AS r_perf_act_name,
                  'user3' AS r_performers
             FROM DUAL
           UNION ALL
           SELECT 1 AS id,
                  -4 AS i_position,
                  NULL AS r_act_name,
                  NULL AS r_complete_witem,
                  'Доработка' AS r_perf_act_name,
                  'user4' AS r_performers
             FROM DUAL
           UNION ALL
           SELECT 1 AS id,
                  -5 AS i_position,
                  NULL AS r_act_name,
                  NULL AS r_complete_witem,
                  'Подпись' AS r_perf_act_name,
                  'user5' AS r_performers
             FROM DUAL
           UNION ALL
           SELECT 1 AS id,
                  -6 AS i_position,
                  NULL AS r_act_name,
                  NULL AS r_complete_witem,
                  'Подпись' AS r_perf_act_name,
                  'user6' AS r_performers
             FROM DUAL
           UNION ALL
           SELECT 1 AS id,
                  -7 AS i_position,
                  NULL AS r_act_name,
                  NULL AS r_complete_witem,
                  'Подпись' AS r_perf_act_name,
                  'user7' AS r_performers
             FROM DUAL)
17 дек 10, 14:20    [9955960]     Ответить | Цитировать Сообщить модератору
 Re: как можно написать без подзапроса?  [new]
zhal
Member

Откуда: Киев-Харьков
Сообщений: 647
Андрей Панфилов
в id и r_act_name нужно значения из соединения протолкнуть, поэтому подзапрос не катит.
база 10.2.0.4
Разве нельзя вынести условия как-то так?
+
WITH t AS (SELECT 1 id, -1 i_position, 'Подпись' r_act_name, 1 r_complete_witem, 'Печать' r_perf_act_name, 'user1' r_performers FROM DUAL UNION ALL
           SELECT 1, -2, NULL, NULL, 'Печать', 'user2' FROM DUAL UNION ALL
           SELECT 1, -3, NULL, NULL, 'Подпись','user3' FROM DUAL UNION ALL
           SELECT 1, -4, NULL, NULL, 'Доработка', 'user4' FROM DUAL UNION ALL
           SELECT 1, -5, NULL, NULL, 'Подпись', 'user5' FROM DUAL UNION ALL
           SELECT 2, -1, 'Подпись', 1, 'Печать', 'user2' FROM DUAL UNION ALL
           SELECT 2, -2, NULL, NULL, 'Печать', 'user1' FROM DUAL UNION ALL
           SELECT 2, -3, NULL, NULL, 'Подпись','user4' FROM DUAL UNION ALL
           SELECT 2, -4, NULL, NULL, 'Доработка', 'user3' FROM DUAL UNION ALL
           SELECT 2, -5, NULL, NULL, 'Подпись', 'user6' FROM DUAL)
SELECT r_performers
  FROM (SELECT t2.id, t3.r_act_name, t2.r_performers, t3.r_complete_witem+1 need_rn
               ,ROW_NUMBER () OVER (PARTITION BY t2.ID, t3.r_act_name ORDER BY t2.i_position DESC) rn
          FROM t t1, t t2, t t3
         WHERE t1.id = t2.id AND t1.id=t3.ID 
           AND t1.r_act_name = t2.r_perf_act_name
           AND t3.i_position = -1)
 WHERE rn = need_rn AND ID = 1 AND r_act_name = 'Подпись'
17 дек 10, 14:25    [9956016]     Ответить | Цитировать Сообщить модератору
 Re: как можно написать без подзапроса?  [new]
zhal
Member

Откуда: Киев-Харьков
Сообщений: 647
с учетом
AND t1.r_act_name = act.t_act
тогда
SELECT r_performers
  FROM (SELECT t2.id, t1.r_act_name, t2.r_performers, t3.r_complete_witem+1 need_rn
               ,ROW_NUMBER () OVER (PARTITION BY t2.ID, t1.r_act_name ORDER BY t2.i_position DESC) rn
          FROM t t1, t t2, t t3
         WHERE t1.id = t2.id AND t1.id=t3.ID 
           AND t1.r_act_name = t2.r_perf_act_name
           AND t3.i_position = -1)
 WHERE rn = need_rn AND ID = 1 AND r_act_name = 'Подпись'
17 дек 10, 14:30    [9956100]     Ответить | Цитировать Сообщить модератору
 Re: как можно написать без подзапроса?  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
Андрей Панфилов
dbms_photoshop,

на таком наборе не работает :(
Да, то я протупил. Так здесь не вывернешься.
Перепиши запрос без скаляра через джойн. Я это сегодня уже в другом топике показывал.
zhal
Разве нельзя вынести условия как-то так?
Учитывая, что в таком случае предикаты не проталкиваются, с производительностью может быть все очень печально.
17 дек 10, 14:34    [9956149]     Ответить | Цитировать Сообщить модератору
 Re: как можно написать без подзапроса?  [new]
nikonian
Member

Откуда: город на Неве
Сообщений: 58
Так?
with
.....
select r_performers from
(
select t.id, t.i_position, nvl(t.r_act_name,t.r_perf_act_name),
max(t.r_complete_witem) over (partition by t.id, nvl(t.r_act_name,t.r_perf_act_name)) r_complete_witem,
max(t.r_act_name) over (partition by t.id) is_r_act,
t.r_performers, row_number() over (partition by t.id, nvl(t.r_act_name,t.r_perf_act_name) order by t.i_position desc) rn
from t
join (select 1 id, 'Печать' t_act from dual) v -- получение значений id и r_act_name
on v.id=t.id and v.t_act=nvl(r_act_name,r_perf_act_name)
) where r_complete_witem+1=rn and is_r_act is not null
17 дек 10, 16:23    [9957468]     Ответить | Цитировать Сообщить модератору
 Re: как можно написать без подзапроса?  [new]
Андрей Панфилов
Member

Откуда: Москва > Melbourne
Сообщений: 3778
dbms_photoshop
Андрей Панфилов
dbms_photoshop,

на таком наборе не работает :(
Да, то я протупил. Так здесь не вывернешься.
Перепиши запрос без скаляра через джойн. Я это сегодня уже в другом топике показывал.
zhal
Разве нельзя вынести условия как-то так?
Учитывая, что в таком случае предикаты не проталкиваются, с производительностью может быть все очень печально.


примерно так получилось то что нужно

with      act AS (SELECT 1 AS t_id, 'Подпись' AS t_act, 1 AS r FROM DUAL)
SELECT t_act,
       CASE
          WHEN r = 1
          THEN
             (SELECT r_performers
                FROM (SELECT t2.id,
                             t2.r_performers,
                             t2.r_perf_act_name,
                             ROW_NUMBER ()
                             OVER (
                                PARTITION BY t2.id, t2.r_perf_act_name
                                ORDER BY t2.i_position DESC)
                                rn
                        FROM t t2)
               WHERE     rn = t1.r_complete_witem + 1
                     AND id = t1.id
                     AND r_perf_act_name = act.t_act)
       END
  FROM act LEFT JOIN t t1 ON (t1.r_act_name = act.t_act AND t1.id = act.t_id)

спасибо всем.
17 дек 10, 16:33    [9957556]     Ответить | Цитировать Сообщить модератору
 Re: как можно написать без подзапроса?  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
Если не смущает то, что для каждой строки из act, при вычислении скаляра будет выполняться полное сканирование t и вычисление row_number - используй на здоровье.
В противном же случае не составит труда придумать маленький тест кейс который покажет что этот запрос будет работать в 100 раз дольше чем переписанный без скаляра.
17 дек 10, 16:50    [9957708]     Ответить | Цитировать Сообщить модератору
 Re: как можно написать без подзапроса?  [new]
Андрей Панфилов
Member

Откуда: Москва > Melbourne
Сообщений: 3778
dbms_photoshop,

я проверил, вроде в окно предикаты (id) протолкнулись
17 дек 10, 16:54    [9957740]     Ответить | Цитировать Сообщить модератору
 Re: как можно написать без подзапроса?  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
Андрей Панфилов
dbms_photoshop,

я проверил, вроде в окно предикаты (id) протолкнулись
А я не проверял, но утверждаю, что поскольку есть row_number - предикат ну никак не может протолкнуться.
Представления и слияние /проталкивание
17 дек 10, 17:09    [9957874]     Ответить | Цитировать Сообщить модератору
 Re: как можно написать без подзапроса?  [new]
Андрей Панфилов
Member

Откуда: Москва > Melbourne
Сообщений: 3778
dbms_photoshop,

автор
Я не смогу слить это представление и не смогу протолкнуть предикаты. Причина в этом случае заключается в номерах строк ROWNUM (обычно то же самое влияние оказывают и аналитические функции). Причина в том, что значение R изменилось бы, если бы я применил предикаты перед присваиванием R значения ROWNUM. Результат выполнения оператора SELECT * FROM (SELECT ..., ROWNUM R FROM T) WHERE <предикат> существенно отличается от результата выполнения оператора

если предикаты в partition by торчат то проталкивание на результат не влияет
17 дек 10, 17:23    [9957995]     Ответить | Цитировать Сообщить модератору
 Re: как можно написать без подзапроса?  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
Андрей Панфилов
если предикаты в partition by торчат то проталкивание на результат не влияет
Таки да. Прикольно. :)
17 дек 10, 17:30    [9958067]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить