要使用“分组依据"一次更新几个未重复的行，请执行以下操作: [英] To update several non dupliated rows at one time using "Group by"
问题描述
But I can't apply that to my case. When I do:
update weighed_directed_edge set endpoint= trunc(1000 * random()+ 1)
from generate_series(1,10) group by 1 where startpoint= from_point;
更新它抱怨的endpointId:
to update endpointId it complains:
ERROR: syntax error at or near "group"
LINE 1: ...nc(1000 * random()+ 1) from generate_series(1,10) group by 1.
我也尝试过:
insert into weighed_directed_edge (startpoint,endpoint)
values (from_point, trunc((1000 * random()+ 1) )
FROM generate_series(1, directed2number)
GROUP BY 1 ;
insert into weighed_directed_edge (startpoint, endpoint, costs)
select 1, trunc(1000 * random()+ 1) from generate_series(1,10) group by 1,1;
不起作用.我需要从另一个表中设置的相同点中选择起点和终点，以填充weighed_directed_edge表.
Not working. I need to select startpoint and endpoint from the same points set from another table, to populate weighed_directed_edge table.
-
交叉点表:有1001个交叉点.
cross_point table : there're 1001 intersections.
Table "public.cross_point"
Column | Type | Modifiers
--------------+---------+---------------------------------------------------------------------
intersection | integer | not null default nextval ('intersection_intersection_seq'::regclass)
x_axis | integer | not null
y_axis | integer | not null
Indexes:
"intersection_pkey" PRIMARY KEY, btree (intersection)
"intersection_x_axis_key" UNIQUE, btree (x_axis, y_axis)
weighed_directed_edge表格:
weighed_directed_edge table:
Table "public.weighed_directed_edge"
Column | Type | Modifiers
------------+------------------+-----------
startpoint | integer |
endpoint | integer |
costs | double precision |
Indexes:
"weighed_directed_edge_startpoint_key" UNIQUE, btree (startpoint, endpoint)
Foreign-key constraints:
"weighed_directed_edge_endpoint_fkey" FOREIGN KEY (endpoint) REFERENCES cross_point(intersection)
"weighed_directed_edge_startpoint_fkey" FOREIGN KEY (startpoint) REFERENCES cross_point(intersection)
一个随机数的端点(从1001点数列中随机获取)对应于每个起点(依次从点数列中获取). 端点数在[1,7]中.
A random number of endpoint (get from the 1001 points column randomly) corresponds to every startpoint (get from the points column sequentially),. The number of endpoint is in [1,7].
要求:
-
起点和终点的组合应该唯一.
The combination of startpoint and endpoint should be unique.
起点集合包含表cross_point的所有1001个交点
Startpoints set contains all 1001 intersection from table cross_point
端点全部来自相同的交点集.
Endpoints comes all from the same intersection point set.
每个星点最多有7个端点匹配，(最大7路交叉点)，并且 端点匹配的数量是随机选择的.
Every starpoint there're at most of 7 endpoint matches, (7 way road intersection is the maximum), and the number of endpoint matching is selected randomly.
我跳过了以下代码中的费用计算:
I skipped the calculation of costs in the following code:
--The largest number of intersection chosen is 7, but this could be altered
create or replace function popluate_weighed_directed_edge() returns void as $$
declare
from_point integer;
to_point integer;
directed2number integer; --the number of node this startpoint leads to
counter integer;
factor float;
weight numeric;
start_pointer record;
begin
for start_pointer in select * from cross_point
loop
from_point := start_pointer.intersection;
directed2number := trunc(Random()*7+1);
counter := directed2number;
while counter > 0
loop
insert into weighed_directed_edge (startpoint) select from_point from generate_series(1,10) ;
update weighed_directed_edge set endpoint= trunc(1000 * random()+ 1) from generate_series(1,10) group by 1 where startpoint= from_point ;
update weighed_directed_edge set costs= trunc(1000 * random()+ 1) from generate_series(1,10) group by 1 where startpoint= from_point ;
counter := counter - 1;
end loop;
end loop;
end
$$ language plpgsql;
推荐答案
在循环中，您可以绘制一个随机数，并在不存在的情况下仅插入随机数(并使循环计数器递减). 伪代码:
Within the loop you could draw a random number and only insert it (and decrement the loopcounter) if it does not exist. Pseudocode:
while (counter > 6)
loop:
this = 1+ random() *1000
insert into weighed_directed_edge (startpoint, endpoint, costs)
VALUES ( :frompoint, :this, xxx* random() )
WHERE NOT EXISTS (
SELECT(*) FROM weighed_directed_edge nx
WHERE nx.startpoint = :frompoint
AND nx.endpoint = :this
);
if (rowcount > 0) counter -= 1;
end loop;
这篇关于要使用“分组依据"一次更新几个未重复的行，请执行以下操作:的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
