カウント数の抽出

質問

こんばんは。お世話になっております。
現在、PHPにてレンタルサーバ上にあるMySQL（4.0）を用いながらサイトを運営しておりますが、以下のような動作をさせたく考えております。

no　id　　date　　　　　ip
1　　21　2007-09-27　162.18.64.2
2　　10　2007-09-27　101.15.69.6
3　　12　2007-09-27　122.18.64.9
4　　21　2007-09-27　121.16.62.1
5　　10　2007-09-27　172.17.64.2
6　　32　2007-09-27　199.11.65.6

と、イメージ的に上記のようなテーブルがあり、アクセス数をカウントしているのですが、id別にアクセス数の多いもの順（※1）から並べ替えすることは出来るものの、id毎に呼び出した際、それが※1で並び替えた場合、上位から何番目に当たるのかを知るには、どのようなスクリプトになるのでしょうか？

色々と思いつくことはあるものの、どれも上手くいかずに、諸先輩方々からのお知恵を頂戴したく投函させて頂きました。お忙しいとは存じますが、アドバイスいただければ幸いに思います。宜しくお願いいたします。

回答No.3

yambejp

idの個数を数えて、順位をつけるということですね？
ここで必要なデータはidだけです。
ほかのnoやdate,ipはとりあえず考えず、以下のようにしてみます。

create table `access` (`id` int);
insert into `access` values
(1),(1),(2),(2),(2),(2),(3),(4),(4),(4),(5),(5);

これをアクセス順に並べるためには
select `id`,count(*) as count
from `access`
group by `id`
order by count desc;
として
id　count
2　4
4　3
1　2
5　2
3　1
となります。
id=1,5が同じcountなのでランク付けするときには
同じランクにしたいと思います。
4.0ではサブクエリが微妙なのでテンポラリを使います。
まずは全体の要素数を変数@allにとっておきます。
その後要素数をカウントするテンポラリを二つ用意して外部結合します。

select @all:=count(distinct `id`) from `access`;

create temporary table `t1`
select `id`,count(*) as `c1`
from `access`
group by `id`;

create temporary table `t2`
select count(*) as `c2`
from `access`
group by `id`;

select `id`,`c1` as count,@all -count(*) +1 as `rank`
from `t1`
inner join `t2` on `c1`>=`c2`
group by `id`
order by `rank`;

結果は
id　count　rank
2 　4 　1
4 　3 　2
1 　2 　3
5 　2 　3
3 　1 　5

前回も書きましたが
全体の要素数(@all)-自分以下の数値(count(*))+1
が自分のランクになります。

お礼

yambejp様

こんにちは。お休みのところ詳細なご指導を有難う御座います！
さきほど、登録アドレス宛に、回答を頂いたとのメールがあり、拝見させていただいておりますが、只今、内容を試す環境ではないため、取り急ぎお礼を伝えたく書き込ませていただいております。
改めて、補足欄にご報告をかねてご連絡させて頂きたいと思います。
有難う御座いました！

回答No.2

yambejp

いまいち仕様がわかりづらいのですが・・・

>それが※1で並び替えた場合、上位から何番目

の「上位」とはid毎の個人内の上位からなのか、全体での上位なのかで
やり方が全然ちがうと思います。

ランクを取るときに頭から単純に序数をつけていけば楽ですが、
実際には同じランクの要素もでてきますのでそれを同位したい場合、
全体の要素数-自分以下の数値+1が自分のランクになります。
（ちょっとわかりづらいかもしれませんが・・・）

お礼

yambejp様

こんばんは。お世話になっております。
質問に言葉足らずなところがあったようで申し訳ありません。
質問にある、※1とは、yambejp様の仰るように、全体での上位順に呼び出した状態を指しております。
先のmoon-night様が仰るように、PHPにて処理した方が宜しいのでしょうか？PHPにしろSQLにしろ、考えてはいるものの、なかなか思うような流れをつかめずに居る次第で御座います。

引き続き、ご指導いただければ幸いに思います。
宜しくお願い申し上げます。

追伸：yambejp様が仰る「要素」を、今一度詳しく教えては頂けませんでしょうか？現在のselect文はyambejp様ご指摘のように、頭から単純に序数をつけている方法を取っているので・・・。

回答No.1

moon_night

SQLだけでやろうとすると面倒そうなので、
米1までSQLで出してからPHPで回したほうが早いと思います。

お礼

moon_night様へ

はじめまして、こんばんは。
早速のアドバイスを有難う御座います。
質問にある、※1をSQLで抽出した後、PHPにて出力した方がいいとのことですが、どんな流れにしたら良いでしょうか？考え方だけでもご指導いただけたら幸いです。
宜しくお願い申し上げます。