如何在不使用 FULL OUTER JOIN 的情况下有条件地分组到列中

问题描述

我要转

TABLEA:
id      type               amount
A       'Customer'          100
A       'Parter'            10
A       'Customer'          200
A       'Parter'            20
B       'Parter'            555

我可以对类型进行硬编码，不需要是动态的，这些类型是枚举

I can hardcode the type, don't need to be dynamic, these types are enum

RESULT:
id      customer_array  customer_sum   partner_array  partner_sum
A       [100, 200]      300            [10, 20]       30
B       []              0              [555]          555

现在我正在使用两个聚合函数

Right now I am using two aggregate function

   WITH customer AS (
      SELECT
        table_A,
        json_agg(row_to_json(amount)) AS customer_array,
        sum(amount)                   AS customer_sum
      FROM table_A WHERE type='Customer'
      GROUP BY id
   ), partner AS (
      SELECT
        table_A,
        json_agg(row_to_json(amount)) AS partner_array,
        sum(amount)                   AS partner_sum
      FROM table_A WHERE type='Partner'
      GROUP BY id
   ) SELECT
       id,
       COALESCE(customer_array, '[]')    AS customer_array,
       COALESCE(customer_sum, 0)         AS customer_sum,
       COALESCE(partner_array, '[]')     AS partner_array,
       COALESCE(partner_sum, 0)          AS partner_sum
   FROM customer FULL OUTER JOIN partner USING (id)

我想知道是否有一种方法可以在不查询两次的情况下实现我想要的?

I am wondering if there is a way to achieve what I want without querying twice?

推荐答案

据我所知，这是一个简单的条件聚合:

This is a simple conditional aggregation as far as I can tell:

select id,
       array_agg(amount) filter (where type = 'Customer') as customer_array,
       sum(amount) filter (where type = 'Customer') as customer_sum,
       array_agg(amount) filter (where type = 'Partner') as partner_array,
       sum(amount) filter (where type = 'Partner') as partner_sum
from table_a
group by id;

如果您想要一个空数组而不是 NULL 值，请将聚合函数包装到 coalesce() 中:

If you want an empty array instead of a NULL value, wrap the aggregation functions into a coalesce():

select id,
       coalesce((array_agg(amount) filter (where type = 'Customer')),'{}') as customer_array,
       coalesce((sum(amount) filter (where type = 'Customer')),0) as customer_sum,
       coalesce((array_agg(amount) filter (where type = 'Partner')),'{}') as partner_array,
       coalesce((sum(amount) filter (where type = 'Partner')),0) as partner_sum
from table_a
group by id;

这篇关于如何在不使用 FULL OUTER JOIN 的情况下有条件地分组到列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！