Версия для печати темы Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Составление SQL-запросов > ПОМОГИТЕ составить запрос

Автор: asdfghjkl 10.9.2009, 08:30

http://ipicture.ru/ Необходимо выбрать из т.2.1.1. id, moment (первый), status, если status неизменен и >0, причем надо дополнительно посчитать длительность этого промежутка времени в минутах и вывести в столбец duration. Думаю, что-то вроде вложенного запроса (может ошибаюсь), внешний выбирает, внутренний проверяет статус, подсчет длительности: (конечный момент- начальный момент)*60. Но как прописать добавку нового столбца, хотя бы набросок? Какой тип запроса использовать?

Автор: Gluttton 10.9.2009, 08:47

Так в чём конкретно проблема? Что именно не получается?

Автор: Akina 10.9.2009, 08:48

Gluttton, она сама ни хрена не знает. http://forum.sources.ru/index.php?showtopic=281619&view=findpost&p=2361774

Автор: asdfghjkl 10.9.2009, 08:55

Цитата(Akina @ 10.9.2009,  08:48) Gluttton, она сама ни хрена не знает. http://forum.sources.ru/index.php?showtopic=281619&view=findpost&p=2361774 Грубость здесь не уместна, не хочешь помочь не надо, может не все такие снобы как ты. если внимательно посмотришь суть запроса изложена. Добавлено через 4 минуты и 14 секунд Цитата(Gluttton @ 10.9.2009,  08:47) Так в чём конкретно проблема? Что именно не получается? не получается организовать проверку на статус, я так понимаю, что должен проходить по строкам  пока татус не изменился, но надо запоминать момент. и еще не сталкивалась с добавлением в выходные данные нового столбца. Написано возможно использование сложного, коррелированного или вложеного запроса, думаю коррелированный запрос не подходит, а вот насчет сложного или вложеного не знаю. Добавлено через 11 минут и 6 секунд сейчас читала про сложные запросы, написано, что если необходимо во вложенных запросах использовать одну и ту же таблицу, то запрос должен быть коррелированным.

Автор: Zloxa 10.9.2009, 09:43

Думаю в задании ошибка. На каком основании посчитано duration = 120 для id =2 moment=14:00 - не ясно Код SQL> with s as (   2     select 1 id, trunc(sysdate)+1/24 moment, 1 status from dual   3     union all select 1 id, trunc(sysdate)+2/24 moment, 1 status from dual   4     union all select 1 id, trunc(sysdate)+2/24+20/24/60 moment, 0 status from dual   5     union all select 1 id, trunc(sysdate)+3/24 moment, 1 status from dual   6     union all select 1 id, trunc(sysdate)+5/24 moment, 1 status from dual   7     union all select 1 id, trunc(sysdate)+7/24 moment, 2 status from dual   8     union all select 1 id, trunc(sysdate)+7.5/24 moment, 2 status from dual   9     union all select 2 id, trunc(sysdate)+11/24 moment, 1 status from dual  10     union all select 2 id, trunc(sysdate)+12/24 moment, 1 status from dual  11     union all select 2 id, trunc(sysdate)+12/24+10/24/60 moment, 0 status from dual  12     union all select 2 id, trunc(sysdate)+14/24 moment, 1 status from dual  13     union all select 2 id, trunc(sysdate)+15/24 moment, 1 status from dual  14  )  15  select id  16    , to_char(min(moment), 'HH24:MI') moment  17    , (max(moment)-min(moment))*24*60 duration  18    , status  19  from (  20    select  21      s.*  22      , sum(start_of_group) over (partition by id order by moment) grp_id  23    from (  24      select s.*  25             , decode(status,lag(status) over (partition by id order by moment),0,1) start_of_group  26      from s order by moment  27    )s  28  )s  29  where status != 0  30  group by grp_id,id,status  31  order by moment  32  ;           ID MOMENT   DURATION     STATUS ---------- ------ ---------- ----------          1 01:00          60          1          1 03:00         120          1          1 07:00          30          2          2 11:00          60          1          2 14:00          60          1

Автор: asdfghjkl 10.9.2009, 09:50

СПАСИБО ОГРОМНОЕ. А можно немного пояснений начиная с 16 строки, плиз? Думаю в последней строки опечатка, а может для проверки специально так задумано.

