PostgreSQL中带有CASE条件的LEFT JOIN

Question

我希望在PostgreSQL中使用CASE条件语句，以决定与另一个表的哪个列进行连接。以下是我的进展情况，我想这可以解释我试图做什么。期待你的想法和建议：

SELECT hybrid_location.*,
       concentration
FROM   hybrid_location
CASE   WHEN EXTRACT(month FROM hybrid_location.point_time) = 1 
            THEN LEFT JOIN (SELECT jan_conc FROM io_postcode_ratios) ON
            st_within(hybrid_location.the_geom, io_postcode_ratios.the_geom) = true
       WHEN EXTRACT(month FROM hybrid_location.point_time) = 2 
            THEN LEFT JOIN (SELECT feb_conc FROM io_postcode_ratios) ON
            st_within(hybrid_location.the_geom, io_postcode_ratios.the_geom) = true
       ELSE LEFT JOIN (SELECT march_conc FROM io_postcode_ratios) ON
            st_within(hybrid_location.the_geom, io_postcode_ratios.the_geom) = true
       END AS concentration;

- TheRealJimShady

---

这是一个非常丑陋的数据库模式。考虑重构它，使当前的列成为一个额外的键。 - Craig Ringer

Answer 1

这是一个非常不寻常的查询，我认为它是无效的。即使条件连接是有效的，查询优化器也很难进行优化。可以重写成连接到单个联合表：

SELECT hybrid_location.*,
       concentration
FROM   hybrid_location
LEFT JOIN (
  SELECT 1 AS month_num, jan_conc AS concentration, io_postcode_ratios.the_geom
  FROM io_postcode_ratios
  UNION ALL
  SELECT 2 AS month_num, feb_conc AS concentration, io_postcode_ratios.the_geom
  FROM io_postcode_ratios
  UNION ALL
  SELECT 3 AS month_num, march_conc AS concentration, io_postcode_ratios.the_geom
  FROM io_postcode_ratios
) AS io_postcode_ratios ON
    EXTRACT(month FROM hybrid_location.point_time) = io_postcode_ratios.month_num
    AND ST_Within(hybrid_location.the_geom, io_postcode_ratios.the_geom)

如果可能的话，更好的组织io_postcode_ratios表格的方法是将每月的*_conc列规范化为一个带有日期或月份列的conc列。这样虽然会增加行数，但更容易查询。

- Mike T

---

谢谢。我已经使用了一些代码来重新格式化我的表格，这样我就可以更轻松地进行匹配了。 - TheRealJimShady