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

Откуда: Москва
Сообщений: 91
Это данные.

DROP TABLE d1
DROP TABLE d2

CREATE TABLE d1
(
a NVARCHAR(1),
b INT,
c INT
)
CREATE TABLE d2
(
a NVARCHAR(1),
b INT
)

INSERT INTO d1
VALUES
( 'a', 1, 1 ),('b', 1, 2),('c', 1, 3),('b', 1, 4),('b', 1, 5),('d', 1, 6)

INSERT INTO d2
VALUES
( 'a', 1 ),('b', 1),('c', 1),('b', 2), ('d', 1)

SELECT *
FROM d1 d

SELECT *
FROM d2 d

А сама задача заключается вот в чем:
необходимо строки в 2 таблицах связать друг с другом таким образом:
Ранжированные функции особо не помогли, есть другие идеи как проранжировать таким образом чтобы сформировать данную таблицу? (см. файл)

К сообщению приложен файл. Размер - 8Kb
24 сен 12, 19:08    [13214811]     Ответить | Цитировать Сообщить модератору
 Re: Задача голову поломать  [new]
Storkich
Member

Откуда: Москва "Крок"
Сообщений: 5
Нужно пронумеровать группы(последовательности), но как это сделать, пока не знаю.
Вот тебе шаблон для тестирования, в комментах указано что должно быть.
DECLARE @t TABLE (o INT, color VARCHAR(10));
INSERT INTO @t
( o,color)
VALUES
(0, 'A'), --1
(1, 'A'), --1
(2, 'B'), --2
(3, 'B'), --2
(4, 'A'), --3
(5, 'B'), --4
(6, 'A'), --5
(7, 'B'), --6
(8, 'C'), --7
(9, 'C')  --7
SELECT  RANK() OVER(PARTITION BY color ORDER BY o),
       ROW_NUMBER() OVER(ORDER BY o),
       *
FROM   @t t
ORDER BY
       t.o
24 сен 12, 19:27    [13214886]     Ответить | Цитировать Сообщить модератору
 Re: Задача голову поломать  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Постановка задачи неполная. Не определён порядок записей в d2.
Поэтому решения не существует.
24 сен 12, 21:30    [13215461]     Ответить | Цитировать Сообщить модератору
 Re: Задача голову поломать  [new]
vpozhidaev
Member

Откуда: Москва
Сообщений: 91
Я немного неверно сформулировал задачу. Главная цель как сказал Storkich в том, что необходимо пронумеровать группы элементов(справа) для которых элементы слева будут являться порядковыми номерами групп. Самое важное в этой задаче сохранить номера элементов слева, к какой бы группе справа они не принадлежали. (пример элементы B слева, 2 и 3 должны быть у 2 B справа, а вот 4 B слева должен быть именно у четвертого B справа)
24 сен 12, 22:49    [13215736]     Ответить | Цитировать Сообщить модератору
 Re: Задача голову поломать  [new]
vpozhidaev
Member

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

Файл отклеился.

К сообщению приложен файл. Размер - 8Kb
24 сен 12, 22:50    [13215737]     Ответить | Цитировать Сообщить модератору
 Re: Задача голову поломать  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
А в чём сложность? Уже много раз решались похожие задачи на форуме.
;WITH
  D1 AS(SELECT * FROM (VALUES
	 (1,'a',1)
	,(2,'b',1)
	,(3,'c',1)
	,(4,'b',1)
	,(5,'b',1)
	,(6,'d',1)
) D1(ID,Code,X))
, D2 AS(SELECT * FROM (VALUES
	 (1,'a',1)
	,(2,'b',1)
	,(3,'c',1)
	,(4,'b',2)
	,(5,'d',1)
) D2(ID,Code,X))
, D3 AS(SELECT	 ID
		,Code
		,Dense_Rank()OVER(PARTITION BY Code ORDER BY Num)	AS Num
		,X
	FROM	(
	SELECT	 ID
		,Code
		,ID - Row_Number()OVER(PARTITION BY Code ORDER BY ID)	AS Num
		,X
	FROM	D1)D
),D4 AS(SELECT	 ID
		,Code
		,Dense_Rank()OVER(PARTITION BY Code ORDER BY Num)	AS Num
		,X
	FROM	(
	SELECT	 ID
		,Code
		,ID - Row_Number()OVER(PARTITION BY Code ORDER BY ID)	AS Num
		,X
	FROM	D2)D
)	SELECT	*
	FROM	D3 D1	JOIN
		D4 D2	 ON D2.Code = D1.Code
			AND D2.Num  = D1.Num 
	ORDER BY D1.ID
