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

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

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

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

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

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)

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

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

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