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

Откуда: Киев
Сообщений: 225
Доброго дня всем.
Помогите пожалуйста разобраться с запросом.
Есть исходные данные:
create table #a (id int, contact_id int, status int, datestart datetime, dateend datetime )
insert into #a values
(30147,	110840,	0,	'2011-03-22 16:21:18.977',	'2011-03-22 16:21:18.977'),
(30147,	110840,	0,	'2011-03-22 16:21:18.977',	'2011-03-23 16:04:20.590'),
(30147,	110840,	0,	'2011-04-18 17:20:03.577',	'2011-04-18 18:25:36.637'),
(30147,	110840,	1,	'2011-04-18 18:25:36.637',	'2011-04-19 18:26:24.280'),
(30147,	110840,	0,	'2011-04-19 18:26:24.280',	'2011-04-27 08:23:55.480'),
(30147,	110840,	0,	'2011-04-27 08:23:55.480',	'2011-07-01 08:24:04.090'),
(30147,	110840,	1,	'2011-07-01 08:24:04.090',	'2012-08-29 03:05:33.310')
select * from #a

В результате запроса необходимо получить следующие данные:
(30147, 110840, 0, '2011-03-22 16:21:18.977', '2011-04-18 18:25:36.637')
(30147, 110840, 1, '2011-04-18 18:25:36.637', '2011-04-19 18:26:24.280')
(30147, 110840, 0, '2011-04-19 18:26:24.280', '2011-07-01 08:24:04.090')
(30147, 110840, 1, '2011-07-01 08:24:04.090', '2012-08-29 03:05:33.310')

Т.е. необходимо группировать данные по Id, contact_id. А дальше "схлопнуть" данные по временному интервалу в зависимости от status.
Заранее благодарен за любую помощь.
25 май 12, 16:28    [12616666]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
TeufelDan
Member

Откуда: оттуда
Сообщений: 61
Сергей.
Попробуйте использовать MIN(datestart), MAX(dateend) ... ну и про ROW_NUMBER не забудьте :)
25 май 12, 16:49    [12616849]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
kirser
Member

Откуда: Киев
Сообщений: 225
Изначально, я так и делал.
Но в результате получил две строки: мин/мак по 1 и мин мак по 0.
А необходимо получать мин/макс дат по каждому изменению статуса.
А что на счет row_number? Как он может помочь?
25 май 12, 16:56    [12616924]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
tt1
Guest
чтото в этом роде, нужно модифицировать под данные, можно расширить под др задачи

exec sp_drop_table '#a1'
select ROW_NUMBER() over(partition by id,contact_id order by datestart) i
      ,*
  into #a1
  from #a

  
--собираем начала периодов
exec sp_drop_table '#a2'
select a.id
      ,a.contact_id 
      ,a.status 
      ,a.datestart 
      ,ROW_NUMBER() over(partition by a.id,a.contact_id order by a.datestart) j
  into #a2
  from #a1 a
  left join #a1 b --предыдущая запись
    on a.contact_id = b.contact_id
   and a.id = b.id
   and a.i = b.i + 1
where b.status is null
   or a.status <> b.status
group by a.id
      ,a.contact_id 
      ,a.status 
      ,a.datestart 
      
--собираем концы периодов
exec sp_drop_table '#a3'
select a.id
      ,a.contact_id 
      ,a.status 
      ,a.dateend 
      ,ROW_NUMBER() over(partition by a.id,a.contact_id order by a.dateend) j
  into #a3
  from #a1 a
  left join #a1 b --след запись
    on a.contact_id = b.contact_id
   and a.id = b.id
   and a.i = b.i - 1
where b.status is null
   or a.status <> b.status
group by a.id
      ,a.contact_id 
      ,a.status 
      ,a.dateend 
      
      
select a.id
      ,a.contact_id
      ,a.status
      ,a.datestart
      ,b.dateend
  from #a2 a 
  join #a3 b
    on a.id = b.id
   and a.contact_id = b.contact_id      
   and a.j = b.j
25 май 12, 17:01    [12616986]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Вот уже боянистое решение

