Форум пользователей MySQL

pimkman · 15.03.2015 17:30:54

Всем доброго времени суток!

Изначально имеется следующая таблица (события триггеров), отсортированная по triggerid и time:

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

SET @next_date=NULL, @id=0;

SELECT

t.triggerid,

t.eventid,

t.time,

t.`value`,

if(@id=t.triggerid, @next_date, (NULL) AND (@id:=t.triggerid)) `nexttime`,

@next_date:=t.time

FROM

(

<...> --Формируется таблица описанная выше

) as t

Получается следующая таблица:

http://s56.radikal.ru/i151/1503/a0/b0105588f475.png

Возникло несколько вопросов:
1) Как убрать вывод столбца @next_date:=t.time?
2) Как сделать, чтобы при максимальной дате вместо null выводилось текущее время. Когда прописываю NOW() или CURRENT_TIMESTAMP выводит почему-то "1"
3) Столбец nexttime почему-то получается в формате BLOB, поэтому при выводе Navicat пишет (BLOB) вместо значения. При экспорте все нормально. Объясните пожалуйста, почему так происходит и как это исправить?

Также есть одна задача:
Нужно для каждого eventid, при условии, что nexttime и time разные дни (TO_DAYS(nexttime)>TO_DAYS(time)), разбить запись на кол-во дней. Например, из такой записи

http://i017.radikal.ru/1503/f5/e645689abaa3.png

мне нужно получить 4 записи:

http://s017.radikal.ru/i438/1503/52/6ee73914b97d.png

vasya · 15.03.2015 17:54:28

pimkman написал:
1) Как убрать вывод столбца @next_date:=t.time?
2) Как сделать, чтобы при максимальной дате вместо null выводилось текущее время. Когда прописываю NOW() или CURRENT_TIMESTAMP выводит почему-то "1"
3) Столбец nexttime почему-то получается в формате BLOB, поэтому при выводе Navicat пишет (BLOB) вместо значения. При экспорте все нормально. Объясните пожалуйста, почему так происходит и как это исправить?

1,2

SELECT triggerid, eventid, `time`, `value`, IFNULL(nexttime,now()) FROM (ваш запрос) x;

3 Попробуйте
SET @next_date='', @id=0;
ну и тогда вместо
IFNULL(nexttime,now())
нужно будет
IF(nexttime='',now(),nexttime)

pimkman · 15.03.2015 19:57:56

vasya,

Спасибо большое! Все работает, как часы. Только в 3 случае прописывать IF(nexttime='',now(),nexttime) не нужно, ведь я в запросе проставляю NULL.

Теперь бы еще задачку решить...

vasya · 15.03.2015 20:46:04

MariaDB [test]> select * from test;

+------+---------------------+---------------------+

| id   | time                | nexttime            |

+------+---------------------+---------------------+

|    1 | 2015-03-12 20:00:29 | 2015-03-15 14:11:29 |

|    2 | 2015-03-04 20:00:29 | 2015-03-04 14:11:29 |

+------+---------------------+---------------------+

2 rows in set (0.00 sec)

MariaDB [test]> select * from days; -- вспомогательная таблица 

+------+

| i    |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|    5 |

|    6 |

|    7 |

|    8 |

|    9 |

|   10 |

|   11 |

|   12 |

|   13 |

|   14 |

|   15 |

|   16 |

|   17 |

|   18 |

|   19 |

|   20 |

|   21 |

|   22 |

|   23 |

|   24 |

|   25 |

|   26 |

|   27 |

|   28 |

|   29 |

|   30 |

|   31 |

+------+

31 rows in set (0.00 sec)

MariaDB [test]> select test.*

    -> from test, days

    -> where day(`time`)<=i and day(nexttime)>=i

    -> order by id,i;

+------+---------------------+---------------------+

| id   | time                | nexttime            |

+------+---------------------+---------------------+

|    1 | 2015-03-12 20:00:29 | 2015-03-15 14:11:29 |

|    1 | 2015-03-12 20:00:29 | 2015-03-15 14:11:29 |

|    1 | 2015-03-12 20:00:29 | 2015-03-15 14:11:29 |

|    1 | 2015-03-12 20:00:29 | 2015-03-15 14:11:29 |

|    2 | 2015-03-04 20:00:29 | 2015-03-04 14:11:29 |

+------+---------------------+---------------------+

5 rows in set (0.00 sec)

Теперь последнюю выборку обходите с помощью переменных и если day(`time`) < day(nexttime), то меняете время на 00:00 и 23:59. Переменные нужны, чтобы корректно обработать первый и последний элемент в группе.

Кто-нибудь знает более изящное решение?

pimkman · 15.03.2015 22:05:08

А можно как-нибудь в селекте создать таблицу чисел? В моем случае нужно таблицу от 0 до максимальной разницы дней между датами.

