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

Откуда:
Сообщений: 2
У меня есть таблица со строкой, в которой числа в порядке возрастания. К примеру 1,2,3,6,7,8,9 ... . Их порядка 5 милионов. Мне нужно сгруппировать их следующим образом, чтобы в полученной выборке они шли следующим образом: 1 3, 6 9. То есть минимальное число последовательного ряда и максимальное. Как только пропущено какое-то число, новая строчка с минимальным числом и максимальным... SELECT MIN(Number), MAX(NUMBER).

Если у кого есть идеи как это сделать, поделитесь пожалуйста.
12 апр 13, 21:11    [14175254]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
qwerty112
Guest
KuzMich9
У меня есть таблица со строкой, в которой числа в порядке возрастания. К примеру 1,2,3,6,7,8,9 ... . Их порядка 5 милионов. Мне нужно сгруппировать их следующим образом, чтобы в полученной выборке они шли следующим образом: 1 3, 6 9. То есть минимальное число последовательного ряда и максимальное. Как только пропущено какое-то число, новая строчка с минимальным числом и максимальным... SELECT MIN(Number), MAX(NUMBER).

Если у кого есть идеи как это сделать, поделитесь пожалуйста.

речь, "в сам деле" про строку в которой 5млн.значений, разделённых запятой ?
или это просто у тебя проблемы со словарным запасом, а "в натуре" имеется в виду что-то другое ?

пример данных / результата на этих данных - сможешь показать ?
12 апр 13, 21:28    [14175292]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
KuzMich9
Member

Откуда:
Сообщений: 2
qwerty112

Мне даже по скайпу другу тяжело было объяснить что мне нужно...
У меня временная таблица, c одним столбцом. Который я инициализирую некой числовой последовательностью. Далее запросом мне нужно объединить "подряд повторяющиеся числа".

DELCARE @TempTable as Table
(
ColNum int
)
// Инициализирую ее
// к примеру так
INSERT INTO @TempTable
(
ColNum
)
VALUES (1),
(2),
(3),
(6),
(7),
(8),
(9)
.....
// Дальше мне нужно "объеденить подряд" идущие промежтуки чисел

SELECT MIN(ColNum), MAX(ColNum)
FROM @TempTable.....

// результат такой
// (числа: 1,2,3,6,7,8,9,10,20,21,22)

// 1, 3
// 6, 10
// 20, 22
12 апр 13, 22:19    [14175380]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
qwerty112
Guest
KuzMich9
qwerty112

Мне даже по скайпу другу тяжело было объяснить что мне нужно...

это тебя хреново характеризует ... :(

DEClARE @TempTable as Table
(
   ColNum int
)
--// Инициализирую ее
--// к примеру так
--// результат такой
--// (числа: 1,2,3,6,7,8,9,10,20,21,22)

INSERT INTO @TempTable (ColNum)VALUES (1)
INSERT INTO @TempTable (ColNum)VALUES (2)
INSERT INTO @TempTable (ColNum)VALUES (3)
INSERT INTO @TempTable (ColNum)VALUES (6)
INSERT INTO @TempTable (ColNum)VALUES (7)
INSERT INTO @TempTable (ColNum)VALUES (8)
INSERT INTO @TempTable (ColNum)VALUES (9)
INSERT INTO @TempTable (ColNum)VALUES (10)
INSERT INTO @TempTable (ColNum)VALUES (20)
INSERT INTO @TempTable (ColNum)VALUES (21)
INSERT INTO @TempTable (ColNum)VALUES (22)

;with cte as
(select ColNum, ColNum - row_number() over(order by ColNum) as xz from @TempTable)

select min(a.ColNum) as xz1, max(b.ColNum) as xz2
from cte a inner join cte b on a.xz=b.xz
group by a.xz 


xz1         xz2
----------- -----------
1           3
6           10
20          22

(3 row(s) affected)
12 апр 13, 22:45    [14175441]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
KuzMich9,

версию вашего сервера укажите пожалуйста.
у вас будет 5 миллионов записей во временной таблице? возможно нужен более производительный вариант.
12 апр 13, 23:00    [14175482]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
qwerty112
Guest
qwerty112
;with cte as
(select ColNum, ColNum - row_number() over(order by ColNum) as xz from @TempTable)

select min(a.ColNum) as xz1, max(b.ColNum) as xz2
from cte a inner join cte b on a.xz=b.xz
group by a.xz 


пятница ... :)
;with cte as
(select ColNum, ColNum - row_number() over(order by ColNum) as xz from @TempTable)

select min(a.ColNum) as xz1, max(a.ColNum) as xz2
from cte a 
group by a.xz 
12 апр 13, 23:03    [14175493]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
как минимум в предложном варианте можно объявить таблицу c PK, что позволит убрать 2 ресурсоемкие сортировки из запроса.
DEClARE @TempTable as Table
(
   ColNum int primary key
)
12 апр 13, 23:33    [14175592]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
Добрый Э - Эх
Guest
KuzMich9,

