复杂的嵌套聚合以获取订单总计 [英] Complex nested aggregations to get order totals
问题描述
我有一个跟踪订单和相关支出的系统。这是在PostgreSQL上运行的Rails应用程序。我的应用程序中有99％是通过普通的旧Rails Active Record调用等获得的。这很丑。
I have a system to track orders and related expenditures. This is a Rails app running on PostgreSQL. 99% of my app gets by with plain old Rails Active Record call etc. This one is ugly.
支出表格看起来像这样：
+----+----------+-----------+------------------------+
| id | category | parent_id | note |
+----+----------+-----------+------------------------+
| 1 | order | nil | order with no invoices |
+----+----------+-----------+------------------------+
| 2 | order | nil | order with invoices |
+----+----------+-----------+------------------------+
| 3 | invoice | 2 | invoice for order 2 |
+----+----------+-----------+------------------------+
| 4 | invoice | 2 | invoice for order 2 |
+----+----------+-----------+------------------------+
每个支出有许多 expenditure_items ，并且订单可以作为发票的父项。该表如下所示：
Each expenditure has many expenditure_items and can the orders can be parents to the invoices. That table looks like this:
+----+----------------+-------------+-------+---------+
| id | expenditure_id | cbs_item_id | total | note |
+----+----------------+-------------+-------+---------+
| 1 | 1 | 1 | 5 | Fuit |
+----+----------------+-------------+-------+---------+
| 2 | 1 | 2 | 15 | Veggies |
+----+----------------+-------------+-------+---------+
| 3 | 2 | 1 | 123 | Fuit |
+----+----------------+-------------+-------+---------+
| 4 | 2 | 2 | 456 | Veggies |
+----+----------------+-------------+-------+---------+
| 5 | 3 | 1 | 34 | Fuit |
+----+----------------+-------------+-------+---------+
| 6 | 3 | 2 | 76 | Veggies |
+----+----------------+-------------+-------+---------+
| 7 | 4 | 1 | 26 | Fuit |
+----+----------------+-------------+-------+---------+
| 8 | 4 | 2 | 98 | Veggies |
+----+----------------+-------------+-------+---------+
我需要跟踪一些内容：
- 需要为订单开具发票的金额（很容易）
- 以上，但每个
cbs_item_id（这是丑陋的部分）
- amounts left to be invoiced on orders (thats easy)
- above but rolled up for each
cbs_item_id(this is the ugly part)
cbs_item_id基本上是一种会计代码，用于对花费的金钱等。我已经看到了最终结果：
The cbs_item_id is basically an accounting code to categorize the money spent etc. I have visualized what my end result would look like:
+-------------+----------------+-------------+---------------------------+-----------+
| cbs_item_id | expenditure_id | order_total | invoice_total | remaining |
+-------------+----------------+-------------+---------------------------+-----------+
| 1 | 1 | 5 | 0 | 5 |
+-------------+----------------+-------------+---------------------------+-----------+
| 1 | 2 | 123 | 60 | 63 |
+-------------+----------------+-------------+---------------------------+-----------+
| | | | Rollup for cbs_item_id: 1 | 68 |
+-------------+----------------+-------------+---------------------------+-----------+
| 2 | 1 | 15 | 0 | 15 |
+-------------+----------------+-------------+---------------------------+-----------+
| 2 | 2 | 456 | 174 | 282 |
+-------------+----------------+-------------+---------------------------+-----------+
| | | | Rollup for cbs_item_id: 2 | 297 |
+-------------+----------------+-------------+---------------------------+-----------+
order_total 是总计 对于给定订单的所有支出项目（类别=订单）。 invoice_total 是所有parent_id =支出.id的支出项目的总和。剩余部分将计算为差异（但不大于0）。实际上，这里的想法是您下达订单并订购1000美元，并且有750美元的发票进来。我需要计算订单上剩下的250美元（剩余）-细分为每个类别（ cbs_item_id ）。然后，我需要汇总所有按 cbs_item_id 分组的剩余值。
order_total is the sum of total for all the expenditure_items of the given order ( category = 'order'). invoice_total is the sum of total for all the expenditure_items with parent_id = expenditures.id. Remaining is calculated as the difference (but not greater than 0). In real terms the idea here is you place and order for $1000 and $750 of invoices come in. I need to calculate that $250 left on the order (remaining) - broken down into each category (cbs_item_id). Then I need the roll-up of all the remaining values grouped by the cbs_item_id.
因此，每个 cbs_item_id 我需要按每个订单分组，找到订单总额，找到针对该订单的发票总额，然后减去两者（也不能为负）。它必须基于每个订单-总的总差异不会返回预期的结果。
So for each cbs_item_id I need group by each order, find the total for the order, find the total invoiced against the order then subtract the two (also can't be negative). It has to be on a per order basis - the overall aggregate difference will not return the expected results.
最后寻找的结果是这样的：
In the end looking for a result something like this:
+-------------+-----------+
| cbs_item_id | remaining |
+-------------+-----------+
| 1 | 68 |
+-------------+-----------+
| 2 | 297 |
+-------------+-----------+
$我猜这可能是GROUP BY的结合，也许是子查询甚至是CTE（对我来说是巫毒教）。我的SQL技能不是很出色，这远远高于我的薪水等级。
I am guessing this might be a combination of GROUP BY and perhaps a sub query or even CTE (voodoo to me). My SQL skills are not that great and this is WAY above my pay grade.
这里是上面数据的小提琴：
Here is a fiddle for the data above:
http://sqlfiddle.com/#!17/2fe3a
备用小提琴：
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=e9528042874206477efbe0f0e86326fb
推荐答案
此查询产生您要查找的结果：
This query produces the result you are looking for:
SELECT cbs_item_id, sum(order_total - invoice_total) AS remaining
FROM (
SELECT cbs_item_id
, COALESCE(e.parent_id, e.id) AS expenditure_id -- ①
, COALESCE(sum(total) FILTER (WHERE e.category = 'order' ), 0) AS order_total -- ②
, COALESCE(sum(total) FILTER (WHERE e.category = 'invoice'), 0) AS invoice_total
FROM expenditures e
JOIN expenditure_items i ON i.expenditure_id = e.id
GROUP BY 1, 2 -- ③
) sub
GROUP BY 1
ORDER BY 1;
db<>小提琴此处
①请注意我如何假设使用 expenditures.parent_id 为整数，而值为 NULL 则为true字符串 nil。这样可以简单地使用 COALESCE 。
① Note how I assume a saner table definition with expenditures.parent_id being integer, and true NULL instead of the string 'nil'. This allows the simple use of COALESCE.
②关于总计 FILTER 子句：
- Aggregate columns with additional (distinct) filters
③在 SELECT 的序数列出项目。示例：
③ Using short syntax with ordinal numbers of an SELECT list items. Example:
- Select first row in each GROUP BY group?
我可以获取所有行中所有剩余的总数吗，还是需要将其包装到另一个子选择中？
can I get the total of all the remaining for all rows or do I need to wrap that into another sub select?
组组 ：
...
GROUP BY GROUPING SETS ((1), ()) -- that's all :)
db<>小提琴此处
相关
- Converting rows to columns
这篇关于复杂的嵌套聚合以获取订单总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
