postgresql - 使用窗口函数将聚合与另一个聚合进行比较

Question

以下查询(使用 Postgresql 11.1 测试)为每个客户/产品组合评估以下元素:

• (A) 客户在该产品上花费的销售值(value)总和
• (B) 客户在该产品的父类别中花费的销售值(value)总和

然后将 A/B 相除得到一个称为忠诚度的指标。

select
  pp.customer, pp.product, pp.category,
  pp.sales_product / pc.sales_category as loyalty
from (
    select
      t.household_key as customer,
      t.product_id as product,
      p.commodity as category,
      sum(t.sales_value) as sales_product
    from transaction_data t
    left join product p on p.product_id = t.product_id
    group by t.household_key, t.product_id, p.commodity
) pp
left join (
    select
      t.household_key as customer,
      p.commodity as category,
      sum(t.sales_value) as sales_category
    from transaction_data t
    left join product p on p.product_id = t.product_id
    group by t.household_key, p.commodity
) pc on pp.customer = pc.customer and pp.category = pc.category
;

结果是这种形式:

customer      product    category     loyalty
---------------------------------------------
       1       tomato        food        0.01
       1         beef        food        0.02
       1   toothpaste     hygiene        0.04
       1   toothbrush     hygiene        0.03

我的问题是，与其依赖两个左连接的子查询，不如使用窗口函数的单个查询可行吗？

我已尝试执行类似以下操作，但显然这不起作用，因为在这种情况下，列“t.sales_value”必须出现在 GROUP BY 子句中或用于聚合函数。我看不出可以做些什么来解决这个问题。

-- does not work
select
  t.household_key as customer,
  t.product_id as product,
  p.commodity as category,
  sum(t.sales_value) as sales_product,
  sum(t.sales_value) over (partition by t.household_key, p.commodity) as sales_category
from transaction_data t
left join product p on p.product_id = t.product_id
group by t.household_key, t.product_id, p.commodity;

Answer 1

我不知道如何在不使用连接或子查询的情况下执行此操作，但这是使用分析函数对子查询执行此操作的一种方法:

WITH cte AS (
    SELECT
        t.household_key AS customer,
        t.product_id AS product,
        p.commodity as category,
        SUM(t.sales_value) OVER (PARTITION BY t.household_key, t.product_id, p.commodity)
            AS sales_product,
        SUM(t.sales_value) OVER (PARTITION BY t.household_key, p.commodity)
            AS sales_category
    FROM transaction_data t
    LEFT JOIN product p
        ON p.product_id = t.product_id
)

SELECT
    t.customer,
    t.product,
    t.category
    MAX(t.sales_product) / MAX(t.sales_category) AS loyalty
FROM cte
GROUP BY
    t.customer,
    t.product,
    t.category;

这里的技巧是对连接表进行单次传递，并使用分析求和来计算所需的聚合，其中有两个不同的分区，一个分区有 2 列，另一个分区有 3 列。然后，我们可以按 3 列进行聚合，并任意取每组聚合的最大值。