Автор: Zloxa 10.9.2009, 09:56

asdfghjkl, Этот запрос АФАИК сможет отработать только на Оракле. Возможно на PG. Какая у вас целевая платформа?

Автор: asdfghjkl 10.9.2009, 10:00

Это задание, вроде как организация использует Oracle, но я сама работала только с MySQL. возможно как-то переписать? я вот начала разбирать, но не все понятно.

Автор: Zloxa 10.9.2009, 10:03

Цитата(asdfghjkl @  10.9.2009,  10:00 ) возможно как-то переписать Как раз над этим сейчас и думаю. Если Gluttton меня опередит, будет ой как досадно

Автор: asdfghjkl 10.9.2009, 10:03

Что происходит в:  20    select 21      s.* 22      , sum(start_of_group) over (partition by id order by moment) grp_id 23    from ( 24      select s.* 25             , decode(status,lag(status) over (partition by id order by moment),0,1) start_of_group 26      from s order by moment 27    )s 28  )s ?

Автор: Akina 10.9.2009, 10:08

Цитата(asdfghjkl @  10.9.2009,  09:55 ) если внимательно посмотришь суть запроса изложена. Пока - нет. Если запрос на приведённых исходных данных даёт приведённый выход, это не значит, что логика получения ответа соответствует истинной. И на другом наборе входных данных может получиться хрень. Именно этого ты никак не можешь понять. Иными словами, модель чёрного ящика всегда приближённая.

Автор: Zloxa 10.9.2009, 10:12

Строка 25 сравнивает статус со следующей по моменту в разрезе id строчкой, в случае если совпадает, выставляет 0, иначе 1. Получается еденичка проставляется для первого статуса в группе подрядидущих. Строка 22 считает накапливающую сумму по посчитанному в строке 25 значению в разрезе id в пореядке возрастания момента. Получается расчитывается какбы уникальный идентификатор группы подрядидущих.

Автор: asdfghjkl 10.9.2009, 10:32

Цитата(Zloxa @ 10.9.2009,  10:12) Строка 25 сравнивает статус со следующей по моменту в разрезе id строчкой, в случае если совпадает, выставляет 0, иначе 1. Получается еденичка проставляется для первого статуса в группе подрядидущих. Строка 22 считает накапливающую сумму по посчитанному в строке 25 значению в разрезе id в пореядке возрастания момента. Получается расчитывается какбы уникальный идентификатор группы подрядидущих. А это одинаково для оракл и MySQL? откуда grp_id, я поняла, что это идентификатор повторяющих групп, почему он не указан в 25 строке? А для чего эта сумма?

Автор: Zloxa 10.9.2009, 10:35

Цитата(asdfghjkl @  10.9.2009,  10:32 ) А это одинаково для оракл и MySQL?  Я же сказал. Это только для оракл. Может быть Постгри так же умеет. Цитата(asdfghjkl @  10.9.2009,  10:32 ) почему он не указан в 25 строке?  Потому что на основании значения, полученного в 25й, он расчитывается Цитата(asdfghjkl @  10.9.2009,  10:32 ) А для чего эта сумма?  Думайте можете взорвать себе еще мозг моделью.. это вот точно ТОЛЬКО для оракла, прчем начиная только с 10й версии работает по тому же принципу Код  15  select id  16         ,to_char(min(moment),'HH24:MI') moment  17         ,(max(moment)-min(moment))*24*60 duration  18         , status  19  from (  20    select * from s  21    model  22      partition by (id)  23      dimension by (row_number() over (order by moment) rn)  24      measures (status,0 grpid,moment)  25      rules(  26        grpid[any] = nvl(grpid[cv()-1],0) + decode(status[cv()],status[cv()-1],0,1)  27      )  28  )s  29  where status > 0  30  group by id,grpid,status  31  order by moment  32  ;           ID MOMENT   DURATION     STATUS ---------- ------ ---------- ----------          1 01:00          60          1          1 03:00         120          1          1 07:00          30          2          2 11:00          60          1          2 14:00          60          1   Добавлено через 6 минут asdfghjkl, А может ну его? Если вы поакажете решение на собеседовании, но затруднитесь повторить на практике в оффисе, врядли вы пройдете испытательный срок. Может куда попроще попробовать сунутсья, где требования чуть мене высокие?

