Сложный вложенный SUM/Sub-Select
ОБНОВЛЕНО с демонстрационными данными и т. д.
Я немного запутался в этом сложном запросе. Немного предыстории: это приложение для рельсов, и у меня есть модель expenditures, в которой много expenditure_items, каждая из которых имеет столбец суммы — все они суммируются в общую сумму соответствующих расходов.
Данный expenditure может быть Заказом, который затем может иметь несколько (или один, или нулевой) связанный Счет expenditures. Я ищу один запрос, который дает мне все заказы с общей суммой счетов-фактур и идентифицирует те, у которых общая сумма счетов-фактур превышает пороговое значение (в моем случае 10%).
Из моего поиска я понял, что мне нужен дополнительный выбор, но я не могу разобраться. Я извиняюсь, так как чистый SQL не является моей рулевой рубкой - обычные вызовы Rails Active Record удовлетворяют 99% моих потребностей.
Пример данных:
=> SELECT * FROM expenditures WHERE id = 17;
id | category | parent_id
-----+----------------+----------
17 | purchase_order |
=> SELECT * FROM expenditures_items WHERE expenditure_id = 17;
id | amount
-----+-------------
1 | 1000.00
2 | 2000.00
Мне нужно получить СУММУ (расходы.сумма) в моем результате - первоначальный заказ на 3000,00 долларов США.
Сопутствующие расходы (счета-фактуры)
=> SELECT * FROM expenditures WHERE category = 'invoice', parent_id = 17;
id | category | parent_id
-----+----------------+----------
46 | invoice | 17
88 | invoice | 17
=> SELECT * FROM expenditures_items WHERE expenditure_id IN (46, 88) ;
id | amount | expenditure_id
-----+----------+---------------
23 | 500.00 | 46
24 | 1000.00 | 46
78 | 550.00 | 88
79 | 1100.00 | 88
В заказе 17 есть два счета (46 и 88) на общую сумму 3 150,00 долларов США — это СУММА всех сумм счетов-фактур.
В конце концов я ищу SQL, который дает мне что-то вроде этого:
=> SELECT * FROM expenditures WHERE category = 'purchase_order';
id | category | expenditure_total | invoice_total | percent
-----+----------------+-------------------+---------------+---------
17 | purchase_order | 3000.00 | 3150.00 | 5
45 | purchase_order | 4000.00 | 3000.00 | -25
75 | purchase_order | 7000.00 | 7000.00 | 0
99 | purchase_order | 10000.00 | 11100.00 | 11
процент равен инвойс_тотал / расход_тотал - 1.
Мне также нужно (возможно, предложение HAVING) отфильтровать только результаты, у которых процент > порога (скажем, 10).
Из всех моих поисков это кажется подзапросом вместе с некоторыми соединениями, но я потерялся в этот момент.
ОБНОВЛЕНО Далее
Я еще раз посмотрел - это близко:
SELECT DISTINCT expenditures.*, SUM( invoice_items.amount ) as invoiced_total FROM "expenditures" JOIN expenditures AS invoices ON invoices.category = 'invoice' AND expenditures.id = CAST( invoices.ancestry AS INT) JOIN expenditure_items ON expenditure_items.expenditure_id = expenditures.id JOIN expenditure_items AS invoice_items ON invoice_items.expenditure_id = invoices.id WHERE "expenditures"."category" IN ($1, $2) GROUP BY expenditures.id HAVING (( SUM( invoice_items.amount ) / SUM( expenditure_items.amount ) ) > 1.1 ) [["category", "work_order"], ["category", "purchase_order"]]
Вот странная вещь - invoiced_total в выборе работает. Я получаю правильные суммы согласно моему примеру. Проблема, кажется, в моем HAVING, где он вытягивает только СУММУ по первому счету.
ОБНОВЛЕНИЕ 3
Ооооочень близко:
SELECT DISTINCT
expenditures.*,
( SELECT
SUM(expenditure_items.amount)
FROM expenditure_items
WHERE expenditure_items.expenditure_id = expenditures.id ) AS order_total,
( SELECT
SUM(expenditure_items.amount)
FROM expenditure_items
JOIN expenditures invoices ON expenditure_items.expenditure_id = invoices.id
AND CAST (invoices.ancestry AS INT) = expenditures.id ) AS invoice_total
FROM "expenditures"
INNER JOIN "expenditure_items" ON "expenditure_items"."expenditure_id" = "expenditures"."id"
WHERE "expenditures"."category" IN ("work_order", "purchase_order")
Единственное, чего я не могу добиться, это устранить расходы, по которым либо нет счетов-фактур, либо которые превышают мое правило 10%. Первый был в моем старом решении с исходным соединением - я не могу понять, как суммировать эти данные соединения.
Я думаю, это понятнее.
Ответы 1
пошаговая демонстрация: db<>рабочий пример
Я уверен, что есть лучшее решение, но это должно работать:
WITH cte AS (
SELECT
e.id,
e.category,
COALESCE(parent_id, e.id) AS parent_id,
ei.amount
FROM
expenditures e
JOIN
expenditures_items ei ON e.id = ei.expenditure_id
),
cte2 AS (
SELECT
id,
SUM(amount) FILTER (WHERE category = 'purchase_order') AS expentiture_total,
SUM(amount) FILTER (WHERE category = 'invoice') AS invoice_total
FROM (
SELECT
parent_id AS id,
category,
SUM(amount) AS amount
FROM cte
GROUP BY (parent_id, category)
) s
GROUP BY id
)
SELECT
*,
(invoice_total/expentiture_total - 1) * 100 AS percent
FROM
cte2
Первый CTE объединяет обе таблицы. Функция COALESCE() отражает идентификатор как parent_id, если в записи его нет (если category = 'purchase_order'). Это можно использовать, чтобы сделать один единственный GROUP для этого идентификатора и категории.
Это делается во втором CTE (самый внутренний подзапрос). [Кстати: я выбираю вариант CTE, потому что считаю его более читабельным. В этом случае вы, конечно, можете выполнять все шаги как подзапросы.] Эта группа суммирует различные категории для каждого (parent_)id.
Внешний подзапрос выполняет поворот. Он сдвигает различные записи для каждой категории в ожидаемый результат с помощью GROUP BY и предложения FILTER (посмотрите на этот шаг в скрипке, чтобы понять это). Не беспокойтесь о функции SUM() здесь. Из-за GROUP BY нужна одна функция агрегации, но она ничего не делает, потому что группировка уже сделана.
Последний шаг — вычисление процентного значения из сводной таблицы.
Я печатал обновление, как вы разместили - посмотрите, пока я тест-драйв, вы отвечаете.
Я видел это. Завтра проверю, сейчас уже поздно :)
Это работает, но я искал что-то вроде SELECT expenditures.*, expenditure_total, invoice_total, percent FROM expenditures... Я хочу, чтобы отдельные записи о расходах ПЛЮС добавляли немного совокупных данных.
Я немного почитал о CTE (postgresql.org/docs/8.4/queries-with.html). Честно говоря, я таких раньше не видел. Начинаю ломать голову над ними. Мне нужно посмотреть, смогу ли я заставить это работать в моем приложении Rails.
1. CTE здесь не совсем нужен, он просто для удобства чтения. Того же можно добиться, просто используя эти операторы в качестве подзапроса: dbfiddle.uk/…, что на самом деле даже немного быстрее (посмотрите на стоимость): dbfiddle.uk/…
2. Ваш (ОБНОВЛЕНИЕ 3) способ, напротив, чрезвычайно медленный из-за множественных вызовов одной и той же таблицы, которых следует избегать каждый раз. Соединения также дороги и DISTINCT. Таким образом, ваш запрос в 10 раз медленнее, чем мой запрос CTE (только одно небольшое соединение, вызов таблицы только один раз, без четкости): dbfiddle.uk/…
Подзапрос с первой агрегацией SUM() можно опустить из-за агрегации SUM() сводной таблицы (но по некоторым причинам он немного медленнее: dbfiddle.uk/…). Последний подзапрос можно опустить, если вы вычисляете SUM() дважды для процента: dbfiddle.uk/…. Избыточное выполнение кода, конечно, дороже: dbfiddle.uk/…
Это так, но в моем случае (приложение Rails) вы строите SQL-запрос следующим образом: Expenditure.where(id: 17) => "SELECT * FROM Расходы WHERE id = 17". Система Rails Active Record создает и выполняет этот запрос для вас. Теперь, когда это сказано, я уверен, что могу построить эти запросы CTE, но простой способ — начать с Expenditure.where(category: 'invoice'....). Я хотел бы также сделать эту работу в Rails, опубликовать работу, а затем принять ответ.
Давайте продолжить обсуждение в чате.
Пожалуйста, покажите нам структуру вашей таблицы, несколько примеров наборов данных и ожидаемый результат.