PostgreSQL 如何创建一组表示值矩阵的条目？

发布于 2024-10-26 00:48:47 字数 542 浏览 2 评论 0原文

拓扑基域还有x、y、z、gradient、tmestamp ,用户 现在我面临着用数据填充我的基地的问题。我有几个具有相同特征但位置不同的补丁。我通过准备一条插入语句和两个嵌套循环来解决这个问题，这些循环改变了x，y（z在填充策略时被修复（至少目前，

编程方法很慢，非常慢，所以我尝试用 SQL 来玩它，我已经做到了：

insert into topo12 ("x","y","z","gradient","user","refresh")
select generate_series(2,4),generate_series(2,4),1024,12222563,'toto',1234567878;

但这并没有给出（逻辑上我知道但是......）一个矩阵，而是一个向量。到目前为止，我正在努力让进一步的子请求发挥作用，因此任何提示都值得赞赏。

原文

Still a topological base fields are x, y, z, gradient, tmestamp, user
now I am at the problem of populating my base with data. I have several patches that have the same characteristic, but a different position.
I solved it with preparing an insert statement and two nested loops varying the x, y (z being fixed when filling up a strate (at least at the moment).

Programmatical approach is slow, very slow so I tried to play it in SQL and I got that far:

insert into topo12 ("x","y","z","gradient","user","refresh")
select generate_series(2,4),generate_series(2,4),1024,12222563,'toto',1234567878;

But that doesn't give (logical I know but...) a matrix, but a vector
and so far I am struggling with getting further sub-requests working, so any hint appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小姐丶请自重 2024-11-02 00:48:47

我认为你想要的是一个连接，这样你就可以生成两个系列的笛卡尔积：

> SELECT x, y FROM generate_series(1,2) AS x, generate_series(1,2) AS y;
 x | y 
---+---
 1 | 1
 1 | 2
 2 | 1
 2 | 2
(4 rows)

你当前正在同时从两个集合返回函数中进行选择。我不明白它的语义应该是什么，但它不会产生您可能想要的值的笛卡尔积：

> SELECT generate_series(1,2), generate_series(1,2);
 generate_series | generate_series 
-----------------+-----------------
               1 |               1
               2 |               2
(2 rows)

> SELECT generate_series(1,2), generate_series(1,3);
 generate_series | generate_series 
-----------------+-----------------
               1 |               1
               2 |               2
               1 |               3
               2 |               1
               1 |               2
               2 |               3
(6 rows)

I think what you want is a join, so you can produce a Cartesian product of two series:

> SELECT x, y FROM generate_series(1,2) AS x, generate_series(1,2) AS y;
 x | y 
---+---
 1 | 1
 1 | 2
 2 | 1
 2 | 2
(4 rows)

You're currently selecting from two set-returning functions at once. I don't understand what the semantics of that are supposed to be, but it's not producing a Cartesian product of values like you probably want:

> SELECT generate_series(1,2), generate_series(1,2);
 generate_series | generate_series 
-----------------+-----------------
               1 |               1
               2 |               2
(2 rows)

> SELECT generate_series(1,2), generate_series(1,3);
 generate_series | generate_series 
-----------------+-----------------
               1 |               1
               2 |               2
               1 |               3
               2 |               1
               1 |               2
               2 |               3
(6 rows)

回复收藏 0 原文

如何视而不见 2024-11-02 00:48:47

insert into topo12 ("x","y","z","gradient","user","refresh")
select
    (random() * 2)::int + 2,
    (random() * 2)::int + 2
    ,1024,12222563,'toto',1234567878
from generate_series(1, 10)

insert into topo12 ("x","y","z","gradient","user","refresh")
select
    (random() * 2)::int + 2,
    (random() * 2)::int + 2
    ,1024,12222563,'toto',1234567878
from generate_series(1, 10)

回复收藏 0 原文

~没有更多了~