时间戳

首页 > 解决方案 > 如何编写根据 GROUP BY 执行不同操作的 SQL 查询

sql - 如何编写根据 GROUP BY 执行不同操作的 SQL 查询

问题描述

我有一个订单分类帐，其中显示了与某种资产相关的一些订单。该表包含某个资产的某个订单的订单更新。我需要能够通过添加不同订单的 amount_filled 列来获取用户拥有的每种资产的总金额，具体取决于它是否是买入/卖出。如果是同一个订单，那么我们应该得到最近的成交量。

该表如下所示：

 asset_id | amount_filled | external_id | type 
----------+---------------+-------------+------
 asset1   |           101 | 20          | buy
 asset1   |            50 | 21          | sell
 asset2   |            30 | 23          | buy
 asset3   |            30 | 25          | buy
 asset3   |            30 | 25          | sell
 asset4   |            30 | 100         | buy
 asset4   |            50 | 100         | buy

预期输出为：

 asset_id | amount 
----------+--------
 asset2   |     30
 asset4   |     50
 asset1   |     51
 asset3   |     0

如果订单不同，则金额为买入金额 - 卖出金额。

这是我可以开始工作的唯一查询：

SELECT asset_id, sum(case when type = 'sell' 
    then amount_filled * -1 
    else amount_filled 
    end) as amount 
from (
    SELECT asset_id, type, max(amount_filled) as amount_filled
    from orders
    WHERE "orders"."user_id" = 'user-0' 
    group by asset_id, external_id, type
) f
group by asset_id;

我不是 100% 确定这个查询是正确的，无论如何我都在尝试优化它，我的想法可能是使用一个DISTINCT ON子句，但到目前为止我真的什么都做不了。

标签： sqlpostgresql

解决方案

演示：db<>小提琴

您可以使用条件聚合，例如使用以下FILTER子句：

SELECT
    asset_id,
    SUM(amount_filled) FILTER (WHERE type = 'buy')          -- 2
    - COALESCE(                                             -- 4
          SUM(amount_filled) FILTER (WHERE type = 'sell'),  -- 3
          0
      )
FROM  (
    SELECT DISTINCT ON (asset_id, external_id, type)        -- 1
        *
    FROM mytable
    ORDER BY asset_id, external_id, type, created_at DESC
) s
GROUP BY asset_id
  1. DISTINCT ON获取有序组的第一条记录。您的分组是(asset_id, external_id, type)按created_at列排序，DESC以便将最新记录排在最前面。
  2. 首先总结buy类型
  3. 然后对类型求和sell并从（1）中减去
  4. COALESCE()是为了避免在没有记录可用的情况下减去一个NULL值sell

推荐阅读