Запрос Postgresql, который подсчитывает промежуточные итоги на основе анализа разделенного списка
PostgreSQL 14
Предполагая, что этот образец данных...
... как лучше всего получить что-то вроде ROLLUP, рекурсивного запроса или UNION для подсчета промежуточных итогов для каждого уровня значения элемента, разбиваясь на двоеточия? Количество уровней (разделителей-двоеточий) для каждого элемента является переменным, но при необходимости я могу определить максимальное значение.
Желаемый результат (я знаю, что ROLLUP поместит больше нулей в разных местах, и это нормально):
@Jens: Достаточно справедливо, и я могу сделать это в принимающем коде, но встроенные функции приближают меня настолько, что, похоже, стоит спросить. Я не уверен, почему вопрос, выполнимо ли это, приводит к тому, что меня минусуют...
Ответы 1
Я использовал рекурсивную стратегию, которая работает в этом случае, потому что я решил сгенерировать parent items, удалив последний сегмент.
Результат: рабочий пример
Вот код для создания вашей таблицы
CREATE TABLE items (
item TEXT,
qty INT
);
вставить в таблицу
INSERT INTO items (item, qty) VALUES
('Fruit:Orange', 1),
('Fruit:Orange', 2),
('Fruit:Orange', 3),
('Fruit:Apple', 4),
('Fruit:Apple', 5),
('Mineral:Jade', 1),
('Mineral:Jade', 2),
('Mineral:Talc:Raw', 3),
('Mineral:Talc:Raw', 4),
('Mineral:Talc:Processed', 5),
('Mineral:Talc:Processed', 6);
Рекурсивная стратегия
WITH RECURSIVE item_hierarchy AS (
SELECT
item,
qty,
item AS level_item,
CASE
WHEN POSITION(':' IN item) > 0 THEN
ARRAY_TO_STRING(TRIM_ARRAY(STRING_TO_ARRAY(item, ':'), 1), ':')
ELSE NULL
END AS parent_item
FROM items
UNION ALL
SELECT
ih.item,
ih.qty,
ih.parent_item AS level_item,
CASE
WHEN POSITION(':' IN ih.parent_item) > 0 THEN
ARRAY_TO_STRING(TRIM_ARRAY(STRING_TO_ARRAY(parent_item, ':'), 1), ':')
ELSE NULL
END AS parent_item
FROM item_hierarchy ih
WHERE ih.parent_item IS NOT NULL
)
SELECT
COALESCE(level_item, 'NULL') AS item,
SUM(qty) AS qty
FROM item_hierarchy
GROUP BY level_item
ORDER BY
LENGTH(COALESCE(level_item, '')) ASC,
level_item;
@ValNik вот что я вижу.... ims.png
Я не вижу строки ('Mineral:Talc', 18)
Согласен с @ValNik, что здесь отсутствует подуровень, но я понимаю, к чему вы клоните с помощью этого подхода.
@DerekRoberts: Похоже, вы слишком агрессивно относитесь к анализу родительского элемента и всегда захватываете первый элемент. Я смотрю, смогу ли я это отрегулировать.
@EMF хаха! разве мне не нравится слово «агрессивный» в этом контексте. родитель был единственной идеей, которую я мог использовать. Я думаю, ты справишься, я в тебя верю!
Думаю, я понял. Замените LEFT(item, POSITION(':' IN item) - 1) на ARRAY_TO_STRING(TRIM_ARRAY(STRING_TO_ARRAY(item, ':'), 1), ':'). То же самое со вторым экземпляром, использующим ih.parent_item. Это не дает мне общей суммы, но для UNION это тривиально.
@DerekRoberts: Если вы обновите свой ответ, я приму его. Вы помогли мне на 99% пути!
@EMF потрясающе звучит, 99% — A++. Я знал, что ты сможешь. сделал бы это сейчас СДЕЛАНО!
Если попробовать новую версию примера запроса - произойдет сбой при неполной рекурсии? (dbfiddle.uk/p-P_F_ge)
@ValNik, правда, я только что запустил код, и кажется, все в порядке... общая сумма не такая, как упоминается в EMF, но она работает ИЛИ вы исчерпали свою машину на dbfiddle за день?
Пожалуйста, посмотрите простой пример (dbfiddle.uk/K6Mgwlu_ ). Я ограничу глубину рекурсии. И еще пример ( dbfiddle.uk/RTCUj3yK).
@ValNik EMF предложила использовать ARRAY_TO_STRING, что ей помогло, вы хотите сказать, что это не работает? Я запустил вот этот код(dbfiddle.uk/p-P_F_ge) и он работает, что же делать?
ARRAY_TO_STRING — хорошая функция. Я о You should use parent_item in recursive part of item_hierarchy: ARRAY_TO_STRING(TRIM_ARRAY(STRING_TO_ARRAY(parent_item, ':'), 1), ':')
@ValNik, ок, сейчас поменяю, дайте мне знать, если все в порядке
Кстати, мои правки работают на меня и дают именно то, что я просил. Я попытался опубликовать весь отредактированный фрагмент, но в нем слишком много символов.
Но да, похоже, что второй экземпляр в ответе ссылается на item вместо ih.parent_item.
sql предназначен не для этого.