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

Откуда: Винница, Украина
Сообщений: 190
Добрый день!
Есть список
val date
88943 11/01/17
89111 11/02/17
89288 11/03/17
84598 11/06/17
89683 11/07/17
89777 11/08/17
90012 11/09/17
90191 11/10/17
90622 11/13/17
90666 11/14/17
90987 11/15/17
91111 11/16/17
9099 11/17/17
92056 11/20/17
91417 11/21/17
91954 11/22/17
92653 11/24/17
91934 11/27/17
92125 11/28/17
92307 11/29/17
92484 11/30/17

Он отсортирован по дате
Реально ли пройтись по списку и выбросить из него те значения VAL которые не являются инкрементальными (аля водила ошибался при вводе показаний одометра и надо их убрать из списка). Хотелось бы это сделать без курсоров и темповой таблички
15 дек 17, 00:08    [21035388]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
xandr_ukr
Member

Откуда: Винница, Украина
Сообщений: 190
Версия сервера 2016
15 дек 17, 00:10    [21035391]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
felix_ff
Member

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

declare @t table (val int, date datetime);

insert into @t values 
(88943,	'11/01/17'),
(89111,	'11/02/17'),
(89288,	'11/03/17'),
(84598,	'11/06/17'),
(89683,	'11/07/17'),
(89777,	'11/08/17'),
(90012,	'11/09/17'),
(90191,	'11/10/17'),
(90622,	'11/13/17'),
(90666,	'11/14/17'),
(90987,	'11/15/17'),
(91111,	'11/16/17'),
(9099,	'11/17/17'),
(92056,	'11/20/17'),
(91417,	'11/21/17'),
(91954,	'11/22/17'),
(92653,	'11/24/17'),
(91934,	'11/27/17'),
(92125,	'11/28/17'),
(92307,	'11/29/17'),
(92484,	'11/30/17');

with x as (
select lead(val, 1, null) over (order by date) as lg, val, date from @t
)
delete from x where lg < val;

select * from @t;
15 дек 17, 00:26    [21035418]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
xandr_ukr
Member

Откуда: Винница, Украина
Сообщений: 190
felix_ff,

По факту Вы оставляете в списке показатели VAL у которых дата позднее максимального значения 92653
15 дек 17, 01:51    [21035478]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
xandr_ukr
Member

Откуда: Винница, Украина
Сообщений: 190
По сути вот реализация с курсором (добавлено еще условие что последующий показатель не должен быть на 1500 миль больше валидного предыдущего)
DECLARE @tmp table (odom int, dt date, qnt decimal(18,2))

insert into @tmp(odom, dt, qnt)
values
(88943,	'2017-11-01 00:00:00.000',			7.27	),
(89111,	'2017-11-02 00:00:00.000',			6.9	),
(89288,	'2017-11-03 00:00:00.000',			6.99	),
(84598,	'2017-11-06 00:00:00.000',			6.73	),
(89683,	'2017-11-07 00:00:00.000',			8.77	),
(89777,	'2017-11-08 00:00:00.000',			7.83	),
(90012,	'2017-11-09 00:00:00.000',			5.71	),
(90191,	'2017-11-10 00:00:00.000',			7.02	),
(90622,	'2017-11-13 00:00:00.000',			6.79	),
(90666,	'2017-11-14 00:00:00.000',			7.22	),
(90987,	'2017-11-15 00:00:00.000',			7.38	),
(91111,	'2017-11-16 00:00:00.000',			6.97	),
(9099,	'2017-11-17 00:00:00.000',			7.71	),
(92056,	'2017-11-20 00:00:00.000',			6.7	),
(91417,	'2017-11-21 00:00:00.000',			7.22	),
(91954,	'2017-11-22 00:00:00.000',			6.84	),
(92653,	'2017-11-24 00:00:00.000',			6.03	),
(91934,	'2017-11-27 00:00:00.000',			7.42	),
(92125,	'2017-11-28 00:00:00.000',			7.52	),
(92307,	'2017-11-29 00:00:00.000',			7.26	),
(92484,	'2017-11-30 00:00:00.000',			6.87	)

DECLARE @tmp_clear table (odom int, dt date, qnt decimal(18,2))
declare v_cur CURSOR FOR select odom, dt, qnt from @tmp order by dt;
declare @v_odom int, @v_dt date, @v_cur_odom int, @v_qnt decimal(18,2);

