Начальная страница › Форумы › PostgreSQL › Вопросы по SQL и другие аспекты работы PostgreSQL

Группировка и сортировка по нескольким полям.

Posted Январь 13th, 2014 by qrem
in

Есть таблица, для хранения сообщений, структура похожая на форум, но это далеко не форум, а минимализм для простых текстовых сообщений:

CREATE TABLE records(id integer PRIMARY KEY, ts DATE DEFAULT NOW(), link_id integer, msg text);
 
INSERT INTO records(id, link_id, msg) VALUES(1, 0, 'Это заметка N1');
INSERT INTO records(id, link_id, msg) VALUES(2, 0, 'Это заметка N2');
INSERT INTO records(id, link_id, msg) VALUES(3, 2, 'Это дополнение к заметке N2');
INSERT INTO records(id, link_id, msg) VALUES(4, 2, 'Это дополнение к заметке N2');
INSERT INTO records(id, link_id, msg) VALUES(5, 1, 'Это дополнение к заметке N1');
INSERT INTO records(id, link_id, msg) VALUES(6, 0, 'Это заметка N3');

поле `link_id' играет роль "в ответ на сообщение с id"
поле `ts' - как вы видите это дата создания записи.

Текущий запрос:

SELECT * FROM records ORDER BY ts, link_id;

В данный момент, не знаю как сформировать запрос,
чтобы извлечь данные в таком порядке:

Записи идут отсортированные по дате (ts),
Следом за каждой записью, идут записи ссылающиеся
на данную запись (link_id = id)

Вывод должен быть таким:

id      ts              link_id       msg
1       2014-01-13      0             Это заметка N1
5       2014-01-13      1             Это дополнение к заметке N1
2       2014-01-13      0             Это заметка N2
3       2014-01-13      2             Это дополнение к заметке N2
4       2014-01-13      2             Это дополнение к заметке N2
6       2014-01-13      0             Это заметка N3

Помогите сформировать запрос.

‹ Внешние ключи и связь таблиц Передача значения переменной в запрос из программы на с++ в базу данных, написанной на Postgresql ›

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

UPD: это нужно для

On Январь 13th, 2014 qrem says:

UPD:

это нужно для древовидного вывода заметок (сообщений).

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

У меня нечто такое тоже есть

On Январь 14th, 2014 altair says:

