Как составить сложный агрегированный запрос?

Question

[holllop]

У меня есть три таблицы

spoiler

-- Это первая таблица
Table "public.order"
     Column     |  Type   | Collation | Nullable |                    Default
----------------+---------+-----------+----------+-----------------------------------------------
 order_id       | integer |           | not null | nextval('"main_table_N_order_seq"'::regclass)
 departament_id | integer |           |          |
 customer_id    | integer |           |          |
 date           | date    |           |          |
 type_expenses  | integer |           |          |
 budget         | boolean |           |          |
Indexes:
    "main_table_pkey" PRIMARY KEY, btree (order_id)
Foreign-key constraints:
    "main_table_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    "main_table_customer_id_fkey1" FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    "main_table_departament_id_fkey" FOREIGN KEY (departament_id) REFERENCES departaments(departament_id)
    "main_table_type_expenses_fkey" FOREIGN KEY (type_expenses) REFERENCES type_expenses(id) NOT
VALID
Referenced by:
    TABLE "works" CONSTRAINT "works_id_order_fkey" FOREIGN KEY (id_order) REFERENCES "order"(order_id)
-- Это вторая таблица
Table "public.works"
   Column    |          Type          | Collation | Nullable |                    Default

-------------+------------------------+-----------+----------+-----------------------------------
------------
 id_work     | integer                |           | not null | nextval('"main_table_N_order_seq"'
::regclass)
 id_order    | integer                |           |          |
 name_object | character varying(100) |           |          |
 n_plan      | integer                |           |          |
Indexes:
    "works_pkey" PRIMARY KEY, btree (id_work)
Foreign-key constraints:
    "works_id_order_fkey" FOREIGN KEY (id_order) REFERENCES "order"(order_id)
Referenced by:
    TABLE "expenses" CONSTRAINT "work_object_work_id_fkey" FOREIGN KEY (n_plan) REFERENCES works(id_work)
-- Это третья таблица
Table "public.expenses"
    Column    |     Type     | Collation | Nullable |                    Default

--------------+--------------+-----------+----------+--------------------------------------------
---
 n_work       | integer      |           | not null | nextval('"main_table_N_order_seq"'::regclas
s)
 n_plan       | integer      |           |          |
 type_work_id | integer      |           |          |
 printer_id   | integer      |           |          |
 material_id  | integer      |           |          |
 performer_id | integer      |           |          |
 format_id    | integer      |           |          |
 n_sheets     | numeric(4,1) |           |          |
 n_page       | numeric(4,1) |           |          |
 n_ruined     | numeric(4,1) |           |          |
Indexes:
    "work_object_pkey" PRIMARY KEY, btree (n_work)
Foreign-key constraints:
    "work_object_format_id_fkey" FOREIGN KEY (format_id) REFERENCES format(format_id)
    "work_object_material_id_fkey" FOREIGN KEY (material_id) REFERENCES material(material_id)
    "work_object_performer_id_fkey" FOREIGN KEY (performer_id) REFERENCES performers(performer_id)
    "work_object_printer_id_fkey" FOREIGN KEY (printer_id) REFERENCES printer(printer_id)
    "work_object_type_work_id_fkey" FOREIGN KEY (type_work_id) REFERENCES type_work(type_work_id)
    "work_object_work_id_fkey" FOREIGN KEY (n_plan) REFERENCES works(id_work)

и из кода выше можно определить следующую структуру, что один order_id соответствует нескольким id_work, а один id_work соответствует нескольким n_work
хочу из них вывести информацию

spoiler

o.order_id, d.name AS dname, c.customer, o.date, w.id_order, w.id_work, w.name_object, e.n_work, tw.type_work, pr.printer, ma.material, pe.performer, f.format, e.n_sheets

По этим столбцам, но прекрасно понимаю что если не применить агрегацию, то это будет куча записей с одинаковым order_id, по этому начал пытатся применять агрегацию.

