Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Поиск периодов с помощью SQL запроса  [new]
Илья115
Guest
Добрый день!
Смотрел топики про транспонирование, вопрос 5 ФАКа, но не помогло.
Объясню, что требуется:

Есть:
IDTYPENUM
1A3
2A4
3A1
4A2
5A7
6A9
7B10
8B11
9B12
10B13


Надо:
TYPENUMSTARTNUMENDTOTAL
A144
A771
A991
B10134


Кто поможет с запросом?
28 июн 11, 17:01    [10888166]     Ответить | Цитировать Сообщить модератору
 Re: Поиск периодов с помощью SQL запроса  [new]
aleks2
Guest
Тебе здорово полегчает, если ты осознаешь: надо искать разрывы непрерывности (начала и концы).
А дальше усе просто...
28 июн 11, 17:11    [10888225]     Ответить | Цитировать Сообщить модератору
 Re: Поиск периодов с помощью SQL запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
WITH CTE AS (SELECT NUM-ROW_NUMBER()OVER(PARTITION BY TYPE ORDER BY NUM) N,* FROM T)
SELECT TYPE, MIN(NUM) NUMSTART, MAX(NUM) NUMEND, COUNT(*) TOTAL
FROM CTE
GROUP BY TYPE,N
ORDER BY TYPE, MIN(NUM);
28 июн 11, 17:24    [10888323]     Ответить | Цитировать Сообщить модератору
 Re: Поиск периодов с помощью SQL запроса  [new]
aleks2
Guest
iap
WITH CTE AS (SELECT NUM-ROW_NUMBER()OVER(PARTITION BY TYPE ORDER BY NUM) N,* FROM T)
SELECT TYPE, MIN(NUM) NUMSTART, MAX(NUM) NUMEND, COUNT(*) TOTAL
FROM CTE
GROUP BY TYPE,N
ORDER BY TYPE, MIN(NUM);


Редкостный по неэффективности алгоритм. Причем его впаривают уже десяток лет...
28 июн 11, 17:28    [10888346]     Ответить | Цитировать Сообщить модератору
 Re: Поиск периодов с помощью SQL запроса  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
aleks2
Причем его впаривают уже десяток лет...
максимум 6 row_number() появился в 2005 году.
aleks2
Редкостный по неэффективности алгоритм
Почему? Какой эффективнее?
28 июн 11, 17:36    [10888407]     Ответить | Цитировать Сообщить модератору
 Re: Поиск периодов с помощью SQL запроса  [new]
Илья115
Guest
Товарищи, так какой тогда будет по-эффективнее?
28 июн 11, 17:54    [10888568]     Ответить | Цитировать Сообщить модератору
 Re: Поиск периодов с помощью SQL запроса  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
Илья115,
Вот немного измененный вариант, который когда-то предложил Добрый Э - Эх

select tip, min(num) [NumStart], max(num) [NumEnd], count(1) [Total] from
(
 select t1.tip, t1.num, count(1) - t1.num [gr]
 from @t t1
 inner join @t t2 on t1.tip = t2.tip and t1.num >= t2.num
 group by t1.tip, t1.num
) tmp
group by tip, gr
order by min(num)
28 июн 11, 19:09    [10888911]     Ответить | Цитировать Сообщить модератору
 Re: Поиск периодов с помощью SQL запроса  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
Тестировал на таких данных:

declare @t table (id int, [tip] varchar(100), num int)
insert @t values (1,'A',3), (2,'A',4), (3,'A',1), (4,'A',2), (5,'A',7), (6,'A',9), (7,'B',10), (8,'B',11), (9,'B',12), (10,'B',13)
28 июн 11, 19:10    [10888916]     Ответить | Цитировать Сообщить модератору
 Re: Поиск периодов с помощью SQL запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
aleks2
iap
WITH CTE AS (SELECT NUM-ROW_NUMBER()OVER(PARTITION BY TYPE ORDER BY NUM) N,* FROM T)
SELECT TYPE, MIN(NUM) NUMSTART, MAX(NUM) NUMEND, COUNT(*) TOTAL
FROM CTE
GROUP BY TYPE,N
ORDER BY TYPE, MIN(NUM);


Редкостный по неэффективности алгоритм. Причем его впаривают уже десяток лет...
Ну и зачем вот это "ля-ля"?
Вроде давно установились мир и благодать!
Странно, что этот выпад - вместо предложения "эффективного" алгоритма.
Я не сомневаюсь, что можно накидать пару десятков запросов по теме.
Ну так вперёд!