Автор: asdfghjkl 10.9.2009, 10:45

Не злитесь, просто мне это объяснять и я волнуюсь, это не просто задание нудного препода. Может я совсем безнадежна, но сдаваться не хочется. Насчет grp_id, я всегда думала, что если что-то вычисляется, то должно указываться куда это все поместить. А сумма- это количество групп, или количество записей в группе? Вроде должно быть записей, но если считается по grp_id...

Автор: Zloxa 10.9.2009, 10:56

Цитата(asdfghjkl @  10.9.2009,  10:45 ) Не злитесь, просто мне это объяснять и я волнуюсь, это не просто задание нудного препода. Вот и я ж о том. Уровень Ваших вопросов говорит о том, что Вами SQL начального уровня еще не освоен. Это же задание претендует елси не на высшую лигу, то минимум на выше среднего. Если Вы пытаетесь выдать себя за того, кем не являетесь, это очень похоже на обан.  Участвовать в этом обмане, как то не сильно хочется.

Автор: asdfghjkl 10.9.2009, 11:52

Цитата(Zloxa @ 10.9.2009,  10:35) asdfghjkl, А может ну его? Если вы поакажете решение на собеседовании, но затруднитесь повторить на практике в оффисе, врядли вы пройдете испытательный срок. Может куда попроще попробовать сунутсья, где требования чуть мене высокие? я не хочу слишком умное решение, хотя бы просто показать, что пробовала. Дело в том, что последний раз работала с БД месяцев 10 назад, навык утрачен, но хочу поднапрячся. А там у них свое дополнительное обучение будет, думаю справлюсь. Это кстати, часть задания, сложная. Что попроше я уже сделала, они одобрили, попросили, мои размышления по поводу вот этого.

Автор: Zloxa 10.9.2009, 12:27

На MySql должно проканать. Если пара id, duration не уникальна - будут завихрения(как и в первом запросе). Принцип - тот же. Код  15  select id  16      , to_char(min(moment), 'HH24:MI') moment  17      , (max(moment)-min(moment))*24*60 duration  18      , status  19  from (  20    select srcsg1.*  21           ,(select sum(start_of_group)  22               from (  23                  select srcrn1.*  24                       ,case when srcrn1.status = srcrn2.status  then 0 else 1 end start_of_group  25                  from (select s1.*,(select count(*) from src s2 where s1.id = s2.id and s2.moment < s1.moment) rn from src s1) srcrn1  26                  left join (select s1.*,(select count(*) from src s2 where s1.id = s2.id and s2.moment < s1.moment) rn from src s1) srcrn2  27                    on srcrn1.rn = srcrn2.rn+1  28               ) srcsg2  29               where srcsg2.id = srcsg1.id  30                  and srcsg2.rn <= srcsg1.rn  31            ) grpid  32    from (  select srcrn1.*  33                 ,case when srcrn1.status = srcrn2.status  then 0 else 1 end start_of_group  34            from (select s1.*,(select count(*) from src s2 where s1.id = s2.id and s2.moment < s1.moment) rn from src s1) srcrn1  35            left join (select s1.*,(select count(*) from src s2 where s1.id = s2.id and s2.moment < s1.moment) rn from src s1) srcrn2  36              on srcrn1.rn = srcrn2.rn+1  37              and srcrn1.id = srcrn2.id  38          ) srcsg1  39  )  40  where status >0  41  group by grpid,id,status  42  order by moment  43  ;           ID MOMENT   DURATION     STATUS ---------- ------ ---------- ----------          1 01:00          60          1          1 03:00         120          1          1 07:00          30          2          2 11:00          60          1          2 14:00          60          1 Добавлено через 14 минут и 42 секунды Цитата(Zloxa @  10.9.2009,  12:27 ) На MySql должно проканать. Перемудрил. Проще: Код  15  select id  16      , to_char(min(moment), 'HH24:MI') moment  17      , (max(moment)-min(moment))*24*60 duration  18      , status  19  from (  20    select srcrn1.*  21           ,(select sum(start_of_group)  22               from (  23                  select srcrn1.*  24                       ,case when srcrn1.status = srcrn2.status  then 0 else 1 end start_of_group  25                  from (select s1.*,(select count(*) from src s2 where s1.id = s2.id and s2.moment < s1.moment) rn from src s1) srcrn1  26                  left join (select s1.*,(select count(*) from src s2 where s1.id = s2.id and s2.moment < s1.moment) rn from src s1) srcrn2  27                    on srcrn1.rn = srcrn2.rn+1  28               ) srcsg2  29               where srcsg2.id = srcrn1.id  30                  and srcsg2.rn <= srcrn1.rn  31            ) grpid  32    from (select s1.*,(select count(*) from src s2 where s1.id = s2.id and s2.moment < s1.moment) rn from src s1) srcrn1  33  )  34  where status >0  35  group by grpid,id,status  36  order by moment  37  ;           ID MOMENT   DURATION     STATUS ---------- ------ ---------- ----------          1 01:00          60          1          1 03:00         120          1          1 07:00          30          2          2 11:00          60          1          2 14:00          60          1

