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

Откуда:
Сообщений: 192
Добрый день! Есть табличка
Field Field1 Field2 Field3
1 a null null
2 null b null
3 null null c
4 d e f

Как из нее получить таблицу вида, с минимальными "затратами"

Field Field1 Field2 Field3
1 a b c
2 d b c
3 d e c
4 d e f

То есть для каждого нулового поля строки нужно найти ближайшее нижнее не нуловое
27 апр 17, 14:27    [20440257]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
aleks2
Guest
RuslanSharipov
Добрый день! Есть табличка
Field Field1 Field2 Field3
1 a null null
2 null b null
3 null null c
4 d e f

Как из нее получить таблицу вида, с минимальными "затратами"

Field Field1 Field2 Field3
1 a b c
2 d b c
3 d e c
4 d e f

То есть для каждого нулового поля строки нужно найти ближайшее нижнее не нуловое


UPDATE в цикле while.
27 апр 17, 14:33    [20440288]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
RuslanSharipov
Member

Откуда:
Сообщений: 192
aleks2, а без цикла?
27 апр 17, 14:35    [20440298]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
RuslanSharipov
Member

Откуда:
Сообщений: 192
желательно таким образом чтобы можно было сделать вьюшку
27 апр 17, 14:37    [20440309]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
WITH T AS(SELECT * FROM(VALUES
 (1,'a',null,null)
,(2,null,'b',null)
,(3,null,null,'c')
,(4,'d','e','f'))T(Field,Field1,Field2,Field3))
SELECT Field
,Field1=(SELECT TOP(1) Field1 FROM T TT WHERE TT.Field>=T.Field AND TT.Field1 IS NOT NULL)
,Field2=(SELECT TOP(1) Field2 FROM T TT WHERE TT.Field>=T.Field AND TT.Field2 IS NOT NULL)
,Field3=(SELECT TOP(1) Field3 FROM T TT WHERE TT.Field>=T.Field AND TT.Field3 IS NOT NULL)
FROM T
ORDER BY Field;
27 апр 17, 14:37    [20440314]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
aleks2
Guest
RuslanSharipov
aleks2, а без цикла?


Не надо "без цикла".
Можно, но не надо.
27 апр 17, 14:38    [20440315]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
RuslanSharipov
Member

Откуда:
Сообщений: 192
iap,спасибо огромное))
27 апр 17, 14:55    [20440414]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
rnk
Member

Откуда:
Сообщений: 126
iap
WITH T AS(SELECT * FROM(VALUES
 (1,'a',null,null)
,(2,null,'b',null)
,(3,null,null,'c')
,(4,'d','e','f'))T(Field,Field1,Field2,Field3))
SELECT Field
,Field1=(SELECT TOP(1) Field1 FROM T TT WHERE TT.Field>=T.Field AND TT.Field1 IS NOT NULL)
,Field2=(SELECT TOP(1) Field2 FROM T TT WHERE TT.Field>=T.Field AND TT.Field2 IS NOT NULL)
,Field3=(SELECT TOP(1) Field3 FROM T TT WHERE TT.Field>=T.Field AND TT.Field3 IS NOT NULL)
FROM T
ORDER BY Field;

В реальной ситуации я бы не рассчитывал на то, что данные лежат в таблице строго по порядку возрастания Field.

Так будет корректнее:
WITH T AS(SELECT * FROM(VALUES
 (1,'a',null,null)
,(2,null,'b',null)
,(3,null,null,'c')
,(4,'d','e','f'))T(Field,Field1,Field2,Field3))
SELECT Field
,Field1=(SELECT TOP(1) Field1 FROM T TT WHERE TT.Field>=T.Field AND TT.Field1 IS NOT NULL ORDER BY Field)
,Field2=(SELECT TOP(1) Field2 FROM T TT WHERE TT.Field>=T.Field AND TT.Field2 IS NOT NULL ORDER BY Field)
,Field3=(SELECT TOP(1) Field3 FROM T TT WHERE TT.Field>=T.Field AND TT.Field3 IS NOT NULL ORDER BY Field)
FROM T
ORDER BY Field;
27 апр 17, 15:03    [20440473]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
RuslanSharipov
Member

Откуда:
Сообщений: 192
iap,а есть менее затратный способ не используя подзапрос для каждого поля?
27 апр 17, 15:03    [20440476]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
RuslanSharipov
iap,спасибо огромное))
А ято глупость написал!
Исправляюсь:
WITH T AS(SELECT * FROM(VALUES
 (1,'a',null,null)
,(2,null,'b',null)
,(3,null,null,'c')
,(4,'d','e','f'))T(Field,Field1,Field2,Field3))
SELECT Field
,Field1=(SELECT TOP(1) Field1 FROM T TT WHERE TT.Field>=T.Field AND TT.Field1 IS NOT NULL ORDER BY TT.Field)
,Field2=(SELECT TOP(1) Field2 FROM T TT WHERE TT.Field>=T.Field AND TT.Field2 IS NOT NULL ORDER BY TT.Field)
,Field3=(SELECT TOP(1) Field3 FROM T TT WHERE TT.Field>=T.Field AND TT.Field3 IS NOT NULL ORDER BY TT.Field)
FROM T
ORDER BY Field;
27 апр 17, 15:04    [20440485]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
RuslanSharipov
Member

Откуда:
Сообщений: 192
RuslanSharipov
iap,а есть менее затратный способ не используя подзапрос для каждого поля?
27 апр 17, 15:08    [20440505]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
RuslanSharipov
iap,а есть менее затратный способ не используя подзапрос для каждого поля?
WITH T AS(SELECT * FROM(VALUES
 (1,'a',null,null)
,(2,null,'b',null)
,(3,null,null,'c')
,(4,'d','e','f'))T(Field,Field1,Field2,Field3))
SELECT T.Field,TT.Field1,TT.Field2,TT.Field3
FROM T OUTER APPLY(SELECT Field1=MIN(Field1),Field2=MIN(Field2),Field3=MIN(Field3) FROM T TT WHERE TT.Field>=T.Field) TT
ORDER BY T.Field;
27 апр 17, 15:09    [20440508]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
Только это практически то же самое...
27 апр 17, 15:09    [20440509]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
iap,

MIN как то слабо подходит
27 апр 17, 15:10    [20440511]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
TaPaK
iap,

MIN как то слабо подходит
Кстати, действительно... Нужно упорядочивать-то по Field, а не по FieldN
27 апр 17, 15:12    [20440527]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
RuslanSharipov
Member

Откуда:
Сообщений: 192
iap,может через over проранжировать каким то образом и сопоставить друг другу исходя из ранка. Только не совсем представляю пока как :-)
27 апр 17, 15:21    [20440594]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
RuslanSharipov
iap,может через over проранжировать каким то образом и сопоставить друг другу исходя из ранка. Только не совсем представляю пока как :-)

если у вас цифры, то можно и через окна, иначе это будет некорректно с MIN/MAX
27 апр 17, 15:43    [20440738]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
RuslanSharipov
Member

Откуда:
Сообщений: 192
TaPaK, Field - дата, все остальное строки
27 апр 17, 15:45    [20440751]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
1
Guest
declare @t table (Field int, Field1 varchar, Field2 varchar, Field3 varchar)

insert into @t
 values 
 (1, 'a', null, null),
 (2, null, 'b', null),
 (3, null, null, 'c'),
 (4, 'd', 'e', 'f')

/*
Field Field1 Field2 Field3
 1 a b c
 2 d b c
 3 d e c
 4 d e f
*/
;with cte as (
select top 1 * from @t order by Field desc

union all

select a.Field, isnull(a.Field1,b.Field1), isnull(a.Field2,b.Field2), isnull(a.Field3,b.Field3)
from @t a inner join cte b on a.Field=b.Field-1
)
select * from cte order by 1
27 апр 17, 15:46    [20440756]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
rnk
Member

Откуда:
Сообщений: 126
1
declare @t table (Field int, Field1 varchar, Field2 varchar, Field3 varchar)

insert into @t
 values 
 (1, 'a', null, null),
 (2, null, 'b', null),
 (3, null, null, 'c'),
 (4, 'd', 'e', 'f')

;with cte as (
select top 1 * from @t order by Field desc

union all

select a.Field, isnull(a.Field1,b.Field1), isnull(a.Field2,b.Field2), isnull(a.Field3,b.Field3)
from @t a inner join cte b on a.Field=b.Field-1
)
select * from cte order by 1

Остроумно. Но в реальной работе айдишники могут иметь "дыры", и тогда условие a.Field=b.Field-1 оборвёт цикл.
27 апр 17, 16:36    [20441003]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
rnk,

автор
Остроумно. Но в реальной работе айдишники могут иметь "дыры", и тогда условие a.Field=b.Field-1 оборвёт цикл.

это просто решается предварительной нумерацией
27 апр 17, 16:39    [20441019]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
rnk
Member

Откуда:
Сообщений: 126
TaPaK
это просто решается предварительной нумерацией

Можно и без предварительной нумерации:
declare @t table (Field int, Field1 varchar, Field2 varchar, Field3 varchar)

insert into @t
 values 
 (1, 'a', null, null),
 (3, null, 'b', null),
 (7, null, null, 'c'),
 (15, 'd', 'e', 'f')

/*
Field Field1 Field2 Field3
 1 a b c
 2 d b c
 3 d e c
 4 d e f
*/
;
with t as (select *,ROW_NUMBER() over (order by Field) as num from @t),
cte as (select top 1 * from t order by num desc
union all
select a.Field, isnull(a.Field1,b.Field1), isnull(a.Field2,b.Field2), isnull(a.Field3,b.Field3), a.num
from t a inner join cte b on a.num=b.num-1
)
select Field,Field1,Field2,Field3 from cte order by 1
27 апр 17, 16:55    [20441103]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
У меня было четкое чувство, что я встречался с этой задачей раньше, но в обратном порядке.

Попытался воспроизвести решение сам через оконные функции - и не смог. поэтому пришлось поискать.

Нашел таки:

WITH T AS(SELECT * FROM(VALUES
 (1,'a',null,null)
,(2,null,'b',null)
,(3,null,null,'c')
,(4,'d','e','f'))T(Field,Field1,Field2,Field3))
select	Field
	,	cast(substring(min(cast(T.Field as binary(4)) + cast(T.Field1 as binary(1))) over (ORDER BY T.Field rows between current row and unbounded following), 5, 1) as char(1))
	,	cast(substring(min(cast(T.Field as binary(4)) + cast(T.Field2 as binary(1))) over (ORDER BY T.Field rows between current row and unbounded following), 5, 1) as char(1))
	,	cast(substring(min(cast(T.Field as binary(4)) + cast(T.Field3 as binary(1))) over (ORDER BY T.Field rows between current row and unbounded following), 5, 1) as char(1))
from T
order by T.Field;

вместо binary(1), понятное дело, должно быть binary подходящего к полю размера (для цифр - 4), во второй части substring тоже должен быть этот размер и верхний cast должен приводить к нужному типу.

Нашел решение здесь: http://sqlmag.com/t-sql/last-non-null-puzzle
27 апр 17, 16:58    [20441113]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
Для скорости необходимо заранее вычислять искомые значения и поместить их в таблицу.
27 апр 17, 16:59    [20441120]     Ответить | Цитировать Сообщить модератору
 Re: Запрос  [new]
1
Guest
Minamoto
Нашел решение здесь: http://sqlmag.com/t-sql/last-non-null-puzzle

+1
а я другой вариант оттуда же вспомнил
тоже можно обойтись без предварительной перенумерации

declare @t table (Field int, Field1 varchar, Field2 varchar, Field3 varchar)

insert into @t
 values 
 (1, 'a', null, null),
 (2, null, 'b', null),
 (5, null, null, 'c'),
 (10, 'd', 'e', 'f')

/*
Field Field1 Field2 Field3
 1 a b c
 2 d b c
 3 d e c
 4 d e f
*/

;WITH C AS
(
SELECT Field, Field1, Field2, Field3 
    ,min(relevantid1) OVER( ORDER BY Field desc ROWS UNBOUNDED PRECEDING ) AS grp1
    ,min(relevantid2) OVER( ORDER BY Field desc ROWS UNBOUNDED PRECEDING ) AS grp2
    ,min(relevantid3) OVER( ORDER BY Field desc ROWS UNBOUNDED PRECEDING ) AS grp3
FROM @t 
    CROSS APPLY ( VALUES( CASE WHEN Field1 IS NOT NULL THEN Field END ) ) AS A1(relevantid1)
    CROSS APPLY ( VALUES( CASE WHEN Field2 IS NOT NULL THEN Field END ) ) AS A2(relevantid2)
    CROSS APPLY ( VALUES( CASE WHEN Field3 IS NOT NULL THEN Field END ) ) AS A3(relevantid3)
)
SELECT Field /*, Field1, Field2, Field3*/
	,min(Field1) OVER( PARTITION BY grp1 ORDER BY Field desc ROWS UNBOUNDED PRECEDING ) AS ResField1
	,min(Field2) OVER( PARTITION BY grp2 ORDER BY Field desc ROWS UNBOUNDED PRECEDING ) AS ResField2
	,min(Field3) OVER( PARTITION BY grp3 ORDER BY Field desc ROWS UNBOUNDED PRECEDING ) AS ResField3
FROM C
order by Field
27 апр 17, 17:12    [20441167]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить