CODEWITHSUNDEEP
sqlpostgresql
John Smith
John Smith

Reputation: 1876

Reuse SUM OVER PARTITION return value in query

I'm looking for a way to save some calculation from being done twice in a query like:

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

I'm not sure if that sum over partition is running all these times.

Thanks for any pointers, I'm sure the query can also be optimized but I'm unsure where to start.

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")
)

A user has many transactions involving a coin (market_transactions.market_coin_id is coins.id), I'm trying to SUM the quantity owned (market_transactions.quantity) for each one and then multiply this value for the price of the coin expressed in different currencies (btc, eur, usd)

this is the result I'm getting, which appears to be correct

Upvotes: 0

Views: 138

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I would suggest aggregating before joining and doing:

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

Upvotes: 1

ravioli
ravioli

Reputation: 3823

Run an EXPLAIN (i.e. EXPLAIN SELECT DISTINCT ...) on the query and see what the query plan is. Most likely, it's only running the window function once. If it is running it multiple times, try adding an outer SELECT:

SELECT DISTINCT 
  price_btc, 
  price_eur, 
  price_usd, 
  currency_name,
  currency_symbol,
  holdings * price_eur AS holdings_eur,
  holdings * price_usd AS holdings_usd,
  holdings * price_btc AS holdings_btc,
  holdings
FROM (
    SELECT
    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 ) AS holdings 
FROM
    market_transactions
    INNER JOIN coins ON coins.id = market_transactions.market_coin_id 
WHERE
    market_transactions.user_id = 1 
) src
ORDER BY
    currency_symbol

Upvotes: 1

Related Questions