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

Откуда:
Сообщений: 512
Помогите, пожалуйста, с запросом (MS SQL SERVER 2008)...

Есть табличка с двумя колонками, Col1 и Col2, с независимыми значениями.
Нужно создать группировку (в примере - Group), которая учитывает значения из обеих колонок независимо.


Col1Col2Group
22100
22100
22100
34300
34300
34300
53200
45200
45200
55200
55200
46200
57200
57200
128400
128400
89500
99500
99500


Т.е. например

5 | 3
4 | 3
4 | 200
4 | 19
7 | 19

это одна группа.


Спасибо.
21 сен 11, 08:26    [11309399]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
puhh,

Ничего непонятно.
puhh
Т.е. например

5 | 3
4 | 3
4 | 200
4 | 19
7 | 19

это одна группа.
Алгоритм какой? Почему это не 2 группы?
21 сен 11, 08:35    [11309412]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
puhh
Member

Откуда:
Сообщений: 512
А я вот и не знаю как это сформулировать... Это как бы пересекающаяся группировка. Или скорее "объединяющая".


5 | 3
4 | 3
4 | 200
4 | 19
7 | 19


(4) объединяет [3], [200] и [19]
(4) и (5) находятся в [3]
(4) и (7) находятся в [19]



Если это все объединить то получится требуемый результат.
21 сен 11, 08:58    [11309457]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
puhh
А я вот и не знаю как это сформулировать... Это как бы пересекающаяся группировка. Или скорее "объединяющая".
Прямо скажите, что вам надо получить. Может быть, поле с заполненными значениями?
Я не понимаю, что значит "получить группировку".

Вы без комментариев привели текст:
5 | 3
4 | 3
4 | 200
4 | 19
7 | 19

Вот именно такой и нужен, даже без названия колонок?
21 сен 11, 09:04    [11309469]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
puhh,

Прочитайте чудесный топик (одна страничка!)
https://www.sql.ru/forum/actualthread.aspx?tid=127456
21 сен 11, 09:05    [11309470]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
alexeyvg
puhh,

Прочитайте чудесный топик (одна страничка!)
https://www.sql.ru/forum/actualthread.aspx?tid=127456
Даже достаточно прочитать п. 6 , десяток строк.
21 сен 11, 09:06    [11309471]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
puhh
Member

Откуда:
Сообщений: 512
Сорри, но... я в первом сообщении написала что

Нужно создать группировку (в примере - Group), которая учитывает значения из обеих колонок независимо


и дальше идет пример: большая симпатичная табличка с тем как это выглядит.


Эта табличка это результат следующего запроса

select col1, col2, group
from
<неизвестный пока запрос который я пытаюсь написать>
21 сен 11, 09:42    [11309589]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
Glory
Member

Откуда:
Сообщений: 104751
puhh
Сорри, но... я в первом сообщении написала что

Нужно создать группировку (в примере - Group), которая учитывает значения из обеих колонок независимо

И что это значит - "учитывает независимо" ? Складывает ? Сравнивает ? Возводит в степень ?

puhh
и дальше идет пример: большая симпатичная табличка с тем как это выглядит.

А алгоритм ее получения можно придумать самому ?
21 сен 11, 09:46    [11309601]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
puhh
и дальше идет пример: большая симпатичная табличка с тем как это выглядит.


Эта табличка это результат следующего запроса

select col1, col2, group
from
<неизвестный пока запрос который я пытаюсь написать>
То есть нужно вычислить в запросе некое поле group?

Хорошо, теперь расскажите про алгоритм, которым оно вычисляется.

Почему тут Group получилось 100, а не 74?
Col1Col2Group
22100
22100
22100


Почему эти записи относятся к одной группе? Потому что в колонках найдено число 5?
Col1Col2Group
53200
45200
45200
21 сен 11, 09:50    [11309613]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
puhh,

Я вам советую всё таки прочитать п. 6 рекомендаций https://www.sql.ru/forum/actualthread.aspx?tid=127456
И своё сообщение оформить прямо по примеру https://www.sql.ru/forum/actualthread.aspx?tid=128200#1014485

Вместо
CREATE TABLE [dbo].[A]...
лучьше даже испольовать переменные:
declare @A table...
21 сен 11, 09:54    [11309644]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
puhh
Member

Откуда:
Сообщений: 512
alexeyvg
То есть нужно вычислить в запросе некое поле group?

Да.
Ну наверно не совсем "вычислить", а "назначить". Число (rank например), текст - это не важно.

