如何在PostgreSQL的WHERE子句中嵌套条件实现指定价格范围过滤

SELECT
  market AS "MARKET",
  SUM(size*price) AS "SUM"
FROM
  app_order
WHERE
  price > ( (SELECT MAX(price) FROM app_order WHERE side = 'up') + (SELECT MIN(price) FROM app_order WHERE side = 'down') ) / 2 - 1
  AND price < ( (SELECT MAX(price) FROM app_order WHERE side = 'up') + (SELECT MIN(price) FROM app_order WHERE side = 'down') ) / 2 + 1
GROUP BY
  market
ORDER BY
  market ASC

条件说明 #

• 通过两个子查询分别获取side='up'的最大price和side='down'的最小price
• 计算这两个值的平均值，以此为基准分别加减1得到筛选区间的边界
• 用AND连接两个条件，筛选出price落在该区间内的记录

内容的提问来源于stack exchange，提问作者Eu Román