Уверен, что в моём коде "на вскидку" много лишнего.

PS: Ссори, писал на постгре.
25 сен 12, 03:01    [13216320]     Ответить | Цитировать Сообщить модератору
 Re: Задача голову поломать  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8877
Mnior
А в чём сложность? Уже много раз решались похожие задачи на форуме.
...
, D2 AS(SELECT * FROM (VALUES
	 (1,'a',1)
	,(2,'b',1)
	,(3,'c',1)
	,(4,'b',2)
	,(5,'d',1)
) D2(ID,Code,X))
...
Уверен, что в моём коде "на вскидку" много лишнего.

PS: Ссори, писал на постгре.


... эм... с чего бы D2 хардкодом?
25 сен 12, 09:00    [13216528]     Ответить | Цитировать Сообщить модератору
 Re: Задача голову поломать  [new]
Storkich
Member

Откуда: Москва "Крок"
Сообщений: 5
Название топика отстой. Поисковиком потом не выйдешь на решение.
MS SQL как пронумеровать повторяющиеся последовательности или группы?
Нужно пронумеровать все строки определив порядок(сортировку), пронумеровать элементы в группах в той же последовательности(сортировке), и вычислить разность.
Спасибо за
Mnior
ID - Row_Number()

Как разбить на последовательности:
DECLARE @t TABLE (o INT, color VARCHAR(10));
INSERT INTO @t
( o,color)
VALUES
(0, 'A'), --1
(1, 'B'), --1
(2, 'B'), --2
(3, 'B'), --2
(4, 'A'), --3
(5, 'B'), --4
(6, 'A'), --5
(7, 'B'), --6
(8, 'C'), --7
(9, 'C')  --7

SELECT *, ROW_NUMBER() OVER(ORDER BY t.o) - ROW_NUMBER() OVER(PARTITION BY color ORDER BY t.o) SequenceID
FROM   @t t
ORDER BY o
25 сен 12, 10:34    [13216997]     Ответить | Цитировать Сообщить модератору
 Re: Задача голову поломать  [new]
Storkich
Member

Откуда: Москва "Крок"
Сообщений: 5
Вот так правильне пронумеровать при помощи DENSE_RANK()
DECLARE @t TABLE (o INT, color VARCHAR(10));
INSERT INTO @t
( o,color)
VALUES
(0, 'A'), --1
(1, 'B'), --1
(2, 'B'), --2
(3, 'B'), --2
(4, 'A'), --3
(5, 'B'), --4
(6, 'A'), --5
(7, 'B'), --6
(8, 'C'), --7
(9, 'C')  --7
SELECT *, DENSE_RANK() OVER(ORDER BY r.rnk)
FROM   (
           SELECT *, ROW_NUMBER()OVER(ORDER BY s.o) -ROW_NUMBER() OVER(PARTITION BY SequenceID, color ORDER BY s.o) rnk
           FROM   (
                      SELECT *, ROW_NUMBER() OVER(ORDER BY t.o) - ROW_NUMBER() OVER(PARTITION BY color ORDER BY t.o) SequenceID
                      FROM   @t t
                  ) s
       )r
ORDER BY
       o
25 сен 12, 12:15    [13217857]     Ответить | Цитировать Сообщить модератору
 Re: Задача голову поломать  [new]
Storkich
Member