alexeyvg
Хорошо, теперь расскажите про алгоритм, которым оно вычисляется.



Я пытаюсь. На словах очень просто, а когда пишу получается запутанно.

Вот еще пример.

Группа A - потому что в первой колонке общее значение, (1)
Группа B - потому что во второй колонке общее значение, [222]

С группой С сложнее:
[333] объединяет значения (4) и (5) из первой
и [190] объединяет значения (4) и (7) из первой
но (4) в свою очередь объединяет [333], [400], [190]

Получается что это все - одна группа


col1col2 group
1100A
1101A
2222B
3222B
5333C
4333C
4400C
4190C
7190C
21 сен 11, 10:03    [11309697]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
iap
Member

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

хорошо, что Вы не мой начальник!
21 сен 11, 10:10    [11309730]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
Glory
Member

Откуда:
Сообщений: 104751
puhh
С группой С сложнее:
[333] объединяет значения (4) и (5) из первой
и [190] объединяет значения (4) и (7) из первой
но (4) в свою очередь объединяет [333], [400], [190]

Получается что это все - одна группа

Это не группировка. Это уже рекурсия
21 сен 11, 10:14    [11309763]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
baracs
Member

Откуда: Москва
Сообщений: 7217
puhh
Ну наверно не совсем "вычислить", а "назначить".

Вот еще пример.

Группа A - потому что в первой колонке общее значение, (1)
Группа B - потому что во второй колонке общее значение, [222]

С группой С сложнее:
[333] объединяет значения (4) и (5) из первой
и [190] объединяет значения (4) и (7) из первой
но (4) в свою очередь объединяет [333], [400], [190]


"Назначить" можно:
SELECT col1, col2, 'A' AS [Group] FROM табличка WHERE col1 = 1

UNION ALL

SELECT col1, col2, 'B' AS [Group] FROM табличка WHERE col2 = 222

UNION ALL

SELECT col1, col2, 'C' AS [Group] FROM табличка 
WHERE col1 = 4
   OR col2 IN (190,333)
Только, это не группировка. Группировать можно потом, по этим, назначенным значениям.
21 сен 11, 10:34    [11309888]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
puhh
Member

Откуда:
Сообщений: 512
baracs
Только, это не группировка. Группировать можно потом, по этим, назначенным значениям.


Да, Вы правы. Называю это "группировкой" потому что потом буду делать group by по этому полю.
Как по другому назвать?... Пусть это будет кластер.

glory
Это не группировка. Это уже рекурсия


Мм...

Попробую еще так сформулировать.


Шаг1. Group by col1 -> получились какие-то группы
Шаг2. Group by col2 -> получились какие-то группы
Шаг3. Объединить пересекающиеся группы.


--====================--


Код.

declare @A table (col1 int, col2 char(1))
insert into @A (col1, col2)
select * from
(
select 5 as c1, 'A' as c2 /*Cluster1*/
union all
select 4 as c1, 'A' as c2 /*Cluster1*/
union all
select 4 as c1, 'B' as c2 /*Cluster1*/
union all
select 4 as c1, 'C' as c2 /*Cluster1*/
union all
select 7 as c1, 'C' as c2 /*Cluster1*/
union all
select 10 as c1, 'D' as c2 /*Cluster2*/
) Q


select * from @A

21 сен 11, 11:04    [11310069]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
Glory
Member

Откуда:
Сообщений: 104751
puhh
Попробую еще так сформулировать.


Шаг1. Group by col1 -> получились какие-то группы
Шаг2. Group by col2 -> получились какие-то группы
Шаг3. Объединить пересекающиеся группы.

У вас 333 (5,4) через 4ку "наследуется" 400 и 190, а через них 7, а через 7ку еще что-то, и тд и тд
Это называется рекурсия. Потому что вы не знаете, насколько глубока эта цепочка вложенностей
21 сен 11, 11:09    [11310113]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
puhh
Member

Откуда:
Сообщений: 512
Я кажется придумала как это сформулировать

row_numbercol1col2cluster
11100A
21101A
32222B
43222B
55301C
64301C
74200C
84190C
97190C


Строки с номерами 1 и 2 находятся в кластере A потому что у них общее значение в колонке 1
Строки с номерами 3 и 4 находятся в кластере B потому что у них общее значение в колонке 2


У строк 6, 7 и 8 общее значение в колонке 1, а также у строк 5 и 6 общее значение в колонке 2 и у строк 8 и 9 общее значение в колонке 2 -> все попадают в кластер С
21 сен 11, 11:12    [11310141]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
puhh
Я кажется придумала как это сформулировать

