Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Выборка строк при изменении значения в одном из полей  [new]
мардахай
Guest
Всем привет!

Есть таблица типа:

ID    DTime    SPEED

1     12.30     50
1     13.40     40
1     15.00     45
1     16.30     0
1     17.00     0
1     18.30     25
1     19.45     30
1     22.30     40
1     23.00     0
1     23.30     40


Как получить те строки, когда значение одной из колонок (в данном случае SPEED) менялось в смысле равенства нулю (т.е., нуль/не нуль).
Т.е., получить такую таблицу:
ID    DTime    SPEED

1     12.30     50
1     16.30     0
1     18.30     25
1     23.00     0
1     23.30     40


Это необходимо для отчета о движениях и стоянках автотранспорта - определение времени движения и отсутствия движения.
31 окт 12, 16:52    [13403727]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк при изменении значения в одном из полей  [new]
aleks2
Guest
declare @t table (ID int,  DTime decimal(8,2), SPEED int);

insert @t
select 1,     12.30,     50
union all
select 1,     13.40,     40
union all
select 1,     15.00,     45
union all
select 1,     16.30,     0
union all
select 1,     17.00,     0
union all
select 1,     18.30,     25
union all
select 1,     19.45,     30
union all
select 1,     22.30,     40
union all
select 1,     23.00,     0
union all
select 1,     23.30,     40
;

declare @tt table (ID int,  DTime decimal(8,2), SPEED int, [01] int, n int identity primary key clustered);

insert @tt(ID,  DTime, SPEED, [01])
select ID,  DTime, SPEED, CAST(SPEED as bit)
from @t
order by ID,  DTime;

select t1.*
from @tt t1 left outer join @tt t2 on t1.ID=t2.ID and t1.n-1=t2.n
where t1.[01] <> t2.[01] or t2.[01] is null;
31 окт 12, 19:26    [13404625]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк при изменении значения в одном из полей  [new]
мардахай
Guest
aleks2, спасибо огромное! Все прекрасно работает!
31 окт 12, 19:54    [13404746]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк при изменении значения в одном из полей  [new]
Добрый Э - Эх
Guest
Как вариант для сервера 2012, новые фичи (аналитические lead/lag):
--
-- Тестовый набор данных:
with
  t(ID ,   DTime,    SPEED) as 
    (
      select 1,     12.30,     50
      union all
      select 1,     13.40,     40
      union all
      select 1,     15.00,     45
      union all
      select 1,     16.30,     0
      union all
      select 1,     17.00,     0
      union all
      select 1,     18.30,     25
      union all
      select 1,     19.45,     30
      union all
      select 1,     22.30,     40
      union all
      select 1,     23.00,     0
      union all
      select 1,     23.30,     40
    )
--
-- Основной запрос:
select ID,DTime,SPEED
  from (
         select t.* ,
                case 
                  when speed = 0 and lag(speed)over(partition by id order by dtime) != 0 
                    or lag(speed)over(partition by id order by dtime) = 0 and speed != 0 
                    or lag(speed)over(partition by id order by dtime) is null
                  then 1 
                end as flag
           from t
       ) v
 where flag = 1
31 окт 12, 20:28    [13404884]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк при изменении значения в одном из полей  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх
--
-- Основной запрос:
select ID,DTime,SPEED
from (
select t.* ,
case
when speed = 0 and lag(speed)over(partition by id order by dtime) != 0
or lag(speed)over(partition by id order by dtime) = 0 and speed != 0
or lag(speed)over(partition by id order by dtime) is null
then 1
end as flag
from t
) v
where flag = 1

[/src]


Вот так оно лаконичней будет:
--
-- Основной запрос:
select ID,DTime,SPEED
  from (
         select t.* ,
                lag(speed)over(partition by id order by dtime) as x_speed
           from t
       ) as v
 where x_speed is null 
    or x_speed  = 0 and speed != 0
    or x_speed != 0 and speed  = 0
31 окт 12, 21:06    [13405052]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк при изменении значения в одном из полей  [new]
Добрый Э - Эх
Guest
Что-то вдруг TOP n WITH TIES вспомнился:
--
-- Основной запрос:
select top 1 with ties t.* 
  from t
 order by case 
            when lag(speed)over(partition by id order by dtime) is null 
              or lag(speed)over(partition by id order by dtime)  = 0 and speed != 0
              or lag(speed)over(partition by id order by dtime) != 0 and speed  = 0 
            then 1 
            else 2 
          end