Откуда: Москва "Крок"
Сообщений: 5
А вот через апдейт, вот только не знаю как сортировку задать
DECLARE @t TABLE (o INT, color VARCHAR(10), s INT);
INSERT INTO @t
( o,color)
VALUES
(0, 'A'), --1
(1, 'B'), --1
(2, 'B'), --2
(3, 'B'), --2
(4, 'A'), --3
(5, 'B'), --4
(6, 'A'), --5
(7, 'B'), --6
(8, 'C'), --7
(9, 'C')  --7
DECLARE @q     AS INT,
        @c     VARCHAR(10)

SELECT @q = 0,
       @c = ''

UPDATE t
SET    @q = CASE 
                 WHEN t.color <> @c THEN 
                   @q + 1
                 ELSE 
                   @q
            END,
       s = @q,
       @c = t.color
FROM   @t AS t


SELECT *
FROM   @t
25 сен 12, 12:50    [13218179]     Ответить | Цитировать Сообщить модератору
 Re: Задача голову поломать  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
SIMPLicity_
... эм... с чего бы D2 хардкодом?
Кхм. Дано же по условию две таблы, и нужно их связать.
25 сен 12, 18:47    [13221066]     Ответить | Цитировать Сообщить модератору
 Re: Задача голову поломать  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Storkich, вы что не решали задачу о Робин Гуде?:
Робин Гуда поймали, но дали шанс. Вот две двери, одна на свободу, другая на казнь. У каждой стоит охранник. Один из них полный лгун, другой всегда правдив. Задай всего один вопрос, любому, но такой что можно ответить либо ДА или НЕТ, и выбирай дверь.

Вы пытаетесь сначала пронумеровать их по отдельности. А зачем?
Соответственно в моём решении нет нумеровки (выброшено), но при этом задача решается - таблицы связаны.

Представить куб в 4х мерном пространстве легко, сначала представляешь его в N мерном, а потом приравниваешь N = 4.

Ну и последнее можно проще (на одну оконную функцию):
WITH D1 AS(SELECT * FROM (VALUES
	 (0,'A')
	,(1,'B')
	,(2,'B')
	,(3,'B')
	,(4,'A')
	,(5,'B')
	,(6,'A')
	,(7,'B')
	,(8,'C')
	,(9,'C')
) D1(ID,Code))

SELECT	*,Dense_Rank()OVER(ORDER BY Grp)			FROM (
SELECT	*,Min(ID)     OVER(PARTITION BY Code,Seq)	 AS Grp	FROM (
SELECT	*,Row_Number()OVER(ORDER BY ID)
	- Row_Number()OVER(PARTITION BY Code ORDER BY ID)AS Seq
FROM	D1	D ) Q ) Q
ORDER BY ID

Storkich
А вот через апдейт, вот только не знаю как сортировку задать
Гарантированного варианта нет, но можно сделать что на тестовых данных работал. Индексы обычно способствуют.
25 сен 12, 23:01    [13221922]     Ответить | Цитировать Сообщить модератору
 Re: Задача голову поломать  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8877
Mnior
Storkich, вы что не решали задачу о Робин Гуде?:
Робин Гуда поймали, но дали шанс. Вот две двери, одна на свободу, другая на казнь. У каждой стоит охранник. Один из них полный лгун, другой всегда правдив. Задай всего один вопрос, любому, но такой что можно ответить либо ДА или НЕТ, и выбирай дверь.


Спросить у ближайшего охранника, что ответит его напарник на вопрос о том, ведёт ли охраняемая (этим напарником) дверь на свободу.

Но я не совсем соотнёс героя с луком и яйцами (но который не пирожок) с представленной задачей.
И мне почему-то подумалось, что правая таблица должна формироваться динамически из левой таблицы...
26 сен 12, 02:12    [13222489]     Ответить | Цитировать Сообщить модератору
 Re: Задача голову поломать  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
SIMPLicity_, спойлярить надо. Тут же Чиполины одна школота гуляет.
26 сен 12, 12:07    [13224197]     Ответить | Цитировать Сообщить модератору
 Re: Задача голову поломать  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8877
Mnior
SIMPLicity_, спойлярить надо. Тут же Чиполины одна школота гуляет.

Ну напиши модеру чтоп удалили...
26 сен 12, 12:19    [13224318]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить