ruby-on-rails - 复杂嵌套 SUM/子选择

Question

已更新示例数据等

对于这个复杂的查询，我有点不知所措。一些背景:这是一个 Rails 应用程序，我有 expenditures 模型，其中有许多 expenditure_items，每个都有一个 amount 列 - 这些都是相关支出的总和。

给定的 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

我需要在我的结果中获得 SUM (expenditures.amount) - 原始订单 3,000.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 美元 - 这是所有发票 expenditure_item 金额的总和。

最后我正在寻找让我得到这样的东西的 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

百分比是 invoice_total/expenditure_total - 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 中，它只提取第一张发票上的 SUM。

更新 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% 规则的支出。第一个是在我的原始连接的旧解决方案中 - 我似乎无法弄清楚如何对该连接数据求和。

Answer 1

step-by-step demo:db<>fiddle

我敢肯定，有更好的解决方案，但这个应该可行:

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 连接两个表。如果记录没有(如果 category = 'purchase_order')，COALESCE() 函数将 id 镜像为 parent_id。这可用于在此 ID 和类别上执行单个 GROUP。

这是在第二个 CTE(最内部子查询)中完成的。 [顺便说一句:我选择 CTE 变体是因为我发现它更具可读性。在这种情况下，您当然可以将所有步骤作为子查询来执行。] 该组总结了每个 (parent_)id 的不同类别。

外部子查询正在做一个枢轴。它借助 GROUP BY 和 FILTER 子句将每个类别的不同记录转移到您的预期结果中(查看 fiddle 中的这一步以了解它) .不用担心这里的 SUM() 函数。因为GROUP BY，一个聚合函数是必要的，但它什么都不做，因为分组已经完成了。

最后一步是计算透视表的百分比值。