31 окт 12, 21:11    [13405069]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк при изменении значения в одном из полей  [new]
мардахай
Guest
Добрый Э - Эх
Что-то вдруг TOP n WITH TIES вспомнился:
--
-- Основной запрос:
select top 1 with ties t.* 
  from t
 order by case 
            when lag(speed)over(partition by id order by dtime) is null 
              or lag(speed)over(partition by id order by dtime)  = 0 and speed != 0
              or lag(speed)over(partition by id order by dtime) != 0 and speed  = 0 
            then 1 
            else 2 
          end


Вот это очень красивый и компактный запрос! Пора обновиться до сервера 2012... :)
Добрый Э - Эх, спасибо!
31 окт 12, 21:45    [13405164]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк при изменении значения в одном из полей  [new]
init.ora
Member

Откуда: гетто
Сообщений: 317
select  id,dtime,speed
from( 
	select z1.*, row_number()over(partition by sp,r order by dtime) rc
	from(
		select t.* ,case when speed>0 then 1 else 0 end sp,
		row_number()over(order by dtime)-row_number()over(partition by case when speed>0 then 1 else 0 end order by dtime) r
		from @t t
	)z1
)z2
where rc=1
order by dtime


вот одно чтение таблицы для 2005-2008
31 окт 12, 22:44    [13405326]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк при изменении значения в одном из полей  [new]
init.ora
Member

Откуда: гетто
Сообщений: 317
вроде должно работать
31 окт 12, 22:44    [13405328]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк при изменении значения в одном из полей  [new]
мардахай
Guest
Забыл одну деталь: желательно получить среднюю скорость на каждом участке, т.е., из таблицы
ID    DTime    SPEED

1     12.30     50
1     13.40     40
1     15.00     45
1     16.30     0
1     17.00     0
1     18.30     25
1     19.45     30
1     22.30     40
1     23.00     0
1     23.30     40

получить таблицу
ID    DTime    SPEED

1     12.30     (50 + 40 + 45)/3 = 45
1     16.30     0
1     18.30     31 
1     23.00     0
1     23.30     40

Сейчас скорость выдается на начало движения на участке, а надо бы среднюю скорость...
1 ноя 12, 02:14    [13405773]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк при изменении значения в одном из полей  [new]
Добрый Э - Эх
Guest
мардахай
Забыл одну деталь: желательно получить среднюю скорость на каждом участке

Заюзать схлопывание интервалов методом инварианта группы и посчитать нужные агрегаты:
--
-- Тестовый набор данных:
with
  t(ID ,   DTime,    SPEED) as 
    (
      select 1,     12.30,     50
      union all
      select 1,     13.40,     40
      union all
      select 1,     15.00,     45
      union all
      select 1,     16.30,     0
      union all
      select 1,     17.00,     0
      union all
      select 1,     18.30,     25
      union all
      select 1,     19.45,     30
      union all
      select 1,     22.30,     40
      union all
      select 1,     23.00,     0
      union all
      select 1,     23.30,     40
    )
--
-- Основной запрос:
select ID, min(DTime) as beg_dtime, max(dtime) as end_dtime, avg(SPEED) as avg_speed
  from (
         select t.* ,
                row_number() over(partition by id, case speed when 0 then 0 else 1 end
                                      order by dtime) -
                row_number() over(partition by id order by dtime) as grp_id
           from t
       ) as v
 group by id, grp_id, case speed when 0 then 0 else 1 end
 order by min(dtime)
1 ноя 12, 04:24    [13405951]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк при изменении значения в одном из полей  [new]
Добрый Э - Эх
Guest
Ну или же опять обратиться к LEAD/LAG и реализовать запрос методом "начала групп":
--
-- Тестовый набор данных:
with
  t(ID ,   DTime,    SPEED) as 
    (
      select 1,     12.30,     50
      union all
      select 1,     13.40,     40
      union all
      select 1,     15.00,     45
      union all
      select 1,     16.30,     0
      union all
      select 1,     17.00,     0
      union all
      select 1,     18.30,     25
      union all
      select 1,     19.45,     30
      union all
      select 1,     22.30,     40
      union all
      select 1,     23.00,     0
      union all
      select 1,     23.30,     40
    )
