ipを保持したアクセスログをとっているのですが。
tableAccess
target int(14)
ip VARCHAR(15)
上記のように、ipと対象となる物をtargetとして情報を持っています。
このtargetをgroupした際に、アクセスログの個数でランキングを取得したいのですが、どのようにしたら良いでしょうか?
単純な数字によるランキング取得は下記のような感じだと思うのですが
http://oshiete.goo.ne.jp/qa/5437094.html
個数をgroupした物を対象にしたランキング付けは見つからないため、お教えいただきたく思います。
「SELECT COUNT(target) AS cnt,ip FROM tableAccess GROUP BY ip ORDER BY cnt DESC」で、どうですか。
こんな感じで、どうでしょうか。
ユーザ定義変数を使ってみました。
set @rank = 0; select @rank:=@rank+1 as rank, x.* from ( select target, count(target) as n from tableAccess group by target order by n desc ) as x limit 10 ;
SQL Fiddle というところで、試してみたのがこちら。
http://sqlfiddle.com/#!2/c32fa3/2
> targetをgroupした際に、アクセスログの個数でランキングを取得したい
「アクセスログ」というのは「ip」の事ですよね?
つまり
SELECT target, COUNT(ip) AS cnt FROM tableAccess GROUP BY target
だろうと思います。
これを仮想的なテーブルと見立てて、他の回答者さんと同じような事をすればいいのでしょう。
手元の SQLite では以下で上手くいくみたいです。MySQL だとまた違った方法もあるでしょう。
SELECT T1.target, MAX(T1.cnt) AS cnt, COUNT(T2.target) + 1 AS rank_1 FROM (SELECT target, COUNT(ip) AS cnt FROM tableAccess GROUP BY target) T1 LEFT OUTER JOIN (SELECT target, COUNT(ip) AS cnt FROM tableAccess GROUP BY target) T2 ON T1.cnt < T2.cnt GROUP BY T1.target ORDER By rank_1 ;
!!!! 追加
ちょっと時間が取れたので MySQL で検証してみました。
質問者さんからのレスが無いので、求めた結果が希望のモノか不明ですが...
先に書いたものは MySQL でも機能しているようです
他にも方法があるようなので追記します。
SELECT T1.target, T1.cnt, (SELECT COUNT(T2.cnt) FROM ( SELECT target, COUNT(target) AS cnt FROM tableAccess GROUP BY target ) T2 WHERE T2.cnt > T1.cnt ) + 1 AS rank_1 FROM ( SELECT target, COUNT(target) AS cnt FROM tableAccess GROUP BY target ) T1 ORDER BY rank_1 ASC;
または
SELECT T1.target, MAX(T1.cnt) AS cnt, COUNT(T2.cnt) AS rank_1 FROM ( SELECT target, COUNT(target) AS cnt FROM tableAccess GROUP BY target ) T1 INNER JOIN ( SELECT target, COUNT(target) AS cnt FROM tableAccess GROUP BY target ) T2 ON T1.cnt <= T2.cnt GROUP BY T1.target ORDER BY rank_1
「順位」というデータはデータベースにはないので、SQLで取ろうとするのがそもそもおかしい気もします。
2014/07/28 18:05:37質問のサンプルにあるのと、a-kuma3さんが仰るようにサブクエリによる、順位自体の算出は分かるのですが、その対象がgroupしたものという対応が分かりませんでした。
2014/07/29 23:20:50