Равномерная выборка записей в Postgres

#sql #postgresql

Доброй ночи, господа.
Возникла необходимость решить проблему равномерности выборки данных из бд.
В качестве более понятного примера:
Нужно выбрать 120 записей с весом от 10 до 12 кг, при этом материал должен быть например
пластик или металл.
И вроде бы просто должно быть нечто вроде:

select obj.*
from objects obj
where obj.weight >= 10 and obj.weight <= 12 and
(obj.material = 'пластик' or obj.material = 'металл' )
limit 120;


Но проблема в том что такой запрос вполне вероятно на большой базе выдаст мне 120
записей с весом 10 кг и материалом - металл, что в моей ситуации не подходит.

Нужна именно равномерная выборка: 20 пластиковых по 10 кг, 20 пластиковых по 11 кг,
20 пластиковых по 12 кг, 20 металлических по 10 кг и далее по аналогии. Итого в сумме
как раз те 120 требуемых.

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

Может быть я изобретаю велосипед и существует готовое решение моей проблемы или же
более оптимальное направление решения? Работаю с PostgreSQL.
    

Ответы

Ответ 1

Чуть доработанный запрос Мелкий:

SELECT *
FROM(
  SELECT ROW_NUMER()OVER(ORDER BY order_in_group)AS total_order, T.* 
  FROM (
    SELECT obj.*,
      ROW_NUMBER() OVER (PARTITION BY material, weight) AS order_in_group,
    FROM objects obj
    WHERE obj.weight >= 10 and obj.weight <= 12 and
      (obj.material = 'пластик' or obj.material = 'металл' )
  )T
)T
WHERE total_order <= 120;


Для каждого уникального набора значений material, weight мы пронумеровали строки(order_in_group).
затем нумеруем с сортировкой по полученному номеру (total_order) - и берём первые
120 записей. Таким образом мы не зная сколько значений материалов и веса, выдадим от
каждой группы значений поровну(или все, если значений в какой-то группе не хватает). 

Т.е. решаем проблему:недостатка в количестве записей по одной из групп условий.

Если хочется каждый раз в результате запроса видеть различные данные. можно слегка
изменить сортировку order_in_group: ROW_NUMBER() OVER (PARTITION BY material, weight
ORDER BY random()) 

ps: вторая нумерация не обязательна, это я для наглядности + мне не нравится использовать
нестандартный limit. Но можно написать чуть проще:

SELECT T.* 
FROM (
  SELECT obj.*,
    ROW_NUMBER() OVER (PARTITION BY material, weight) AS order_in_group,
  FROM objects obj
  WHERE obj.weight >= 10 and obj.weight <= 12 and
    (obj.material = 'пластик' or obj.material = 'металл' )
)T
ORDER BY order_in_group
LIMIT 120;


Ответ 2

SELECT /**/ FROM (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY material, weight ORDER BY /*по какому критерию
отсортировать записи в группе и выбрать 10 из всех подходящих*/) AS lim,
    /**/
  from objects obj
  where obj.weight >= 10 and obj.weight <= 12 and
    (obj.material = 'пластик' or obj.material = 'металл' )
) x
WHERE x.lim <= 10;


Будут возвращены по 10 записей каждого имеющегося в таблице блока material и weight.
Общий limit необязателен.


Ответ 3

Такой костыль вас устроит?

select obj.*
from objects obj
where obj.weight >= 10 and obj.weight <= 12 and
(obj.material = 'пластик' or obj.material = 'металл' )
order by random()
limit 120;


Идея простая - превратить все записи таблицы в фарш рандомизацией.