spoiler

SELECT
    w.id_order,
    w.id_work,
    w.name_object,
    STRING_AGG(e.n_work::text, ',') AS n_works,
    STRING_AGG(tw.type_work, ',') AS types_of_work,
    STRING_AGG(pr.printer, ',') AS printers,
    STRING_AGG(ma.material, ',') AS materials,
    STRING_AGG(pe.performer, ',') AS performers,
    STRING_AGG(f.format, ',') AS formats,
    STRING_AGG(e.n_sheets::text, ',') AS total_sheets
    FROM 
      works w
    INNER JOIN 
      expenses e ON w.id_work = e.n_plan
    INNER JOIN 
      type_work tw ON e.type_work_id = tw.type_work_id
    INNER JOIN 
      printer pr ON e.printer_id = pr.printer_id
    INNER JOIN 
      material ma ON e.material_id = ma.material_id
    INNER JOIN 
      performers pe ON e.performer_id = pe.performer_id
    INNER JOIN 
      format f ON e.format_id = f.format_id
    GROUP BY 
      w.id_order, w.id_work, w.name_object;

вроде этой команды получал из неё

spoiler

[
{
id_order: 15,
id_work: 16,
name_object: "80",
n_works: "61,60,59,58",
types_of_work: "Ч/б печать,Ч/б печать,Ч/б печать,Ч/б печать",
printers: "Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,Kyosera 3510i",
materials: "A3,A4,A4,A4",
performers: "",
formats: "A0,A0,A0,A0",
total_sheets: "14.0,100.0,100.0,100.0"
},
{
id_order: 12,
id_work: 25,
name_object: "",
n_works: "27",
types_of_work: "Ч/б печать",
printers: "Kyosera 3510i",
materials: "A3",
performers: "Буракова О.Ю.",
formats: "A3",
total_sheets: "12.0"
},
{
id_order: 12,
id_work: 26,
name_object: "",
n_works: "28",
types_of_work: "Ч/б печать",
printers: "Kyosera 3510i",
materials: "A3",
performers: "",
formats: "A3",
total_sheets: "13.0"
},
{
id_order: 13,
id_work: 30,
name_object: "какой-то",
n_works: "31",
types_of_work: "Цветная печать",
printers: "HP DesignJet 500",
materials: "914*50m",
performers: "",
formats: "A1",
total_sheets: "0.0"
},
{
id_order: 13,
id_work: 32,
name_object: "какой-то",
n_works: "33",
types_of_work: "Цветная печать",
printers: "HP DesignJet 500",
materials: "914*50m",
performers: ".",
formats: "A1",
total_sheets: "0.0"
},
{
id_order: 13,
id_work: 34,
name_object: "какой-то",
n_works: "41,42,43,44,45,46,47,48,49,50,51,40,53,54,55,56,39,38,57,37,36,35,52",
types_of_work: "Ч/б печать,Ч/б печать,Ч/б печать,Ч/б печать,Ч/б печать,Ч/б печать,Ч/б печать,Ч/б печать,Ч/б печать,Ч/б печать,Ч/б печать,Ч/б печать,Ч/б печать,Ч/б печать,Ч/б печать,Ч/б печать,Ч/б печать,Ч/б печать,Ч/б печать,Ч/б печать,Цветная печать,Ч/б печать,Ч/б печать",
printers: "Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,Kyosera 3510i,HP DesignJet 500,Kyosera 3510i,Kyosera 3510i",
materials: "610*175m,610*175m,610*175m,610*175m,610*175m,610*175m,610*175m,610*175m,610*175m,610*175m,610*175m,610*175m,610*175m,610*175m,610*175m,610*175m,610*175m,610*175m,610*175m,610*175m,914*50m,A3,610*175m",
performers: "",
formats: "A0,A0,A0,A0,A0,A0,A0,A0,A0,A0,A0,A0,A0,A0,A0,A0,A0,A0,A0,A0,A1,A3,A0",
total_sheets: "0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,12.0,0.0,0.0,0.0,0.0"
}
]

