Имеется табличка:
host_id integer,
event_id integer,
created timestamp;
--example---
1 1 10:12
1 2 11:20
1 1 12:50
---
event_id=1 on
event_id=2 off
итого, мы получаем:
1 хост в 10:12 — on, в 11:20 off и с 12:50 по сей момент находится в состоянии on
В итоге очень нужно получить это ввиде таблицы:
host_id created_on created_off
--example--
1 10:12 11:20
1 12:50 NULL
Последовательность действий контролируется тригером и они возможны только в порядке
on-off-on-off-on-...
порядка действий в on-on-off-off или похожих — не возможно.
Уже замучался с разными вариациями self join итд.
Помогите плз.
Здравствуйте, Darkman_VLT, Вы писали:
D_V>Имеется табличка D_V>В итоге очень нужно получить это ввиде таблицы: D_V>host_id created_on created_off D_V>--example-- D_V>1 10:12 11:20 D_V>1 12:50 NULL
select t1.host_id, t1.created as created_on, min( t2.created ) as created_off
from t t1 left join t t2
on t2.host_id = t1.host_id
and t2.created > t1.created
where
t1.event_id = 1
and isnull( t2.event_id, 2 ) = 2
group by
t1.host_id, t1.created
D_V>P.S. SQL в реализации Postgres'a
Проверено на mssql. В Postgres должно быть также
H>>select t1.host_id, t1.created as created_on, min( t2.created ) as created_off
H>>from t t1 left join t t2
H>>on t2.host_id = t1.host_id
H>>and t2.created > t1.created
H>>where
H>>t1.event_id = 1
H>>and isnull( t2.event_id, 2 ) = 2
H>>group by
H>>t1.host_id, t1.created
H>>
D_V>>>P.S. SQL в реализации Postgres'a
D_V>А ещё более не понятна конструкция isnull( t2.event_id, 2 ) = 2
Это означает, что если t2.event_id будет NULL, то вместо него подставить 2 и сравниться с 2. Т.о. здесь приводиться t2.event_id к 2 ибо значения могут быть числовыми (0, 1, 2, ...100,...) и NULL и пожтому с NULL сравнивать не получиться. Значит надо привести NULL к числу, которое нам необходимо. В Oracle isnull имеет синоним NVL.
FR>Это означает, что если t2.event_id будет NULL, то вместо него подставить 2 и сравниться с 2. Т.о. здесь приводиться t2.event_id к 2 ибо значения могут быть числовыми (0, 1, 2, ...100,...) и NULL и пожтому с NULL сравнивать не получиться. Значит надо привести NULL к числу, которое нам необходимо. В Oracle isnull имеет синоним NVL.
--Это означает, что если t2.event_id будет NULL, то вместо него подставить 2 и сравниться с 2.
Вообще не понял
Зачем сравнивать с 2, если мы уже к 2йке привели?
H>>select t1.host_id, t1.created as created_on, min( t2.created ) as created_off
H>>from t t1 left join t t2
H>>on t2.host_id = t1.host_id
H>>and t2.created > t1.created
H>>where
H>>t1.event_id = 1
H>>and isnull( t2.event_id, 2 ) = 2
H>>group by
H>>t1.host_id, t1.created
H>>
D_V>Мне не совсем понятно логика работы запроса. D_V>А ещё более не понятна конструкция isnull( t2.event_id, 2 ) = 2 isnull( t2.event_id, 2 ) = 2 отрабатывает ситуацию 1 12:50 NULL
если после on, не последовал off, то в t2 не будет соответствия для t1, и соответственно t2.event_id будет нуллом.
D_V>Не могли бы обьяснить, более точнее по какому принципу Вы делаете LEFT JOIN (t1.host_id=t2.host_id — понятно )
Ну а что конкретно неясно c left join? t2.created > t1.created означает, что off по времени идет после on
Вообще-то запрос довольно примитивный. Проще самому поизменять его и понять, что и как он выбирает.
Здравствуйте, Darkman_VLT, Вы писали:
D_V>Имеется табличка: D_V>host_id integer, D_V>event_id integer, D_V>created timestamp;
D_V>--example--- D_V>1 1 10:12 D_V>1 2 11:20 D_V>1 1 12:50 D_V>--- D_V>event_id=1 on D_V>event_id=2 off
D_V>итого, мы получаем: D_V>1 хост в 10:12 — on, в 11:20 off и с 12:50 по сей момент находится в состоянии on
D_V>В итоге очень нужно получить это ввиде таблицы: D_V>host_id created_on created_off D_V>--example-- D_V>1 10:12 11:20 D_V>1 12:50 NULL
D_V>Последовательность действий контролируется тригером и они возможны только в порядке D_V>on-off-on-off-on-... D_V>порядка действий в on-on-off-off или похожих — не возможно.
D_V>Уже замучался с разными вариациями self join итд. D_V>Помогите плз.
D_V>P.S. SQL в реализации Postgres'a
select t1.host_id, t1.created as created_on, min( t2.created ) as created_off
from t t1 left join t t2
on t2.host_id = t1.host_id
and t2.created > t1.created
where
t1.event_id = 1
and isnull( t2.event_id, 2 ) = 2
group by
t1.host_id, t1.created
Работать не будет
Помогите, исправить.
Правка:
and t2.created > t1.created
на >= не даёт нужного эфекта, понятное дело.
А как привязатся к event_id (порядковому номеру, primary index) чёт не получается.
)