row_numbertcol1tcol2tclustert
1t1t100tA
2t1t101tA
3t2t222tB
4t3t222tB
5t5t301tC
6t4t301tC
7t4t200tC
8t4t190tC
9t7t190tC


Строки с номерами 1 и 2 находятся в кластере A потому что у них общее значение в колонке 1
Строки с номерами 3 и 4 находятся в кластере B потому что у них общее значение в колонке 2


У строк 6, 7 и 8 общее значение в колонке 1, а также у строк 5 и 6 общее значение в колонке 2 и у строк 8 и 9 общее значение в колонке 2 -> все попадают в кластер С
Это и есть рекурсия.
На первом шаге отбираем все различные пары и присваиваем им Ваш "кластер".
На втором шаге отбираем пары, которые ссылаются на уже отобранные по первому полю или по второму. "Кластер" берём у "родителей".
Повторяем второй шаг, пока пары не закончатся.
Так?
Берём рекурсивное CTE и реализуем написанное "в лоб".
21 сен 11, 11:22    [11310204]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
puhh
Я кажется придумала как это сформулировать
Да в общем то уже понятно.

Просто группировка - совершенно определённый термин для РСУБД и конкретно для SQL SERVER
Ваша группировка в понятие SQL группировки не входит, они разные.

Поэтому нужно расписывать алгоритм (в терминах описания алгоритмов - ЕСЛИ, ТО, ИНАЧЕ) и дальше уже его реализовывать.

Реализовать можно рекурсивно перебирая первичные группы (у которых одно из полей повторяется), по одной группе за цикл рекурсии.
21 сен 11, 11:24    [11310222]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
puhh
Member

Откуда:
Сообщений: 512
Glory
У вас 333 (5,4) через 4ку "наследуется" 400 и 190, а через них 7, а через 7ку еще что-то, и тд и тд
Это называется рекурсия. Потому что вы не знаете, насколько глубока эта цепочка вложенностей



Да. Так и есть.


--====--

ЧТобы не плодить сообщения, код для второго примера (все то же самое, только с номером строки)

declare @B table (row_num int, col1 int, col2 int)
insert into @B (row_num, col1, col2)
select * from
(
select 1 as row_num, 1 as col1, 100 as col2 /*ClusterA*/
union all
select 2 as row_num, 1 as col1, 101 as col2 /*ClusterA*/
union all
select 3 as row_num, 2 as col1, 222 as col2 /*ClusterB*/
union all
select 4 as row_num, 3 as col1, 222 as col2 /*ClusterB*/
union all
select 5 as row_num, 5 as col1, 301 as col2 /*ClusterC*/
union all
select 6 as row_num, 4 as col1, 301 as col2 /*ClusterC*/
union all
select 7 as row_num, 4 as col1, 200 as col2 /*ClusterC*/
union all
select 8 as row_num, 4 as col1, 190 as col2 /*ClusterC*/
union all
select 9 as row_num, 7 as col1, 190 as col2 /*ClusterC*/
) Q


select * from @B
21 сен 11, 11:27    [11310241]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
Glory
Member

Откуда:
Сообщений: 104751
puhh
Да. Так и есть.


--====--

ЧТобы не плодить сообщения, код для второго примера (все то же самое, только с номером строки)

Ну так рекурсивные задача решаются рекурсивными запросами. Причем тут группировка то ?
21 сен 11, 11:32    [11310274]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
shut-up-and-skate
Member

Откуда: ВАО
Сообщений: 4259
puhh,

вот пример

create table #tmptableex123 (
  col1 int,
  col2 int,
  grpn int
)

create table #tmptableex123tmp (
  col int
)

declare @min int,
		@max int,
		@i   int,
		@grp int

