VPF::ПОМОГИТЕ составить запрос - Форум программистов

Zloxa

Дата 10.9.2009, 10:56 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Цитата(asdfghjkl @ 10.9.2009, 10:45

)

Не злитесь, просто мне это объяснять и я волнуюсь, это не просто задание нудного препода.

Вот и я ж о том.
Уровень Ваших вопросов говорит о том, что Вами SQL начального уровня еще не освоен.
Это же задание претендует елси не на высшую лигу, то минимум на выше среднего.
Если Вы пытаетесь выдать себя за того, кем не являетесь, это очень похоже на обан.
Участвовать в этом обмане, как то не сильно хочется.

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

asdfghjkl

Дата 10.9.2009, 11:52 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 15
Регистрация: 9.9.2009

Репутация: нет
Всего: нет

Цитата(Zloxa @ 10.9.2009, 10:35)

asdfghjkl, А может ну его? Если вы поакажете решение на собеседовании, но затруднитесь повторить на практике в оффисе, врядли вы пройдете испытательный срок. Может куда попроще попробовать сунутсья, где требования чуть мене высокие?

я не хочу слишком умное решение, хотя бы просто показать, что пробовала. Дело в том, что последний раз работала с БД месяцев 10 назад, навык утрачен, но хочу поднапрячся. А там у них свое дополнительное обучение будет, думаю справлюсь. Это кстати, часть задания, сложная. Что попроше я уже сделала, они одобрили, попросили, мои размышления по поводу вот этого.

Zloxa

Дата 10.9.2009, 12:27 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

На 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

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

asdfghjkl

Дата 10.9.2009, 12:55 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 15
Регистрация: 9.9.2009

Репутация: нет
Всего: нет

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

Zloxa

Дата 10.9.2009, 13:03 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Цитата(asdfghjkl @ 10.9.2009, 12:55

)

это имеет специальное значение или такое название?

Нет. Это общепринятое название метода.
Я даже знаю где Вы это встречали.
Обычно так называют это поле для того, чтобы читающему было понятно о чем речь, и можно было легко найти по ключевому слову.
На том ресурсе, где Вы встерчали этот термин, уже давно гнушаются отвечать на подобные вашему вопросы.
Типичный ответ там на ваш вопрос - "STFF start_of_goup"

Это сообщение отредактировал(а) Zloxa - 10.9.2009, 13:06

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

asdfghjkl

Дата 10.9.2009, 14:10 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 15
Регистрация: 9.9.2009

Репутация: нет
Всего: нет

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

asdfghjkl

Дата 10.9.2009, 14:43 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 15
Регистрация: 9.9.2009

Репутация: нет
Всего: нет

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

Zloxa

Дата 10.9.2009, 15:12 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Цитата(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:13

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Zloxa

Дата 10.9.2009, 15:52 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Еще один способ. Идея заимствована у Azket.
Должно работатать еще и на 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

Это сообщение отредактировал(а) Zloxa - 10.9.2009, 16:09

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

asdfghjkl

Дата 10.9.2009, 15:56 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 15
Регистрация: 9.9.2009

Репутация: нет
Всего: нет

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

Simpliest

Дата 10.9.2009, 23:14 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 625
Регистрация: 1.9.2009

Репутация: нет
Всего: 3

Угу, тред читается на одном дыхании

А последний момент, просто кульминация.

Цитата(asdfghjkl @ 10.9.2009, 15:56

)

дурак думала

Конспирология в действии.

Это я к чему, да к тому, что знание SQL как езда на велосипеде. Ты можешь забыть нюансы, но не разучится ездить.

P.S. не люблю я таких ТС :(

--------------------

Gluttton

Дата 11.9.2009, 00:07 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: 24
Всего: 54

Цитата(Zloxa @ 10.9.2009, 10:03

)

Как раз над этим сейчас и думаю. Если Gluttton меня опередит, будет ой как досадно

Жутко хотелось поучаствовать, но на работе сегодня был загруженый день... Да и когда за решение взялся не всё так просто оказалось smile

.
Итак...

Цитата(Zloxa @ 10.9.2009, 09:43

)

Думаю в задании ошибка. На каком основании посчитано duration = 120 для id =2 moment=14:00 - не ясно

Я думаю, что это задание не столько на "результат" сколько на "рассуждение" и возможно это умышленно внесенная ошибка, но если подходить к решению исключительно формально, то можно предположить, что для id=2, необходимо выводить не просто продолжительность, а сумму продолжительности с накоплением. О как smile

...

Начну со своего крокодила smile

... Чистейший 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, без всяких любых других преобразований.
А теперь - картинки smile

!
На следующих исходных данных:
user posted image

Запрос вернет следующие данные:
user posted image

"Усилим" тестовый набор данных:
user posted image

Кстати на эти данные показывают следующую неопределенность: прерывать ли подсчет периода при смене id, но при том же status? Я например прерываю, а может и не нужно?
Получаем результат:
user posted image

Т.е. сумма с накоплением для 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:
user posted image

А на метках 11-18, мы для каждого отобраного (на этапе 1-7) значения moment находим максимальную границу (при "неприрывном" status и id) и вычисляем их разницу...

Как всегда, запрос у Zloxa лаконичнее smile

, и вообще подлежит анализу smile

... Зато у меня ANSI!

Это сообщение отредактировал(а) Gluttton - 11.9.2009, 12:12

--------------------

Слава Україні!

Zloxa

Дата 11.9.2009, 00:17 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Gluttton, спасибо что поддержал.
Сени уже поздно, и я уж малость вина выпил.
Завтра повзрываю себе мозг твоими выкладками.

Цитата(Gluttton @ 11.9.2009, 00:07

)

использовал CTE

Оппа... в FB есть?
Для меня новость, спасибо !

upd А в with FB рекурсию держит? как это в MSSQL и DB2.
Оракля не держит, а жаль... мне кажется чудная возможность, чудное поле для извращений smile

Это сообщение отредактировал(а) Zloxa - 11.9.2009, 00:32

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Gluttton

Дата 11.9.2009, 12:51 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: 24
Всего: 54

Цитата(Zloxa @ 11.9.2009, 00:17

)

upd А в with FB рекурсию держит? как это в MSSQL и DB2.Оракля не держит, а жаль... мне кажется чудная возможность, чудное поле для извращений

Зато теперь я знаю, что такое upd smile

.
Да поддерживает (используется в частности для обхода, деревьев).

--------------------

Слава Україні!

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Составление SQL-запросов \| Следующая тема »