使用join和条件聚合进行postgreSQL更新

Question

我在PostgreSQL 11.3上有两个表：

销售：

+------------+-----------+--------+----------+-----------+ |event_week |store_num |菲萨尔杜 |纬度 |经度 | +------------+-----------+--------+----------+-----------+ |    201946 |    11249 |K2K3C2 |空 |空 | |    201947 |    11250 |V6B0G5 |空 |空 | |    201948 |    11251 |N9H0G6 |空 |空 | +------------+-----------+--------+----------+-----------+

fsa_latlong：

+--------+-------+------+--------------------+-----------+ |菲萨尔杜 |FSA5 |FSA4 |     纬度 |经度 | +--------+-------+------+--------------------+-----------+ |K2K3C2 |K2K32 |K2K3 |47.006479999999996 | -52.9587 | |V6B0G5 |V6B0G |V6B0 |47.416990000000006 |-53.19438 | |N9H0G1 |N9H0G |N9H0 |47.326370000000004 |-52.80969 | |N9H0G2 |N9H0G |N9H0 |47.326370000000004 |-52.80969 | |N9H0G3 |N9H0G |N9H0 |47.326370000000004 |-52.80969 | |N9H0G4 |N9H0G |N9H0 |47.326370000000004 |-52.80969 | |N9H0G5 |N9H0G |N9H0 |47.326370000000004 |-52.80969 | +--------+-------+------+--------------------+-----------+

我想执行联接以从fsa_latlong表中更新销售表中的纬度和经度。这是使用以下查询完成的：

update sales s
set latitude = l.latitude,
longitude = l.longitude
from fsa_latlong l
where s.fsaldu = l.fsaldu;

但是，由于邮政编码被停用或重新分配，销售表中的某些fsaldu值在fsa_latlong表中没有匹配项。

因此，我想完成的是，当整个邮政编码不完全匹配时，从fsa5 或 fsa4级别获取平均纬度和经度。

我的第一次尝试是这样的，在第一次连接完成后使用第二个查询。

update sales s
set latitude = (SELECT AVG(l.latitude)
FROM fsa_latlong l
WHERE s.latitude is null
AND (
l.fsa5 = substring(s.fsaldu, 1, 5)
OR l.fsa4 = substring(s.fsaldu, 1, 4)
)),
longitude = (SELECT AVG(l.longitude)
FROM fsa_latlong l
WHERE s.longitude is null
AND (
l.fsa5 = substring(s.fsaldu, 1, 5)
OR l.fsa4 = substring(s.fsaldu, 1, 4)
));

但是，这并没有按预期工作。理想情况下，我想通过单个查询完成此操作，我怀疑它使用CASE语句或COALESCE以便在初始匹配为 null 时计算聚合。

非常感谢对此的任何帮助。

Answer 1

这样的东西应该可以工作(未经测试)：

WITH
average_values AS (
SELECT    s.fsaldu,
AVG(l.latitute)  AS avg_latitude,
AVG(l.longitude) AS avg_longitude
FROM      sales s
LEFT
JOIN      fsa_latlong   fl
ON     l.fsa5 = substring(s.fsaldu, 1, 5)
OR     l.fsa4 = substring(s.fsaldu, 1, 4)
GROUP BY 1
),
selected_values AS (
SELECT   av.fsaldu,
coalesce(fl.laitude,   av.avg_latitude)  AS latitude,
coalesce(fl.longitude, av.avg_longitude) AS longitue
FROM     average_values av
LEFT
JOIN     fsa_latlong   fl
ON   wf.fsaldu = fl.fsaldu
)
UPDATE sales s
SET latitude = sv.latitude,
longitude = sv.longitude
FROM selected_values sv
WHERE s.fsaldu = sv.fsaldu;

笔记：

• 你有什么充分的理由写"我想用一个查询完成这个"吗？ 通常，有两个单独的查询比一个过于复杂的查询更好
• 我的查询效率不是很高，因为平均值是针对销售表中的所有 FSALDU 值计算的。这可以很容易地得到改善。

一个更一般的评论 - 使用 CTE 进行子查询，CTE 可读且高效(例如，您不必在单独的地方计算经度/纬度)。

Answer 2

就我个人而言，我更喜欢分步做这种事情。在这种情况下，通过测试前面的步骤是否失败(通过 NULL 纬度/纬度)条件。

---

UPDATE sales s
SET latitude = l.latitude
,longitude = l.longitude
FROM fsa_latlong l
WHERE s.fsaldu = l.fsaldu;
UPDATE sales s
SET latitude = agg.latitude
,longitude = agg.longitude  
FROM (SELECT l.fsa5
, AVG(l.latitude) AS latitude
, AVG(l.longitude) AS longitude
FROM fsa_latlong l
GROUP BY l.fsa5
) agg
WHERE agg.fsa5 = substring(s.fsaldu, 1, 5)
AND (s.latitude is null OR s.longitude is null)
;
UPDATE sales s
SET latitude = agg.latitude
,longitude = agg.longitude
FROM (SELECT l.fsa4
, AVG(l.latitude) AS latitude
, AVG(l.longitude) AS longitude
FROM fsa_latlong l
GROUP BY l.fsa4
) agg
WHERE agg.fsa4 = substring(s.fsaldu, 1, 4)
AND (s.latitude is null OR s.longitude is null)
;

Answer 3

让我假设sales每一行都有一个唯一的 id，我将它称为sale_id：

UPDATE sales
SET latitude = COALESCE(l.latitude, l5.latitude, l4.latitude),
longitude = COALESCE(l.longitude, l5.longitude, l4.longitude)
FROM sales s LEFT JOIN
fsa_latlong l
ON s.fsaldu = l.fsaldu LEFT JOIN
(SELECT SUBSTR(fsaldu, 1, 5) fsaldu5, AVG(latitude) as latitude, AVG(longitude) as longitude
FROM fsa_latlong l
GROUP BY SUBSTR(fsaldu, 1, 5)
) l5
ON l5.fsaldu5 = SUBSTR(s.fsaldu, 1, 5) LEFT JOIN
(SELECT SUBSTR(fsaldu, 1, 4) as fsaldu4, AVG(latitude) as latitude, AVG(longitude) as longitude
FROM fsa_latlong l
GROUP BY SUBSTR(fsaldu, 1, 4)
) l4
ON l4.fsaldu4 = SUBSTR(s.fsaldu, 1, 4) 
WHERE s.sales_id = sales.sales_id;