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

Откуда:
Сообщений: 53
В таблицу пишутся NULL значения если это повтор от предыдущего, нужно убрать все NULL-значения.
Думаю, что эту задачу можно решить используя:
- группировку по инварианту группы (разность двух разнооконных row_number-ов) а после стравить функции LAG (возможно я ошибаюсь);
--Тестовый набор данных 
WITH t (id, cod, name, model) AS
(SELECT * FROM (VALUES
(1,1,'AA',25),  (2,1,'AA',NULL), (3,1,'AA',NULL), (4,1,'AA',NULL),  (5,1,'AA',50), (6,1,'AA',NULL), (7,1,'AA',NULL),  (8,1,'AA',NULL), 
(9,1,'BB',100),(10,1,'BB',NULL), (11,1,'BB',NULL),(12,1,'BB',NULL),(13,1,'BB',150), (14,1,'BB',NULL),(15,1,'BB',NULL),(16,1,'BB',NULL))
v(a,b,c,d))
SELECT * FROM t

P.S. мой select получился неуклюжий, просьба помочь.

К сообщению приложен файл. Размер - 11Kb
27 ноя 12, 17:39    [13538445]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Можно так:

WITH t (id, cod, name, model) AS
(SELECT * FROM (VALUES
(1,1,'AA',25),  (2,1,'AA',NULL), (3,1,'AA',NULL), (4,1,'AA',NULL),  (5,1,'AA',50), (6,1,'AA',NULL), (7,1,'AA',NULL),  (8,1,'AA',NULL), 
(9,1,'BB',100),(10,1,'BB',NULL), (11,1,'BB',NULL),(12,1,'BB',NULL),(13,1,'BB',150), (14,1,'BB',NULL),(15,1,'BB',NULL),(16,1,'BB',NULL))
v(a,b,c,d))
SELECT t.*, t2.* FROM t OUTER APPLY (SELECT TOP(1) model FROM t AS t1 WHERE model IS NOT NULL AND t1.id <= t.id ORDER BY t1.id DESC) AS t2
27 ноя 12, 17:59    [13538627]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Добрый Э - Эх
Guest
Neosan
Думаю, что эту задачу можно решить используя:
- группировку по инварианту группы (разность двух разнооконных row_number-ов)
смотрю, меня уже на цитаты разбирают...

Вот забавный вариант под MS SQL Server 2012:
--Тестовый набор данных 
WITH t (id, cod, name, model) AS
(SELECT * FROM (VALUES
(1,1,'AA',25),  (2,1,'AA',NULL), (3,1,'AA',NULL), (4,1,'AA',NULL),  (5,1,'AA',50), (6,1,'AA',NULL), (7,1,'AA',NULL),  (8,1,'AA',NULL), 
(9,1,'BB',100),(10,1,'BB',NULL), (11,1,'BB',NULL),(12,1,'BB',NULL),(13,1,'BB',150), (14,1,'BB',NULL),(15,1,'BB',NULL),(16,1,'BB',NULL))
v(a,b,c,d))
--
--
SELECT t.*,max(model) over(partition by cod, name order by id )
  FROM t

Жаль только что это магия данных в чистом виде...


З.Ы.
Эх, не хватает ignore nulls в оконных функциях...
27 ноя 12, 18:11    [13538696]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Neosan
Member

Откуда:
Сообщений: 53
спасибо за запрос, но нужен запрос именно по группам т.к. если немного изменить к примеру:
WITH t (id, cod, name, model) AS
(SELECT * FROM (VALUES
(1,1,'AA',25),  (2,1,'AA',NULL), (3,1,'AA',NULL), (4,1,'AA',NULL),  (5,1,'AA',50), (6,1,'AA',NULL), (7,1,'AA',NULL),  (8,1,'AA',NULL), 
(9,1,'BB',100),(10,1,'AA',NULL), (11,1,'BB',NULL),(12,1,'BB',NULL),(13,1,'BB',150), (14,1,'BB',NULL),(15,1,'BB',NULL),(16,1,'BB',NULL))
v(a,b,c,d))
SELECT t.*, t2.* 
FROM t OUTER APPLY (SELECT TOP(1) model FROM t AS t1 WHERE model IS NOT NULL AND t1.id <= t.id ORDER BY t1.id DESC) AS t2


К сообщению приложен файл. Размер - 11Kb
27 ноя 12, 18:12    [13538701]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Neosan
Member

