问题[sql]

实际上，我引用了任务条件：在 PostgreSQL 中编写一个查询，该查询将显示有史以来购买金额最大的买家的姓名（一个订单中可能有几种不同的产品）。该任务的数据库由三个表组成：

1. 买家 - 买家：包含字段buyer_id（主键）、name、city
2. 产品 - 产品：product_id（主键）、product_name、price
3. 订单 - 订单：order_id（主键）、order_date、buyer_id（外键）、product_id（主键）、数量

数据库和查询的模型：https://onecompiler.com/postgresql/43csjz2v6 模型似乎显示正确，但测试不接受问题的解决方案。也曾经怀疑过自己没有理解清楚条件，并不是对用户所有购买进行求和，而是找出最“贵”的购买，但是这也没有起到作用。我做错什么了？

原始请求代码：

  WITH total AS (
  SELECT buyers.buyer_id, buyers.name, SUM(orders.quantity * products.price) AS total_order
  FROM buyers
  INNER JOIN orders ON orders.buyer_id = buyers.buyer_id
  INNER JOIN products ON products.product_id = orders.product_id
  GROUP BY buyers.buyer_id, orders.order_id)
SELECT name FROM (
  SELECT name, SUM(total_order) AS sum_bought FROM total
  GROUP BY buyer_id, name) AS max_bought
ORDER BY sum_bought DESC LIMIT 1

该模型的示例如下：

WITH RECURSIVE test_data (id, p_id) AS (
       VALUES
      (1, NULL),
      (2, 1),
      (3, 1),
      (4, 3),
      (5, 4),
      (6, null),
      (7, null)
),
 r (id) AS (
   SELECT id FROM test_data WHERE id = 3
   UNION
   SELECT td.id FROM r JOIN test_data td ON r.id = td.p_id
   UNION
   SELECT td.p_id FROM r JOIN test_data td ON r.id = td.id
 )
SELECT * FROM r;

在这种情况下，我希望查询不仅在搜索后代的方向上扩展（第一个递归部分），而且还在搜索父母的方向上扩展（第二个递归部分）。分别地，两个递归部分都起作用（如果你注释掉第二个部分）。但它们结合在一起会出现错误：

[42P19] 错误：对查询“r”的递归引用不得出现在其非递归项中

有没有办法在递归 CTE 中使用两个 UNION，或者这是严格禁止的并且没有解决方法？

有人面临每周在 DB2 数据库上运行 SQL 查询的任务吗？也许有使用数据库本身来解决这个问题的方法？

目前，我唯一的想法是编写一个 bash 脚本并将其执行分配给 Cron 任务调度程序。操作系统 Red Hat Linux。

2012年，服务器累计达到了一个新高度，非常酷。累积乘积又如何呢？现在有两个选择：

1. 递归——缓慢
2. CLR——太好了，它可以工作，但是没有办法……

不使用 CLR 怎样实现累积乘法？

有一个表，其中 pars_col 列包含不同结构的 json 字符串。第一行类型：{'values': [{'test': 'A', 'values': [{'value': 8, 'test': 'B'}]}]}，第二行类型：{'values': [{'value': 21.0, 'test': 'D'}]}。一行可以包含多个在测试中含义不同、名称不同的值。我需要提取每一行的所有值并将它们相加。所有这些都需要保存在一列中。

代码有几个错误，感谢@ValNik，他的评论使它变得更好。还剩下一个问题，我的代码没有从数组双重嵌套的行中提取所有值。

例如表格中包含一行：{"values": [{"test": "A", "values": [{"value": 2, "test": "B"}]}, {"test": "D", "values": [{"value": 3, "test": "E"}]}]}'::TEXT)，代码只会从中提取2，但是需要提取2和3并将它们相加。

我的代码：

-- временная таблица для примера 
CREATE TEMP VIEW df AS
SELECT
    prg_id,
    name,
    dt,
    pars_col::json as pars_col
FROM
(
    SELECT
        prg_id,
        name,
        dt,
        pars_col
    FROM
        (VALUES
            (1, 'Product A', '2023-01-01'::DATE, '{"values": [{"test": "A", "values": [{"value": 8, "test": "B"}]}]}'::TEXT),
            (2, 'Product B', '2023-02-01'::DATE, '{"values": [{"value": 21.0, "test": "D"}]}'::TEXT),
            (23, 'Product C', '2023-01-01'::DATE, '{"values": [{"test": "A", "values": [{"value": 5, "test": "B"}]}, {"value": 10, "test": "D"}]}'::TEXT),
            (245, 'Product D', '2023-02-01'::DATE, '{"values": [{"test": "A", "values": [{"value": 2, "test": "B"}]}, {"test": "D", "values": [{"value": 3, "test": "E"}]}]}'::TEXT),
            (32, 'Product E', '2023-01-01'::DATE, '{"values": [{"value": 15, "test": "F"}]}'::TEXT),
            (31, 'Product G', '2023-02-01'::DATE, '{"values": [{"test": "G", "values": [{"value": 7, "test": "H"}]}]}'::TEXT)
        ) AS data(prg_id, name, dt, pars_col)
) as sub; 

-- запрос, с помощью которого я пытаюсь распарсить строки в колонке pars_col
SELECT
    prg_id,
    name,
    dt,
    sum(combined.value)
FROM df 
    CROSS JOIN LATERAL (
        SELECT
            SUM((elem ->> 'value')::numeric) AS value
        FROM
            pg_catalog.json_array_elements(pars_col -> 'values') AS elem

        UNION 

        SELECT
            SUM((elem2 ->> 'value')::numeric) AS value
        FROM
            pg_catalog.json_array_elements((pars_col -> 'values') -> 0 -> 'values') AS elem2
    ) AS combined
GROUP BY prg_id, name, dt

输出为：

该表显示 prg_id 245 未完全提取值。请帮我修复错误。