open v_cur
FETCH NEXT FROM v_cur INTO @v_odom, @v_dt, @v_qnt
WHILE @@FETCH_STATUS = 0
BEGIN
	select @v_cur_odom = max(odom) from @tmp_clear

	IF (@v_odom > @v_cur_odom AND @v_odom - @v_cur_odom < 1500)
	OR @v_cur_odom IS NULL 
	
		INSERT INTO @tmp_clear values(@v_odom, @v_dt, @v_qnt)
		FETCH NEXT FROM v_cur INTO @v_odom, @v_dt, @v_qnt
END

CLOSE v_cur
DEALLOCATE  v_cur

select  * from @tmp_clear
15 дек 17, 02:01    [21035487]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
xandr_ukr
Member

Откуда: Винница, Украина
Сообщений: 190
Вот еще проще, но с курсором (а хотелось бы без него)
DECLARE @tmp table (odom int, dt date, qnt decimal(18,2))

insert into @tmp(odom, dt, qnt)
values
(88943,	'2017-11-01 00:00:00.000',			7.27	),
(89111,	'2017-11-02 00:00:00.000',			6.9		),
(89288,	'2017-11-03 00:00:00.000',			6.99	),
(84598,	'2017-11-06 00:00:00.000',			6.73	),
(89683,	'2017-11-07 00:00:00.000',			8.77	),
(89777,	'2017-11-08 00:00:00.000',			7.83	),
(90012,	'2017-11-09 00:00:00.000',			5.71	),
(90191,	'2017-11-10 00:00:00.000',			7.02	),
(90622,	'2017-11-13 00:00:00.000',			6.79	),
(90666,	'2017-11-14 00:00:00.000',			7.22	),
(90987,	'2017-11-15 00:00:00.000',			7.38	),
(91111,	'2017-11-16 00:00:00.000',			6.97	),
(9099,	'2017-11-17 00:00:00.000',			7.71	),
(92056,	'2017-11-20 00:00:00.000',			6.7	),
(91417,	'2017-11-21 00:00:00.000',			7.22	),
(91954,	'2017-11-22 00:00:00.000',			6.84	),
(92653,	'2017-11-24 00:00:00.000',			6.03	),
(91934,	'2017-11-27 00:00:00.000',			7.42	),
(92125,	'2017-11-28 00:00:00.000',			7.52	),
(92307,	'2017-11-29 00:00:00.000',			7.26	),
(92484,	'2017-11-30 00:00:00.000',			6.87	)

DECLARE @tmp_clear table (odom int, dt date, qnt decimal(18,2))
declare v_cur CURSOR FOR select odom, dt, qnt from @tmp order by dt;
declare @v_odom int, @v_dt date, @v_cur_odom int, @v_qnt decimal(18,2);

open v_cur
FETCH NEXT FROM v_cur INTO @v_odom, @v_dt, @v_qnt

WHILE @@FETCH_STATUS = 0
BEGIN

	IF (@v_odom > @v_cur_odom AND @v_odom - @v_cur_odom < 1500)
	OR @v_cur_odom IS NULL
	BEGIN
		INSERT INTO @tmp_clear values(@v_odom, @v_dt, @v_qnt)
		SET @v_cur_odom = @v_odom
	END
	FETCH NEXT FROM v_cur INTO @v_odom, @v_dt, @v_qnt
END

CLOSE v_cur
DEALLOCATE  v_cur

select  * from @tmp_clear
15 дек 17, 02:22    [21035497]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
Ennor Tiegael
Member

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

А если водила ошибся в большую сторону один раз, после чего вводил правильно, что будете делать?
15 дек 17, 07:16    [21035566]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3247
Если же буквально, то вот:

declare @t table (Amount int, DT date);

insert into @t values 
(88943,	'11/01/17'),
(89111,	'11/02/17'),
(89288,	'11/03/17'),
(84598,	'11/06/17'),
(89683,	'11/07/17'),
(89777,	'11/08/17'),
(90012,	'11/09/17'),
(90191,	'11/10/17'),
(90622,	'11/13/17'),
(90666,	'11/14/17'),
(90987,	'11/15/17'),
(91111,	'11/16/17'),
(9099,	'11/17/17'),
(92056,	'11/20/17'),
(91417,	'11/21/17'),
(91954,	'11/22/17'),
(92653,	'11/24/17'),
(91934,	'11/27/17'),
(92125,	'11/28/17'),
(92307,	'11/29/17'),
(92484,	'11/30/17');

