人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

MySQLで質問です。
ipを保持したアクセスログをとっているのですが。

tableAccess
target int(14)
ip VARCHAR(15)

上記のように、ipと対象となる物をtargetとして情報を持っています。
このtargetをgroupした際に、アクセスログの個数でランキングを取得したいのですが、どのようにしたら良いでしょうか?

単純な数字によるランキング取得は下記のような感じだと思うのですが
http://oshiete.goo.ne.jp/qa/5437094.html
個数をgroupした物を対象にしたランキング付けは見つからないため、お教えいただきたく思います。

●質問者: makocan
●カテゴリ:ウェブ制作
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● POGPI
●0ポイント

「SELECT COUNT(target) AS cnt,ip FROM tableAccess GROUP BY ip ORDER BY cnt DESC」で、どうですか。


makocanさんのコメント
すみません。少し分かりづらかったかもしれません。 ランキング情報が欲しいのではなく、順位の数字がほしい状態です。 要するに、1位?10位までのIP情報がほしいのではなく、3位とかの順位を取得したいのですね。一番近い動作は、質問に書いてあるURLだと思います。

POGPIさんのコメント
個数が多い順に出るので、whileループとかで、+1していけば順位の数字は取れると思います。

makocanさんのコメント
10000件以上が対象で表示が10件なため、出来ればSQLで完結できればと思います。

POGPIさんのコメント
10位までなら、「 LIMIT 10」を後ろに付ければいいですね。11位から20位は、さらに「 OFFSET 10」とか付ければ取れます。

POGPIさんのコメント
「順位」というデータはデータベースにはないので、SQLで取ろうとするのがそもそもおかしい気もします。

makocanさんのコメント
質問のサンプルにあるのと、a-kuma3さんが仰るようにサブクエリによる、順位自体の算出は分かるのですが、その対象がgroupしたものという対応が分かりませんでした。

2 ● a-kuma3
●100ポイント

こんな感じで、どうでしょうか。
ユーザ定義変数を使ってみました。

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


3 ● tezcello
●100ポイント

> 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
関連質問

●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