Как улучшить запрос с условием аггрегирования?

Question

[SwoCoder]

Здравствуйте! Есть таблица новостей содержащая поля source, category, published_on и т.д.:

CREATE TABLE news (
  id serial PRIMARY KEY,
  title character varying NOT NULL,
  description text,
  source character varying NOT NULL,
  category character varying NOT NULL,
  published_on date NOT NULL
);

Нужно получить записи таким образом, чтобы число новостей из категорий c1 + c2 из одного источника было не больше 5 (если в категории c1 новостоей больше 5, то берутся 5 последних, если меньше, то недостающее количество набирается из категории c2, но в сумме должно быть не больше 5 новостей с категориями c1 + c2 с одного источника), из остальных категорий можно брать любое количество новостей.
У меня есть решение, но оно является довольно костыльным:

with t1 as (
  select n.source,(array_agg(n.id order by published_on desc))[1:5] as ids
  from news n
  where n.category = 'c1'
  group by n.source
),
t2 as (
  select n.source,
  t1.ids||(array_agg(n.id order by published_on desc))[1:5-coalesce(cardinality(t1.ids),0)] as ids
  from t1
  left join news n
  on n.source = t1.source
  where n.category = 'c2' AND coalesce(cardinality(t1.ids),0) < 5
  group by n.source,t1.ids
),
t3 as (
  select *
  from t1
  where cardinality(t1.ids) = 5 OR NOT EXISTS (select 1 from news where source = t1.source AND category = 'c2')
  union
  select *
  from t2
)
select n.id,n.title,n.category,n.source,n.published_on
from news n
where n.id in (select unnest(t3.ids) from t3)
OR n.category NOT IN ('c1', 'c2');

Примерный набор данных:

INSERT INTO news (title, description, source, category, published_on)
VALUES
('n1', 'desc for n1', 's1', 'c1', '2017-03-24'),
('n2', 'desc for n2', 's1', 'c1', '2017-03-29'),
('n3', null, 's1', 'c1', '2017-05-15'),
('n4', null, 's1', 'c2', '2017-04-12'),
('n5', 'desc for n5', 's1', 'c1', '2017-02-14'),
('n6', 'desc for n6', 's1', 'c1', '2017-04-16'),
('n7', null, 's1', 'c1', '2017-04-23'),
('n8', null, 's1', 'c2', '2017-02-02'),
('n9', 'desc for n9', 's2', 'c1', '2017-05-03'),
('n10', 'desc for n10', 's2', 'c1', '2017-05-12'),
('n11', null, 's2', 'c2', '2017-04-14'),
('n12', null, 's2', 'c2', '2017-03-16'),
('n13', null, 's2', 'c2', '2017-05-10'),
('n14', null, 's2', 'c2', '2017-03-10'),
('n15', null, 's2', 'c3', '2017-04-11'),
('n16', null, 's3', 'c3', '2017-04-01'),
('n17', null, 's3', 'c4', '2017-03-26'),
('n18', null, 's4', 'c2', '2017-02-13'),
('n19',null,'s5','c1','2017-03-08');

И результат соответсвенно следуюший:
id | title | category | source | published_on
----+-------+----------+--------+--------------
1 | n1 | c1 | s1 | 2017-03-24
2 | n2 | c1 | s1 | 2017-03-29
3 | n3 | c1 | s1 | 2017-05-15
6 | n6 | c1 | s1 | 2017-04-16
7 | n7 | c1 | s1 | 2017-04-23
9 | n9 | c1 | s2 | 2017-05-03
10 | n10 | c1 | s2 | 2017-05-12
11 | n11 | c2 | s2 | 2017-04-14
12 | n12 | c2 | s2 | 2017-03-16
13 | n13 | c2 | s2 | 2017-05-10
15 | n15 | c3 | s2 | 2017-04-11
16 | n16 | c3 | s3 | 2017-04-01
17 | n17 | c4 | s3 | 2017-03-26
18 | n18 | c2 | s4 | 2017-02-13
19 | n19 | c1 | s5 | 2017-03-08