with cte as (
	select t.*, max(t.Amount) over(order by t.DT) as [MaxAmount] from @t t
)
select c.DT, c.Amount
from cte c
where c.Amount = c.MaxAmount
order by c.DT;

По сути, вариант felix_ff, только учитывается не одно значение, а все.
15 дек 17, 07:28    [21035568]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
aleks222
Guest
DECLARE @tmp table (odom int, dt date, qnt decimal(18,2))

insert into @tmp(odom, dt, qnt)
values
(88943, '2017-11-01 00:00:00.000', 7.27 ),
(89111, '2017-11-02 00:00:00.000', 6.9 ),
(89288, '2017-11-03 00:00:00.000', 6.99 ),
(84598, '2017-11-06 00:00:00.000', 6.73 ),
(89683, '2017-11-07 00:00:00.000', 8.77 ),
(89777, '2017-11-08 00:00:00.000', 7.83 ),
(90012, '2017-11-09 00:00:00.000', 5.71 ),
(90191, '2017-11-10 00:00:00.000', 7.02 ),
(90622, '2017-11-13 00:00:00.000', 6.79 ),
(90666, '2017-11-14 00:00:00.000', 7.22 ),
(90987, '2017-11-15 00:00:00.000', 7.38 ),
(91111, '2017-11-16 00:00:00.000', 6.97 ),
(9099, '2017-11-17 00:00:00.000', 7.71 ),
(92056, '2017-11-20 00:00:00.000', 6.7 ),
(91417, '2017-11-21 00:00:00.000', 7.22 ),
(91954, '2017-11-22 00:00:00.000', 6.84 ),
(92653, '2017-11-24 00:00:00.000', 6.03 ),
(91934, '2017-11-27 00:00:00.000', 7.42 ),
(92125, '2017-11-28 00:00:00.000', 7.52 ),
(92307, '2017-11-29 00:00:00.000', 7.26 ),
(92484, '2017-11-30 00:00:00.000', 6.87 );


DECLARE @clear table (odom int, dt date, qnt decimal(18,2), primary key(odom, dt));

insert @clear select * from @tmp;

declare @rc int = @@rowcount;

while @rc > 0 begin

with t as ( select * from @clear )
delete t
from t cross apply( select top(1) * from t as tt where tt.dt < t.dt order by tt.dt desc ) as p
where t.odom < p.odom;
set @rc = @@rowcount;

with t as ( select * from @clear )
delete t
from t cross apply( select top(1) * from t as tt where tt.dt < t.dt order by tt.dt desc ) as p
where t.odom - p.odom >= 1500;
set @rc = @rc + @@rowcount;

end;

select * from @clear order by dt;
15 дек 17, 07:28    [21035569]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20527
xandr_ukr
пройтись по списку и выбросить из него те значения VAL которые не являются инкрементальными

Задача неоднозначна.
Пример:
VALDate
11/1/1
32/2/2
23/3/3
44/4/4

Какую из записей выбросить - вторую или третью? выбрасывание любой приводит список "в порядок"...
15 дек 17, 08:25    [21035611]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20527
PS. Контролировать надо непосредственно ввод данных, а не итог абы какого ввода.
15 дек 17, 08:26    [21035615]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
982183
Member

Откуда: VL
Сообщений: 3352
Совсем недавно что-то подобное обсуждали про сбои в датчиках.
15 дек 17, 08:53    [21035663]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
xandr_ukr, если делать в одном запросе, то нужно сравнивать и с предыдущим значением, и со следующим, и только если с обеими не сходится, тогда запись будет подозрительной.

Примерно вот так, я просто пометил подозрительные строки, если захотите их удалить (я бы не стал, на самом деле, а отдал бы на ручную перепроверку), то просто перенесите условие в where для запроса на удаление:

declare @t table (Amount int, DT date);