Автор: asdfghjkl 10.9.2009, 12:55

Спасибочки, буду разбираться перед отправкой, кстати все намного понятней для моего восприятия, только один вопрос: start_of_group еще кое-где встречала, это имеет специальное значение или такое название?

Автор: Zloxa 10.9.2009, 13:03

Цитата(asdfghjkl @  10.9.2009,  12:55 ) это имеет специальное значение или такое название?  Нет. Это общепринятое название метода. Я даже знаю где Вы это встречали. Обычно так называют это поле для того, чтобы читающему было понятно о чем речь, и можно было легко найти по ключевому слову. На том ресурсе, где Вы встерчали этот термин, уже давно гнушаются отвечать на подобные вашему вопросы. Типичный ответ там на ваш вопрос - "STFF start_of_goup"

Автор: asdfghjkl 10.9.2009, 14:10

Правильно я поняла?: В строках 25, 26- строка таблицы соединяется сама с собой. В 24- проверка на неизменность статуса, группируются блоки.  Я наверное задам глупый вопрос, но все же: что происходит в 27, 29-32?

Автор: asdfghjkl 10.9.2009, 14:43

Пересмотрела, наверное :в 25-31 соединение двух последовательных строк по одному id с присвоением grpid. в 23- устанавливается 1 в первый отличный статус. Засмейте меня, но не пойму зачем сумма?

Автор: Zloxa 10.9.2009, 15:12

Цитата(asdfghjkl @  10.9.2009,  14:43 ) но не пойму зачем сумма?  Сумма нужна для того, чтобы сложить единички. Нам нужно выделить группы подряд идущих значений. Для того, чтобы можно делать группировку, нам необходимо чтобы строки в пределах одной группы имели одинаковое значение по полю группировки. Необходимо так же, чтобы это значение различалось у разных групп. Что мы можем сделать? Мы можем сравнить каждую строчку со следующей и найти начало( или конец) группы. Но как присвоить идентификатор всей группе? Пометить начало каждой группы единичкой и пустить накапливающую сумму по ней. В результате  получим такую сумму: Код порядок    значение         сумма 1              1              1 2                             1 3                             1 4              1              2 5              1              3 6                             3 7              1              4 Так как единички определяют начало группы, то сумма этих единичек обозначает порядковый номер группы Теперь по этому значению можно группироваться.

Автор: Zloxa 10.9.2009, 15:52

Еще один способ. Идея заимствована у http://forum.vingrad.ru/index.php?showtopic=268586&view=findpost&p=1937016. Должно работатать еще и на MS SQL. Достаточно легко переписывается на каунт. Оверхеда меньше чем в предыдущем варианте. При первом взгляде кажется что может дать не верный результат. При втором взгляде кажется что первый взгляд - кажется. Так что если ктото придумает на каких исходных данных результат окажется не корректным(кроме не уникальных id,moment), буду только рад. Код  15  select id  16         , to_char(min(moment), 'HH24:MI') moment  17         , (max(moment)-min(moment))*24*60 duration  18         , status  19  from (  20    select row_number() over (partition by id order by moment)  21            -row_number() over (partition by id,status order by moment) rnd  22           ,src.*  23    from  src  24  )  25  where status >0  26  group by rnd,id,status  27  order by moment  28  ;           ID MOMENT   DURATION     STATUS ---------- ------ ---------- ----------          1 01:00          60          1          1 03:00         120          1          1 07:00          30          2          2 11:00          60          1          2 14:00          60          1