У меня нечто такое тоже есть в моём приложении.
Там я обхожусь просто:
SELECT * FROM records ORDER BY ts, link_id;
беру приблизительно такой же запрос, добавляю это всё в List<> (C#) и вызываю рекурсивно функцию построения дерева, которая при каждом вызове проверяет, нет ли у сообщения связанных сообщений. Средствами SQL - это делать сложно и неинтуитивно, а также усложняет запрос.

К сожалению нет времени

On Январь 14th, 2014 admin says:

К сожалению нет времени разбираться и ставить эксперименты, поэтому могу лишь посоветовать вам поработать с этой главой:
http://postgresql.ru.net/manual/queries-with.html#QUERIES-WITH-SELECT
там есть описание и примеры рекурсивных запросов.

Ещё один способ - использовать несколько запросов, опираясь уже на само приложение. Т.е. в самом приложении делать рекурсию, оставляя PostgreSQL простые запросы, первым из которых пойдёт запрос на выборку данных из таблицы, для которых link_id не задан

Очень не хороший вариант

On Январь 14th, 2014 Vitalts says:

Цитата:
Ещё один способ - использовать несколько запросов, опираясь уже на само приложение. Т.е. в самом приложении делать рекурсию, оставляя PostgreSQL простые запросы, первым из которых пойдёт запрос на выборку данных из таблицы, для которых link_id не задан

С каждым запросом к базе вы вносите задержки в выполнении ПО. Старайтесь вытаскивать необходимые данные одним запросом, с нужной разбивкой, сортировкой, группировкой и т.д.
Не изобретайте велосипедов в коде, когда можно эту задачу возложить на базу.

Цитата:С каждым запросом к

On Январь 14th, 2014 admin says:

Цитата:
С каждым запросом к базе вы вносите задержки в выполнении ПО. Старайтесь вытаскивать необходимые данные одним запросом, с нужной разбивкой, сортировкой, группировкой и т.д.
Не изобретайте велосипедов в коде, когда можно эту задачу возложить на базу.

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

Само собой, все зависит от задачи.

On Январь 14th, 2014 Vitalts says:

Само собой, все зависит от задачи. Иногда быстрее прогнать все данные таблицы сдампив ее целиком. Однако, это явно не тот случай. Грамотно составленный запрос, правильная расстановка индексов, лимит, оффсет и все будет хорошо.

Средствами SQL это сделать значительно проще

On Январь 14th, 2014 Vitalts says:

Цитата:
Средствами SQL - это делать сложно и неинтуитивно, а также усложняет запрос.

Средствами SQL это сделать значительно проще и нет ничего страшного в усложнении запросов. Лучше + пару строк к запросу, чем мудрить в коде с рекурсивными функциями и где-нить еще и накосить. И меньше затратите на разработке и получите прирост скорости выполнения.
Если же для вас проще писать код, чем добавить пару строк к запросу, то, возможно, стоит подтянуть свои знания SQL?

Сформируйте четкие правила сортировки и все встанет на свои мест

On Январь 14th, 2014 Vitalts says:

Сформируйте четкие правила сортировки и все встанет на свои места. Пока их описание слишком поверхностно и трактовать можно по разному. Один из возможных вариантов, в моем поинимании поставленной задачи. Сортируем по дате родительской записи в порядке убывания, затем, сортируем по id родительской записи, ну и на последок, сортируем по дате самой записи в порядке убывания

SELECT * FROM records r
ORDER BY case when link_id = 0 then ts else (SELECT t.ts FROM records t WHERE t.id = r.link_id) end DESC, case when link_id = 0 then id else link_id end, ts DESC

Твой вариант подходит!

On Январь 14th, 2014 qrem says:

Твой вариант подходит! Спасибо огромное !

конечный вариант с выводом последних записей в начале:

SELECT * FROM records r
ORDER BY case when link_id = 0 then ts else (SELECT t.ts FROM records t WHERE t.id = r.link_id) end, case when link_id = 0 then id else link_id end DESC, ts;

Ещё бы LIMIT 50 (и OFFSET) прикрутить к сообщениям "link_id = 0" - было бы супер, да только что-то не получается пока.

PS: спасибо за пример с CASE'ами ! я ещё никогда ими не пользовался !

Странное желание

On Январь 14th, 2014 Vitalts says:

Очень странное пожелание, ибо OFFSET + LIMIT обычно используется для жесткого ограничения кол-ва записей на выходе запроса, в вашем же случае это значение не предсказуемо. Но, хозяин - барин. Делаете отсортированную лимитированную выборку "родителей" (link_id = 0) с нужным смещением и цепляете к ней записи потомков + самих же себя из таблицы. Вуаля.

SELECT ch.* FROM (
SELECT id, ts FROM records
ORDER BY ts, id
offset 0
LIMIT 50
) p
JOIN records ch ON (ch.link_id = 0 AND ch.id = p.id) OR ch.link_id = p.id
ORDER BY p.ts, case when ch.link_id = 0 then ch.id else ch.link_id end DESC, ch.ts;

Всем спасибо за помощь кто

On Январь 14th, 2014 qrem says:

Всем спасибо за помощь кто откликнулся!
В особенности Vitalts

Я в основном занимаюсь разработкой прикладного ПО на С++,
а вот с SQL у меня не оч (самые базовые значния), но буду стараться,
на примерах данных вами в частности.

СПАСИБО !

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Back to top

(С) Виктор Вислобоков, 2008-2023