и как видно выводится всё равно несколько одинаковых order_id хотя по сути должен быть вывод всего 3 записей

Comments

[Ivan Ustûžanin]

если ожидаете всего 3 строки, то зачем группируете по чему-то кроме w.id_order?

[Adamos]

IvanU7n, подозреваю, ТС хочется вывести и два других поля, но агрегировать их не получилось, а БД ругается ;)

[holllop]

IvanU7n, вы имеете в виду GROUP BY w.id_order, w.id_work, w.name_object; эту часть команды ? если я уберу остальные параметры и оставлю GROUP BY w.id_order;, то получаю ошибку

столбец "w.id_work" должен фигурировать в предложении GROUP BY или использоваться в агрегатной функции

[holllop]

Adamos, именно

[Adamos]

holllop, и вы хотите, чтобы мы подсказали вам, чего вы хотите от этих двух столбцов?

[holllop]

Adamos, я бы даже сказал, что эти два поля мне важнее

[Adamos]

holllop, так потрудитесь сформулировать проблему, которую решаете.
Вот у вас в заказе три операции с тремя разными номерами. БД-то что должна вам выдать, если вы их сваливаете в одну строчку?

[holllop]

Adamos, в каком смысле ? если вдруг не понятно из контекста, то агрегацией я занимаюсь впервые. И поэтому ваши "ловкие" подстёбы проходят мимо меня. Я просто хочу получить запись где один order_id из таблицы order соответствует нескольким записям id_work из таблицы works, каждая из которых может соответствовать нескольким n_works из таблицы expenses. И банально даже не знаю можно ли так сделать. Получая таким образом многовложенность или хз как это называется.

[Adamos]

holllop, это не подстебы, это вопросы по существу. Хотите несколько операций - получайте их разными строчками и собирайте информацию после получения ее из БД. Хотите одной строчкой - определитесь, что в ней должно быть.
Никакой многовложенности и прочего, что вы себе фантазируете, БД не предполагает, приземлитесь уже.

[holllop]

Adamos,

что вы себе фантазируете, БД не предполагает, приземлитесь уже.

Братик квадратик ты чем читал ?

если вдруг не понятно из контекста, то агрегацией я занимаюсь впервые

,

И банально даже не знаю можно ли так сделать

. Вот вопрос может ли быть в одной строке реализовано вывод столбцов o.order_id, w.id_work, e.n_plan. При этом получая три записи, а не пять как у меня, так как уникальных order_id 3?

[Adamos]

holllop, увы, хамить мальки учатся раньше, чем думать...
Ладно, посмотрел еще в хрустальный шар и погуглил за вас: в Постгре есть функция ARRAY_ARG - наверное, вы мечтали об этом.

[holllop]

Adamos,

увы, хамить мальки учатся раньше, чем думать...

увы хамством на хамство, не чего личного

[Voland69]

holllop, смотрите, все очень и очень просто:
вы хотите вывести o.order_id, w.id_work, e.n_plan, при этом уникальных order_id у вас 3.
Но проблема в том, что уникальных w.id_work у вас 5, и посгря без подсказки понятия не имеет, как 5 запихнуть в 3.
И эту подсказку в запросе необходимо указать.
Собственно ошибка "столбец "w.id_work" должен фигурировать в предложении GROUP BY или использоваться в агрегатной функции" о том и говорит.
что можно сделать:

• GROUP BY w.id_work и 5 в 3 запихивать на стороне клиента базы
  
• исключить w.id_work из запроса
  
• придумать, найти (если есть) или написать (если нет) соответствующую аггрегатную функцию (к примеру, конкатенировать w.id_work через точку с запятой и возвращать клиенту стрингу)
  

