SQLITE. Выбор 10 ближайших значений к заданному

32
20 января 2022, 07:30

В таблице пользователи есть колонка возраст. Мне нужно получить 10 ближайших значений к этому возрасту (в любом направлении, как старше, так и младше). Я знаю про ф-ю min и max, но они возвращают 1 значение. Ф-я between хорошо подходит, но не понятно какой диапазон задавать. Можно с помощью ф-и min и max в цикле к БД обращаться, и каждый раз брать новый минимум и максимум (игнорируя старые), можно так же с помощью ф-и between обрабатывать дальнейший вывод. Но хочется стандартными методами СУБД это сделать, т.к. иначе будет теряться производительность.

Answer 1

Для этих целей хорошо подходит функция взятия модуля ABS, которая есть и в MySQL и в SQLite.

Чтобы получить ближайшие значения, надо отсортировать пользователей по модулю разницы между их возрастом и заданным возрастом. С помощью LIMIT можно получить только 10 ближайших значений.

  SELECT Users.*
    FROM Users
ORDER BY ABS(@age - age)
   LIMIT 10

UPDATE

Пояснения. Сначала мы вычисляем разницу между возрастом, заданным в переменной @age и возрастом каждого пользователя. К сожалению, у нас может получиться и -10 и 1, при этом 1 гораздо ближе к заданному возрасту, чем -10, но -10 формально меньше.

Чтобы избавиться от этой асимметрии, мы берём разницу по модулю. Функция ABS возвращает число без знака, в результате -10 превратится в 10, а 1 останется 1.

После функции ABS 1 будет меньше 10, то есть разница в один год будет меньше разницы в 10 лет независимо от порядка величин при сравнении.

Клауза ORDER BY сортирует пользователей по указанному значению. Обычно сортируют по одному или двум полям, но SQL-сервер способен сортировать записи и по сложным выражениям. В самом начале у нас будут пользователи с наименьшей разницей в возрасте, а затем всё с большей.

Финальный штрих это клауза LIMIT 10, который ограничивает размер выборки десятью. Так мы получаем 10 пользователей с наименьшей разницей в возрасте от указанного в переменной @age.

Answer 2
WITH cte AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY age) rn
              FROM sourcetable )
SELECT *
FROM cte
WHERE rn - ( SELECT rn
             FROM cte
             WHERE user_id = ? ) BETWEEN -10 AND 10
READ ALSO
Проблема при установке MySQL Server 8.0

Проблема при установке MySQL Server 8.0

Кто-нибудь знает в чем может быть дело? При установке MySQL Installer Community на этапе конфигурации просит ввести старый пароль root Current Root PasswordЯ не помню...

95
Экспорт из Excel и импорт в MySql

Экспорт из Excel и импорт в MySql

Есть огромная база в excelЗнаю, что можно тупо сохранить файл в csv и импортировать его в базу данных mysql

97
Как с помощью Python добавить инкрементируемый объект в MySQL?

Как с помощью Python добавить инкрементируемый объект в MySQL?

подскажите, пожалуйста: Есть таблица

95
Пауза между скриптами Js

Пауза между скриптами Js

есть 3 скрипта асинхронных, и каждый ждет другого, но выполняется он 1 минуту, и потом ничего не происходит, что и как поправитьЯ использую...

51