Откуда:
Сообщений: 53
Добрый Э - Эх,
это правда смотрел ваши посты
27 ноя 12, 18:14    [13538721]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Neosan
Member

Откуда:
Сообщений: 53
Добрый Э - Эх,
Ваш вариант самый сжатый из всех которые я перебирал (у меня к примеру вышло аж 4-строки кода), спасибо.
27 ноя 12, 18:21    [13538757]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Добрый Э - Эх
Guest
Neosan
Добрый Э - Эх,
Ваш вариант самый сжатый из всех которые я перебирал (у меня к примеру вышло аж 4-строки кода), спасибо.
я же предупредил - вариант завязан на магию данных. В общем случае работать не будет.

Лучше уж вариант на аутер апляе довести до рабочего состояния:

WITH t (id, cod, name, model) AS
(SELECT * FROM (VALUES
(1,1,'AA',25),  (2,1,'AA',NULL), (3,1,'AA',NULL), (4,1,'AA',NULL),  (5,1,'AA',50), (6,1,'AA',NULL), (7,1,'AA',NULL),  (8,1,'AA',NULL), 
(9,1,'BB',100),(10,1,'AA',NULL), (11,1,'BB',NULL),(12,1,'BB',NULL),(13,1,'BB',150), (14,1,'BB',NULL),(15,1,'BB',NULL),(16,1,'BB',NULL))
v(a,b,c,d))
--
-- Основной запрос:
SELECT t.*, t2.model as x_model
  FROM t 
 OUTER APPLY (SELECT TOP(1) model 
                FROM t AS t1
               WHERE t1.model is not null
                 AND t1.id <= t.id
                 and t1.cod = t.cod 
                 and t1.name = t.name
               ORDER BY t1.id DESC) AS t2
27 ноя 12, 18:25    [13538784]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Добрый Э - Эх
Guest
Ну или как вариант на "инварианте" группы:
WITH t (id, cod, name, model) AS
(SELECT * FROM (VALUES
(1,1,'AA',55),  (2,1,'AA',NULL), (3,1,'AA',NULL), (4,1,'AA',NULL),  (5,1,'AA',50), (6,1,'AA',NULL), (7,1,'AA',NULL),  (8,1,'AA',NULL), 
(9,1,'BB',100),(10,1,'AA',NULL), (11,1,'BB',NULL),(12,1,'BB',NULL),(13,1,'BB',150), (14,1,'BB',NULL),(15,1,'BB',NULL),(16,1,'BB',NULL))
v(a,b,c,d))
--
--
select v.id, v.cod, v.name, v.model, 
       max(model)over(partition by cod, name, grp_id) as x_model -- а теперь честная "протяжка", без завязки на магию данных.
  from ( 
         SELECT t.*, 
                sum(model) over(partition by cod, name order by id) as grp_id -- тот самый инвариант группы :)
           FROM t 
       ) as v
27 ноя 12, 18:36    [13538820]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Добрый Э - Эх
Guest
Neosan
P.S. мой select получился неуклюжий, просьба помочь.
Для полноты картины покажи свой вариант...
27 ноя 12, 18:44    [13538863]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Добрый Э - Эх
Ну или как вариант на "инварианте" группы:
sum(model) over(partition by cod, name order by id)

SUM не дружит с order by.

И запрос возвращает
1 1 AA 55 55
2 1 AA NULL 55
3 1 AA NULL 55
4 1 AA NULL 55
5 1 AA 50 55
6 1 AA NULL 55
7 1 AA NULL 55
8 1 AA NULL 55
10 1 AA NULL 55
9 1 BB 100 150
11 1 BB NULL 150
12 1 BB NULL 150
13 1 BB 150 150
14 1 BB NULL 150
15 1 BB NULL 150
16 1 BB NULL 150

Мне кажется, немного не то.
27 ноя 12, 18:59    [13538936]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Добрый Э - Эх
Guest
Jovanny
SUM не дружит с order by.
...
Мне кажется, немного не то.

Раз автор упомянул LAG, стало быть у него MS SQL Server 2012. А в нем SUM очень даже дружит с ORDER BY и запрос вернет ровно то, что требовалось ТС. Если нет под рукой 2012 сервера, можешь проверить на sqlfiddle.com ;)
27 ноя 12, 19:05    [13538960]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Добрый Э - Эх
Guest
Jovanny
SUM не дружит с order by.

Ну и в образовательных целях ссылка на документацию, на всякий случай
27 ноя 12, 19:09    [13538970]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Neosan
Member

