[解決済み] Postgresqlのselect句でSQL Serverのようにfrom句にjoinしてサブクエリを実行するには？

質問

postgresqlで以下のようなクエリを作成しようとしています。

select name, author_id, count(1), 
    (select count(1)
    from names as n2
    where n2.id = n1.id
        and t2.author_id = t1.author_id
    )               
from names as n1
group by name, author_id

これはMicrosoft SQL Serverでは確かに動作しますが、postegresqlでは全く動作しません。ドキュメントを少し読んでみたところ、以下のように書き換えることができるようです。

select name, author_id, count(1), total                     
from names as n1, (select count(1) as total
    from names as n2
    where n2.id = n1.id
        and n2.author_id = t1.author_id
    ) as total
group by name, author_id

しかし、これはpostegresql上で次のエラーを返します: "FROMのサブクエリは同じクエリレベルの他のリレーションを参照できません"。というエラーが出てしまい、行き詰まっています。誰かそれを達成する方法を知っていますか？

ありがとうございます。

どのように解決するのですか？

あなたの意図が完璧に理解できているかどうかわかりませんが、おそらく以下のような方法が、あなたの望むものに近いと思われます。

select n1.name, n1.author_id, count_1, total_count
  from (select id, name, author_id, count(1) as count_1
          from names
          group by id, name, author_id) n1
inner join (select id, author_id, count(1) as total_count
              from names
              group by id, author_id) n2
  on (n2.id = n1.id and n2.author_id = n1.author_id)

残念ながら、これは最初のサブクエリを名前とauthor_idだけでなくidでもグループ化するという要件を追加するもので、私はこれを望んでいなかったと思います。 しかし、2番目のサブクエリで結合するためにidを利用できるようにする必要があるので、それを回避する方法はよくわかりません。 おそらく、他の誰かがより良い解決策を思いつくでしょう。

共有して楽しんでください。