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

Откуда: Berlin
Сообщений: 185
есть таблица:
msid Account
10011
10021
10031
10041
10052
10062
10072
10081
10091
10101

надо получить максимальное и минимальное значение для каждого account для каждого окна:
msid_min msid_max Account
1001 1004 1
1005 1007 2
1008 1010 1

use tempdb
go
create table t1 (msid char(4), account_id int)
go
insert into t1
select '1001', 1 union all
select '1002', 1 union all
select '1003', 1 union all
select '1004', 1 union all
select '1005', 2 union all
select '1006', 2 union all
select '1007', 2 union all
select '1008', 1 union all
select '1009', 1 union all
select '1010', 1

вот так:
select top 1 with ties
  min (msid) over (partition by account_id) as msid_min 
, max (msid) over (partition by account_id) as msid_max
, account_id as Account
from t1
order by rank() over (partition by account_id order by msid)
возвращает
msid_min msid_max Account
1001 1010 1
1005 1007 2

где подправить?
желательно без CTE

Сервер: Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
в режиме совместимости 80
2 авг 11, 13:07    [11058322]     Ответить | Цитировать Сообщить модератору
 Re: Как сгруппировать данные или оконная функция  [new]
Glory
Member

Откуда:
Сообщений: 104751
А почему вы считаете, что у вас 3 окна, а не 2 ?
2 авг 11, 13:08    [11058333]     Ответить | Цитировать Сообщить модератору
 Re: Как сгруппировать данные или оконная функция  [new]
edyaN
Member

Откуда: Berlin
Сообщений: 185
я думаю, что если сделать сортировку по msid, то будет 3 окна
2 авг 11, 13:11    [11058351]     Ответить | Цитировать Сообщить модератору
 Re: Как сгруппировать данные или оконная функция  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Пока aleks2 не пришёл, покажу самый плохой с его точки зрения вариант:
declare @T table(msid char(4), account_id int)

insert into @T(msid, account_id)
select '1001', 1 union all
select '1002', 1 union all
select '1003', 1 union all
select '1004', 1 union all
select '1005', 2 union all
select '1006', 2 union all
select '1007', 2 union all
select '1008', 1 union all
select '1009', 1 union all
select '1010', 1;

WITH CTE AS
(
 SELECT *,
  N=ROW_NUMBER()OVER(ORDER BY msid)-ROW_NUMBER()OVER(PARTITION BY account_id ORDER BY msid)
 FROM @T
)
SELECT MIN(msid)[MIN(msid)], MAX(msid)[MAX(msid)], account_id
FROM CTE
GROUP BY account_id,N
ORDER BY 1;
Время будет, напишу ещё :)
2 авг 11, 13:39    [11058569]     Ответить | Цитировать Сообщить модератору
 Re: Как сгруппировать данные или оконная функция  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Коррелированный подзапрос в SELECTе:
declare @T table(msid char(4), account_id int)

insert into @T(msid, account_id)
select '1001', 1 union all
select '1002', 1 union all
select '1003', 1 union all
select '1004', 1 union all
select '1005', 2 union all
select '1006', 2 union all
select '1007', 2 union all
select '1008', 1 union all
select '1009', 1 union all
select '1010', 1;

SELECT T.msid[MIN(msid)],
(
 SELECT MAX(TT.msid)
 FROM @T TT
 WHERE TT.account_id=T.account_id AND TT.msid>=T.msid
   AND NOT EXISTS(SELECT * FROM @T TTT WHERE TTT.account_id<>TT.account_id AND TTT.msid>T.msid AND TTT.msid<TT.msid)
)[MAX(msid)],
T.account_id
FROM @T T
WHERE NOT EXISTS(SELECT * FROM @T TT WHERE TT.account_id=T.account_id AND TT.msid<T.msid
  AND NOT EXISTS(SELECT * FROM @T TTT WHERE TTT.account_id<>TT.account_id AND TTT.msid BETWEEN TT.msid AND T.msid))
ORDER BY 1;
2 авг 11, 13:52    [11058668]     Ответить | Цитировать Сообщить модератору
 Re: Как сгруппировать данные или оконная функция  [new]
ilyaBS
Member

Откуда: Киев
Сообщений: 77

declare @t1 table (msid char(4), account_id int)

insert into @t1
select '1001', 1 union all
select '1002', 1 union all
select '1003', 1 union all
select '1004', 1 union all
select '1005', 2 union all
select '1006', 2 union all
select '1007', 2 union all
select '1008', 1 union all
select '1009', 1 union all
select '1010', 1

select * from @t1

select Ma.account_id,Mi.msid as MinId, Ma.msid as MaxId from 
(select row_number() over (order by t1.msid) as id,t1.msid,t1.account_id from @t1 t1 
left join @t1 t2 on t1.msid=t2.msid-1 and t1.account_id=t2.account_id
where t2.msid is null) as Ma
inner join 
(select row_number() over (order by t1.msid) as id,t1.msid,t1.account_id from @t1 t1 
left join @t1 t2 on t1.msid=t2.msid+1 and t1.account_id=t2.account_id
where t2.msid is null) as Mi
on Ma.id=Mi.id
2 авг 11, 13:54    [11058700]     Ответить | Цитировать Сообщить модератору
 Re: Как сгруппировать данные или оконная функция  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Коррелированный подзапрос, переделанный в CROSS APPLY:
declare @T table(msid char(4), account_id int)

insert into @T(msid, account_id)
select '1001', 1 union all
select '1002', 1 union all
select '1003', 1 union all
select '1004', 1 union all
select '1005', 2 union all
select '1006', 2 union all
select '1007', 2 union all
select '1008', 1 union all
select '1009', 1 union all
select '1010', 1;

SELECT T.msid[MIN(msid)],TT.msid[MAX(msid)],T.account_id
FROM @T T
CROSS APPLY
(
 SELECT MAX(TT.msid)
 FROM @T TT
 WHERE TT.account_id=T.account_id AND TT.msid>=T.msid
   AND NOT EXISTS(SELECT * FROM @T TTT WHERE TTT.account_id<>TT.account_id AND TTT.msid>T.msid AND TTT.msid<TT.msid)
) TT(msid)
WHERE NOT EXISTS(SELECT * FROM @T TT WHERE TT.account_id=T.account_id AND TT.msid<T.msid
  AND NOT EXISTS(SELECT * FROM @T TTT WHERE TTT.account_id<>TT.account_id AND TTT.msid BETWEEN TT.msid AND T.msid))
ORDER BY 1;
2 авг 11, 13:57    [11058722]     Ответить | Цитировать Сообщить модератору
 Re: Как сгруппировать данные или оконная функция  [new]
edyaN
Member

Откуда: Berlin
Сообщений: 185
спасибо!
3 авг 11, 13:59    [11064446]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить