创建联接表的多个过滤结果集以用于聚合函数 [英] Create multiple filtered result sets of a joined table for use in aggregate functions

问题描述

我有一个（大大简化的）订单表，总计是美元金额，其中包含：

I have a (heavily simplified) orders table, total being the dollar amount, containing:

| id | client_id |   type | total |
|----|-----------|--------|-------|
|  1 |         1 |   sale |   100 |
|  2 |         1 | refund |   100 |
|  3 |         1 | refund |   100 |

和客户表包含：

| id | name |
|----|------|
|  1 | test |

我正在尝试按客户细分销售，退款，销售额，退款总额等。

I am attempting to create a breakdown, by client, metrics about the total number of sales, refunds, sum of sales, sum of refunds etc.

为此，我要查询客户表并加入订单表。订单表包含销售和退款，由 type 列指定。

To do this, I am querying the clients table and joining the orders table. The orders table contains both sales and refunds, specified by the type column.

我的想法是使用子查询两次加入订单，并为那些过滤后的表创建别名。然后将别名用于聚合函数中，以求和，求平均值等。我尝试了两次将订单表加入联接的多种方法，以实现此目的，但会产生相同的错误结果。该查询表明了这个想法：

My idea was to join the orders twice using subqueries and create aliases for those filtered tables. The aliases would then be used in aggregate functions to find the sum, average etc. I have tried many variations of joining the orders table twice to achieve this but it produces the same incorrect results. This query demonstrates this idea:

SELECT
  clients.*,
  SUM(sales.total) as total_sales,
  SUM(refunds.total) as total_refunds,
  AVG(sales.total) as avg_ticket,
  COUNT(sales.*) as num_of_sales
FROM clients   
LEFT JOIN (SELECT * FROM orders WHERE type = 'sale') as sales
  ON sales.client_id = clients.id   
LEFT JOIN (SELECT * FROM orders WHERE type = 'refund') as refunds
  ON refunds.client_id = clients.id  
GROUP BY clients.id

结果：

| id | name | total_sales | total_refunds | avg_ticket | num_of_sales |
|----|------|-------------|---------------|------------|--------------|
|  1 | test |         200 |           200 |        100 |            2 |

预期结果：

| id | name | total_sales | total_refunds | avg_ticket | num_of_sales |
|----|------|-------------|---------------|------------|--------------|
|  1 | test |         100 |           200 |        100 |            1 |

当第二个联接包含在查询中时，第一个联接返回的行将再次返回第二次加入。它们乘以第二个联接中的行数。很明显，我对联接和/或子查询的理解是不完整的。

When the second join is included in the query, the rows returned from the first join are returned again with the second join. They are multiplied by the number of rows in the second join. It's clear my understanding of joining and/or subqueries is incomplete.

我知道我可以使用每个汇总函数过滤订单表。这会产生正确的结果，但效率似乎很低：

I understand that I can filter the orders table with each aggregate function. This produces correct results but seems inefficient:

SELECT
  clients.*,
  SUM(orders.total) FILTER (WHERE type = 'sale') as total_sales,
  SUM(orders.total) FILTER (WHERE type = 'refund') as total_refunds,
  AVG(orders.total) FILTER (WHERE type = 'sale') as avg_ticket,
  COUNT(orders.*) FILTER (WHERE type = 'sale') as num_of_sales      
FROM clients    
LEFT JOIN orders 
  on orders.client_id = clients.id     
GROUP BY clients.id 

创建过滤后的此联接表的别名版本？

What is the appropriate way to created filtered and aliased versions of this joined table?

此外，在我的初始查询中，两个子查询的连接正好发生了什么。我希望即使它们在相同的（订单）表上运行也被视为单独的子集。

Also, what exactly is happening with my initial query where the two subqueries are joined. I would expect them to be treated as separate subsets even though they are operating on the same (orders) table.

推荐答案

对所需的所有聚合执行一次（过滤后的）聚合，然后将其加入结果。由于您的汇总不需要 clients 表中的任何列，因此可以在派生表中完成。通常，这也比对联接结果进行分组要快。

You should do the (filtered) aggregation once for all aggregates you want, and then join to the result of that. As your aggregation doesn't need any columns from the clients table, this can be done in a derived table. This is also typically faster than grouping the result of the join.

SELECT clients.*,
       o.total_sales, 
       o.total_refunds,
       o.avg_ticket,
       o.num_of_sales
FROM clients    
  LEFT JOIN (
    select client_id, 
           SUM(total) FILTER (WHERE type = 'sale') as total_sales,
           SUM(total) FILTER (WHERE type = 'refund') as total_refunds,
           AVG(total) FILTER (WHERE type = 'sale') as avg_ticket,
           COUNT(*) FILTER (WHERE type = 'sale') as num_of_sales      
    from orders
    group by client_id
  ) o on o.client_id = clients.id     

这篇关于创建联接表的多个过滤结果集以用于聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！