баян несусветный
13 апр 13, 04:42    [14175880]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
Добрый Э - Эх
Guest
Вот так - более подробно :)
13 апр 13, 04:47    [14175881]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
aleks2
Guest
qwerty112
это тебя хреново характеризует ... :(

;with cte as
(select ColNum, ColNum - row_number() over(order by ColNum) as xz from @TempTable)

select min(a.ColNum) as xz1, max(b.ColNum) as xz2
from cte a inner join cte b on a.xz=b.xz
group by a.xz 



Тебя тоже. Низзя такое фуфло впаривать.

DEClARE @TempTable as Table
(
   ColNum int
)
--// Инициализирую ее
--// к примеру так
--// результат такой
--// (числа: 1,2,3,6,7,8,9,10,20,21,22)

INSERT INTO @TempTable (ColNum)VALUES (1)
INSERT INTO @TempTable (ColNum)VALUES (2)
INSERT INTO @TempTable (ColNum)VALUES (3)
INSERT INTO @TempTable (ColNum)VALUES (6)
INSERT INTO @TempTable (ColNum)VALUES (7)
INSERT INTO @TempTable (ColNum)VALUES (8)
INSERT INTO @TempTable (ColNum)VALUES (9)
INSERT INTO @TempTable (ColNum)VALUES (10)
INSERT INTO @TempTable (ColNum)VALUES (20)
INSERT INTO @TempTable (ColNum)VALUES (21)
INSERT INTO @TempTable (ColNum)VALUES (22)

declare @b table (ColNum int, n int identity primary key clustered );

insert @b(ColNum)
  select T.ColNum from @TempTable t left outer join @TempTable tt on tt.ColNum = t.ColNum-1
    where tt.ColNum is null
    order by T.ColNum asc;

declare @e table (ColNum int, n int identity primary key clustered );

insert @e(ColNum)
  select T.ColNum from @TempTable t left outer join @TempTable tt on tt.ColNum = t.ColNum+1
    where tt.ColNum is null
    order by T.ColNum asc;

select b.ColNum, e.ColNum from @b b inner join @e e on b.n=e.n
13 апр 13, 05:41    [14175885]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
aleks2
Guest
qwerty112
это тебя хреново характеризует ... :(

;with cte as
(select ColNum, ColNum - row_number() over(order by ColNum) as xz from @TempTable)

select min(a.ColNum) as xz1, max(b.ColNum) as xz2
from cte a inner join cte b on a.xz=b.xz
group by a.xz 



Тебя тоже. Низзя такое фуфло впаривать.

DEClARE @TempTable as Table
(
   ColNum int
)
--// Инициализирую ее
--// к примеру так
--// результат такой
--// (числа: 1,2,3,6,7,8,9,10,20,21,22)

INSERT INTO @TempTable (ColNum)VALUES (1)
INSERT INTO @TempTable (ColNum)VALUES (2)
INSERT INTO @TempTable (ColNum)VALUES (3)
INSERT INTO @TempTable (ColNum)VALUES (6)
INSERT INTO @TempTable (ColNum)VALUES (7)
INSERT INTO @TempTable (ColNum)VALUES (8)
INSERT INTO @TempTable (ColNum)VALUES (9)
INSERT INTO @TempTable (ColNum)VALUES (10)
INSERT INTO @TempTable (ColNum)VALUES (20)
INSERT INTO @TempTable (ColNum)VALUES (21)
INSERT INTO @TempTable (ColNum)VALUES (22)

declare @b table (ColNum int, n int identity primary key clustered );

insert @b(ColNum)
  select T.ColNum from @TempTable t left outer join @TempTable tt on tt.ColNum = t.ColNum-1
    where tt.ColNum is null
    order by T.ColNum asc;

declare @e table (ColNum int, n int identity primary key clustered );

insert @e(ColNum)
  select T.ColNum from @TempTable t left outer join @TempTable tt on tt.ColNum = t.ColNum+1
    where tt.ColNum is null
    order by T.ColNum asc;

select b.ColNum, e.ColNum from @b b inner join @e e on b.n=e.n
13 апр 13, 05:57    [14175886]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Без промежуточных таблиц будет быстрее:
with a as
(
  select
   t.ColNum,
   row_number() over (order by t.ColNum) as n
  from
   @TempTable t left join
   @TempTable tt on tt.ColNum = t.ColNum - 1
  where
   tt.ColNum is null
),
b as
(
  select
   t.ColNum,
   row_number() over (order by t.ColNum) as n
  from
   @TempTable t left join
   @TempTable tt on tt.ColNum = t.ColNum + 1
  where
   tt.ColNum is null
)
select
 a.ColNum, b.ColNum
from
 a join
 b on b.n = a.n;
14 апр 13, 23:46    [14179308]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
aleks2
Guest
invm
Без промежуточных таблиц будет быстрее:


Нибудет.
15 апр 13, 05:59    [14179551]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
aleks2
Нибудет.
Как обычно, безапеляционно и бездоказательно :)
+ Тестовые данные
use tempdb;
go

if object_id('dbo.testTable', 'U') is not null
 drop table dbo.TestTable;
go

create table dbo.TestTable (ColNum int not null primary key clustered);
go

insert into dbo.TestTable
select top (5000000)
 row_number() over (order by (select 1))
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b cross join
 master.dbo.spt_values c;

with x as
(
 select top (10) percent
  ColNum
 from
  dbo.TestTable
 order by
  checksum(newid())
)
delete from x;
go
CPUDurationReadsWrites
С временными таблицами4337684121599211232
С табличными переменными393166922158845145
Без таблиц29495499330140
15 апр 13, 08:53    [14179733]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
aleks2
Guest
invm
aleks2
Нибудет.
Как обычно, безапеляционно и бездоказательно :)

