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

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