sql - 在查询中重用 SUM OVER PARTITION 返回值

Question

我正在寻找一种方法来避免在查询中进行两次计算，例如:

SELECT DISTINCT
    coins.price_btc,
    coins.price_eur,
    coins.price_usd,
    coins.currency_name,
    coins.currency_symbol,
    SUM ( market_transactions.quantity ) OVER ( PARTITION BY market_transactions.market_coin_id ) * coins.price_eur AS holdings_eur,
    SUM ( market_transactions.quantity ) OVER ( PARTITION BY market_transactions.market_coin_id ) * coins.price_usd AS holdings_usd,
    SUM ( market_transactions.quantity ) OVER ( PARTITION BY market_transactions.market_coin_id ) * coins.price_btc AS holdings_btc,
    SUM ( market_transactions.quantity ) OVER ( PARTITION BY market_transactions.market_coin_id ) AS holdings 
FROM
    market_transactions
    INNER JOIN coins ON coins.id = market_transactions.market_coin_id 
WHERE
    market_transactions.user_id = 1 
ORDER BY
    coins.currency_symbol

我不确定分区上的总和是否一直在运行。

感谢任何指点，我确信查询也可以优化，但我不确定从哪里开始。

CREATE TABLE "public"."coins" (
  "id" int8 NOT NULL DEFAULT nextval('coins_id_seq'::regclass),
  "currency_symbol" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL,
  "currency_name" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL,
  "price_usd" numeric(16,7) NOT NULL DEFAULT NULL,
  "price_eur" numeric(16,7) NOT NULL DEFAULT NULL,
  "price_btc" numeric(16,7) NOT NULL DEFAULT NULL,
  CONSTRAINT "coins_pkey" PRIMARY KEY ("id")
)

CREATE TABLE "public"."market_transactions" (
  "id" int8 NOT NULL DEFAULT nextval('market_transactions_id_seq'::regclass),
  "user_id" int4 NOT NULL DEFAULT NULL,
  "quantity" numeric(18,8) NOT NULL DEFAULT NULL,
  "market_coin_id" int4 DEFAULT NULL,
  CONSTRAINT "market_transactions_pkey" PRIMARY KEY ("id")
)

用户有很多涉及硬币的交易(market_transactions.market_coin_id is coins.id)，我正在尝试对拥有的数量求和(market_transactions.数量)，然后将这个值乘以不同货币(btc，eur，usd)表示的硬币价格

Answer 1

我建议在joining 和做之前进行聚合:

SELECT c.*,
       mt.quantity * c.price_eur AS holdings_eur,
       mt.quantity * c.price_usd AS holdings_usd,
       mt.quantity * c.price_btc AS holdings_btc,
       mt.quantity * c.market_coin_id AS holdings 
FROM coins c JOIN
     (SELECT mt.market_coin_id, SUM(mt.quantity) as quantity
      FROM market_transactions t
      WHERE mt.user_id = 1 
      GROUP BY mt.market_coin_id
     ) mt
     ON c.id = mt.market_coin_id 
ORDER BY c.currency_symbol