--
-- Основной запрос:
select ID, min(DTime) as beg_dtime, max(dtime) as end_dtime, avg(SPEED) as avg_speed
  from (
         select v0.*, sum(start_of_group) over(partition by id order by dtime) as grp_id
           from (
                  select t.*, 
                         case
                           when 
                             case speed
                               when 0 then 0
                               else 1
                             end = lag(case speed when 0 then 0 else 1 end)
                                     over(partition by id order by dtime)
                           then 0
                           else 1
                         end as start_of_group
                    from t
                ) as v0
       ) as v1
 group by id, grp_id
 order by min(dtime)
1 ноя 12, 04:32    [13405956]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк при изменении значения в одном из полей  [new]
aleks2
Guest
Фигня это фсе. На больших размерах данных тупо просрет временным таблицам

declare @t table (ID int,  DTime decimal(8,2), SPEED int);

insert @t
select 1,     12.30,     50
union all
select 1,     13.40,     40
union all
select 1,     15.00,     45
union all
select 1,     16.30,     0
union all
select 1,     17.00,     0
union all
select 1,     18.30,     25
union all
select 1,     19.45,     30
union all
select 1,     22.30,     40
union all
select 1,     23.00,     0
union all
select 1,     23.30,     40
;

declare @tt table (ID int,  DTime decimal(8,2), SPEED int, [01] int, n int identity primary key clustered);

insert @tt(ID,  DTime, SPEED, [01])
select ID,  DTime, SPEED, CAST(SPEED as bit)
from @t
order by ID,  DTime;

declare @ttt table (ID int,  DTime decimal(8,2), [01] int, n int , nn int identity primary key clustered);

insert @ttt(ID,  DTime, [01], n)
select t1.ID,  t1.DTime, t1.[01], t1.n
from @tt t1 left outer join @tt t2 on t1.ID=t2.ID and t1.n-1=t2.n
where t1.[01] <> t2.[01] or t2.[01] is null
order by n;

select t1.ID,  t1.DTime, t1.[01], (select avg(SPEED) from @tt where n between t1.n and isnull(t2.n-1, t1.n)) avgSPEED
from @ttt t1 
left outer join @ttt t2 on t1.ID=t2.ID and t1.nn+1=t2.nn
1 ноя 12, 07:37    [13406008]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк при изменении значения в одном из полей  [new]
Добрый Э - Эх
Guest
aleks2
Фигня это фсе. На больших размерах данных тупо просрет временным таблицам
В таких категоричных утверждениях, кмк, нужно либо "IMHO" добавлять, либо приводить убедительные доказательства сказанному.
1 ноя 12, 07:44    [13406012]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк при изменении значения в одном из полей  [new]
мардахай
Guest
Спасибо всем за помощь! Огромное спасибо!
1 ноя 12, 12:24    [13407550]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк при изменении значения в одном из полей  [new]
aleks2
Guest
Добрый Э - Эх
aleks2
Фигня это фсе. На больших размерах данных тупо просрет временным таблицам
В таких категоричных утверждениях, кмк, нужно либо "IMHO" добавлять, либо приводить убедительные доказательства сказанному.

Всем, кому должен, прощаю.

ЗЫ. Мне то какая нужда доказательства собирать.
1 ноя 12, 16:14    [13409799]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк при изменении значения в одном из полей  [new]
Добрый Э - Эх
Guest
aleks2
Мне то какая нужда доказательства собирать.

Ну тогда отвечу тебе твоими же словами:
Фигня это фсе. На больших размерах данных временные таблицы тупо просрут аналитике
1 ноя 12, 18:16    [13410691]     Ответить | Цитировать Сообщить модератору
 Re: Выборка строк при изменении значения в одном из полей  [new]
Добрый Э - Эх
Guest
А на просьбу привести доказательства сказанному скромно замечу:

ЗЫ. Мне то какая нужда доказательства собирать.
1 ноя 12, 18:17    [13410698]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить