通过自左连接传递聚合函数列
我想在列上调用一次聚合函数（成本），并通过自左连接级联它（用于创建行的排列或组合）。这样我算出在连接 O(row^joins) 之后调用它的成本是 O(n) 。从这样的事情开始：
id | cost
----------
1 | 5
2 | 10
我想做一些类似于下面的事情。我可以做类似的事情，选择一个临时表并连接它，但是我想避免使用临时表...
CREATE TEMP TABLE tmp_750309_plans AS (SELECT *, cost(id) as cost FROM plans WHERE plans.id IN (1,2,...));
SELECT * FROM tmp_750309_plans AS t1 LEFT JOIN tmp_750309_plans AS t2 ON ...
我更愿意做类似的事情：
SELECT id, cost(id) as cost FROM plans AS t1
LEFT JOIN t1 AS t2
ON t1.id != t2.id
AND ...
要得到这样的东西：
id | cost | id | cost |
-----------------------
1 | 5 |NULL| NULL |
2 | 10 |3 | 15 |
任何帮助将不胜感激。
I would like to call a aggregate function (cost) once on a column and cascade it through the self left joins (used to create a permutation or combination of rows). That way I figure the cost is O(n) apposed to calling it after the joins O(row^joins). Starting with something like this:
id | cost
----------
1 | 5
2 | 10
I would like to do something similar to below. I can do something similar with a select into a temporary table and joining off that but I would like to avoid using a temporary table...
CREATE TEMP TABLE tmp_750309_plans AS (SELECT *, cost(id) as cost FROM plans WHERE plans.id IN (1,2,...));
SELECT * FROM tmp_750309_plans AS t1 LEFT JOIN tmp_750309_plans AS t2 ON ...
I would prefer to do something like:
SELECT id, cost(id) as cost FROM plans AS t1
LEFT JOIN t1 AS t2
ON t1.id != t2.id
AND ...
To get something like this:
id | cost | id | cost |
-----------------------
1 | 5 |NULL| NULL |
2 | 10 |3 | 15 |
Any help would be greatly appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
无需创建临时表，只需使您的 SELECT 成为派生表：
或使用公共表表达式的替代解决方案（适用于 PostgreSQL 8.4 及更高版本）
No need to create the temporary table, just make your SELECT a derived table:
or an alternative solution with a common table expression (for PostgreSQL 8.4 and above)
听起来您需要使用窗口函数请参阅这个示例。
例子：
Sounds like you need to make use of window functions see this example.
Example: