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

[nerik]

Доброго дня.
Заранее благодарен всем, кто сможет помочь:) Уже 2 неделю бьюсь над задачей. Буду благодарен за любую мысль:)
Есть таблица с записями вида

    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

таких записей очень много. Примерная задача: надо найти варианты 4-дневных диапазонов, в которых count выше 0, т.е. нужен запрос, который вывел бы из данных данной таблицы такое:

   day1            day2         id_work
20130330     20130402          1
20130331     20130403          1

А при 3-дневном диапазоне такие варианты:

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

Принцип работы запроса. Скажем нужны 3-дневные диапазоны.

Берет первую запись 20130326, смотрит - count больше 0, это 1 день диапазона. Далее берет следующую запись 20130327, смотрит - count больше 0. Это 2 день диапазона. Далее берет следующую запись 20130328, смотрит - count больше 0. Это 3 день диапазона. 3 дня есть, то выводим

20130326 20130327 1

Далее уже начинаем с записи 20130327. Берет первую запись 20130327, смотрит - count больше 0, это 1 день диапазона. Далее берет следующую запись 20130328, смотрит - count больше 0. Это 2 день диапазона. Далее берет следующую запись 20130329, смотрит - ага, count равен 0. Все это диапазон уже не нужно выводить.

Далее уже начинаем с записи 20130328. Берет первую запись 20130328, смотрит - count больше 0, это 1 день диапазона. Далее берет следующую запись 20130329, смотрит - ага, count равен 0. Это диапазон уже не нужно выводить.

И так далее. Тут без переменных и вложенных запросов никак. Уже весь мозг себе сломал)) Возможно ли организовать запрос, который бы сделал нужные выводы? Спасибо.

P.S. Прощу прощения, если неправильно выразил задачу, ещё не все возможности MySQL изучил:) В голове все перемешалось. Заранее спасибо, всем кто даже прочтет.

[RXL]

nerik, четче сформулируй условие и решение найдется само.

[nerik]

nerik, четче сформулируй условие и решение найдется само.

Прошу прощения, но четче не получается, я уже и принцип работы запроса описал(((

[RXL]

Примерная задача: надо найти варианты 4-дневных диапазонов, в которых count выше 0

Нужно не примерное, а четкое условие.

[Dale]

Далеко не самое красивое решение, к тому же плохо масштабируемое в случае, если интервал должен быть гораздо больше 4-х дней:

Код: (SQL)

SELECT W1.DAY
FROM (([WORK] W1
INNER JOIN [WORK] W2 ON (W2.DAY = W1.DAY+1))
INNER JOIN [WORK] W3 ON (W3.DAY = W1.DAY+2))
INNER JOIN [WORK] W4 ON (W4.DAY = W1.DAY+3)
WHERE (W1.Cnt >0) AND (W2.Cnt > 0) AND (W3.Cnt > 0) AND (W4.Cnt > 0);

Набросал запрос на диалекте MS Access, подправьте под MySql.

[Dimka]

Эх...

1) Нужно написать JOIN таблицы самой с собой T as T1 join T as T2 по условию, что даты второй копии находятся в диапазоне между датой текущей записи и датой, увеличенной на нужное число дней: T2.date >= T1.date and T2.date <= dateadd(day, 3, T1.date). В результат включить дату первой копии T1.date и все поля второй копии таблицы T2.*: каждой дате из T1 (назовём её опорной) будут соответствовать все записи в пределах 3-х дней вперёд от этой даты.
2) Сделать группировку по опорной дате GROUP BY T1.date.
3) Если в группе взять MIN(T2.qty) по количеству, он, очевидно, покажет 0, если нули в группе есть, или не 0, если нулей нет.
4) Если в группе взять COUNT(T2.date), он покажет, сколько разных дней попадает в группу: если COUNT равен заданному числу дней n, значит "дырок" по дням в группе нет.
5) Во вторичном горизонтальном фильтре HAVING написать условие, что MIN(T2.qty)>0 AND COUNT(T2.date)=n.
6) MIN(T2.date) и MAX(T2.date) дадут искомый диапазон дней, в которых выполняется условие.

Причём там выше work_id, я не понял - он везде одинаковый

[nerik]

Dale, этот вариант у меня был самый первый) Вы правы он плохо масштабируется и БД умрет если записей будет миллион)

Dimka, work_id будет разный, и даты уже будут под него. Я написал самый простой пример, чтобы не запутатся. В идеале записи примерно будут такими

 

day       id_work     count
        другие записи
20130326        1            2
20130327        1            4
20130326        2            3
20130328        1            2
20130329        1            0
20130327        2            1
20130328        2            3
20130330        1            2
20130331        1            3
20130329        2            0
20130401        1            2
20130402        1            2
20130330        2            1
20130403        1            1
        другие записи

Dimka, спасибо за подробное описание действий. Я так и предполагал сделать. Просто смотрю ещё варианты, чтобы найти более скоростной для БД. Есть вариант переложить это на силы php, но лучше сделаю два варианта, чтобы оценить быстроту работы каждого.

[Алексей++]

nerik, может быть стоит загрузить эти колонки в память и там уже обсчитать ? Особенно, если операция нечастая

[nerik]

nerik, может быть стоит загрузить эти колонки в память и там уже обсчитать ? Особенно, если операция нечастая

Как раз таки операция является частой) она используется в поиске данных.
Дело в том, что нужно сгружать все данные в память и там их сортировать, что повлечет много ресурсов сервера (БД работает и програ). А нужно чтобы тока БД делала операцию и сортировала.
Хотя все это нужно исследовать конечно. Но такая идея у меня остается как последний вариант) Пока рассматриваю другие)

[Алексей++]

nerik, ну, к примеру, на миллион записей уйдёт (пусть на дату 8 байтов, и ещё две колонки по 4) примерно 16 метров. Данные можно синхронизировать - появились в БД новые записи, докинь их в память. Если были серьёзные обновления в БД - заново перезагрузи (это редко).
Ну тут всё как обычно, короче, хочешь скорость - жертвуй ресурсами и наоборот ))
В ОЗУ эта операция будет выполняться мгновенно. Синхронизация тоже - ведь там надо то, проверить, не появились ли записи старше последней, считать эти 5 записей и дописать в массив

Добавлено через 1 минуту и 17 секунд:
я же правильно понимаю, что count для прошедших дней - это константа ?

[HandKot]

а как вам такой вариант (для периода три дня)

Код:

select 
	DateAdd(DD, x.t - 2, t.dt) 
	, DateAdd(DD, x.t, t.dt) 
from 
	@t t, (values (0), (1), (2)) x(t)
group by
	DateAdd(DD, x.t - 2, t.dt) 
	, DateAdd(DD, x.t, t.dt) 
having
	Sum(Case When t.cnt = 0 Then 1 Else 0 end) = 0	
	And COUNT(*) = 3	
order by
	1

по идее обходимся одним сканом таблицы
и маштабируемость есть (изменить куски values (0), (1), (2) , x.t - 2 и COUNT(*) = 3 даже можно параметрами)

ЗЫЖ это для MSSQL, но и после доработки можно применить на MySql

[RXL]

Эх, вы! Совсем не даете человеку головой поработать. Заскучали, видать...

MySQL 5.1 и выше.

Код: (MySQL)

CREATE TABLE counters (
    `day` INT UNSIGNED NOT NULL,
    `id_work` INT,
    `count` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`day`, `count`)
);

SELECT t1.`day` `from`, t1.`day` + 3 `to`
    FROM (
            SELECT 0 n
            UNION SELECT 1
            UNION SELECT 2
            UNION SELECT 3
        ) seq, counters t1, counters t2
    WHERE t2.`day` = t1.`day` + seq.n
        AND t1.`count` > 0
        AND t2.`count` > 0
    GROUP BY t1.`day`
    HAVING COUNT(t2.`day`) = 4;

Full index scan по PK t1, range index scan по PK t2.

Если формат таблицы INNODB, можно попробовать убрать count из PK. Но непременно проверить разницу!

[nerik]

Алексей++, да count по сути является константой. В очень очень редких случаях может меняться.

HandKot, RXL спасибо, буду пробовать:)

[HandKot]

RXL, Вы можете проверить мой вариант (у нас они очень похожи)?
заменs
1. VALUES = select ... union
2. dateadd = +

интересно он вообще рабочий?
или же дождемся nerikа

[Алёна]

Эх, вы! Совсем не даете человеку головой поработать. Заскучали, видать...

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

