时间戳

首页 > 解决方案 > Postgres：查询结果不符合预期？

postgresql - Postgres：查询结果不符合预期？

问题描述

我以前用 C 和 VB 编写过，但这是我第一次使用 Prostgres。我已经为我的申请工作了大约一个月，但遇到了一个困扰我大约一周的问题。

我在一个名为 xyzfloats 的表中有大约 7000 行。每行包含 3 个浮点数（x 浮点数，y 浮点数，z 浮点数）（以米为单位的点云表示））最终版本中将有大约 26 亿行，所以我尝试使用一个子集来节省时间。

我的另一个表 xyztable4 包含 24,000,000 行。每行是：xcell int、ycell int、points int、total float。

前两个整数包含一个 x,y 索引。我通过使用带有交叉连接的 generate_series 创建了这个

create table xyztable4(

xcell int,
   ycell int,
   points int default 0,
   total float default 0
)

insert into xyztable4 (xcell, ycell) 

select from generate_series(-1000,6000) as xcell cross join generate_series(-40,3360) as ycell;

计划是将浮动表中的每个x，y放入一个边长为0.05m的小正方形中

当 x 和 y 的正确正方形被识别时，z 被添加到“total”列，“points”列增加。

这是代码：

with subquery as (
     select x,y,z
     from xyzfloats)
update xyztable4
set
   points=points+1,
   total = total + subquery.z
from subquery

where xcell= floor(subquery.x/0.05) and ycell = floor(subquery.y/0.05)

我尝试使用 cast，但负面的索引是错误的。

地板给出正确的结果。

问题是我只处理了 234 行而不是 7000。

我虽然这可能是数字精度的问题，即可能有一些训练数字意味着它不完全是一个 int。我认为 postgres 使用整数除法，如 C？

处理的行数 (234) 始终相同。它将每个点列加 1。如果我再次运行查询，我会在 points 列中得到 2。所以它一直是错误的。

我已经在小范围内对此进行了测试。（ 9 行 xyz 的），它完美地工作

标签： postgresqlsubquerypoint-clouds

解决方案

UPDATE ... FROM ...由于postgres 中的工作方式，该查询没有按照您的预期执行。这是文档中的引用：

当存在 FROM 子句时，实质上发生的情况是目标表连接到 from_list 中提到的表，连接的每个输出行代表目标表的更新操作。使用 FROM 时，应确保连接为要修改的每一行生成最多一个输出行。换句话说，目标行不应连接到来自其他表的多个行。如果是这样，那么只有一个连接行将用于更新目标行，但将使用哪一个是不容易预测的。
由于这种不确定性，仅在子选择中引用其他表更安全，但通常比使用连接更难阅读且速度较慢。

您需要确保您正在更新的表 ( xyztable4) 与FROM子句中的表之间的连接在主表中每行产生一行。你可以这样做：

with subquery as (
     select 
       floor(x/0.05) xcell,
       floor(y/0.05) ycell,
       count(z) cnt,
       sum(z) total_z
     from xyzfloats
     group by 1, 2
)
update xyztable4 
set
   points=points + subquery.cnt,
   total = total + subquery.total_z
from subquery

where xyztable4.xcell = subquery.xcell and xyztable4.ycell = subquery.ycell

推荐阅读