使用SQL窗口函数执行加入

发布于 2025-02-08 14:47:52 字数 1219 浏览 2 评论 0原文

我正在尝试在红移DB上消除2列值的所有常见实例。我已经编写了2个选择的语句，这些语句都独立起作用，但是在尝试加入它们时也有错误，甚至只是在另一个之后运行1时都会出现错误。

这是

select * 

from
(SELECT distinct management_firm_id ,
                LISTAGG(city,',')
                WITHIN GROUP (Order by city)
                OVER (PARTITION BY ci.management_firm_id) as FlatCity
)                AS ci

JOIN

(SELECT distinct management_firm_id , 
                 LISTAGG(sub_market,',')
                 WITHIN GROUP (Order by sub_market)
                 OVER (PARTITION BY sm.management_firm_id) AS flatSubMarket
                 from (SELECT distinct management_firm_id,sub_market from tableau_prep.dom_complete_manager_info) AS sm)

on ci.management_firm_id = sm.management_firm_id

我收到的消息下面的消息：

执行SQL命令时发生错误：

select * 

from
(SELECT distinct management_firm_id ,
                LISTAGG(city,',')
                WITHIN GROUP (Order by city)
                O...

ERROR: syntax error at or near "from"
  Position: 222

)               from (SELECT distinct management_firm_id,city from tableau_prep.dom_complete_manager_info) AS ci
            ^

1语句失败。

我想获得3列：1 Fund_manager_id 2二级资产3子市场，

任何想法都将不胜感激

原文

I'm trying to eliminate all common instances of 2 column values, on a Redshift db.
I've got as far as writing 2 SELECT statements which both work independently, but have errors when trying to join them, or even simply when running the 1 after the other.

Here they are

select * 

from
(SELECT distinct management_firm_id ,
                LISTAGG(city,',')
                WITHIN GROUP (Order by city)
                OVER (PARTITION BY ci.management_firm_id) as FlatCity
)                AS ci

JOIN

(SELECT distinct management_firm_id , 
                 LISTAGG(sub_market,',')
                 WITHIN GROUP (Order by sub_market)
                 OVER (PARTITION BY sm.management_firm_id) AS flatSubMarket
                 from (SELECT distinct management_firm_id,sub_market from tableau_prep.dom_complete_manager_info) AS sm)

on ci.management_firm_id = sm.management_firm_id

the message I'm getting is below:

An error occurred when executing the SQL command:

select * 

from
(SELECT distinct management_firm_id ,
                LISTAGG(city,',')
                WITHIN GROUP (Order by city)
                O...

ERROR: syntax error at or near "from"
  Position: 222

)               from (SELECT distinct management_firm_id,city from tableau_prep.dom_complete_manager_info) AS ci
            ^

1 statement failed.

I would like to get 3 columns: 1 fund_manager_id 2 secondary asset 3 sub-market

Any thoughts would be appreciated

分享到QQ

分享到微博

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

发布评论

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

花伊自在美 2025-02-15 14:47:52

因此，看来您的第一个子查询中没有子句。

... (SELECT distinct management_firm_id , LISTAGG(city,',') WITHIN GROUP (Order by city) OVER (PARTITION BY ci.management_firm_id) as FlatCity ) AS ci

这可能是问题所在。

另外，您正在使用ListAgg（）窗口函数，看起来您需要ListAgg（）聚合函数。是的，有2个Listagg（）函数。您不需要独特或结束，但会结束。这将使您的代码更清晰并更快地运行。请参阅： https://docs.aws.aws.aws.aws.aws.amazon.com/redshift/最新/dg/r_listagg.html

So it looks like you don't have a FROM clause in your first subquery.

... (SELECT distinct management_firm_id , LISTAGG(city,',') WITHIN GROUP (Order by city) OVER (PARTITION BY ci.management_firm_id) as FlatCity ) AS ci

This is likely what the problem.

Also, you are using the LISTAGG() window function and it looks like you want the LISTAGG() aggregate function. Yes there are 2 LISTAGG() functions. You shouldn't need DISTINCT or OVER but will have GROUP BY. This will make your code clearer and run faster. See: https://docs.aws.amazon.com/redshift/latest/dg/r_LISTAGG.html

回复收藏 0 原文

~没有更多了~