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

Откуда: Москва
Сообщений: 4822
-- есть таблица, где DT дата YYYYMMDD отконвертированная в INT, 
-- ID идентификатор сущности, Value - некое значение сущности, NDays время действия этого значения   

DECLARE @T TABLE(dt INT, ID INT, VALUE DECIMAL(20, 3), NDays TINYINT DEFAULT 1)    

-- на первое число месяца запись всегда есть, но если сущность появилась в середине месяца, то первая запись будет датирована более поздним числом
-- необходимо убрать дубликаты VALUE
-- а именно (20140101, 1, 10009.0, 1),...(20140109, 1, 10009.0, 1), должны замениться записью (20140101, 1, 10009.0, 9), 
-- то есть значение 10009.0 для ID = 1 существовало 9 дней потом значение стало другим 
-- задача охватывает список всех значений по каждой сущности за каждый месяц PARTITION BY (pk_date / 100, ID)

INSERT INTO @T VALUES
(20140101, 1, 10009.0, 1),
(20140102, 1, 10009.0, 1),
(20140103, 1, 10009.0, 1),
(20140104, 1, 10009.0, 1),
(20140105, 1, 10009.0, 1),
(20140106, 1, 10009.0, 1),
(20140107, 1, 10009.0, 1),
(20140108, 1, 10009.0, 1),
(20140109, 1, 10009.0, 1),
(20140110, 1, 10000.0, 1),
(20140111, 1, 10000.0, 1),
(20140112, 1, 10000.0, 1),
(20140113, 1, 10000.0, 1),
(20140114, 1, 10000.0, 1),
(20140115, 1, 10000.0, 1),
(20140116, 1, 10000.0, 1),
(20140117, 1, 10000.0, 1),
(20140118, 1, 10000.0, 1),
(20140119, 1, 10000.0, 1),
(20140120, 1, 10000.0, 1),
(20140121, 1, 10000.0, 1),
(20140122, 1, 10000.0, 1),
(20140123, 1, 10001.0, 1),
(20140124, 1, 10001.0, 1),
(20140125, 1, 10001.0, 1),
(20140126, 1, 10001.0, 1),
(20140127, 1, 10001.0, 1),
(20140128, 1, 10001.0, 1),
(20140129, 1, 10001.0, 1),
(20140130, 1, 10001.0, 1),
(20140131, 1, 10001.0, 1),
(20140108, 2, 10000.0, 1),
(20140109, 2, 10000.0, 1),
(20140110, 2, 10000.0, 1),
(20140111, 2, 10000.0, 1),
(20140112, 2, 10000.0, 1),
(20140113, 2, 10001.0, 1),
(20140114, 2, 10001.0, 1),
(20140115, 2, 10001.0, 1),
(20140116, 2, 10001.0, 1),
(20140117, 2, 10001.0, 1),
(20140118, 2, 10001.0, 1),
(20140119, 2, 10001.0, 1),
(20140120, 2, 10001.0, 1),
(20140121, 2, 10001.0, 1),
(20140122, 2, 10001.0, 1),
(20140123, 2, 10001.0, 1),
(20140124, 2, 10001.0, 1),
(20140125, 2, 10001.0, 1),
(20140126, 2, 10002.0, 1),
(20140127, 2, 10002.0, 1),
(20140128, 2, 10002.0, 1),
(20140129, 2, 10002.0, 1),
(20140130, 2, 10002.0, 1),
(20140131, 2, 10002.0, 1)
;


SELECT * 
FROM @T



DECLARE @T1 TABLE(dt INT, ID INT, VALUE DECIMAL(20, 3), NDays TINYINT DEFAULT 1)    
-- конечный результат для примера выше ожидается такой 
INSERT INTO @T1 VALUES
(20140101, 1, 10009.0, 9),
(20140110, 1, 10000.0, 13),
(20140123, 1, 10001.0, 9),
(20140108, 2, 10000.0, 5),
(20140113, 2, 10001.0, 13),
(20140126, 2, 10002.0, 6)
;

SELECT * FROM @T1

-- SQL 2014 -- все поледние оконные функции и навороты к вашим услугам 
-- надо написать SELECT, который за 1 проход без JOIN самого на себя, без получит из первого второе
-- интересует оптимальный по скорости рузельтат на больших объёмах данных 
-- в реальной таблице ID и Value это по несколько полей 
 