[HandKot]

Эх, вы! Совсем не даете человеку головой поработать. Заскучали, видать...

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

не, язык один T-SQL, вот диалекты разные
вариант от RXL вообще в стандарте ANSI и пойдёт на любом сервере, да и я предложил замены для приведения к стандарту ANSI

вот рациональность, тут да. она может измерятся в разных ипостасях. тут пусть ТС сам думает

[Dimka]

Мысль про PHP я категорически не одобряю. PHP не обгонит MySQL или другую СУБД по скорости обработки фильтров и групп. А если записей миллион, львиная потеря времени будет на процесс сериализации, передачи по сети и десериализации этих записей - и это не считая обработки на PHP.

[RXL]

 :rtfm:Немного изменил: INT тут не подходит для даты в таком формате.

Код: (MySQL)

CREATE TABLE counters (
    `day` DATE NOT NULL,
    `id_work` INT UNSIGNED NOT NULL,
    `count` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id_work`, `day`, `count`)
);

INSERT INTO counters VALUES
    (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);

SELECT t2.`day` `from`, t2.`day` + INTERVAL 3 DAY `to`, t2.`id_work`
    FROM (
            SELECT 0 n
            UNION SELECT 1
            UNION SELECT 2
            UNION SELECT 3
        ) seq, counters t1, counters t2
    WHERE t1.`id_work` = t2.`id_work`
        AND t2.`day` = t1.`day` + INTERVAL seq.n DAY
        AND t1.`count` > 0
        AND t2.`count` > 0
    GROUP BY t2.`id_work`, t2.`day`
    HAVING COUNT(t2.`count`) = 4;

+------------+------------+---------+
| from       | to         | id_work |
+------------+------------+---------+
| 2013-03-30 | 2013-04-02 |       1 |
| 2013-03-31 | 2013-04-03 |       1 |
+------------+------------+---------+
2 rows in set (0.00 sec)

+----+--------------+----------------+-------+---------------+---------+---------+-----------------+------+-----------------------------------------------------------+
| id | select_type  | table          | type  | possible_keys | key     | key_len | ref             | rows | Extra                                                     |
+----+--------------+----------------+-------+---------------+---------+---------+-----------------+------+-----------------------------------------------------------+
|  1 | PRIMARY      | t1             | index | PRIMARY       | PRIMARY | 11      | NULL            |    9 | Using where; Using index; Using temporary; Using filesort |
|  1 | PRIMARY      | t2             | ref   | PRIMARY       | PRIMARY | 4       | test.t1.id_work |    4 | Using where; Using index                                  |
|  1 | PRIMARY      | <derived2>     | ALL   | NULL          | NULL    | NULL    | NULL            |    4 | Using where; Using join buffer                            |
|  2 | DERIVED      | NULL           | NULL  | NULL          | NULL    | NULL    | NULL            | NULL | No tables used                                            |
|  3 | UNION        | NULL           | NULL  | NULL          | NULL    | NULL    | NULL            | NULL | No tables used                                            |
|  4 | UNION        | NULL           | NULL  | NULL          | NULL    | NULL    | NULL            | NULL | No tables used                                            |
|  5 | UNION        | NULL           | NULL  | NULL          | NULL    | NULL    | NULL            | NULL | No tables used                                            |
| NULL | UNION RESULT | <union2,3,4,5> | ALL   | NULL          | NULL    | NULL    | NULL            | NULL |                                                           |
+----+--------------+----------------+-------+---------------+---------+---------+-----------------+------+-----------------------------------------------------------+

Для тестирования слишком мало данных. Надо порядка 10⁴ строк.

[nerik]

Ребята, спасибо Всем за содействие:)

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

Добавлено через 5 дней, 6 минут и 59 секунд:
RXL, Ваш запрос хорошо работает, правда есть потребность избавления having, но это мы уже подправили.

Сейчас заказчик ввел новую хотелку)) Диапазон количества дней в периоде. Раньше было фиксировано - например, 3-дневный диапазон. А теперь хотят например, от 3-дневного до 5-дневного. Возможно ли в Вашем запросе это воплотить не применяя union?

Скажем упростить запрос

Код: (MySQL)

(SELECT t2.`day` `from`, t2.`day` + INTERVAL 3 DAY `to`, t2.`id_work`
    FROM ( SELECT 0 n UNION SELECT 1 UNION SELECT 2 ) seq, counters t1, counters t2
    WHERE t1.`id_work` = t2.`id_work`
        AND t2.`day` = t1.`day` + INTERVAL seq.n DAY
        AND t1.`count` > 0
        AND t2.`count` > 0
    GROUP BY t2.`id_work`, t2.`day`
    HAVING COUNT(t2.`count`) = 3)
UNION
(SELECT t2.`day` `from`, t2.`day` + INTERVAL 3 DAY `to`, t2.`id_work`
    FROM ( SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 ) seq, counters t1, counters t2
    WHERE t1.`id_work` = t2.`id_work`
        AND t2.`day` = t1.`day` + INTERVAL seq.n DAY
        AND t1.`count` > 0
        AND t2.`count` > 0
    GROUP BY t2.`id_work`, t2.`day`
    HAVING COUNT(t2.`count`) = 4)
UNION
(SELECT t2.`day` `from`, t2.`day` + INTERVAL 3 DAY `to`, t2.`id_work`
    FROM ( SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) seq, counters t1, counters t2
    WHERE t1.`id_work` = t2.`id_work`
        AND t2.`day` = t1.`day` + INTERVAL seq.n DAY
        AND t1.`count` > 0
        AND t2.`count` > 0
    GROUP BY t2.`id_work`, t2.`day`
    HAVING COUNT(t2.`count`) = 5)

[RXL]

Вариантов запросов всего два: мой и Dale. Оба требуют коррекции при изменении количества дней. Запрос придется формировать динамически: либо на клиенте, либо в процедуре (если использовать только MySQL и клиентская библиотека версии 5 и старше, то поддерживается режим множественных рекордсетов и из процедуры можно вернуть результат SELECT (и даже нескольких).

Еще предлагаю рассмотреть вариант со вспомогательной таблицей, со значениями от 1 до максимального числа дней. Запрос из нее и having параметризировать.

Посмотрел запрос в предыдущем посте. А зачем так сложно? Условие задачи изменилось?

[Dimka]

RXL, а моего, значит, варианта, где всего этого не требуется, и количество дней прекрасно параметризируется, значит нету?

[RXL]

Dimka, нету. Ты ж его не написал и не отладил.

[HandKot]

RXL, а моего, значит, варианта, где всего этого не требуется, и количество дней прекрасно параметризируется, значит нету?

мой тоже в расчет не взяли  хотя и написал и отладил (правда на MSSQL)

Dimka, нету. Ты ж его не написал и не отладил.

Но ведь должен же быть простор для полета фантазии у nerik . Использовать уже готовое - не интересно. Иногда приятнее сделать своими руками, даже если используешь советы других

[Dimka]

RXL, "а может ещё и ключ от квартиры, где деньги лежат?" (c)

[RXL]

Может, Дим, может.
Вариантов всегда много. Но рассматривать вариант без реализации, проверки эффективности и доводки как минимум неудобно.

Добавлено через 1 минуту и 7 секунд:
HandKot, да-да, потому что MS SQL — к MySQL неприменимо.

[Sla]

Код:

(
            SELECT 0 n
            UNION SELECT 1
            UNION SELECT 2
            UNION SELECT 3
        ) seq

А можно ли эту конструкцию заменить на что-то подобное: ?
SET @rank=0;
SELECT @rank:=@rank+1 AS rank where rank < нужного числа дней

[RXL]

Нельзя. Во-первых, рекордсет не участвует в WHERE. Во-вторых, т.к. реальной таблицы нет, строка может быть только одна.

[Dimka]

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

Так уж и быть, дословно записываю на MySQL (с учётом work_id):

Код: (SQL)

DECLARE @d INT;
SET @d = 4;
SELECT
  MIN(t2.DAY) AS first_day,
  MAX(t2.DAY) AS last_day,
  t2.work_id
FROM
  test AS t1
    JOIN test AS t2
      ON
        t2.DAY BETWEEN t1.DAY AND date_add(t1.DAY, INTERVAL @d - 1 DAY)
GROUP BY t1.DAY, t2.work_id
HAVING
      COUNT(DISTINCT t2.DAY) = @d
  AND MIN(t2.COUNT) > 0
ORDER BY first_day, last_day, work_id;

Считая колонку day имеющую тип datetime, иначе добавить конвертирование. Результат отдельный для каждого work_id.

[Sla]

Нельзя. Во-первых, рекордсет не участвует в WHERE. Во-вторых, т.к. реальной таблицы нет, строка может быть только одна.

"num_rows" можно получить из любой таблицы
тем самым убрав union

[RXL]

Начнем с того, что запрос не без ошибок. В том же work_id. Почему я и говорю, что сперва нужно проверить. А по рабочему запросу можно будет и план проверить.

Добавлено через 1 минуту и 46 секунд:
Sla, можно. Но надо иметь надлежащую таблицу и быть уверенным, что число строк не меньше нужного.

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

Это надежнее и результат будет сортирован по PK.

[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.

[nerik]

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

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

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

Ваш запрос интересен, но в моей БД исключены дублирование записей в таблице prds. Я использую сейчас оба запроса (от RXL и Dimka). По моим тестам запрос RXL выигрывает в скорости в разы. Но второй запрос мне нужен для вывода других данных. По Вашей просьбе вот результаты запросов:) В таблице 1090 записей.

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 t2.allo>0
group by t2.date, t2.id_work having count(t2.date)=3;
#Отображает строки 0 - 29 ( 495 всего, Запрос занял 0.0240 сек.)

select min(t2.date) `from`, max(t2.date) `to`, t2.id_work `id_work`
from 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 ( 495 всего, Запрос занял 0.2913 сек.)

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;
#Отображает строки 0 - 29 ( 495 всего, Запрос занял 0.0146 сек.)

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

[HandKot]

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

Дабы быть непредвзятым во всем, то сравнивал все три варианта
Исходные данные:
Таблица - 2046 записей
id_work - постоянный

1 случай: allo - всегда больше 0, кол-во периодов 2046
Dimka - 3272 ms
RXL - 50 ms
HandKot - 8ms

2 случай: allo - каждый 10 равен 0, кол-во периодов 1433
Dimka - 2481 ms
RXL - 60 ms
HandKot - 8 ms

3 случай: allo - всегда равен 0, кол-во периодов 0
Dimka - 2 ms
RXL - 1 ms
HandKot - 7 ms

все эксперименты проводил тут http://sqlfiddle.com/#!2/042f9/1/0

эти данные подтвердил и автор топика (пост выше)

ЗЫЖ могу предположить, что на некотором наборе данных, мой запрос будет либо проигрывать в скорости, либо выдавать неверный результат, либо и то и другое. Но приемущество его в том, что он всегда использует всего один скан таблицы с постоянным временем исполнения, тогда как вариант от RLX  использует 2 скана, по поводу Вашего варианта, то там сложно сказать кол-во сканов (оно зависит от распределения данных по таблице)

ЗЗЫЖ

Запятая во from - это cross join (полное декартово произведение) в отличие от inner join в остальных примерах, где результаты этого декартова произведения подвергаются обработке горизонтальным фильтром.

это понятно, но надо учитывать и кол-во получаемых строк. Inner Join хорош когда в условии соединения идет знак равенства (применяется hash match). При неопределенном условии (операторы больше и меньше) мы , скорее всего перейдем на loop join (при большом кол-ве строк, ну очень затратный метод)

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

кол-ве групп будет при таблице в 1000 строк = cross join -> 1004 групп на 3000 строках с кучей дубликатов. Агрегатные функции одни из самых быстрых в БД


в общем как-то так

[RXL]

Запрос занял 0.0240 сек.

На малых значениях это не показатель. Поищи в мануале «query profile». Это позволит делать более точную диагностику.

[Dimka]

Индексы и их эффективность тоже важны при выборе между поиском по хэш и перебором. Так что индексы следует описывать, равно, как и знать характер данных.

Исходные данные:
Таблица - 2046 записей
id_work - постоянный

1 случай: allo - всегда больше 0, кол-во периодов 2046

А будто бы должно быть меньше, чем исходных записей.

[HandKot]

А будто бы должно быть меньше, чем исходных записей.

точно описался, в таблице 2048.
если перейти по ссылке, то там есть все изыскания (только копируйте ее полностью)

[Dimka]

HandKot, не ходил: пусть автор темы ходит, если ему охота.