P.S. У нас всё ещё есть свобода слова или я ошибаюсь?
28 июн 11, 19:55    [10889059]     Ответить | Цитировать Сообщить модератору
 Re: Поиск периодов с помощью SQL запроса  [new]
aleks2
Guest
Начинающий SQL 2008
aleks2
Причем его впаривают уже десяток лет...
максимум 6 row_number() появился в 2005 году.
aleks2
Редкостный по неэффективности алгоритм
Почему? Какой эффективнее?


1. Эта идея была известна ишо ДО row_number().
2. Любой без группировки будет лучше.

declare @t table(id int identity, [TYPE] char(1), NUM int, primary key clustered([TYPE], NUM, id))

insert @t
VALUES('A', 3)
insert @t
VALUES('A', 4)
insert @t
VALUES('A', 1)
insert @t
VALUES('A', 2)
insert @t
VALUES('A', 7)
insert @t
VALUES('A', 9)
insert @t
VALUES('A', 3)
insert @t
VALUES('B', 10)
insert @t
VALUES('B', 11)
insert @t
VALUES('B', 12)
insert @t
VALUES('B', 13)

;with
-- начала интервалов непрерывности
b as (select T.*, ROW_NUMBER()OVER(PARTITION BY T.TYPE ORDER BY T.NUM) as N FROM @T T left outer join @T P ON T.TYPE=P.TYPE AND T.Num-1=P.Num WHERE P.NUM IS NULL)
,
-- концы интервалов непрерывности
e as (select T.*, ROW_NUMBER()OVER(PARTITION BY T.TYPE ORDER BY T.NUM) as N FROM @T T left outer join @T S ON T.TYPE=S.TYPE AND T.Num+1=S.Num WHERE S.NUM IS NULL)
-- ну и фсе
select b.TYPE, b.NUM as NUMSTART, e.NUM as NUMEND, e.NUM-b.NUM+1 as TOTAL FROM b inner join e on b.TYPE=e.TYPE AND b.N=e.N
29 июн 11, 06:22    [10890390]     Ответить | Цитировать Сообщить модератору
 Re: Поиск периодов с помощью SQL запроса  [new]
Илья115
Guest
aleks2
Начинающий SQL 2008
пропущено...
максимум 6 row_number() появился в 2005 году.
пропущено...
Почему? Какой эффективнее?


1. Эта идея была известна ишо ДО row_number().
2. Любой без группировки будет лучше.

declare @t table(id int identity, [TYPE] char(1), NUM int, primary key clustered([TYPE], NUM, id))

insert @t
VALUES('A', 3)
insert @t
VALUES('A', 4)
insert @t
VALUES('A', 1)
insert @t
VALUES('A', 2)
insert @t
VALUES('A', 7)
insert @t
VALUES('A', 9)
insert @t
VALUES('A', 3)
insert @t
VALUES('B', 10)
insert @t
VALUES('B', 11)
insert @t
VALUES('B', 12)
insert @t
VALUES('B', 13)

;with
-- начала интервалов непрерывности
b as (select T.*, ROW_NUMBER()OVER(PARTITION BY T.TYPE ORDER BY T.NUM) as N FROM @T T left outer join @T P ON T.TYPE=P.TYPE AND T.Num-1=P.Num WHERE P.NUM IS NULL)
,
-- концы интервалов непрерывности
e as (select T.*, ROW_NUMBER()OVER(PARTITION BY T.TYPE ORDER BY T.NUM) as N FROM @T T left outer join @T S ON T.TYPE=S.TYPE AND T.Num+1=S.Num WHERE S.NUM IS NULL)
-- ну и фсе
select b.TYPE, b.NUM as NUMSTART, e.NUM as NUMEND, e.NUM-b.NUM+1 as TOTAL FROM b inner join e on b.TYPE=e.TYPE AND b.N=e.N


Не подходит.
При немного другом наборе получаются дикие ошибки:
insert @t
VALUES('A', 4)
insert @t
VALUES('A', 4)
insert @t
VALUES('A', 1)
insert @t
VALUES('A', 2)
insert @t
VALUES('A', 7)
insert @t
VALUES('A', 9)
insert @t
VALUES('A', 3)
insert @t
VALUES('B', 10)
insert @t
VALUES('B', 11)
insert @t
VALUES('B', 12)
insert @t
VALUES('B', 13)
TYPENUMSTARTNUMENDTOTAL
A144
A74-2
A97-1
B10134
29 июн 11, 09:58    [10890787]     Ответить | Цитировать Сообщить модератору
 Re: Поиск периодов с помощью SQL запроса  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Илья115, dense_rank поиспользуйте вместо row_number, либо обеспечте уникальность.
29 июн 11, 10:12    [10890859]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить