Hatena::Grouprubyist

Rubyを勉強しようと思ってはじめた日記

2008-05-30

SQLで集計してランキングを出す(0.54)

| 00:55 | SQLで集計してランキングを出す(0.54) - Rubyを勉強しようと思ってはじめた日記 を含むブックマーク はてなブックマーク - SQLで集計してランキングを出す(0.54) - Rubyを勉強しようと思ってはじめた日記 SQLで集計してランキングを出す(0.54) - Rubyを勉強しようと思ってはじめた日記 のブックマークコメント

アクセスログを読み込んで、DBMySQL)にデータを保存していくsetup.rbはこんな感じ。これをcronで定期的に実行すればログファイルは、DBに入ります。

setup.rb

require 'rubygems'
require 'active_record'
require 'date'
require 'time'

ActiveRecord::Base.establish_connection(
   :adapter  => 'mysql',
   :host     => 'localhost',
   :username => 'username',
   :password => 'mysql',
   :database => 'access_log'
)

class Entrys < ActiveRecord::Base
    set_table_name "entry"
end

montn_array = { 
    'Jan' => 1,
    'Feb' => 2,
    'Mar' => 3,
    'Apr' => 4,
    'May' => 5,
    'Jun' => 6,
    'Jul' => 7,
    'Aug' => 8,
    'Sep' => 9,
    'Oct' => 10, 
    'Nov' => 11, 
    'Dec' => 12
}

logfile = '../access_log'
logs    = open(logfile,'r')
while text = logs.gets do
    /^([^ ]*) ([^ ]*) ([^ ]*) \[([^]]*)\] "([^ ]*)(?: *([^ ]*) *([^ ]*))?" ([^ ]*) ([^ ]*) "(.*?)" "(.*?)"/u =~ text
    url = $6

    begin

        entry = Entrys.new
        entry.url = url
        entry.ref = $10
        entry.ua  = $11
        /^(.*)\/(.*)\/(.*):(.*):(.*):(.*) \+0900/ =~ $4
        at_time = Time.local($3, montn_array[$2], $1, $4, $5, $6) 
        entry.access_date = at_time

        if url =~ /(php$|html$|^\/$)/
            entry.save
        end
    rescue => ex
        print ex,' : ',text
    end
end
logs.close

CsillaCsilla2012/10/16 03:17It's great to find smoeone so on the ball

yyrfefyyrfef2012/10/16 21:25QDzUBW <a href="http://qdtjqimtggdv.com/">qdtjqimtggdv</a>

vietomxbxmvietomxbxm2012/10/19 13:41YnimI4 , [url=http://sqyibqypbirq.com/]sqyibqypbirq[/url], [link=http://amuhalrutzsq.com/]amuhalrutzsq[/link], http://cdwfkcsrwnts.com/

zkvnjmfzkvnjmf2012/10/20 01:13rdsFmS <a href="http://nhrfmgpcggmr.com/">nhrfmgpcggmr</a>

ozxjazmqdvozxjazmqdv2012/10/20 11:24IdFqLz , [url=http://uxhchildmspq.com/]uxhchildmspq[/url], [link=http://xmevsiqztvcq.com/]xmevsiqztvcq[/link], http://jmvryykukpkf.com/

2008-05-21

SQLで集計してランキングを出す(0.53)日付の範囲を指定する

| 01:48 | SQLで集計してランキングを出す(0.53)日付の範囲を指定する - Rubyを勉強しようと思ってはじめた日記 を含むブックマーク はてなブックマーク - SQLで集計してランキングを出す(0.53)日付の範囲を指定する - Rubyを勉強しようと思ってはじめた日記 SQLで集計してランキングを出す(0.53)日付の範囲を指定する - Rubyを勉強しようと思ってはじめた日記 のブックマークコメント

日付指定のSQLってこう難しい。

select url,access_date,count(*) as count from entry GROUP BY url HAVING access_date BETWEEN '2008-05-08 00:00:00' AND '2008-05-08 23:59:59' ORDER BY count(*) DESC limit 0,10; 

だと5月8日のだけ取得できました。

select url,access_date,count(*) as count from entry GROUP BY url HAVING access_date < '2008-05-08' ORDER BY count(*) DESC limit 0,10;

だと2008-05-08というは2008-05-08 00:00:00と同じ意味のようです。


さて、これで日付指定が出来るようになりました(なんとか)。これをまとめていけば出来そうですね。ようやく見えてきたよ...。

2008-05-20

SQLで集計してランキングを出す(0.52)PHPとHTMLのアクセスのみを登録する

| 03:25 | SQLで集計してランキングを出す(0.52)PHPとHTMLのアクセスのみを登録する - Rubyを勉強しようと思ってはじめた日記 を含むブックマーク はてなブックマーク - SQLで集計してランキングを出す(0.52)PHPとHTMLのアクセスのみを登録する - Rubyを勉強しようと思ってはじめた日記 SQLで集計してランキングを出す(0.52)PHPとHTMLのアクセスのみを登録する - Rubyを勉強しようと思ってはじめた日記 のブックマークコメント

指定したテーブルを空にする

delete from テーブル名

例外処理の中でのif文

なんだかエラーになるような?これはいったい?でも、対応方法は判明

setup.rb

一部

while text = logs.gets do
    /^([^ ]*) ([^ ]*) ([^ ]*) \[([^]]*)\] "([^ ]*)(?: *([^ ]*) *([^ ]*))?" ([^ ]*) ([^ ]*) "(.*?)" "(.*?)"/u =~ text
    url = $6

    begin
        entry = Entrys.new
        entry.url = url
        entry.ref = $10
        entry.ua  = $11
        /^(.*)\/(.*)\/(.*):(.*):(.*):(.*) \+0900/ =~ $4
        at_time = Time.local($3, montn_array[$2], $1, $4, $5, $6)
        entry.access_date = at_time

        if url =~ /(php$|html$)/
            entry.save
        end
    rescue => ex
        print ex,' : ',text
    end
end

2008-05-11

課題だけメモ

| 09:15 | 課題だけメモ - Rubyを勉強しようと思ってはじめた日記 を含むブックマーク はてなブックマーク - 課題だけメモ - Rubyを勉強しようと思ってはじめた日記 課題だけメモ - Rubyを勉強しようと思ってはじめた日記 のブックマークコメント

うまく時間が取れていないので、課題だけメモをしておきます

アクセス解析について当面の課題

  • phphtmlのアクセスのみを登録する
  • 日付でアクセスした日を集計する

今後の課題

2008-05-08

実際のログを保存してみた

| 19:49 | 実際のログを保存してみた - Rubyを勉強しようと思ってはじめた日記 を含むブックマーク はてなブックマーク - 実際のログを保存してみた - Rubyを勉強しようと思ってはじめた日記 実際のログを保存してみた - Rubyを勉強しようと思ってはじめた日記 のブックマークコメント

15時間ぐらいのログで46000カラムほどありました。1日で5万カラムだとして....破たんする。

画像と、CSSへのアクセスは使わない方がよさそう。

DBへの登録時に、htmlphpだけを登録したほうがいいのかも。あとxmlrssatomかな

ActiveRecordで「/tmp/mysql.sock」というエラー

| 19:47 | ActiveRecordで「/tmp/mysql.sock」というエラー - Rubyを勉強しようと思ってはじめた日記 を含むブックマーク はてなブックマーク - ActiveRecordで「/tmp/mysql.sock」というエラー - Rubyを勉強しようと思ってはじめた日記 ActiveRecordで「/tmp/mysql.sock」というエラー - Rubyを勉強しようと思ってはじめた日記 のブックマークコメント

ActiveRecordを使っていてエラーが出ました。

No such file or directory - /tmp/mysql.sock (Errno::ENOENT)

このmysql.sock

対策

下記のような感じで書く

ActiveRecord::Base.establish_connection(
 :adapter  => "mysql",
 :username => "user",
 :password => "password",
 :database => "hoge",
 :socket   => "/var/run/mysqld/mysqld.sock"
)

参考というかそのまま

2008-05-07

SQLで集計してランキングを出す(0.51)ActiveRecordでSQLを実行する

| 00:03 | SQLで集計してランキングを出す(0.51)ActiveRecordでSQLを実行する - Rubyを勉強しようと思ってはじめた日記 を含むブックマーク はてなブックマーク - SQLで集計してランキングを出す(0.51)ActiveRecordでSQLを実行する - Rubyを勉強しようと思ってはじめた日記 SQLで集計してランキングを出す(0.51)ActiveRecordでSQLを実行する - Rubyを勉強しようと思ってはじめた日記 のブックマークコメント

あっさり出来た!と思っていました。でも、合計数を表示することが出来ません。SQLを直接実行すると取得は出来ています。MySQLcount関数を良く見てみると、asというのが気になります。どうやら合計した数をasで指定したカラム名で表示できるようです!というわけで、改めてやってみました。

sqlstring = 'select url,count(*) as count from entry GROUP BY url HAVING url REGEXP \'\.php|^/$|\.html\' ORDER BY count(*) DESC limit 0,10;'
entrys    = Entrys.find_by_sql(sqlstring)

なんだかやっていることが、DBについて時間を費やしている気がします...。

2008-05-06

SQLで集計してランキングを出す(0.5)ActiveRecordでSQLを実行する

| 22:48 | SQLで集計してランキングを出す(0.5)ActiveRecordでSQLを実行する - Rubyを勉強しようと思ってはじめた日記 を含むブックマーク はてなブックマーク - SQLで集計してランキングを出す(0.5)ActiveRecordでSQLを実行する - Rubyを勉強しようと思ってはじめた日記 SQLで集計してランキングを出す(0.5)ActiveRecordでSQLを実行する - Rubyを勉強しようと思ってはじめた日記 のブックマークコメント

ActiveRecordSQLを実行する方法を調べていたら、ActiveRecord::Baseが「find_by_sql」というメソッドがあることがわかりました。これを使ってみると、問題なく値が取得できました。なんというあさっさり。

実行したコードの一部

sqlstring = 'select url,count(*) from entry GROUP BY url HAVING url REGEXP \'\.php|^/$|\.html\' ORDER BY count(*) DESC limit 0,10;'
entrys    = Entrys.find_by_sql(sqlstring)

entrys.each{|entry|
    print entry.url,'<br>'
}

参考

2008-05-03

課題めも

| 17:58 | 課題めも - Rubyを勉強しようと思ってはじめた日記 を含むブックマーク はてなブックマーク - 課題めも - Rubyを勉強しようと思ってはじめた日記 課題めも - Rubyを勉強しようと思ってはじめた日記 のブックマークコメント

進捗

  • ランキングは取れるようになった

課題

  • SQLの実行をRubyActiveRecord)を通して実行したい
    • 実行した結果はどうなるのか?配列に入るの?
  • 実行結果をブラウザーで見られるようにする