select min(tt.datestart), max(isNull(ev.val,tt.dateend) )
  from (
         select  datestart,dateend,
                row_number() over(partition by  id, contact_id,status order by datestart)-
                row_number() over(partition by id, contact_id order by datestart) as grp_id
           from #a
           
       ) as tt
       cross apply (select val=MAX(dateend) from #a  where datestart <=tt.datestart)ev

 group by grp_id
 order by min(tt.datestart)

имхо пора его в фак заносить, со всеми вариациями
25 май 12, 17:01    [12616990]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
kirser
Member

Откуда: Киев
Сообщений: 225
Близко к необходимому результату.
Всем огромное спасибо за помощь
25 май 12, 17:25    [12617208]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
invm
Member

Откуда: Москва
Сообщений: 9833
with x as
(
 select
  *,
  row_number() over (partition by id, contact_id order by datestart) -
  row_number() over (partition by id, contact_id, status order by datestart) as p
 from
  #a
),
y as
(
 select top (1) with ties
  id, contact_id, status, datestart, dateend
 from
  x
 order by
  row_number() over (partition by p order by datestart)
)
select
 *
from
 y
order by
 datestart;
25 май 12, 17:31    [12617260]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
kirser,
имхо и у invm и у меня неправильные запросы, при появлении новых групп по id , contact_id дают неправильный результат.
К сожалению только после выходных могу чего-нибудь еще предложить.
25 май 12, 18:03    [12617515]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
kirser
Member

Откуда: Киев
Сообщений: 225
Только собирался об этом написать.
Добавил новые группы (Id и Contact_id) и результат "поплыл".
А в моей ситуации как раз необходимо учитывать множество групп.
Не могли бы вы помочь подправить скрипт под новые обстоятельства?
Спасибо
25 май 12, 18:10    [12617562]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
invm
Member

Откуда: Москва
Сообщений: 9833
kirser,

Всего лишь нужно немножко подумать:
declare @t table (id int, contact_id int, status int, datestart datetime, dateend datetime);

insert into @t values
(30147,	110840,	0,	'20110322 16:21:18.977',	'20110322 16:21:18.977'),
(30147,	110840,	0,	'20110322 16:21:18.977',	'20110323 16:04:20.590'),
(30147,	110840,	0,	'20110418 17:20:03.577',	'20110418 18:25:36.637'),
(30147,	110840,	1,	'20110418 18:25:36.637',	'20110419 18:26:24.280'),
(30147,	110840,	0,	'20110419 18:26:24.280',	'20110427 08:23:55.480'),
(30147,	110840,	0,	'20110427 08:23:55.480',	'20110701 08:24:04.090'),
(30147,	110840,	1,	'20110701 08:24:04.090',	'20120829 03:05:33.310'),
(30148,	110841,	0,	'20110322 16:21:18.977',	'20110322 16:21:18.977'),
(30148,	110841,	0,	'20110322 16:21:18.977',	'20110323 16:04:20.590'),
(30148,	110841,	0,	'20110418 17:20:03.577',	'20110418 18:25:36.637'),
(30148,	110841,	1,	'20110418 18:25:36.637',	'20110419 18:26:24.280'),
(30148,	110841,	0,	'20110419 18:26:24.280',	'20110427 08:23:55.480'),
(30148,	110841,	0,	'20110427 08:23:55.480',	'20110701 08:24:04.090'),
(30148,	110841,	1,	'20110701 08:24:04.090',	'20120829 03:05:33.310');

with x as
(
 select
  *,
  row_number() over (partition by id, contact_id order by datestart) -
  row_number() over (partition by id, contact_id, status order by datestart) as p
 from
  @t
),
y as
(
 select top (1) with ties
  id, contact_id, status, datestart, dateend
 from
  x
 order by
  row_number() over (partition by id, contact_id, p order by datestart)
)
select
 *
from
 y
order by
 id, contact_id, datestart;
25 май 12, 18:21    [12617609]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
kirser
Member

Откуда: Киев
Сообщений: 225
invm,
в результате получим по группам, таким как :
(30147,	110840,	0,	'2011-03-22 16:21:18.977',	'2011-03-22 16:21:18.977'),
(30147,	110840,	0,	'2011-03-22 16:21:18.977',	'2011-03-23 16:04:20.590'),
(30147,	110840,	0,	'2011-04-18 17:20:03.577',	'2011-04-18 18:25:36.637'),

результирующее значение:
(30147,	110840,	0,	'2011-03-22 16:21:18.977',	'2011-03-22 16:21:18.977'),

а необходимо получить :

(30147,	110840,	0,	'2011-03-22 16:21:18.977',	'2011-04-18 18:25:36.637'),


Взал скрипт tt1 - > работает как надо.
25 май 12, 18:48    [12617731]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
invm
Member

Откуда: Москва
Сообщений: 9833
Мде. Не хотите вы думать, вам готовенькое нужно...
А ведь все так просто:
with x as
(
 select
  *,
  row_number() over (partition by id, contact_id order by datestart) -
  row_number() over (partition by id, contact_id, status order by datestart) as p
 from
  @t
),
y as
(
 select top (1) with ties
  id, contact_id, status, datestart, max(dateend) over (partition by id, contact_id, p) as dateend
 from
  x
 order by
  row_number() over (partition by id, contact_id, p order by datestart)
)
select
 *
from
 y
order by
 id, contact_id, datestart;
25 май 12, 19:21    [12617878]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
invm
Member

Откуда: Москва
Сообщений: 9833
Или уж совсем просто:
with x as
(
 select
  *,
  row_number() over (partition by id, contact_id order by datestart) -
  row_number() over (partition by id, contact_id, status order by datestart) as p
 from
  @t
)
select
 id, contact_id, status, min(datestart), max(dateend)
from
 x
group by
 id, contact_id, status, p
order by
 id, contact_id, min(datestart);
25 май 12, 19:47    [12618010]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Привет всем,
сегодня со свежими мыслями наконец то получился правильный запрос
declare @t table  (id int, contact_id int, status int, datestart datetime, dateend datetime )
insert into @t 
select	30148,	110840,	0,	convert(datetime,'2011-03-22 16:21:18.977',121),	convert(datetime,'2011-03-22 16:21:18.977',121)
union select 30147,	110840,	0,	convert(datetime,'2011-03-22 16:21:18.977',121),	convert(datetime,'2011-03-23 16:04:20.590',121)
union select 30149,	110840,	0,	convert(datetime,'2011-04-18 17:20:03.577',121),	convert(datetime,'2011-04-18 18:25:36.637',121)
union select 30147,	110840,	1,	convert(datetime,'2011-04-18 18:25:36.637',121),	convert(datetime,'2011-04-19 18:26:24.280',121)
union select 30147,	110840,	1,	convert(datetime,'2011-04-19 18:26:24.280',121),	convert(datetime,'2011-04-27 08:23:55.480',121)
union select 30150,	110840,	0,	convert(datetime,'2011-04-27 08:23:55.480',121),	convert(datetime,'2011-07-01 08:24:04.090',121)
union select 30147,	110840,	0,	convert(datetime,'2011-07-01 08:24:04.090',121),	convert(datetime,'2012-08-29 03:05:33.310',121)


;with ot(num,num2,id,contact_id,status,datestart,dateend) as (	
select 
	pp = row_number() over(partition by id,contact_id  order by datestart) 
	,pp2 = row_number() over(partition by id,contact_id,status  order by datestart) 
	,id
	,contact_id
	,status
	,datestart
	,dateend
from @t
)
,ot2(id,contact_id,status,datestart,dateend,pp) as(
select
	id
	,contact_id
	,MIN(ot.status)
	,MIN(datestart)
	,MAX(dateend)
	,pp=num-num2
from ot
group by id,contact_id,num-num2
)
select
	t.id
	,t.contact_id
	,t.status
	,t.datestart
	,isNull(t2.datestart, t.dateend)
from	ot2 t
		left join ot2 t2
		on	t2.id= t.id
			and t2.contact_id = t.contact_id
			and t2.pp-1 = t.pp
			
28 май 12, 11:15    [12624550]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить