Нестандартный вывод записей из таблицы

[Dimka]

Начнем с того, что запрос не без ошибок. В том же work_id.

Это мне?

[RXL]

Да, Дим.
Зачем work_id, если в связи таблиц он отсутствует?

[Dimka]

RXL, автор сказал, что ему надо раздельно по каждому work_id, что я и сделал. И причём тут связь таблиц?

[nerik]

RXL, Dimka не ругайтесь ребята)) Я провел исследования обоими Вашими запросами и вот что получилось. В базе пока 444 записи.

select min(t2.date) `from`, max(t2.date) `to`, t2.id_work `id_work`
from prds t1 join prds t2 on t2.date between t1.date and date_add(t1.date,interval 3 day)
group by t1.date, t2.id_work having count(distinct t2.date)=4 and min(t2.allo)>0
order by `from`, `to`, `id_work`; #Отображает строки 0 - 26 ( 27 всего, Запрос занял 0.0907 сек.)

select t1.date `from`, t2.date `to`, t2.id_work `id_work`
from (select 0 n union select 1 union select 2 union select 3) seq, prds t1, prds t2   
where t1.id_work=t2.id_work and t2.date=t1.date+interval seq.n day and t1.allo>0 and t1.allo>0
group by t2.date, t2.id_work having count(t2.date)=4; #Отображает строки 0 - 26 ( 27 всего, Запрос занял 0.0069 сек.)


Из результатов видно, что оба варианта хорошо справляются. Но применение join ухудшает производительность выполнения запроса.
Осталось проверить оба запроса, когда некоторые дни будут отсутствовать.

[Dimka]

nerik, ну если вопрос в скорости то разумно отфильтровывать ненужное на самых ранних стадиях обработки:

prds t1 join prds t2 on t1.allo > 0 and t2.allo > 0 and t2.date between t1.date and date_add(t1.date,interval 3 day)

Тогда из having можно убрать min(t2.allo)>0

И сортировку убери, раз не нужно.

Потому что тема создавалась с вопросом "можно ли написать запрос". Можно.

[HandKot]

Осталось проверить оба запроса, когда некоторые дни будут отсутствовать.

и вот тут-то и начнутся проблемы , либо придется запросы переписывать доделывать (допиливать)

[nerik]

Dimka, спасибо. Уже лучше в разы) время выполнения стало гораздо меньше)

select t1.date `from`, t2.date `to`, t2.id_work `id_work`
from (select 0 n union select 1 union select 2) seq, prds t1, prds t2   
where t1.id_work=t2.id_work and t2.date=t1.date+interval seq.n day and t1.allo>0 and t1.allo>0
group by t2.date, t2.id_work having count(t2.date)=3;
#Отображает строки 0 - 29 ( 34 всего, Запрос занял 0.0057 сек.)

select min(t2.date) `from`, max(t2.date) `to`, t2.id_work `id_work`
prds t1 join prds t2 on t1.allo>0 and t2.allo>0 and t2.date between t1.date and date_add(t1.date,interval 2 day)
group by t1.date, t2.id_work having count(distinct t2.date)=3;
#Отображает строки 0 - 29 ( 34 всего, Запрос занял 0.0082 сек.)

Потому что тема создавалась с вопросом "можно ли написать запрос". Можно.

Прошу прощения)) На то время я думал, что не получиться составить такой запрос и нужно перестраивать таблицу.

[Dimka]

HandKot, а какие ты видишь проблемы?

[HandKot]

HandKot, а какие ты видишь проблемы?

Код:

having count(distinct t2.date)=4

и

когда некоторые дни будут отсутствовать.

здесь могут вылезти косяки

[Dimka]

HandKot, я и спрашиваю, какие?

[HandKot]

ну так я и говорю

имеем

day       id_work     count 20130326        1            2 20130327        1            4 20130328        1            2 20130329        1            0 20130330        1            2 20130331        1            3 20130401        1            2 20130402        1            2 20130403        1            1

получаем 3-дневные диапозоны

day1            day2         id_work 20130326     20130328          1 20130330     20130401          1 20130331     20130402          1 20130401     20130403          1

если, к примеру, у нас не будет записи с датой 20130328       
то у нас потеряется диапозон 20130326     20130328          1, т.к не пройдет условие

Код:

count(distinct t2.date)=3

может это и будет правильно, но в начальном варианте задачи было сказано

надо найти варианты x-дневных диапазонов, в которых count выше 0

а отсутствие строки не означает, что там 0. это означает что там "ничего"
т.е либо нужно уточнение задачи, либо допилка запроса

[Dimka]

HandKot, т.е. ты условие группы понял как "существуют allo > 0", а все остальные тут в теме толкуют про "все allo > 0". Да, запрос выбрасывает диапазоны по более мягкому "существует allo = 0", а не по "все allo = 0".

Для твоего варианта изменения самые минимальные - достаточно убрать having вообще Поскольку выброшенными должны оказаться группы, в которых нет ни одной записи, а они и так будут выброшены без всяких условий. Это если не надо каждое редкое значение окружать всеми возможными диапазонами. Т.е, допустим, существует единственная запись 01.04, тогда должны как бы "из воздуха" появиться 3-хдневные диапазоны 30.03-01.04, 31.03-02.04, 01.04-03.04.

[HandKot]

достаточно убрать having вообще

если это сделать, то мы получим вместо 3-дневного диапозона и 2-дневный и однодневный

т.е для всех варианот, мы получим еще и следующие группы

20130402        20130403 20130403        20130403

так что having нужно оставлять, но придется переделывать

возможно есть и другие подводные камни, которые не видны на тестовых (считать идеальных) данных, но могут вылезти на продакшене и, что еще хуже, не сразу

[Dimka]

HandKot, ну разницу max - min по дате в группе сделать в 3 дня.

Пока что я не уверен, что проблема вообще есть.

возможно есть и другие подводные камни, которые не видны на тестовых (считать идеальных) данных, но могут вылезти на продакшене и, что еще хуже, не сразу

Ты упорно держишься этой мысли. Откуда такая паранойя? Ты исключаешь возможность аналитически доказать правильность запроса, или вывести запрос из условий и исходных данных дедуктивно?

[HandKot]

HandKot, ну разницу max - min по дате в группе сделать в 3 дня.

Пока что я не уверен, что проблема вообще есть.

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

возможно есть и другие подводные камни, которые не видны на тестовых (считать идеальных) данных, но могут вылезти на продакшене и, что еще хуже, не сразу

Ты упорно держишься этой мысли. Откуда такая паранойя? Ты исключаешь возможность аналитически доказать правильность запроса, или вывести запрос из условий и исходных данных дедуктивно?

это не паранойя - это опыт, сын ошибок трудных...


ЗЫЖ запросы для данного набора данных и указанных условий верны (по крайней мере, я не вижу ошибки)

[nerik]

HandKot, а какие ты видишь проблемы?

Код:

having count(distinct t2.date)=4

и

когда некоторые дни будут отсутствовать.

здесь могут вылезти косяки

Это мы сейчас будем проверять)

nerik, ну если вопрос в скорости

Я так понимаю при Вашем варианте запроса индекс лучше ставить на одно поле - date?

[Dimka]

Я так понимаю при Вашем варианте запроса индекс лучше ставить на одно поле - date?

Я про индексы ничего не говорил. Вопрос об индексах можно решать, только зная характер хранящихся данных: подбор индексов - это задача опытная, а не теоретическая. В том числе со временем по мере накопления данных и изменения запросов систему индексации следует пересматривать.

Оценка однообразия Ux=max(count(x) group by x), Ua=avg(count(x) group by x)=count(x)/count(distinct x), Un=min(count(x) group by x) - каково наибольшее, среднее и наименьшее количество записей на 1 уникальное значение поискового ключа (он может быть составным - из нескольких полей). С точки зрения поиска по хэшу оценка значит, что в худшем, среднем и лучшем случае столько записей придётся подвергать последовательному перебору. Чем ниже однообразие, тем эффективнее индекс - меньше необходимость в последовательном переборе. Можно построить коэффициент эффективности индекса e=1-U/count(x) - такая доля записей каждом из случаев (худшем, среднем и лучшем) будет исключена из последовательного перебора. Чем меньше e, тем меньше смысла в индексировании. Максимум для e равен 1-1/count(x) - все записи уникальны. На максимум можно нормировать, получив абстрактный коэффициент от 0 до 1, не зависящий от количества записей в таблице и пригодный для сравнения исторических данных после изменения количества записей.

Но эти рассуждения годятся для любых полей и их сочетаний. Если есть конкретный запрос, то смотреть нужно на поля, участвующие сначала в join и where, затем в group by и having. Применительно к данному запросу нужно посмотреть эффективности индексов для date, allo, пары (date, allo), выбрав для индексации самый эффективный вариант (на вскидку это будет пара). Затем посмотреть эффективность индексов для выбранного варианта и его сочетания с work_id. Если добавление work_id повышает эффективность, то и его включить в индекс (если в таблице нет других полей, то повысит до максимума, поскольку пара date и work_id образуют первичный ключ, хотя для индекса важнее способность быстро отыскать allo = 0, и только потом уже будет использоваться work_id для группировки).

Стоит заметить, что большие составные индексы более ресурсоёмки в обслуживании, поэтому увлекаться микроскопическими повышениями эффективности, включая в индекс всё подряд, не стоит - можно проиграть. Не исключено, что для рассматриваемого запроса достаточно будет индекса по date, если количество разных вариантов work_id и allo не велико. В конечном итоге нужно сравнивать планы выполнения запроса в случае более простых и сложных индексов. Если усложнение индекса не даёт изменения плана, значит усложнение бесполезно для СУБД, если при этом растёт время обработки запроса, значит вообще вредно. (Также стоит оценивать скорости insert, update и delete до и после введения индекса - иногда это критично.)

[nerik]

Dimka, спасибо за информацию, очень полезно для раздумий) Буду проводить исследования)

Кстати проверил по поводу выпадов дат - оба запроса работают правильно) и игнорируют записи, где даты выпали).
Ребята, всем спасибо большое за помощь)

[HandKot]

Пока что я не уверен, что проблема вообще есть.

еще вот нашел проблему ПРИ УСЛОВИИ , что на одну дату могут быть несколько записей 

Буду проводить исследования

это нужно всегда, если конечно хотите, чтобы конечный продукт был "rjyatnrjq". дерзайте 

[Dimka]

еще вот нашел проблему ПРИ УСЛОВИИ , что на одну дату могут быть несколько записей

Чего-то все твои проблемы больше свидетельствуют о неумении читать SQL и незнании тонкостей, чем о реальных проблемах.

count(distinct x) тем и интересен, что возвращает количество уникальных значений (игнорирует повторы) - в отличие от count(x), возвращающего количество не-NULL значений, даже если там есть повторы. А count(*) вообще возвращает количество записей в результате (включая NULL).

Тут, как говорится, лучше матчать учить, чем проблемы выдумывать.

[HandKot]

Тут, как говорится, лучше матчать учить, чем проблемы выдумывать.

стараюсь по возможности. учусь у других. в общем пытаюсь совершенствоваться
и предложенные и Вами и RXL вроде как внимательно изучил (хотя MySQL не мой профиль), т.к интересен сам подход, который применяют люди к той или иной задаче и беру на заметку удачные решения

но к делу
я вообще-то имел ввиду, что при таких данных

day       id_work     count 20130326        1            2 20130327        1            4 20130327        1            0 20130328        1            2 20130329        1            0 20130330        1            2 20130331        1            3 20130401        1            2 20130402        1            2 20130403        1            1

и если автор выбрал такой запрос

Код:

select min(t2.date) `from`, max(t2.date) `to`, t2.id_work `id_work`
prds t1 join prds t2 on t1.allo>0 and t2.allo>0 and t2.date between t1.date and date_add(t1.date,interval 2 day)
group by t1.date, t2.id_work having count(distinct t2.date)=3;

получаем 3-дневные диапозоны

day1            day2         id_work 20130326     20130328          1 20130330     20130401          1 20130331     20130402          1 20130401     20130403          1

и если я прав, то это неверно
и придутся вернуться к первому варианту с

Код:

and min(t2.allo)>0

с временем выполнения на порядок больше

ЗЫЖ Dimka, Вы не подумайте, что я придераюсь. Вариант RXL мне даже очень понравился, интересный подход (можно сказать даже нестандартный)

[Dimka]

HandKot, чего-то формализм какой-то. Формально 27-го есть 0. Но по смыслу-то нужно найти дни, в которых allo=0, и 27 - не такой день, т.к. есть allo=4.

Твоё же изобретение всё равно отсекается в where через exists или in подзапрос. Хотя время обработки увеличится.

[HandKot]

Dimka, ну пусть будет формализм, не столь суть  

возвращаясь к задаче, прошу опять взять в рассмотрение и мою версию
постарался сделать как в MySql и если есть косяки в написании, то прошу либо Dimka, либо RXL поправить (у меня просто нет такой возможности), а nerik пусть протестирует (чисто академический интерес)

Код: (MySQL)

select
        t.date + INTERVAL seq.n - 2 `from`,  
        t.date + INTERVAL seq.n `to`,  
        t.id_work `id_work`
from
        prds t, (select 0 n union select 1 union select 2) seq
group by
        t.date + INTERVAL seq.n - 2,
        t.date + INTERVAL seq.n,
        t.id_work
having
        SUM(Case When t.allo = 0 Then 1 Else 0 end) = 0
        And COUNT(distinct t.date) = 3 
order by
        `from`, `to`, `id_work`;

[Dimka]

HandKot, в этом нет смысла. Ты от каждой даты "раскидываешь" интервал в прошлое и будущее. В практическом плане это ничего не дает, поскольку нет иных дат, нежели имеющиеся в таблице - ничего не "поймаешь" сверх сопоставления записей. Какие-нибудь уравнения численно решать, заглядывая в окрестности точки в метрическом пространстве - смысл есть. Но базы данных - это совсем другая "епархия" дискретных атомов (значений), отношений между ними и реляционной алгебры.

Т.е. у RXL этот seq смотрится как приём избегания join таблицы самой с собой (что для массивных таблиц актуально). Ты же этот чисто технический приём превращаешь в какой-то математический алгоритм - ход мысли совершенно не в духе реляционной алгебры.

[HandKot]

HandKot, в этом нет смысла.

т.е. не имеет смысла в проверке? Если так, то Вы ошибаетесь, запрос работает и выдает корректные данные для представленного набора данных. ИМХО Вас подвела "аналитическое доказательство правильности запроса" (видать сказался конец дня).
Вы не до конца поняли тот алгоритм, который я реализовал. Он, как мне кажется, полностью в духе "реляционной алгебры". В нем есть только работа с множествами: объединение и пересечение. Если интересно, то могу расписать как да что 

ЗЫЖ хотя может и я не до конца понимаю термин "реляционной алгебры", т.к есть пробелы в мат.части )

ЗЗЫЖ nerik  в запросе была ошибка (синтаксическая). вот корректный запрос

Код: (MySQL)

SELECT
        t.date + INTERVAL seq.n - 2 DAY `from`,
        t.date + INTERVAL seq.n DAY `to`,
        t.id_work `id_work`
FROM
        prds t, (SELECT 0 n UNION SELECT 1 UNION SELECT 2) seq
GROUP BY
        t.date + INTERVAL seq.n - 2 DAY,
        t.date + INTERVAL seq.n DAY,
        t.id_work
HAVING
        SUM(CASE WHEN t.allo = 0 THEN 1 ELSE 0 END) = 0
        AND COUNT(DISTINCT t.date) = 3;

[Dimka]

т.е. не имеет смысла в проверке?

Ну ты тут юнит-тесты и не предлагаешь. Поэтому твоя проверка сводится всё к тем же аналитическим идеям, что и выработка самого запроса: ты пробуешь сочинять доказательства от противного, подбирая отдельные примеры. Сам этот метод не даёт никаких гарантий, что ты сумел придумать все возможные контрпримеры. Единственный надёжный путь: прямое доказательство через мысленное разложение запроса на отдельные операции реляционной алгебры. Но запрос (по крайней мере мой) так и создавался с самого первого поста, где перечислены пункты - по построению, как говорится.

Поэтому я не понимаю, чем ты занимаешься. RXL понимаю - он оптимизировал план исполнения. Ты такой задачи себе не ставишь, и получается, что единственным твоим устремлением является коллекционирование всех эквивалентных запросов. Хочу заметить, что их бесконечное количество - по мере нагромождения лишних операций, не влияющих на конечный результат. И какой в этом смысл?

Если бы ты предложил эквивалентный запрос, в котором какая-либо из элементарных операций реляционной алгебры была бы исключена, и тем самым доказана её избыточность (что можно добиться результата более простым способом) - это был бы полезный и информативный вклад в тему. Всё остальное - прах.

[HandKot]

Если бы ты предложил эквивалентный запрос, в котором какая-либо из элементарных операций реляционной алгебры была бы исключена, и тем самым доказана её избыточность (что можно добиться результата более простым способом) - это был бы полезный и информативный вклад в тему. Всё остальное - прах.

либо я что-то не допонимаю, либо одно из двух.
В моем "эквивалентном" варианте (под эквивалентным я подразумеваю, что результат соответствует другим вариантам), как минимум, нет лишнего джойна, т.е. получаем "в котором какая-либо из элементарных операций реляционной алгебры была бы исключена, и тем самым доказана её избыточность (что можно добиться результата более простым способом)"

или я не прав ?

в общем я закругляюсь.
Я предложил еще один вариант для автора топика, а уж как он решит ему одному известно

[Dimka]

В моем "эквивалентном" варианте (под эквивалентным я подразумеваю, что результат соответствует другим вариантам), как минимум, нет лишнего джойна, т.е. получаем "в котором какая-либо из элементарных операций реляционной алгебры была бы исключена, и тем самым доказана её избыточность (что можно добиться результата более простым способом)"

или я не прав ?

Конечно, не прав. Запятая во from - это cross join (полное декартово произведение) в отличие от inner join в остальных примерах, где результаты этого декартова произведения подвергаются обработке горизонтальным фильтром. Т.е. у тебя на группировку попадает больше групп, чем нужно. Более того, твой запрос, в котором вариации распространены и на to, и на from, порождает больше групп в штуках (хотя и эквивалентных друг другу дубликатов), чем варианты с одной фиксированной опорной датой и другой вариативной (где дубликатов вовсе нет). А ведь эти лишние группы ещё надо убирать.

Что-то я не замечаю во всём этом большей простоты и производительности.

[HandKot]

Dimka, не хочу спорить.

Лишь прошу автора топика протестировать все три варианта запроса и выдать все возможные параметры выполнения: время, ЦП, IO
с указанием кол-ва записей в исходной таблице (это немало важный показатель)

уже самому интересно кто прав, а то может точно мне надо пойти учить мат.часть

[Dimka]

HandKot, я тебе из без тестов скажу, что по времени, загрузке и вводу/выводу запрос с соединением таблицы с самой собой будет работать медленнее просто потому, что во второй таблице больше записей. Его преимущество перед схемой решения RXL в универсальности за счёт параметра, а преимущество подхода RXL в скорости. Поэтому если ты на что-то надеешься, то сравнивай с вариантом RXL.