vasya · 15.03.2015 22:23:15

можно
.. (select 1 unoin select 2 union .... select 31) as days ..

pimkman · 15.03.2015 22:28:17

А как-нибудь автоматически загенерить от 0 до n можно? Как в Oracle - CONNECT BY LEVEL <= 10 или в MS SQL - FROM master..spt_values
Я вот пытаюсь найти в интернете, как так сделать, но пока безуспешно...

vasya · 15.03.2015 22:34:06

До mariaDB 10.0 нельзя, после
https://mariadb.com/kb/en/mariadb/sequence/

vasya · 15.03.2015 22:56:54

Можно без переменных

MariaDB [test]> select * from

    -> (select *

    ->      from test, days

    ->      where day(`time`)<=i and day(nexttime)>=i) t

    -> join

    -> (select id, count(*) co, min(i) mi, max(i) ma

    ->      from test, days

    ->      where day(`time`)<=i and day(nexttime)>=i

    ->      group by id) t1

    -> using(id)

    ->      order by id,i;

+------+---------------------+---------------------+------+----+------+------+

| id   | time                | nexttime            | i    | co | mi   | ma   |

+------+---------------------+---------------------+------+----+------+------+

|    1 | 2015-03-12 20:00:29 | 2015-03-15 14:11:29 |   12 |  4 |   12 |   15 |

|    1 | 2015-03-12 20:00:29 | 2015-03-15 14:11:29 |   13 |  4 |   12 |   15 |

|    1 | 2015-03-12 20:00:29 | 2015-03-15 14:11:29 |   14 |  4 |   12 |   15 |

|    1 | 2015-03-12 20:00:29 | 2015-03-15 14:11:29 |   15 |  4 |   12 |   15 |

|    2 | 2015-03-04 20:00:29 | 2015-03-04 14:11:29 |    4 |  1 |    4 |    4 |

+------+---------------------+---------------------+------+----+------+------+

Теперь вместо * пишите if которые будут сравнивать i,mi,ma и формировать нужные даты.
if i =mi, то time без изменений, иначе часы обнуляются и в качестве дня берется значение i
аналогично для nexttime

P.S. Этот пример упрощенный - предполагает, что начальный и конечный период в пределах 1 месяца.

pimkman · 16.03.2015 00:17:49

Спасибо Вам большое! Очень помогли.

Получилось следующее:

DROP TEMPORARY TABLE IF EXISTS temp, days;

SET @next_date='', @id=0;

CREATE TEMPORARY TABLE temp

SELECT 

m.triggerid,

m.eventid,

m.curtime,

m.`value`,

IFNULL(m.nexttime,now()) nexttime,

IFNULL(TO_DAYS(nexttime)-TO_DAYS(curtime),TO_DAYS(now())-TO_DAYS(curtime)) as daydiff

FROM

(

SELECT

t.*,

if(@id=t.triggerid, @next_date, (null) AND (@id:=t.triggerid)) `nexttime`,

@next_date:=t.curtime

FROM

(

<...> --Формируется таблица описанная выше

) as t

)m;

CREATE TEMPORARY TABLE days

SELECT  @curRow := @curRow + 1 AS daycnt

FROM    `events`

JOIN    (SELECT @curRow := -1) r

WHERE   @curRow<(SELECT MAX(daydiff) FROM temp);

SELECT

triggerid,

eventid,

`value`,

CASE

WHEN daycnt=0 THEN curtime

ELSE DATE_ADD(TIMESTAMP(DATE(curtime)),INTERVAL daycnt DAY)

END curtime,

CASE

WHEN daydiff <> daycnt THEN TIMESTAMP(DATE_ADD(DATE_ADD(DATE(nexttime),INTERVAL (-daydiff+daycnt+1) DAY),INTERVAL -1 SECOND))

ELSE nexttime

END nexttime

FROM temp, days

WHERE 

daydiff>=daycnt

Форум пользователей MySQL

#1 15.03.2015 17:30:54

Разбить одну запись на несколько

#2 15.03.2015 17:54:28

Re: Разбить одну запись на несколько

pimkman написал:

#3 15.03.2015 19:57:56

Re: Разбить одну запись на несколько

#4 15.03.2015 20:46:04

Re: Разбить одну запись на несколько

#5 15.03.2015 22:05:08

Re: Разбить одну запись на несколько

#6 15.03.2015 22:23:15

Re: Разбить одну запись на несколько

#7 15.03.2015 22:28:17

Re: Разбить одну запись на несколько

#8 15.03.2015 22:34:06

Re: Разбить одну запись на несколько

#9 15.03.2015 22:56:54

Re: Разбить одну запись на несколько

#10 16.03.2015 00:17:49

Re: Разбить одну запись на несколько

Board footer