Как можно оптимизировать запрос sql?

Question

[vosyukov]

SELECT "r"."ticker_symbol" AS symbol, (SELECT COUNT(*) FROM "requests" "re" WHERE "re"."wallet_address" = :walletAddress AND "r"."ticker_symbol" = "re"."ticker_symbol" AND "re"."request_status" = :requestStatus) AS "pendingCount", (SELECT SUM("r"."amount")::varchar FROM "requests" "rew" WHERE "rew"."wallet_address" = :walletAddress AND "r"."ticker_symbol" = "rew"."ticker_symbol" AND "rew"."request_status" = :requestStatus) AS "expected" FROM "requests" "r" WHERE "r"."wallet_address" = :walletAddress GROUP BY "r"."ticker_symbol"

Подскажите как можно красивее написать запрос такого типа?

Comments

[Rsa97]

И указать, о каком именно диалекте SQL идёт речь.

[kocherman]

а еще неплохо результаты EXPLAIN сюда опубликовать

Answer 1

[Vitsliputsli]

Аналогично, но в разы быстрее будет так:

SELECT 
            "ticker_symbol" AS symbol, 
	    count(case when "request_status" = :requestStatus then 1 else null end) "pendingCount",
	    sum(case when "request_status" = :requestStatus then "amount" else null end)::varchar "expected"
	FROM "requests"  
	WHERE "wallet_address" = :walletAddress 
	GROUP BY "ticker_symbol"

Если я ниего не напутал.
А если нулевые значения не нужны, то можно еще проще:

SELECT 
            "ticker_symbol" AS symbol, 
	    count(*) "pendingCount",
	    sum("amount")::varchar "expected"
	FROM "requests"  
	WHERE "wallet_address" = :walletAddress and "request_status" = :requestStatus
	GROUP BY "ticker_symbol"