Откуда:
Сообщений: 53
Добрый Э - Эх,
как говорил у меня на 4-строки, работает достаточно шустро (учился в том числе на ваших примерах):
WITH t (id, cod, name, model) AS--Тестовый набор данных 
(SELECT * FROM (VALUES
(1,1,'AA',25),  (2,1,'AA',NULL), (3,1,'AA',NULL), (4,1,'AA',NULL),  (5,1,'AA',50), (6,1,'AA',NULL), (7,1,'AA',NULL),  (8,1,'AA',NULL), 
(9,1,'BB',100),(10,1,'AA',NULL), (11,1,'BB',NULL),(12,1,'BB',NULL),(13,1,'BB',150), (14,1,'BB',NULL),(15,1,'BB',NULL),(16,1,'BB',NULL))
v(a,b,c,d))
--
,cte1 AS (SELECT * ,[y1]=ROW_NUMBER() OVER (PARTITION BY cod,name ORDER BY id)-ROW_NUMBER() OVER (PARTITION BY cod,name,model ORDER BY id) FROM t) 
,cte2 AS (SELECT * ,[num]=ISNULL(0*[model],ROW_NUMBER()OVER(PARTITION BY cod,name,y1 ORDER BY id)) FROM cte1)
,cte3 AS (SELECT * ,[TARGET]=LAG(model,num) OVER (PARTITION BY cod,name ORDER BY id) FROM cte2)
--
SELECT id, cod, name, model, target FROM cte3 ORDER BY name,id
27 ноя 12, 19:27    [13539044]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Jovanny
Member

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

Таки да, извиняюсь.
27 ноя 12, 19:27    [13539047]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Neosan
Member

Откуда:
Сообщений: 53
Добрый Э - Эх,
да, интересный код получился с SUM (для mssql 2012), пожалуй короче уже не напишешь, спасибо.
27 ноя 12, 19:37    [13539084]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Добрый Э - Эх
Guest
Neosan
пожалуй короче уже не напишешь, спасибо.
Дождемся следующей версии сервера и будем использовать last_value(model ignore nulls) :)
В чисто иллюстративных целях: запрос под Oracle
27 ноя 12, 20:02    [13539207]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Neosan
Member

Откуда:
Сообщений: 53
Добрый Э - Эх,
считаю краткость (кода) сестра таланта.
В довесок в плане работы, что лучше использовать (что быстрей работает)
для "Вложенных запросов" 1 вар. - описанный в напке CTE или 2 вар. - в теле самого SELECT или все без разницы?

--Пример вышеуказанного кода с описанием вложенных запросов в "шапку" CTE:
,cte1 AS (SELECT * ,[y1]=ROW_NUMBER() OVER (PARTITION BY cod,name ORDER BY id)-ROW_NUMBER() OVER (PARTITION BY cod,name,model ORDER BY id) FROM t) 
,cte2 AS (SELECT * ,[num]=ISNULL(0*[model],ROW_NUMBER()OVER(PARTITION BY cod,name,y1 ORDER BY id)) FROM cte1)
SELECT id, cod, name, model,[target]=LAG(model,num) OVER (PARTITION BY cod,name ORDER BY id) FROM cte2

--Пример того же кода с описанием вложенных запросов в SELECT CTE:
SELECT id, cod, name, model,[target]=LAG(model,num) OVER (PARTITION BY cod,name ORDER BY id) 
FROM (SELECT * ,[num]=ISNULL(0*[model],ROW_NUMBER()OVER(PARTITION BY cod,name,y1 ORDER BY id))
FROM (SELECT * ,[y1]=ROW_NUMBER() OVER (PARTITION BY cod,name ORDER BY id)-ROW_NUMBER() OVER (PARTITION BY cod,name,model ORDER BY id) FROM t) t1) t2
28 ноя 12, 11:39    [13541326]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Добрый Э - Эх
Guest
Neosan,
планы выполнения запросов что показывают? Если планы одинаковы или "эквивалентны", то и разницы в производительности запросов не будет.
28 ноя 12, 13:22    [13542291]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Neosan
Member

Откуда:
Сообщений: 53
Добрый Э - Эх,
понятно (тест на больших данных различия не показал)
, кстати в вашем коде с функциями SUM и MAX оказывается нельзя использовать числа "0" и "меньше 0", но это конечно не критично их всегда можно заменить на положительные числа (для расчета), главное код шустро работает при минимализме (конечно не как в оракле, но надеюсь MS будет улучшать свои функции SQL до того как они будут морально устаревать).
WITH t (id, cod, name, model) AS--Тестовый набор данных 
(SELECT * FROM (VALUES
(1,1,'AA',25),  (2,1,'AA',-1), (30,1,'AA',NULL), (4,1,'AA',NULL),  (5,1,'AA',0), (6,1,'AA',NULL), (7,1,'AA',0),  (8,1,'AA',NULL), 
(9,1,'BB',100),(10,1,'AA',NULL), (11,1,'BB',NULL),(12,1,'BB',-1),(13,1,'BB',150), (14,1,'BB',NULL),(15,1,'BB',-1),(16,1,'BB',NULL))
v(a,b,c,d))
--Вар 2.1. Заполнение NULL-данных вышестоящим значением, через описание вложенных запросов в "шапке" CTE для функций
-- SUM и MAX но нельзя использовать числа "<0" и "0"):
,cte1 AS (SELECT * ,SUM(model) OVER(PARTITION BY cod, name ORDER BY id) AS grp_id FROM t)
SELECT v.*--v.id, v.cod, v.name, v.model
,MAX (model)OVER(PARTITION BY cod, name, grp_id) AS x_model 
FROM cte1 AS v 
WHERE name = 'AA'
ORDER BY id
28 ноя 12, 14:11    [13542832]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Добрый Э - Эх
Guest
Neosan
кстати в вашем коде с функциями SUM и MAX оказывается нельзя использовать числа "0" и "меньше 0"
а также строковые и дата-временные типы данных :)


А если серьезно, то в сумму нужно CASE от исходного поля поставить, или вообще на COUNT заменить:

WITH t (id, cod, name, model) AS
(SELECT * FROM (VALUES
(1,1,'AA',5),  (2,1,'AA',NULL), (3,1,'AA',NULL), (4,1,'AA',NULL),  (5,1,'AA',-5), (6,1,'AA',NULL), (7,1,'AA',NULL),  (8,1,'AA',NULL), 
(9,1,'BB',0),(10,1,'AA',NULL), (11,1,'BB',NULL),(12,1,'BB',NULL),(13,1,'BB',0), (14,1,'BB',NULL),(15,1,'BB',NULL),(16,1,'BB',NULL))
v(a,b,c,d))
--
--
 
         SELECT t.*, 
                sum(model) over(partition by cod, name order by id) as grp_id, -- некорректно работает с нулями и отрицательными значениями
                sum(case when model is not null then 1 end) over(partition by cod, name order by id) as grp_id_1, -- работает как надо
                count(model) over(partition by cod, name order by id) as grp_id_2 -- тоже работает как надо...
           FROM t 
28 ноя 12, 14:45    [13543187]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Neosan
Member

Откуда:
Сообщений: 53
Добрый Э - Эх,
спасибо за пример COUNT на базе вашего примера вывел 2-варианта решения - через MAX и LAG (последний ~ 25% медленней
тестировал на таблице в 100 тыс.строк)
WITH T0 (id, cod, name, model) AS--Тестовый набор данных
(SELECT * FROM (VALUES
(1,1,'AA',45),  (2,1,'AA',-1), (30,1,'AA',NULL), (4,1,'AA',0),  (5,1,'AA',5), (6,1,'AA',0), (7,1,'AA',100),  (8,1,'AA',0), 
(9,1,'BB',100),(10,1,'AA',0), (11,1,'BB',0),(12,1,'BB',-1),(13,1,'BB',150), (14,1,'BB',0),(15,1,'BB',-1),(16,1,'BB',0))
v(a,b,c,d))
--Заметка: для работы агрегатных функций по нумерации требуются NULL-данные.
--Заполнение "0" или NULL-данных вышестоящим значением (функции COUNT и MAX)
,T1 AS (SELECT * ,[xNULLIF]=NULLIF([model],0) FROM T0)--"0" заменяем NULL
,T2 AS (SELECT * ,[xCOUNT]=COUNT(xNULLIF) OVER(PARTITION BY cod, name ORDER BY id) FROM T1)--нумерация группы
,T3 AS (SELECT * ,[xNUM]=ROW_NUMBER()OVER(PARTITION BY cod, name, xCOUNT ORDER BY id) FROM T2)--замена разности 2-разнооконных row_number
--
SELECT A.id, A.cod, A.name, A.model
,[xMAX]=MAX(xNULLIF)OVER(PARTITION BY cod, name, xCOUNT)            --Вар.1 решение через функцию [MAX]
,[xLAG]=LAG(xNULLIF,xNUM-1) OVER (PARTITION BY cod,name ORDER BY id)--Вар.2 решение через функцию [LAG] медленней на 25% 
FROM T3 AS A
WHERE name = 'AA'
ORDER BY name, id

кстати неожиданно для себя нашел замену "Разности 2-разнооконных row_number" - которые давали ошибку на больших данных (одинаковые числа для разных групп
например 12435-1433=4500-6502 в результате одинаковый номер группы 11002), еще раз спасибо и с наилучшими пожеланиями...

К сообщению приложен файл. Размер - 4Kb
28 ноя 12, 19:59    [13545765]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Добрый Э - Эх
Guest
Neosan
"Разности 2-разнооконных row_number" - которые давали ошибку на больших данных (одинаковые числа для разных групп например 12435-1433=4500-6502 в результате одинаковый номер группы 11002),
Тут варианта всего два:
1)Ты неправильно (или не к месту) воспользовался этим методом
2) Ты неправильно провел интерпретацию полученного результата.

А вообще, нумерация групп может повторятся для разных групп. Уникальная идентификация группы формируется по совокупности инварианта и столбца, по которому идет разбиение данных на группы. В этом случае нумерация будет железно уникальной.
29 ноя 12, 05:08    [13547056]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Neosan
Member

Откуда:
Сообщений: 53
Добрый Э - Эх,
что то с разностью разнооконых у меня не лады (может что то упускаю из вида), например:
--ОБУЧЕНИЕ "Инвариант группы"
WITH T0 (id, cod, name, model) AS--Тестовый набор данных
(SELECT * FROM (VALUES
(1,1,'AA',45),  (2,1,'AA',-1), (30,1,'AA',NULL), (4,1,'AA',0),  (5,1,'AA',NULL), (6,1,'AA',0), (7,1,'AA',100),  (8,1,'AA',0), 
(9,1,'BB',100),(10,1,'AA',0), (11,1,'BB',0),(12,1,'BB',0),(13,1,'BB',150), (14,1,'BB',0),(15,1,'BB',-1),(16,1,'BB',0))
v(a,b,c,d))
--Заполнение "0" или NULL-данных вышестоящим значением
,T1 AS (SELECT * ,[xNULLIF]=NULLIF(model,0)FROM T0)--"0" заменяем NULL
,T2 AS (SELECT * ,[x1]=ROW_NUMBER()OVER(PARTITION BY cod, name ORDER BY id)
                 ,[x2]=ROW_NUMBER()OVER(PARTITION BY cod, name, xNULLIF ORDER BY id) FROM T1)
--
SELECT *
FROM T2
WHERE name = 'BB'
ORDER BY name, id


К сообщению приложен файл. Размер - 5Kb
29 ноя 12, 12:53    [13548639]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Добрый Э - Эх
Guest
Инвариант применим там, где есть некий набор последовательно меняющих друг друга и постоянно чередующихся значений, либо там где есть монотонно возрастающая (убывающая) последовательность данных, имеющая разрывы.
Как пример первого - смена состояния объекта: вкл-вкл-вкл-вкл-выкл-выкл-выкл-вкл-вкл-вкл-выкл-выкл-выкл. Нужно собрать последовательно идущие состояния в одну строку и получить на выходе такое: вкл-выкл-вкл-выкл .
Как пример второго - список чисел с пропусками (1-2-3-5-6-8-9-10), и нужно собрать непрерывные последовательности в диапазоны (1-3, 5-6, 8-10)

Вот тут инвариант будет к месту.

А конкретно в твоем случае данный метод "в лоб" неприменим. Ибо здесь группа (поле MODEL) не представляет собой последовательно идущие (в сортировке по ID) или чередующиеся значения.
29 ноя 12, 13:32    [13549038]     Ответить | Цитировать Сообщить модератору
 Re: Заполнение NULL-группы предыдущим значением  [new]
Neosan
Member

Откуда:
Сообщений: 53
Добрый Э - Эх,
понятно т.е. в моем примере лучше всего сработает код на COUNT+MAX
P.S. спасибо за понятное и лаконичное объяснение инварианта (в инете подобные знания приходится по частицам собирать)...
29 ноя 12, 14:05    [13549486]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить