sql - 选择 postgreSQL 中可用的最准确数据？

Question

我的数据库中有地址和价格数据。定价数据不完整，通常适用于一个地区，有时适用于特定地址。我有一个 plpgsql 函数，可以检索可用的最准确的定价数据。我目前在我的函数中有这个:

SELECT price_est.sau_price_avg INTO _est_price FROM price_est WHERE price_est.neighbourhood_id = _neighbourhood_id AND  --neighbourhood level
                                                                    price_est.district_id = _district_id AND
                                                                    price_est.municipality_id = _municipality_id AND
                                                                    price_est.county_id = _county_id;
IF _est_price IS NULL THEN
    SELECT price_est.sau_price_avg INTO _est_price FROM price_est WHERE price_est.neighbourhood_id IS NULL AND          --district level
                                                                        price_est.district_id = _district_id AND
                                                                        price_est.municipality_id = _municipality_id AND
                                                                        price_est.county_id = _county_id;
END IF;
...

如果某个地方的数据不可用，则没有对该区域的外键引用。

这对我来说似乎是一个非常笨拙的解决方案。有更好的方法吗？

我可以更改表格并且需要数据呈现。

--------------------------------编辑------------ --------------------------

我让 Nick Barnes 的回答对我来说是这样的:

SELECT price_est.sau_price_avg
FROM price_est
WHERE
    (price_est.county_id = _county_id OR price_est.county_id IS NULL) AND
    (price_est.municipality_id = _municipality_id OR price_est.municipality_id IS NULL) AND
    (price_est.district_id = _district_id OR price_est.district_id IS NULL) AND
    (price_est.neighbourhood_id = _neighbourhood_id OR price_est.neighbourhood_id IS NULL)
ORDER BY price_est.neighbourhood_id NULLS LAST,
         price_est.district_id NULLS LAST,
         price_est.municipality_id NULLS LAST,
         price_est.county_id NULLS LAST
LIMIT 1;

Answer 1

您可以同时选择它们，并使用 ORDER BY/LIMIT 来按准确性排序:

SELECT price_est.sau_price_avg
INTO _est_price
FROM price_est
WHERE
  (price_est.neighbourhood_id = _neighbourhood_id OR price_est.neighbourhood_id IS NULL) AND
  price_est.district_id = _district_id AND
  price_est.municipality_id = _municipality_id AND
  price_est.county_id = _county_id
ORDER BY price_est.neighbourhood_id NULLS LAST
LIMIT 1;

请注意，NULLS LAST 是默认顺序，因此并非绝对必要，但在这种情况下最好明确说明您的意图。