29 окт 14, 15:15    [16773240]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
vartAG
Member

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

А где интересная задачка? )

SELECT min(dt), ID, VALUE, SUM(NDays)
FROM @T
group by ID, VALUE
29 окт 14, 15:55    [16773516]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
Добрый Э - Эх
Guest
vartAG
a_voronin,

А где интересная задачка? )

SELECT min(dt), ID, VALUE, SUM(NDays)
FROM @T
group by ID, VALUE
Есть подозрение, что автор неудачно подобрал тестовые данные. Суть задачи, скорее всего - выделить диапазоны действия чередующихся значений. Твой же запрос сольет в единое целое два диапазона с одинаковым VALUE, разделенных диапазоном с другим значением VALUE, выдав на выходе всего две строки. В то время как ТС ожидает в этом случае вывода трех строк...
29 окт 14, 16:04    [16773574]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
Добрый Э - Эх
Guest
a_voronin,

а вообще, задача на инвариант группы. решается через группировку по разности двух разнооконных row_number-ов. Как вариант, можно заморочиться делением данных на группы через LEAD/LAG с последующим накопительным суммированием "флагов" (start_of_group метод).
Оба вариант показывал тут на форуме неоднократно...
29 окт 14, 16:07    [16773597]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
o-o
Guest
vartAG
a_voronin,

А где интересная задачка? )

SELECT min(dt), ID, VALUE, SUM(NDays)
FROM @T
group by ID, VALUE

просто тестовые данные писать тоже надо уметь.
он хочет интервалы непрерывности, а вы все схлопываете в одно.
давайте спросим a_voronin-а,
он что хочет вот на таких данных:
INSERT INTO @T VALUES
(20140101, 1, 10009.0, 1),
(20140102, 1, 10009.1, 1),
(20140103, 1, 10009.1, 1),
(20140104, 1, 10009.0, 1)


(No column name) ID VALUE (No column name)
20140101 1 10009.000 2
20140102 1 10009.100 2
???

думаю, что другое:

(No column name) ID VALUE (No column name)
20140101 1 10009.000 1
20140102 1 10009.100 2
20140104 1 10009.100 1
29 окт 14, 16:09    [16773613]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
Wlr-l
Member

Откуда:
Сообщений: 524
"а именно (20140101, 1, 10009.0, 1),...(20140109, 1, 10009.0, 1), должны замениться записью (20140101, 1, 10009.0, 9)"

Сразу напрашивается group by ID, VALUE и min(dt), SUM(NDays).

Единственное, что смущает это "JOIN самого на себя"
29 окт 14, 16:11    [16773630]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
vartAG
Member

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

А где интересная задачка? )

SELECT min(dt), ID, VALUE, SUM(NDays)
FROM @T
group by ID, VALUE
Есть подозрение, что автор неудачно подобрал тестовые данные. Суть задачи, скорее всего - выделить диапазоны действия чередующихся значений. Твой же запрос сольет в единое целое два диапазона с одинаковым VALUE, разделенных диапазоном с другим значением VALUE, выдав на выходе всего две строки. В то время как ТС ожидает в этом случае вывода трех строк...


Фразу "необходимо убрать дубликаты VALUE" сложно связать с слопыванием интервалов иденчичных значений VALUE )
29 окт 14, 16:19    [16773691]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
Добрый Э - Эх
Guest
вариант на инварианте группы
вариант на start_of_group-е
29 окт 14, 16:26    [16773741]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
Добрый Э - Эх
Guest
vartAG
Фразу "необходимо убрать дубликаты VALUE" сложно связать с слопыванием интервалов иденчичных значений VALUE )
учись читать не то, что написано автором, а то, что он ХОТЕЛ написать. Думаю, через пару-тройку лет на форуме ты разовьешь себе хрустальный шар до нужного уровня. Готов биться об заклад, что автор всё же хочет то, что я написал, а не то, что ты понял...
29 окт 14, 16:28    [16773756]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
Добрый Э - Эх
Guest
vartAG
Фразу "необходимо убрать дубликаты VALUE" сложно связать с слопыванием интервалов иденчичных значений VALUE )
Если что, ключевая фраза в посыле автора:
a_voronin
-- то есть значение 10009.0 для ID = 1 существовало 9 дней потом значение стало другим