SQLで集計してランキングを出す(0.4)TOP句?

| 11:05 | SQLで集計してランキングを出す(0.4)TOP句? - Rubyを勉強しようと思ってはじめた日記 を含むブックマーク はてなブックマーク - SQLで集計してランキングを出す(0.4)TOP句? - Rubyを勉強しようと思ってはじめた日記 SQLで集計してランキングを出す(0.4)TOP句? - Rubyを勉強しようと思ってはじめた日記 のブックマークコメント

TOP句という便利そうなのがあるらしいです。それはそれで調べてみようと思います。いろいろな機能があるんですね、MySQLって(他のDBも同じだとは思うのですが...)。ちなみに環境をまとめていなかったのでまとめ。

環境

集計をして並び替えてphphtmlだけ抽出する

select url,count(*) from entry GROUP BY url HAVING url REGEXP '\.php|^/$|\.html' ORDER BY count(*) DESC limit 0,10;

出来た!こうなってくると面白いのでいろいろ

ユーザーエージェント

アクセスログだったので、UAを見てみました。IE6が多いですね。あと、百度も案外多い。

+---------------------------------------------------------------------------------------------------------------------------------------+
| ua                                                                                                                                    |
+---------------------------------------------------------------------------------------------------------------------------------------+
| Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)                                                            | 
| Mozilla/5.0 (Windows; U; Windows NT 5.1; ja; rv:1.8.1.13) Gecko/20080311 Firefox/2.0.0.13                                             | 
| Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)                                                                               | 
| Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506)                | 
| Mozilla/5.0 (compatible; Yahoo! Slurp; http://help.yahoo.com/help/us/ysearch/slurp)                                                   | 
| Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)                                                              | 
| Mozilla/5.0                                                                                                                           | 
| Baiduspider+(+http://www.baidu.com/search/spider_jp.html)                                                                             | 
| Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)                                        | 
| NetTracker/7.5 Enterprise                                                                                                             | 
| Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; .NET CLR 3.0.04506)                                     | 
| msnbot/1.1 (+http://search.msn.com/msnbot.htm)                                                                                        | 
| Mozilla/5.0 (Windows; U; Windows NT 6.0; ja; rv:1.8.1.13) Gecko/20080311 Firefox/2.0.0.13                                             | 
| Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322)                                                                 | 
| Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506) Sleipnir/2.6.2 | 
| Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.0.3705; .NET CLR 1.1.4322)                                         | 
| Mozilla/5.0 (compatible; heritrix/1.12.0 +http://seekda.com)                                                                          | 
| Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)                                                                                    | 
| Mozilla/5.0 (Macintosh; U; PPC Mac OS X 10_4_11; ja-jp) AppleWebKit/525.13 (KHTML, like Gecko) Version/3.1 Safari/525.13              | 
| Mediapartners-Google                                                                                                                  | 
+---------------------------------------------------------------------------------------------------------------------------------------+

2008-05-01

SQLで集計してランキングを出す(0.3)並び替えて10個だけ

| 07:38 | SQLで集計してランキングを出す(0.3)並び替えて10個だけ - Rubyを勉強しようと思ってはじめた日記 を含むブックマーク はてなブックマーク - SQLで集計してランキングを出す(0.3)並び替えて10個だけ - Rubyを勉強しようと思ってはじめた日記 SQLで集計してランキングを出す(0.3)並び替えて10個だけ - Rubyを勉強しようと思ってはじめた日記 のブックマークコメント

ちょっとドタバタしていたので、中途半端。

並び替えて10個だけ

並び替えて、10個だけ

select url,count(*) from entry GROUP BY url ORDER BY count(*) DESC limit 0,10;
カレンダー
2007 | 12 |
2008 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 09 | 10 | 11 | 12 |
2009 | 06 | 08 |