insert into #tmptableex123(col1, col2, grpn) values (2,2,0)
insert into #tmptableex123(col1, col2, grpn) values (2,2,0)
insert into #tmptableex123(col1, col2, grpn) values (2,2,0)
insert into #tmptableex123(col1, col2, grpn) values (3,4,0)
insert into #tmptableex123(col1, col2, grpn) values (3,4,0)
insert into #tmptableex123(col1, col2, grpn) values (3,4,0)
insert into #tmptableex123(col1, col2, grpn) values (5,3,0)
insert into #tmptableex123(col1, col2, grpn) values (4,5,0)
insert into #tmptableex123(col1, col2, grpn) values (4,5,0)
insert into #tmptableex123(col1, col2, grpn) values (5,5,0)
insert into #tmptableex123(col1, col2, grpn) values (5,5,0)
insert into #tmptableex123(col1, col2, grpn) values (4,6,0)
insert into #tmptableex123(col1, col2, grpn) values (5,7,0)
insert into #tmptableex123(col1, col2, grpn) values (5,7,0)
insert into #tmptableex123(col1, col2, grpn) values (12,8,0)
insert into #tmptableex123(col1, col2, grpn) values (12,8,0)
insert into #tmptableex123(col1, col2, grpn) values (8,9,0)
insert into #tmptableex123(col1, col2, grpn) values (9,9,0)
insert into #tmptableex123(col1, col2, grpn) values (9,9,0)

select @min = min(col1) from #tmptableex123
select @max = max(col1) from #tmptableex123

select @i = @min
select @grp = 100


while @i <= @max
begin

  -- tmp
  delete from #tmptableex123tmp  
  insert into #tmptableex123tmp values (@i)
  insert into #tmptableex123tmp
  select distinct col1 
  from #tmptableex123
  where col1 in (select distinct col2 from #tmptableex123 where col1 = @i) 
     or col1 = @i
  
  -- grp
  update #tmptableex123 set grpn = @grp
  where #tmptableex123.col1 in (select #tmptableex123tmp.col from #tmptableex123tmp)
    and #tmptableex123.grpn = 0
  
  select @i = @i + 1
  select @grp = @grp + 100
end

select * from #tmptableex123 order by grpn

drop table #tmptableex123tmp
drop table #tmptableex123
21 сен 11, 11:41    [11310360]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
puhh
Member

Откуда:
Сообщений: 512
alexeyvg
Реализовать можно рекурсивно .


Несколько миллионов строк, но уровень вложенности для большинства небольшой (меньше 5). Наверно будет работать не очень долго? (в моем случае полчаса - это нормально)
21 сен 11, 11:43    [11310373]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
puhh
alexeyvg
Реализовать можно рекурсивно .


Несколько миллионов строк, но уровень вложенности для большинства небольшой (меньше 5). Наверно будет работать не очень долго? (в моем случае полчаса - это нормально)
Для милиона за полчаса конечно посчитает. Это не так много.
21 сен 11, 11:48    [11310434]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по одному из столбцов  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Никакая это не рекурсия. Это ещё хуже.
Вот, написал по мотивам последних предложенных нам данных:
SET NOCOUNT ON;
WITH
 B AS
 (
  SELECT * FROM
  (
   VALUES
    (1,1,100)
   ,(2,1,101)
   ,(2,1,101)
   ,(3,2,222)
   ,(4,3,222)
   ,(5,5,301)
   ,(6,4,301)
   ,(7,4,200)
   ,(8,4,190)
   ,(9,7,190)
  )T(row_num,col1,col2)
 )
,CTE(row_num,col1,col2,Cluster) AS
(
 SELECT TOP(1) WITH TIES row_num,col1,col2, CHAR(ASCII('A')+DENSE_RANK()OVER(ORDER BY row_num)-1)
 FROM B
 ORDER BY ROW_NUMBER()OVER(PARTITION BY (SELECT MIN(col)FROM(VALUES(col1),(col2))T(col)) ORDER BY row_num)
 UNION ALL
 SELECT B.row_num,B.col1,B.col2,CTE.Cluster
 FROM CTE JOIN B ON B.row_num>CTE.row_num AND (CTE.col1=B.col1 OR CTE.col2=B.col2)
)
SELECT *
FROM CTE
ORDER BY Cluster,col1,col2
OPTION(MAXRECURSION 0);
Казалось бы, всё тип-топ! Ан нет!
Как выбрать "корневые" узлы, от которых плясать рекурсию?
Ведь в дальнешем выяснится, что выбранный корневой узел должен на самом деле
принадлежать совсем другому множеству, в то время как он с самого начала уже приписан к "своему"!
Ибо не сразу, но найдётся некое "связующее звено" между ними. (см результат, который у меня получился.)

Выходит, мы можеи лишь взять какой-нибудь один "стартовый" узел и рекурсивным CTE (или другим способом)
выбрать все элементы, связанные с ним (входящие в это же множество).
После чего заняться оставшимися записями по аналогии с первым множеством (например, в цикле).
Пока все записи не закончатся.
Множества последовательно помечаем "Кластер А", "Кластер Б" и т.д.

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