как раз и говорит, что нужно найти период непрерывного действия значения.
Просто автор "забыл" упомянуть, что после другого значения вновь могло вернуться первое и его уже нужно было определять в другой интервал...
29 окт 14, 16:31    [16773774]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
Wlr-l
Member

Откуда:
Сообщений: 524
Глядя на исходные данные, можно сделать вывод, что значения VALUE не убывают. Поэтому все приведенные варианты решения дают один и тот же результат.
29 окт 14, 16:55    [16773981]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
Добрый Э - Эх
как раз и говорит, что нужно найти период непрерывного действия значения.
Просто автор "забыл" упомянуть, что после другого значения вновь могло вернуться первое и его уже нужно было определять в другой интервал...


я так вижу автор от лишних данных пытается избавится, если данные идут от датчиков, то зачем хранить повторяюшиеся данные
29 окт 14, 17:01    [16774036]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4822
o-o
думаю, что другое:

(No column name) ID VALUE (No column name)
20140101 1 10009.000 1
20140102 1 10009.100 2
20140104 1 10009.100 1


Да нужно вот это

1111 2222 3333 111

должны стать 1 2 3 1
29 окт 14, 17:07    [16774083]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4822
Wlr-l
Глядя на исходные данные, можно сделать вывод, что значения VALUE не убывают. Поэтому все приведенные варианты решения дают один и тот же результат.


Value могут меняться как угодно в том числе туда обратно 1 1 2 2 3 3 1 1 1 2 2 2 1 1 1
29 окт 14, 17:08    [16774089]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
Wlr-l
Member

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

Скорее не датчики, а счетчики.
29 окт 14, 17:09    [16774097]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4822
Lepsik
Добрый Э - Эх
как раз и говорит, что нужно найти период непрерывного действия значения.
Просто автор "забыл" упомянуть, что после другого значения вновь могло вернуться первое и его уже нужно было определять в другой интервал...


я так вижу автор от лишних данных пытается избавится, если данные идут от датчиков, то зачем хранить повторяюшиеся данные


Вы правы, но ситуация такова, что это унаследованный функционал и я хочу ужать то, что уже накопилось. А данные это срез склада за каждый день. Так что представьте, сколько там записей.

И на самом деле понятно, что то GROUP BY в лоб тут не решение
29 окт 14, 17:11    [16774117]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
a_voronin
Member

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


На самом деле вот это вариант дает то, что нужно. Единственно в partition by надо добавить pk_date / 100 . Добрый Э - Эх крут.

select min(dt) as dt, id, value, sum(NDays)
  from (
         select v0.*, sum(start_of_group) over(partition by id order by dt) as grp_id
           from (
                  select t.*
                       , case lag(value) over(partition by id order by dt)
                           when value then 0 else 1
                         end as start_of_group
                    from t
                ) v0
       ) v1
 group by grp_id, id, value
 order by id, min(dt);
29 окт 14, 17:16    [16774149]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
o-o
Guest
a_voronin,
если мной угадано и надо интервалы непрерывности, то под 2008 вот так
(Добрый Э - Эх вроде то же самое понял):

DECLARE @T TABLE(dt INT, ID INT, VALUE DECIMAL(20, 3), NDays TINYINT DEFAULT 1) 
   
INSERT INTO @T VALUES
(20140101, 1, 10009.0, 1),
(20140102, 1, 10009.1, 1),
(20140103, 1, 10009.1, 1),
(20140104, 1, 10009.0, 1),
(20140108, 2, 10000.0, 1),
(20140109, 2, 10000.3, 1),
(20140110, 2, 10000.0, 1),
(20140111, 2, 10000.0, 1),
(20140112, 1, 10009.1, 1)


;with cte as
(
select dt, id, value,
       dt - row_number() over(partition by id, value order by dt) as grp
from @t) 

select id, value, max(dt) - min(dt) + 1 as duration
from cte
group by grp, id, value;