[/src][/spoiler]
CPUDurationReadsWrites
С временными таблицами4337684121599211232
С табличными переменными393166922158845145
Без таблиц29495499330140


Это ерунда. Ибо данные могут быть двух сортов:
а) мало разрывов (много меньше, чем данных)
б) много разрывов

На первом типе "Без таблиц" ишо сканает. На втором - обломается, ибо будет TableSpool.
"С табличными переменными" будет ровно.

Как-то так.

ЗЫ. Для этого совсем необязательно тесты проводить.
15 апр 13, 09:16    [14179802]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
aleks2
Это ерунда. Ибо данные могут быть двух сортов:
а) мало разрывов (много меньше, чем данных)
б) много разрывов
Т.е. чем меньше данных, тем хуже. Или чем больше дырка, тем хуже. Круто.
Если тестовые данные заменить на
use tempdb;
go

if object_id('dbo.testTable', 'U') is not null
 drop table dbo.TestTable;
go

create table dbo.TestTable (ColNum bigint not null primary key clustered);
go

insert into dbo.TestTable
select top (500000)
 row_number() over (order by (select 1)) * 1000000
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b cross join
 master.dbo.spt_values c;
То картина не изменится. Более того, запрос вообще работает неправильно.

Правильный вариант:
with x as
(
 select
  ColNum,
  row_number() over (order by ColNum) as n
 from
  dbo.@TempTable
)
select
 a.ColNum, b.ColNum
from
 x a join
 x b on b.n = a.n + 1
where
 b.ColNum > a.ColNum + 1;
15 апр 13, 10:41    [14180163]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
Добрый Э - Эх
Guest
invm,
попробуй потестирвоать на скорость ещё и вариант без самообъединения, но на группировке...просто чисто интересно...
15 апр 13, 10:53    [14180226]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Добрый Э - Эх,

Вот этот -- 14175493?

CPUDurationReadsWrites
С группировкой516333875203400
15 апр 13, 11:09    [14180292]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
qwerty112
Guest
Добрый Э - Эх
invm,
попробуй потестирвоать на скорость ещё и вариант без самообъединения, но на группировке...просто чисто интересно...

на 1млн. записей минус 100т.случайных записей (мой "ипад" больше не потянул :) ),
"вариант без самообъединения, но на группировке" "продул" варианту aleks2, по времени, в 4.3 -:- 4.6 раз

и, я так понимаю, при росте к-ва записей, этот "продул" будет только расти ...
15 апр 13, 11:12    [14180308]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
Добрый Э - Эх
Guest
invm,

не совсем, ТАМ же один хрен зачем-то селфджойнится табличка сама на себя. нужно чистый ROW_NUMBER + group by, без джойнов
15 апр 13, 11:18    [14180349]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
Добрый Э - Эх
Guest
qwerty112,

какой именно запрос тестил?
15 апр 13, 11:19    [14180354]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
qwerty112
Guest
Добрый Э - Эх
qwerty112,

какой именно запрос тестил?

тот, что тут - 14175493 , исправленный
этот
;with cte as
(select ColNum, ColNum - row_number() over(order by ColNum) as xz from @TempTable)

select min(a.ColNum) as xz1, max(a.ColNum) as xz2
from cte a 
group by a.xz 
15 апр 13, 11:20    [14180362]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Добрый Э - Эх,

Нет-нет, там исправленный вариант, без селфджоина.

qwerty112,

В отличии от варианта от aleks2, ваш работает правильно.
15 апр 13, 11:31    [14180449]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
invm
qwerty112,

В отличии от варианта от aleks2, ваш работает правильно.
Хотя, вот на таких данных 14180163, тоже неправильно.
15 апр 13, 11:39    [14180494]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с агрегированием числового ряда  [new]
aleks2
Guest
KuzMich9
У меня есть таблица со строкой, в которой числа в порядке возрастания. К примеру 1,2,3,6,7,8,9 ... . Их порядка 5 милионов. Мне нужно сгруппировать их следующим образом, чтобы в полученной выборке они шли следующим образом: 1 3, 6 9. То есть минимальное число последовательного ряда и максимальное. Как только пропущено какое-то число, новая строчка с минимальным числом и максимальным... SELECT MIN(Number), MAX(NUMBER).

Если у кого есть идеи как это сделать, поделитесь пожалуйста.


invm
То картина не изменится. Более того, запрос вообще работает неправильно.

Правильный вариант


Глазки надо протереть.
15 апр 13, 12:06    [14180692]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить