[pgsql-jp: 37427] Re: 一部のデータを SELECT した際に全体の順位取得

[whi te]

こんにちは。
ずいぶん遅くまで起きていらっしゃいますね
私は(-.-)Ｚｚzzz．．．．

石田さんが示して下さった構文を元に実環境のデータに
当てはめてみた所問題なく順位を出すことが出来ました。
ご協力頂いた方、本当にありがとうございました。



-----------sampleデータ------------

server=# SELECT * from test ;
 name | points
------+--------
 aa   |     60
 bb   |     80
 cc   |     70
 dd   |     40
 ee   |     20
 aa   |     10
 bb   |     30
 cc   |      5
 aa   |      5
(9 rows)

SELECT r1.name,r1.points_sum, count(r2.*)+1 as rank
FROM
(SELECT name, sum(points) AS points_sum
  FROM test
 GROUP BY name ) AS r1 
LEFT JOIN
(SELECT name, sum(points) AS points_sum
  FROM test
 GROUP BY name ) AS r2
ON r1.points_sum < r2.points_sum
GROUP BY r1.name, r1.points_sum
ORDER BY rank;
 name | points_sum | rank
------+------------+------
 bb   |        110 |    1
 cc   |         75 |    2
 aa   |         75 |    2
 dd   |         40 |    4
 ee   |         20 |    5
(5 rows)





> こんにちは。石田@苫小牧市です。
> 
> 06/08/17 に whi te<white_wwjd ＠ yahoo.co.jp> さんは書き
ました:
> > ご返信ありがとうございます。
> >
> > > 順位 ＝（自分より大きい points の人の数＋１）と考
える
> > と、
> >
> > なるほど。
> > 確かに順位ってそういう事ですね。
> > 順位の意味(!?)を考えてみた事なかったですが、
> > そういう風に考え直すと、SQL文が作成出来ますね。
> > 勉強になりました。
> >
> > ただ、実際のデータってランキングテーブルに
> > 同一人物のデータが複数存在します。
> > ですので、例示して頂いたSQLを基に以下の様なSQL文を作
成し
> > てみたのですが、エラーが出てしまいます。
> >
> > COUNT/SUMしたデータを条件文に含む事は出来ないのでし
ょう
> > か。
> > ON句のSUMを試しに抜かしてみると、結果は違いますが、
> > SQLはとおります。
> >
> > SELECT r1.name,r1.points,SUM(r2.points)+1 as rank
> > FROM ランキング r1 LEFT JOIN ランキング r2 ON
> > SUM(r1.points) < SUM(r2.points)
> > GROUP BY r1.name,r1.points,r2.points
> > ORDER BY rank DESC
> >
> > ERROR:  aggregates not allowed in JOIN conditions
> 
> エラーの直接の原因はエラーメッセージの通り、JOINの条件
式には
> 集約関数を使うことができないということです。
> 
> しかし、藤田さんのやりたいことは「合計してから順位を求
める」
> ということだと思います。FROM句はSUMなどよりも先に評価
> されるので、サブクエリに入れる必要があります。
> 試してませんが、多分こんな感じだと思います。
> 
> SELECT r1.name,r1.points_sum, count(r2.*)+1 as rank
> FROM
> (SELECT name, sum(points) AS points_sum
>   FROM ランキング
>  GROUP BY name ) AS r1
> LEFT JOIN
> (SELECT name, sum(points) AS points_sum
>   FROM ランキング
>  GROUP BY name ) AS r2
> ON r1.points < r2.points
> GROUP BY r1.name,r1.points
> ORDER BY rank
> 
> 
> -- 
> ISHIDA Akio <iakio ＠ mono-space.net/ishida ＠ cycleof5th.com>