insert into @t values 
(88943,	'11/01/17'),
(89111,	'11/02/17'),
(89288,	'11/03/17'),
(84598,	'11/06/17'),
(89683,	'11/07/17'),
(89777,	'11/08/17'),
(90012,	'11/09/17'),
(90191,	'11/10/17'),
(90622,	'11/13/17'),
(90666,	'11/14/17'),
(90987,	'11/15/17'),
(91111,	'11/16/17'),
(9099,	'11/17/17'),
(92056,	'11/20/17'),
(91417,	'11/21/17'),
(91954,	'11/22/17'),
(92653,	'11/24/17'),
(91934,	'11/27/17'),
(92125,	'11/28/17'),
(92307,	'11/29/17'),
(92484,	'11/30/17');

select  *
    ,   lead(t.Amount, 1) over (order by DT) - t.Amount
    ,   t.Amount - lag(t.Amount, 1) over (order by DT)
    ,  case 
        when not(lead(t.Amount, 1) over (order by DT) - t.Amount between 0 and 1500) and
             not(t.Amount - lag(t.Amount, 1) over (order by DT) between 0 and 1500) 
          then 1 end 
from    @t as t 
15 дек 17, 10:29    [21035979]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20527
Minamoto
если делать в одном запросе, то нужно сравнивать и с предыдущим значением, и со следующим, и только если с обеими не сходится, тогда запись будет подозрительной.
В моём примере опять-таки и 2-я, и 3-я записи "подозрительны". Какую удалять будем?
15 дек 17, 11:41    [21036221]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Akina
Minamoto
если делать в одном запросе, то нужно сравнивать и с предыдущим значением, и со следующим, и только если с обеими не сходится, тогда запись будет подозрительной.
В моём примере опять-таки и 2-я, и 3-я записи "подозрительны". Какую удалять будем?

Minamoto
(я бы не стал, на самом деле, а отдал бы на ручную перепроверку)

Вопрос то не ко мне, а к ТС и к его требованиям к обработанным данным.
15 дек 17, 11:58    [21036291]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
xandr_ukr
Member

Откуда: Винница, Украина
Сообщений: 190
Валидация данных в момент вставки - не моя компетенция. У меня лишь срез данных, по кторому строю отчет
15 дек 17, 12:30    [21036420]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
xandr_ukr
Member

Откуда: Винница, Украина
Сообщений: 190
Minamoto,

Ваш запрос исключает из списка значение 92056, а оно должно быть включено т.к. соседнее после 91111
15 дек 17, 12:32    [21036431]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
xandr_ukr
Member

Откуда: Винница, Украина
Сообщений: 190
Akina,

В Вашем случае верно будет вернуть список 1,3,4
15 дек 17, 12:32    [21036436]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
Добрый Э - Эх
Guest
xandr_ukr
Akina,

В Вашем случае верно будет вернуть список 1,3,4
а каков формальный критерий этой верности?
15 дек 17, 12:36    [21036453]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20527
xandr_ukr
В Вашем случае верно будет вернуть список 1,3,4
Обоснуйте. Как по мне, так список 1,2,4 ничем не хуже...
15 дек 17, 12:39    [21036467]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
xandr_ukr
Minamoto,

Ваш запрос исключает из списка значение 92056, а оно должно быть включено т.к. соседнее после 91111

А после него идут 91417, 91954, значит они - невалидные?
15 дек 17, 12:41    [21036480]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
xandr_ukr
Member

Откуда: Винница, Украина
Сообщений: 190
Minamoto,

Да, они невалидные т.к. уже меньше предыдущего максимального на тот момент - 92056
15 дек 17, 13:01    [21036590]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20527
xandr_ukr, а если "водила ошибся при вводе показаний одометра" и залепил в показания офигеть большое число (скажем, цифра задвоилась, а он прозевал) - то ты так всю историю после этой ошибки удалишь?
15 дек 17, 13:03    [21036604]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Что, неужели найти точки экстремумов и выкинуть их на помойку?
15 дек 17, 13:53    [21036816]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная сортировка  [new]
iii2
Member

Откуда:
Сообщений: 202
Руслан Дамирович
Что, неужели найти точки экстремумов и выкинуть их на помойку?

Да нет, эта задача, похоже, на локальную фильтрацию, ну или полосовую фильтрацию.
Ну там, "левый фильтр" - значение должно быть больше предыдущего, "правый фильтр" - меньше следующего, "срединный фильтр" - в диапазоне между n + 2 и n - 2 значением.
Сглаживание, короче.

ТС пусть вспомнит соответствующий курс из матстатистики, и скажет, какой из вариантов сглаживания его больше устраивает.
17 дек 17, 07:10    [21040200]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить