我没有postgres立即测试，但看看这个sql，希望这对你有用.

SELECT
  product,
  SUM(amount) AS amount,
  SUM(no_of_customers) AS no_of_customers,
  SUM(no_of_bills) AS no_of_bills
FROM (
  SELECT
    product,
    amount,
    no_of_customers
  FROM table1
  UNION ALL
  SELECT
    product,
    0 AS amount,
    0 AS no_of_customers,
    no_of_bills
  FROM table2
) AS t
GROUP BY product
ORDER BY product;

sql使用UNION ALL操作从两个表中创建临时表t，该操作允许合并表，而不管是否存在重复的行。然后，查询按产品列对表t中的数据进行分组，其中具有相同产品名称的所有行都分组在一起。对于每个组，查询将汇总列amount、no_of_customers和no_of_bills columns中的值，并按以下顺序返回结果：product、amount、no_of_customers、no_of_bills。
这就是逻辑

我不确定这是否是最简单的解决方案，但它对我有效。我将表命名为products和bills：

WITH product_summary AS (
  SELECT
    p.product,
    SUM(p.amount) AS amount,
    SUM(p.no_of_customers) AS no_of_customers
  FROM
    products p
  GROUP BY
    p.product
),
bill_summary AS (
  SELECT
    b.product,
    SUM(b.no_of_bills) AS no_of_bills
  FROM
    bills b
  GROUP BY
    b.product
)
SELECT *
FROM (
  SELECT
    ps.product,
    COALESCE(ps.amount, 0) AS amount,
    COALESCE(ps.no_of_customers, 0) AS no_of_customers,
    COALESCE(bs.no_of_bills, 0) AS no_of_bills
  FROM
    product_summary ps
  FULL JOIN
    bill_summary bs
  ON
    ps.product = bs.product
  UNION ALL
  SELECT
    'Total' AS product,
    SUM(COALESCE(ps.amount, 0)) AS total_amount,
    SUM(COALESCE(ps.no_of_customers, 0)) AS total_customers,
    SUM(COALESCE(bs.no_of_bills, 0)) AS total_bills
  FROM
    product_summary ps
  FULL JOIN
    bill_summary bs
  ON
    ps.product = bs.product
) AS result
ORDER BY
  result.product;

我使用通用表表达式（CTE）product_summary来计算products表中每个产品的amount和no_of_customers之和。CTE bill_summary计算bills表中每个产品的no_of_bills之和。然后将这两个结果与UNION ALL合并。

您可以使用group by和grouping sets计算小计和总计，然后使用join将CTE s的两个结果合并合并：

with product_customers_cte as (
  select COALESCE (product,'Total') as product, sum(amount) as amount, sum(no_of_customers) as no_of_customers
  from product_customers
  group by
    grouping sets (
        (product),
        ()
    )
),
product_bills_cte as (
  select COALESCE (product,'Total') as product, sum(no_of_bills) as no_of_bills
  from product_bills
  group by
    grouping sets (
        (product),
        ()
    )
)
select pc.*, pb.no_of_bills
from product_customers_cte pc
join product_bills_cte pb on pb.product = pc.product
order by product

测试结果：

product amount  no_of_customers no_of_bills
Prdct_1 300     24              9
Prdct_2 50      2               4
Prdct_3 120     10              15
Prdct_4 10      1               2
Total   480     37              30

Demo here