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

Откуда:
Сообщений: 438
Здравствуйте! Когда-то я задавал похожий вопрос, но техзадание поменялось.
Есть таблица. Группы выделены разным цветом, новая группа начинается, если val = 1

idxval
11
20
30
40
51
60
71
81


Надо получить строки с максимальным idx для каждой группы:
idxval
40
60
71
81


Тестовые данные:
declare @t table (idx int identity, val int)

insert @t
 select 1 union all select 0 union all select 0 union all select 0 union all
 select 1 union all select 0 union all select 1 union all select 1
 
select * from @t

select @@version

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) 
	Feb  9 2007 22:47:07 
	Copyright (c) 1988-2005 Microsoft Corporation
	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
16 авг 11, 10:51    [11124693]     Ответить | Цитировать Сообщить модератору
 Re: И снова группировка  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Начинающий SQL 2008, накопительный итог по val, а далее выборка максимального по группе. Справитесь?
16 авг 11, 10:56    [11124726]     Ответить | Цитировать Сообщить модератору
 Re: И снова группировка  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
kDnZP,
справился

select t1.*, (select sum(val) from @t t2 where t2.idx <= t1.idx) [gr]
from @t t1

Но боюсь, что долго будет отрабатывать на таблице ~ 100 тыс.зап :(
16 авг 11, 11:03    [11124771]     Ответить | Цитировать Сообщить модератору
 Re: И снова группировка  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
Уверен, есть еще варианты.
16 авг 11, 11:10    [11124824]     Ответить | Цитировать Сообщить модератору
 Re: И снова группировка  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Начинающий SQL 2008
Уверен, есть еще варианты.

Напишите разные запросы, сравните планы. Вот, как вариант.
SELECT t1.* FROM (
select DISTINCT MAX(t1.idx) OVER (PARTITION BY SUM(t2.val)) idx
from @t t1
LEFT JOIN @t t2 ON t1.idx >= t2.idx
GROUP BY t1.idx
) t JOIN @t t1 ON t.idx=t1.idx
16 авг 11, 11:15    [11124862]     Ответить | Цитировать Сообщить модератору
 Re: И снова группировка  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
kDnZP, да... Написал, так написал))). 2 группировки явно будут тормознее вашего варианта, если его доделать.
16 авг 11, 11:21    [11124896]     Ответить | Цитировать Сообщить модератору
 Re: И снова группировка  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
kDnZP,
вот мой доделанный вариант. Здесь определить второй cte и выбрать rn=1 оказалось чуть быстрее, чем
select top 1 with ties

;with cte as (
 select t1.*, (select sum(val) from @t t2 where t2.idx <= t1.idx) [gr]
 from @t t1
),
cte2 as (
 select *, row_number() over (partition by gr order by idx desc) [rn]
from cte
)
select idx,val
from cte2 where rn=1
16 авг 11, 11:26    [11124936]     Ответить | Цитировать Сообщить модератору
 Re: И снова группировка  [new]
iljy
Member

Откуда:
Сообщений: 8711
Начинающий SQL 2008,

есть другой вариант, на большой таблице наверняка будет быстрее
declare @t table (idx int identity, val int)

insert @t
 select 1 union all select 0 union all select 0 union all select 0 union all
 select 1 union all select 0 union all select 1 union all select 1
 
 select top 1 with ties idx, val
 from(
	 select *, ROW_NUMBER() over(partition by val order by idx) rn from @t
)t
order by ROW_NUMBER()over(partition by case val when 0 then idx-rn else rn end order by idx desc) 
 
16 авг 11, 11:33    [11124994]     Ответить | Цитировать Сообщить модератору
 Re: И снова группировка  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Начинающий SQL 2008
Уверен, есть еще варианты.
SET NOCOUNT ON;

DECLARE @T TABLE(idx int, val int);
INSERT @T(idx, val)VALUES
 (1, 1)
,(2, 0)
,(3, 0)
,(4, 0)
,(5, 1)
,(6, 0)
,(7, 1)
,(8, 1);

SELECT *
FROM @T T
WHERE NOT EXISTS(SELECT * FROM @T TT WHERE TT.idx=T.idx+1 AND TT.val=0);
16 авг 11, 11:39    [11125061]     Ответить | Цитировать Сообщить модератору
 Re: И снова группировка  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
iljy, iap
Спасибо!
16 авг 11, 11:43    [11125102]     Ответить | Цитировать Сообщить модератору
 Re: И снова группировка  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Начинающий SQL 2008
iljy, iap
Спасибо!
Если idx с разрывами, то придётся усложнять
16 авг 11, 11:44    [11125114]     Ответить | Цитировать Сообщить модератору
 Re: И снова группировка  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
iap,
нет, idx будет идти без разрывов.

Но если не затруднит, привидите, пожалуйста, вариант, если idx c разрывами.
16 авг 11, 11:49    [11125162]     Ответить | Цитировать Сообщить модератору
 Re: И снова группировка  [new]
iljy
Member

Откуда:
Сообщений: 8711
Начинающий SQL 2008
iap,
нет, idx будет идти без разрывов.

Но если не затруднит, привидите, пожалуйста, вариант, если idx c разрывами.

Добавьте ROW_NUMBER() over(order by idx) и используйте в вычислениях его
16 авг 11, 11:51    [11125189]     Ответить | Цитировать Сообщить модератору
 Re: И снова группировка  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
iljy,
да, действительно, чего-то ступил...
16 авг 11, 11:51    [11125197]     Ответить | Цитировать Сообщить модератору
 Re: И снова группировка  [new]
Уленшпигель
Member

Откуда:
Сообщений: 115
Начинающий SQL 2008,

Вот еще так можно:

DECLARE @T TABLE(idx int, val int);
INSERT @T(idx, val)
SELECT 1, 1
UNION ALL
SELECT 2, 0
UNION ALL
SELECT 3, 0
UNION ALL
SELECT 4, 0
UNION ALL
SELECT 5, 1
UNION ALL
SELECT 6, 0
UNION ALL
SELECT 7, 1 
UNION ALL
SELECT 8, 1;

SELECT a.idx, a.val
FROM @t a
LEFT JOIN @t b
ON a.idx + 1 = b.idx
WHERE IsNull(b.val, 1) <> 0
16 авг 11, 12:01    [11125322]     Ответить | Цитировать Сообщить модератору
 Re: И снова группировка  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
Уленшпигель,
спасибо, тоже хороший вариант.
16 авг 11, 12:17    [11125473]     Ответить | Цитировать Сообщить модератору
 Re: И снова группировка  [new]
_anybody
Guest
Начинающий SQL 2008
idxval
11
20
30
40
51
60
71
81


Надо получить строки с максимальным idx для каждой группы:
idxval
40
60
71
81

Для представленных данных можно так:
SELECT
  idx = idx - 1,
  val = val - 1
FROM @t
WHERE val = 1 AND idx != 1
20 окт 11, 23:03    [11475785]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить