从表中获取分组数据

Question

我有一个名为交易的表 交易表具有以下模式：

id (int)
exchange_name (VARCHAR)
exchange_icon (VARCHAR)
trade_time (bigint)
price_quote (int)
price_usd (int)
trade_value (int)
base_asset_icon (VARCHAR)
qty (int)
quoteQty (int)
is_buyer_maker (boolean)
pair (VARCHAR)
base_asset_trade (VARCHAR)
quote_asset_trade (VARCHAR)

我需要这种格式的数据：

    base_asset_trade
    base_asset_icon
    sold: (number of times is_buyer_maker was **true** for this base asset)
    bought: (number of times is_buyer_maker was **false** for this base asset)
    sold_trade_value: (sum of trade_value for all where base asset is sold)
    bought_trade_value: (sum of trade_value for all where base asset is bought)
    price_usd
total_trades: (sum of bought and sold)

如何使用Postgres查询获得这些结果？

到目前为止，我已经尝试过：

SELECT base_asset_trade, trade_value, price_in_usd, base_asset_icon, is_buyer_maker  
FROM (SELECT * 
      FROM trades 
      WHERE  trade_time / 1000 > (extract(epoch from now()) - (86400)*1))
) AS derivedTable 
GROUP BY is_buyer_maker 

但这给出了几个错误。

样本输出：

base_asset: BTC
base_asset_icon: btc.png
sold: 48
bought: 56
sold_trade_value: 200000000
bought_trade_value: 25644300
price: 19000
total_trades: 104

Answer 1

目前尚不清楚什么构成对或错，因此您在此处看到的案例表达可能需要校正，但是查询的结构应适用。您需要一个子句和聚合功能的组计数和总和。

SELECT
      base_asset_trade
    , base_asset_icon
    , count(CASE WHEN is_buyer_maker = 'sold' THEN 1 END) AS sold
    , count(CASE WHEN is_buyer_maker = 'bought' THEN 1 END) AS bought
    , sum(CASE WHEN is_buyer_maker = 'sold' THEN trade_value ELSE 0 END) AS sold_trade_value
    , sum(CASE WHEN is_buyer_maker = 'bought' THEN trade_value ELSE 0 END) AS bought_trade_value
    , price_usd
    , sum(trade_value) AS total_trades
FROM trades
GROUP BY
      base_asset_trade
    , base_asset_icon
    , price_usd

注意：在聚合函数中使用案例表达式时，它被称为“条件聚合”。