Можно посмотреть на pastebin'e
Бесспорно, здесь должно быть более грамотное решение, которое был бы рад увидеть. Спасибо.

Answer 1

[mishania666]

SELECT id, title, description, "source", category, published_on
FROM (
	SELECT *, rank() OVER (PARTITION BY "source" ORDER BY category, published_on DESC)
	FROM news 
	WHERE category IN ('c1', 'c2')
) t
WHERE rank < 6
UNION
SELECT *
FROM news
WHERE category NOT IN ('c1', 'c2')

Comments

[freeExec]

А можно без ранга, просто взять 5 из с1 и 5 из с2, объединить и отрезать только первые 5.

[mishania666]

freeExec: Это как например?

[freeExec]

mishania666:

(	(SELECT * FROM news 
		WHERE category = 'c1'
LIMIT 5)
UNION ALL
	(SELECT * FROM news 
		WHERE category = 'c2'
	LIMIT 5)
LIMIT 5)
UNION ALL
(SELECT * FROM news 
		WHERE category NOT IN ( 'c1', 'c2')
)

[mishania666]

freeExec: так не пойдёт :( Автор указывает -

c1 + c2 из одного источника было не больше 5

а Ваш запрос, в лучшем случае, выберет 5 записей из этих категорий для всех источников да ещё и со случайным порядком

[freeExec]

mishania666: Да промахнулся, суть не до конца уловил.

[SwoCoder]

Спасибо, т.е. window functions to the resque! Пара вопросов:

1. Почему обрамили source кавычками?
   
2. Как вы считаете, можно ли решить задачу с использованием lateral join?
   

[mishania666]

source в двойных кавычках, т.к. это может быть зарезервированным словом в некотором контексте (https://www.postgresql.org/docs/current/static/sql... хотя в этом запросе они не обязательны.
Насчёт латеральных соединений, честно говоря на вскидку так и не приходит на ум, как (и главное зачем;)) их можно приспособить для вашего случая.

[SwoCoder]

mishania666: В решении все-таки есть интересный нюанс, заключающийся в том, что нужно будет каждый раз самому учитывать порядок для отбора категории, т.е. когда нужно сначала избирать записи для c1, и добавлять при нехватке из c2 будет вот такая конструкция:

OVER (PARTITION BY "source" ORDER BY category, published_on DESC)

Но если нужно будет выбирать сначала записи для категории c2, и при нехватке добавлять в пятерку записи из категории c1, то нужно будет прописывать:

OVER (PARTITION BY "source" ORDER BY category DESC, published_on DESC)

[mishania666]

SwoCoder: Безусловно :-) При изменении любого из условий SQL-запросы всегда будут требовать модификации. Не существует универсального запроса, который будет верно отвечать на все задачи.

[SwoCoder]

mishania666: Это да, но все-таки, sql же является декларативным, мы по хорошему должны описывать только данные, которые хотим получить, и СУБД уже извлекает соответствующий набор записей. Если рассматривать напр. мое решение, то там не нужно думать о порядке сортировки по категории, там идет последовательный отбор по цепочке интересующих категорий, и при случае замены порядка интересующих категорий, нужно будет изменить непосредственно сами названия категорий в нужной последовательности, не думая о том, какое-же нужно направление (asc, desc), чтобы получить правильный набор. Еще один немаловажный аспект заключается в следующем: если нужно будет извлекать не по двум, а по трем категориям, сначала по c2, при нехватке набирать из c1, если и после этих операций для каких-то источников не набралось до 5, то добавлять при наличии из c3 (c2->c1->c3)? Тут уже и направление сортировки не поможет, не так ли?

[mishania666]

SwoCoder: Поможет, но только нужно будет извернуться с этой сортировкой (использовать CASE например) :-). Хотя на самом деле, я бы создал отдельную таблицу-справочник для категорий, сделал бы в ней поле по которому можно было бы проводить "правильную" сортировку, а не по названию. SQL ведь реляционный язык, не так ли ;-). Из вашего решения ведь совсем не очевидно что нужно будет менять в случае перестановки двух названий, а уж если придётсся добавлять 3-й и т.д. придётся и вовсе добавлять десятки строк кода :-) Более того, с точки зрения производительности, запрос сильно проигрывает - например на тестовых данных стоимость вашего решения 87, моего 50, что будет ещё более заметно при увеличении тестового набора.

[SwoCoder]

mishania666: При помощи CASE для двух категорий можно решить для свопинга категорий, например как-то так:

SELECT id, title, description, source, category, published_on
FROM (
  SELECT *, (
    CASE WHEN (select json_agg(t.cat ORDER BY t.cat ASC) ->> 0 = ('["c2", "c1"]'::json) ->> 0
      from (select json_array_elements_text('["c2", "c1"]') as cat) t)
    THEN  rank() OVER (PARTITION BY source ORDER BY category, published_on DESC)
    ELSE
    rank() OVER (PARTITION BY source ORDER BY category DESC, published_on DESC)
    END
  )
  FROM app_news
  WHERE category IN ('c1', 'c2')
  -- next part

и можно будет только изменять порядок внутри json массива, но для трех и более категорий же, по-видимому, невозможно будет составить запрос? В этом случае я только могу предложить добавлять в таблицу временный столбец, в который записывать определенные значения исходя из того, каким правильным образом должны сортироваться записи, т.е. напр, если нужно брать в таком порядке (с2->c1->c3), то временный столбец наполнится, например, следующим маппингом (с2->c1, c1->c2, c3->c3) и, соответственно, в оконной функции проводить сортировку уже по этому временному столбцу.
Да, безусловно, для хранения категорий лучше использовать отдельную таблицу (да здравствует концепция нормальных форм! :)), просто такая задача мне попалась как-то в тесте, и там было условие, что таблица новостей одна, других таблиц создавать не нужно; и возможные решения меня интересуют чисто в целях любопытства.
В моем решении нужно будет менять категории в конструкции where во временных таблицах t1 и t2, и категорию в конструкции NOT EXISTS в t3. Если добавиться условие для третьей и т.д. категорий, то да, там количество кода значительно увеличится :).

[mishania666]

SwoCoder: Я вижу у вас большая тяга к использованию новомодных фитч :-) что ж, это не плохо но я имел ввиду более простое выражение, например:

SELECT id, title, description, "source", category, published_on
FROM (
  SELECT *, 
  rank() OVER (PARTITION BY "source" ORDER BY (CASE WHEN category = 'c1' THEN 1
  						WHEN category = 'c2' THEN 2 
                                              END)
  			, published_on DESC)
  FROM news 
  WHERE category IN ('c1', 'c2')
) t
WHERE rank < 6
UNION
SELECT *
FROM news
WHERE category NOT IN ('c1', 'c2')

В этом случае при добавлении категории нужно будет лишь добавить ещё один WHEN category = 'c3' THEN 3, а также в WHERE category IN (...), ну и/или для изменения порядка изменять цифры после THEN

[mishania666]

Или даже, если не создавать реальную таблицу, а воспользоваться CTE:

WITH categories AS (
	SELECT 'c1' category, 1 cat_order
	UNION
	SELECT 'c2', 2
	UNION
	SELECT 'c3', 3
)
SELECT id, title, description, "source", category, published_on
FROM (
  SELECT *, 
  	rank() OVER (PARTITION BY "source" ORDER BY cat_order, published_on DESC)
  FROM news
  	JOIN categories USING(category)
) t
WHERE rank < 6
UNION
SELECT n.*
FROM news n
	LEFT JOIN categories c USING(category)
WHERE c.category IS NULL

Тогда все изменения нужно будет производить только в одном месте :-)

[SwoCoder]

mishania666: Да, действительно, хорошие получаются решения. Спасибо что уделили время!