P.S. хамить на уточняющие вопросы - харам. То что вы называете "многовложенность или хз как это называется" - это и есть аггрегатные функции. Самый простой пример SUM() или COUNT().
Для более конкретного ответа опишите логику, в каком виде вы хотите получить несколько w.id_work у одного o.order_id в выборке.

[Adamos]

Voland69, самое загадочное в этой истории - что же ТС хотел от тех двух полей, что собрать их в такие же STRING_AGG, как все остальные, ему не подошло. Поэтому я и высказал предположение, что нужен ARRAY_AGG.
Но тут, видимо, программирование методом тыка и копипасты без понимания, что происходит...

[holllop]

Adamos, конечно братка, ты ж мой учитель, гуру и проводник в мире программирования, от куда я могу знать больше, если у меня такой учитель ?

[holllop]

Voland69, да я это понимаю, что оно опирается на количество уникальных значений w.id_work. Вариант действительно не плохой

GROUP BY w.id_work и 5 в 3 запихивать на стороне клиента базы

попробую скорее всего это.

исключить w.id_work из запроса

для меня это не вариант, т.к. данные мне оттуда всё таки нужны.

хамить на уточняющие вопросы - харам.

был бы уточняющий, я бы ответил, и даже расписал, что конкретно не понятно.

и вы хотите, чтобы мы подсказали вам, чего вы хотите от этих двух столбцов?

по моему это не очень уточняющий вопрос и выглядит как "И чё хочет эта челядь ?"

Для более конкретного ответа опишите логику, в каком виде вы хотите получить несколько w.id_work у одного o.order_id в выборке.

ну не знаю поймёте ли вы, если скажу что хочу получить из запроса тройной массив

[Akina]

У меня есть три таблицы

1. Выложите их в виде CREATE TABLE. Выложите приме данных в виде INSERT INTO (2-3 записи базовой таблице, по 2-3 связанных для базовой записи в каждой дочерней таблице). Скрипты должны быть готовы к запуску (проверить!). Ненужные для задаваемого вопроса поля - удалить.

хочу из них вывести информацию

2. Покажите в отформатированном в таблицу виде (а вовсе даже не в виде JSON) точный результат, который должен быть получен для данных, опубликованных по пункту 1.

3. Добавьте подробные пояснения по каждой строке результата - почему именно так, а не иначе.

[Voland69]

по моему это не очень уточняющий вопрос и выглядит как "И чё хочет эта челядь ?"

Не судите строго, на Q&A ежедневно пишутся вопросы, автор которых хочет либо странного, либо сам не вполне понимает чего хочет. Как видите, после уточнения стало сразу стало понятнее, чего хотите вы, и что не получается.
Поймите главное, в голове у собеседников нет контекста Вашей задачи, и перед глазами собеседника нет экрана с Вашей базой и прочим: поэтому максимальное количество вводных, что хотите, что сделали для этого, что не получилось.

ну не знаю поймёте ли вы, если скажу что хочу получить из запроса тройной массив

не вполне, тройной массив не знаю: но если понимать как массив из трех элементов, то вам нужна аггрегатная функция ARRAY_AGG, о чем собственно Adamos и писал выше.

[holllop]

Voland69,я нашёл ответ сам. Я не просто задаю вопросы не осознано, я сам тружусь над их решением, не жду "маны небесной" или пока кто-то решит.
Правильный sql запрос для того, что я хотел выглядит так

SELECT 
      o.order_id, 
      d.name AS dname, 
      c.customer, 
      o.date, 
      array_agg(
        json_build_object(
          'id_works', w.id_work::text, 
          'name_object', w.name_object, 
          'work_items', subq.work_items
        )
      ) AS works_info
      FROM "order" o
      JOIN works w ON o.order_id = w.id_order
      JOIN departaments d ON o.departament_id = d.departament_id
      JOIN customers c ON o.customer_id = c.customer_id
      LEFT JOIN (
        SELECT
            w.id_work,
            w.name_object,
            json_agg(json_build_object(
                'n_works', e.n_work::text,
                'type_works', tw.type_work,
                'printers', pr.printer,
                'materials', ma.material,
                'performers', pe.performer,
                'formats', f.format,
                'n_sheets', e.n_sheets::text
            )) AS work_items
        FROM works w
        LEFT JOIN expenses e ON w.id_work = e.n_plan
        LEFT JOIN type_work tw ON e.type_work_id = tw.type_work_id
        LEFT JOIN printer pr ON e.printer_id = pr.printer_id
        LEFT JOIN material ma ON e.material_id = ma.material_id
        LEFT JOIN performers pe ON e.performer_id = pe.performer_id
        LEFT JOIN format f ON e.format_id = f.format_id
        GROUP BY w.id_work, w.name_object
      ) AS subq ON w.id_work = subq.id_work
    GROUP BY o.order_id, d.name, c.customer, o.date

получаю я из него следующее

{
order_id: 13,
dname: "ПЭО",
customer: "Лимановский Р.А.",
date: "2010-01-14T21:00:00.000Z",
works_info: [
{
id_works: "30",
name_object: "какой-то",
work_items: [
{
n_works: "31",
type_works: "Цветная печать",
printers: "HP DesignJet 500",
materials: "914*50m",
performers: "Хакимов Р.С.",
formats: "A1",
n_sheets: "0.0"
}
]
},
{
id_works: "34",
name_object: "какой-то",
work_items: [
{
n_works: "35",
type_works: "Ч/б печать",
printers: "Kyosera 3510i",
materials: "A3",
performers: "Буракова О.Ю.",
formats: "A3",
n_sheets: "0.0"
},
{
n_works: "36",
type_works: "Цветная печать",
printers: "HP DesignJet 500",
materials: "914*50m",
performers: "Хакимов Р.С.",
formats: "A1",
n_sheets: "0.0"
},
]
},
{
id_works: "32",
name_object: "какой-то",
work_items: [
{
n_works: "33",
type_works: "Цветная печать",
printers: "HP DesignJet 500",
materials: "914*50m",
performers: "Хакимов Р.С.",
formats: "A1",
n_sheets: "0.0"
}
]
}
]
}

решил показать только один из элементов массива который выводится, чтобы не копировать всё

Answer 1

[holllop]

Правильный sql запрос для того чтобы реализовать, то что я хотел выглядит так

SELECT 
      o.order_id, 
      d.name AS dname, 
      c.customer, 
      o.date, 
      array_agg(
        json_build_object(
          'id_works', w.id_work::text, 
          'name_object', w.name_object, 
          'work_items', subq.work_items
        )
      ) AS works_info
      FROM "order" o
      JOIN works w ON o.order_id = w.id_order
      JOIN departaments d ON o.departament_id = d.departament_id
      JOIN customers c ON o.customer_id = c.customer_id
      LEFT JOIN (
        SELECT
            w.id_work,
            w.name_object,
            json_agg(json_build_object(
                'n_works', e.n_work::text,
                'type_works', tw.type_work,
                'printers', pr.printer,
                'materials', ma.material,
                'performers', pe.performer,
                'formats', f.format,
                'n_sheets', e.n_sheets::text
            )) AS work_items
        FROM works w
        LEFT JOIN expenses e ON w.id_work = e.n_plan
        LEFT JOIN type_work tw ON e.type_work_id = tw.type_work_id
        LEFT JOIN printer pr ON e.printer_id = pr.printer_id
        LEFT JOIN material ma ON e.material_id = ma.material_id
        LEFT JOIN performers pe ON e.performer_id = pe.performer_id
        LEFT JOIN format f ON e.format_id = f.format_id
        GROUP BY w.id_work, w.name_object
      ) AS subq ON w.id_work = subq.id_work
    GROUP BY o.order_id, d.name, c.customer, o.date