Автор: asdfghjkl 10.9.2009, 15:56

Ну спасибо, а я дурак думала, что единички складываются в одну общую сумму по всей таблице, т.е. сумма будет показывать количество групп.

Автор: Simpliest 10.9.2009, 23:14

Угу, тред читается на одном дыхании А последний момент, просто кульминация. Цитата(asdfghjkl @  10.9.2009,  15:56 ) дурак думала Конспирология в действии. Это я к чему, да к тому, что знание SQL как езда на велосипеде. Ты можешь забыть нюансы, но не разучится ездить. P.S. не люблю я таких ТС :(

Автор: Gluttton 11.9.2009, 00:07

Цитата(Zloxa @  10.9.2009,  10:03 ) Как раз над этим сейчас и думаю. Если Gluttton меня опередит, будет ой как досадно   Жутко хотелось поучаствовать, но на работе сегодня был загруженый день... Да и когда за решение взялся не всё так просто оказалось  . Итак... Цитата(Zloxa @  10.9.2009,  09:43 ) Думаю в задании ошибка. На каком основании посчитано duration = 120 для id =2 moment=14:00 - не ясно Я думаю, что это задание не столько на "результат" сколько на "рассуждение" и возможно это умышленно внесенная ошибка, но если подходить к решению исключительно формально, то можно предположить, что для id=2, необходимо выводить не просто продолжительность, а сумму продолжительности с накоплением. О как  ... Начну со своего крокодила  ... Чистейший ANSI обязан пойти на любом сервере (все запросы выполнялись на Firebird 2.1). Код select     sf.id,     sf.moment,     case         when sf.id=1         then sf.duration         else         (             select sum(sk.duration)             from             (                 select                     se.id,                     se.moment,                     cast                     (                         (                             (                                 select sc.moment                                 from s as sc                                     where                                     sc.moment=                                     (                                         select max(si.moment)                                         from s as si                                             where si.moment>se.moment                                             and se.id=si.id                                             and se.status=si.status                                             and not exists                                             (                                                 select *                                                 from s as sj                                                     where sj.moment>se.moment                                                     and sj.moment<si.moment                                                     and sj.status<>se.status                                             )                                     )                             )-se.moment                         )/60 as integer                     ) as duration,                     se.status                 from                 (                     select                         s.id,                         s.moment,                         s.status                     from                         s                         where exists                         (                             select *                             from s as sa                                 where s.id=sa.id                                 and s.status=sa.status                                 and sa.moment=                                 (                                     select min(sb.moment)                                     from s as sb                                         where sb.moment>s.moment                                 )                         )                 )   as se             )   as sk                 where sk.id=2                 and sf.moment>=sk.moment         )     end as duration,     sf.status from (     select         se.id,         se.moment,         cast         (             (                 (                     select sc.moment                     from s as sc                         where                         sc.moment=                         (                             select max(si.moment)                             from s as si                                 where si.moment>se.moment                                 and se.id=si.id                                 and se.status=si.status                                 and not exists                                 (                                     select *                                     from s as sj                                         where sj.moment>se.moment                                         and sj.moment<si.moment                                         and sj.status<>se.status                                 )                         )                 )-se.moment             )/60 as integer         ) as duration,         se.status     from     (         select             s.id,             s.moment,             s.status         from             s             where exists             (                 select *                 from s as sa                     where s.id=sa.id                     and s.status=sa.status                     and sa.moment=                     (                         select min(sb.moment)                         from s as sb                             where sb.moment>s.moment                     )             )     )   as se )   as sf Не буду коментировать этот код, а прокоментирую приведенный ниже. Что бы хоть как то облагородить запрос использовал CTE (а вот тут уже начинаються отличия серверов, на сколько знаю, на MS SQL должно пойти) и вот, что получилось: Код with     sub(id, moment, duration, status)     as     (         select             se.id,             se.moment,             cast             (                 (                     (                         select sc.moment                         from s as sc                             where                             sc.moment=                             (                                 select max(si.moment)                                 from s as si                                     where si.moment>se.moment                                     and se.id=si.id                                     and se.status=si.status                                     and not exists                                     (                                         select *                                         from s as sj                                             where sj.moment>se.moment                                             and sj.moment<si.moment                                             and sj.status<>se.status                                     )                             )                     )-se.moment                 )/60 as integer             ) as duration,             se.status         from         (             select                 s.id,                 s.moment,                 s.status             from                 s                 where exists                 (                     select *                     from s as sa                         where s.id=sa.id                         and s.status=sa.status                         and sa.moment=                         (                             select min(sb.moment)                             from s as sb                                 where sb.moment>s.moment                         )                 )         )   as se     ) select     sub.id,     sub.moment,     case         when sub.id=1         then sub.duration         else         (             select sum(sk.duration)             from sub as sk                 where sk.id=2                 and sub.moment>=sk.moment         )     end as duration,     sub.status from sub Отмечу, что второй запрос преобразуется в первый простым "разворачиванием" CTE, без всяких любых других преобразований. А теперь - картинки  ! На следующих исходных данных: Запрос вернет следующие данные: "Усилим" тестовый набор данных: Кстати на эти данные показывают следующую неопределенность: прерывать ли подсчет периода при смене id, но при том же status? Я например прерываю, а может и не нужно? Получаем результат: Т.е. сумма с накоплением для id=2 работает. Теперь о запросе. Очевидно, что это Код with     sub(id, moment, duration, status)     as     (         ...         ...         ...     ) select     sub.id,     sub.moment,     case         when sub.id=1         then sub.duration         else         (             select sum(sk.duration)             from sub as sk                 where sk.id=2                 and sub.moment>=sk.moment         )     end as duration,     sub.status from sub "Шелуха" для обеспечения выдуманой мною суммы с накоплением... Внутри остается (убрал преобразование интервала времени в целое, что бы глаза не мозолило): Код Select                                    -- 8. Выбираем     se.id,                                -- 9. id     se.moment,                            -- 10. moment (Назовем это значение рассматриваемым)     (         select sc.moment         from s as sc             where             sc.moment=             (                 select max(si.moment)             -- 11. Из наибольшего значения,                 from s as si                     where si.moment>se.moment     -- 12. у которого                     and se.id=si.id               -- 13. такие же id                     and se.status=si.status       -- 14. и status                     and not exists                -- 15. так, что бы                     (                         select *                         from s as sj                         where sj.moment>se.moment -- 17. между ним                         and sj.moment<si.moment   -- 16. и рассматриваемым                         and sj.status<>se.status  -- 18. не было значений с отличным status’ом                     )             )     )-se.moment as duration,                      -- 19. вычитаем рассматриваемое.     se.status                             -- 20. status from                     -- 1. Из подзапроса который выбирает (     select         s.id,         s.moment,         s.status         -- 2. строки,     from         s         where exists     -- 3. Которые имеют         (             select *             from s as sa                 where s.id=sa.id             -- 6. с такими же id                 and s.status=sa.status       -- 7. и status                 and sa.moment=                 (                     select min(sb.moment)    -- 5. значение moment                     from s as sb                     where sb.moment>s.moment -- 4. следующее                   )         ) )   as se Вот результат запроса для меток 1-7: А на метках 11-18, мы для каждого отобраного (на этапе 1-7) значения moment находим максимальную границу (при "неприрывном" status и id) и вычисляем их разницу... Как всегда, запрос у Zloxa лаконичнее  , и вообще подлежит анализу  ... Зато у меня ANSI!

Автор: Zloxa 11.9.2009, 00:17

Gluttton, спасибо что поддержал. Сени уже поздно, и я уж малость вина выпил. Завтра повзрываю себе мозг твоими выкладками. Цитата(Gluttton @  11.9.2009,  00:07 ) использовал CTE  Оппа... в FB есть? Для меня новость, спасибо ! upd А в with FB рекурсию держит? как это в MSSQL и DB2. Оракля не держит, а жаль... мне кажется чудная возможность, чудное поле для извращений

Автор: Gluttton 11.9.2009, 12:51

Цитата(Zloxa @  11.9.2009,  00:17 ) upd А в with FB рекурсию держит? как это в MSSQL и DB2.Оракля не держит, а жаль... мне кажется чудная возможность, чудное поле для извращений  Зато теперь я знаю, что такое upd  . Да поддерживает (используется в частности для обхода, деревьев).

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)