ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Предикат для сортировки по близости к определенной дате
8 сообщений из 8, страница 1 из 1
Предикат для сортировки по близости к определенной дате
    #39979209
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Коллеги, помогите придумать выражение для сортировки.
Второй час туплю, уперся как баран!

Есть последовательность дат (тип datetime). Даты могут многократно повторяться.
Необходимо отсортировать ее по степени близости к значению заданной переменной @date, по следующему правилу:
1. Даты, которые ближе, по модулю, к указанной дате - имеют меньший вес, чем те, которые дальше (по модулю)
2. Даты, которые расположены на одинаковом, по модулю, расстоянии, но по разные стороны от @date, имеют разный вес. Дата в прошлом от @date - имеет меньший вес, в будущем от @date - больший вес.
3. Сортируем от меньших весов - к большим.
4. Точность сравнения - поминутная.
5. Две даты, различающиеся только секундами - одинаковые по весу.

Есть нюанс.
Отобрать нужно записи с датой из top 10 из списка ближайших, но исходная таблица - содержит примерно миллиард (утрирую, но не сильно) записей.
Причем иногда записи идут очень густо, десятки тысяч в окрестностях часа, иногда - лакуны по неделе между последовательными записями.

Тривиальное решение - взять datediff от даты в записи и точки @date в минутах, вычислить abs от этого, а потом прибавить 0.1*sign от этого datediff - не прокатывает, т.к. никакими индексами это не ускорить, и, как результат, выборка будет длиться вечно.
...
Рейтинг: 0 / 0
13.07.2020, 20:03
| Ответить | Цитировать | Написать  
Предикат для сортировки по близости к определенной дате
    #39979215
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
uaggster,

для знака надо сделать колонку 0/1, а не 0.1*sign. И сортировать по разнице и знаку.
...
Рейтинг: 0 / 0
13.07.2020, 20:13
| Ответить | Цитировать | Написать  
Предикат для сортировки по близости к определенной дате
    #39979217
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
если нужен +/-10 ближайших соседей, то можно попробовать создать индекс по дате и использовать аналитические функции.
...
Рейтинг: 0 / 0
13.07.2020, 20:19
| Ответить | Цитировать | Написать  
Предикат для сортировки по близости к определенной дате
    #39979219
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Как-то так
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select top (10)
 *
from
 (
  select top (10) 0, datediff(mi, dt, @dt), dt from MyTable where dt <= @dt order by dt desc
  union all
  select top (10) 1, datediff(mi, @dt, dt), dt from MyTable where dt >= @dt order by dt
 ) t(f, dtdiff, dt)
order by
 dtdiff, f;
...
Рейтинг: 0 / 0
13.07.2020, 20:24
| Ответить | Цитировать | Написать  
Предикат для сортировки по близости к определенной дате
    #39979225
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
uaggster,

несколько громозко, но в плане только одна сортировка.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
declare @center int = 100;

with tbl1 as(
select top (10) val.number from master.dbo.spt_values val
where type = 'p'
	and val.number >= @center
order by val.number
),
tbl2 as
(
select top (10) val.number from master.dbo.spt_values val
where type = 'p'
	and val.number <= @center
order by val.number desc
),
tbl3 as
(
select number from tbl1
union all
select number from tbl2
)
select top (10) number from tbl3
order by number desc;



PS помахнулся, выборка нерепрезентативная, надо использовать разницу по модулю.
...
Рейтинг: 0 / 0
13.07.2020, 20:38
| Ответить | Цитировать | Написать  
Предикат для сортировки по близости к определенной дате
    #39979240
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
invm
Как-то так
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select top (10)
 *
from
 (
  select top (10) 0, datediff(mi, dt, @dt), dt from MyTable where dt <= @dt order by dt desc
  union all
  select top (10) 1, datediff(mi, @dt, dt), dt from MyTable where dt >= @dt order by dt
 ) t(f, dtdiff, dt)
order by
 dtdiff, f;


Да, тоже нечто подобное думал, но недопилил.
Проблема в том, что нужно отобрать записи with ties.
Т.е, определяем окрестность из 10 самых близких к точке дат, с округлением до минуты.
Потом отбираем все записи с этими датами.

Получается, что для записей раньше точки, нужно отобрать записи с Dense_rank() over (order by округленная до минут дата) =< 10, и аналогично - позже точки. Потом - определить в этой локальной области топ 10 по вышеописанным правилам.
А потом для крайне ранней и крайне поздней точек - выбрать все записи, between между ними.
Как то слишком сложно...
...
Рейтинг: 0 / 0
13.07.2020, 21:54
| Ответить | Цитировать | Написать  
Предикат для сортировки по близости к определенной дате
    #39979371
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
uaggster
Получается, что для записей раньше точки, нужно отобрать записи с Dense_rank() over (order by округленная до минут дата) =< 10,
Без ограничения диапазона с обоих концов, не взлетит.

В голову приходит только один вариант:
- в таблицу добавить выч. столбец вида datediff(mi, convert(datetime, '19000101', 112), [Дата]) as [Минуты], плюс индекс по нему
- индексированное представление
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
create view ...
with schemabinding
as
select
 [Минуты],
 count_big(*) as c
from
 ...
group by
 [Минуты];
go

create unique clustered index ... ([Минуты]);
go



Тогда можно будет по представлению делать отбор и лезть за данными в основную таблицу.
...
Рейтинг: 0 / 0
14.07.2020, 11:50
| Ответить | Цитировать | Написать  
Предикат для сортировки по близости к определенной дате
    #39979390
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Если создать колонку, заполненную разницей между минимальной датой таблицы и датой каждой строки, то задача достаточно просто решается, надо вычислить разницу между минимальной датой и переданной и отобрать по полученному смещению диапазон строк.

Однако, если минимальная дата будет изменяться, то придется пересчитывать весь массив.

С другой стороны, "минимальной" может служить фиксированное значение, например 19000101 и пересчитывать не понадобится.
...
Рейтинг: 0 / 0
14.07.2020, 12:27
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Предикат для сортировки по близости к определенной дате
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&fpage=54&tid=1685882]:
 0ms
get settings:
 11ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 35ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 51ms
get tp. blocked users:
 2ms
others: 19ms
total:  155ms
созд. / загр.: 155ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]