----
id	value	duration
1	10009.000	1
1	10009.100	2
1	10009.000	1
2	10000.000	1
2	10000.000	2
2	10000.300	1
1	10009.100	1
29 окт 14, 17:17    [16774157]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
Wlr-l
Member

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

Получается от group by никуда не дется + интервалы непрерывности.
29 окт 14, 17:19    [16774180]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
Wlr-l
Member

Откуда:
Сообщений: 524
Интервал непрерывности о-о вычисляет так:

dt - row_number() over(partition by id, value order by dt) as grp


А Добрый Э - Эх так:

, row_number() over(partition by id, value order by dt)
  - row_number() over(partition by id order by dt) as grp_id


Во втором случае результат будет "правильным" даже при пропуске дат, т.е. это более общее решение.
29 окт 14, 17:37    [16774333]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
o-o
Guest
Wlr-l
Во втором случае результат будет "правильным" даже при пропуске дат, т.е. это более общее решение.

у меня в примере данных нет "пропуска дат"?
т.е. вы намекаете, что
(20140103, 1, 10009.1, 1),
(20140112, 1, 10009.1, 1)
-- подряд идущие даты для 1, 10009.1?

кстати, в результате я что-то дату не вывожу, надо исправить:
;with cte as
(
select dt, id, value,
       dt - row_number() over(partition by id, value order by dt) as grp
from @t) 

select min(dt) as dt, id, value, max(dt) - min(dt) + 1 as duration
from cte
group by grp, id, value;

-----------------
dt	id	value	duration
20140101	1	10009.000	1
20140102	1	10009.100	2
20140104	1	10009.000	1
20140108	2	10000.000	1
20140110	2	10000.000	2
20140109	2	10000.300	1
20140112	1	10009.100	1
29 окт 14, 17:50    [16774404]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4822
Wlr-l,

Кстати в случае пропуска дат было бы неплохо получить реальное кол-во дней, то есть dt(2) - dt(1) + 1
29 окт 14, 17:52    [16774414]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
Wlr-l
Member

Откуда:
Сообщений: 524
o-o,
dt - row_number() - предполагает монотонность dt, а пропуски дат (не у Вас, а в исходных данных) нарушают эту монотонность, что может привести к "лишним" интервалам непрерывности.

INSERT INTO @T VALUES
(20140101, 1, 10009.0, 1),
(20140102, 1, 10009.1, 1),
--(20140103, 1, 10009.1, 1), --а если убрать знаки комментария?
(20140104, 1, 10009.1, 1),
(20140105, 1, 10010.1, 1)
29 окт 14, 18:03    [16774458]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
Wlr-l
Member

Откуда:
Сообщений: 524
a_voronin, если есть пропуски, то интервалы непрерывности нужно искать так:

, row_number() over(partition by id, value order by dt)
  - row_number() over(partition by id order by dt) as grp_id

слагаемые можно переставить местами.
29 окт 14, 18:11    [16774479]     Ответить | Цитировать Сообщить модератору
 Re: Кто хочет поломать голову над интересной задачкой SQL 2014  [new]
o-o
Guest
Wlr-l,
убрать комментарий или наоборот оставить?
вы меня запутали.
в каком случае результат не нравится: (1) или (2)?

(1)

(20140101, 1, 10009.0, 1),
(20140102, 1, 10009.1, 1),
--(20140103, 1, 10009.1, 1), --а если убрать знаки комментария?
(20140104, 1, 10009.1, 1),
(20140105, 1, 10010.1, 1)

dt id value duration
20140101 1 10009.000 1
20140102 1 10009.100 1 -- длилось 1 день
20140104 1 10009.100 1 -- длилось 1 день (ну не 3 же! у него же длительность указана: 1 день -- default. 20140103 выпал. о нем науке неизвестно)
20140105 1 10010.100 1

--------------------------------------
(2)

(20140101, 1, 10009.0, 1),
(20140102, 1, 10009.1, 1),
(20140103, 1, 10009.1, 1),
(20140104, 1, 10009.1, 1),
(20140105, 1, 10010.1, 1)

dt id value duration
20140101 1 10009.000 1
20140102 1 10009.100 3 -- длилось 3 дня (20140102, 20140103, 20140104). а сколько надо?
20140105 1 10010.100 1
29 окт 14, 18:14    [16774489]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить