[三流君] －－＞ [ＡＳＰで遊ぶ、失敗する] －－＞ [バックナンバー一覧]
－－＞ No.038 SQL GROUP BY句でグループ化する

SQL GROUP BY句でグループ化する

本文(発行内容)

＜SQL GROUP BY句 で グループ化する＞
こんにちは、三流プログラマーのＫｅｎ３です。

今回は、
やはり気になる訪問者、訪問者のログをＭＤＢで管理
の続きで、収集したログ情報をグループ化して集計してみたいと思います。

何かの参考となれば幸いです。

/*
 * １．集計元データの説明
*/
前回の、[No.37 訪問者のログをＭＤＢで管理]
( http://www.ken3.org/backno/backno_asp08.html#37 を参照)
で、
ＩＰアドレスＵＲＬをAccess MDBへ保存しました。

cnt.mdb
テーブル名:log
IP
URL
referrer
WriteTime
と項目りデータを保存しました。

テストで、最近訪問した人のデータ３０件を表示は、
http://www.ken3.org/cgi-bin/test/test037-1.asp
で
訪問者の足跡が表示されます。

/*
 * ２．SQL GROUP BY句 で グループ化する
*/

データを収集したら、今度は、そのデータをまとめたいですよね。
そんな時便利なのが
SQL文の GROUP BY句です。

使い方は簡単で
Select 項目名
 From テーブル名
  GROUP BY グループ化する項目名です。

http://www.ken3.org/cgi-bin/test/test038-1.asp
<%@LANGUAGE=VBScript%>
<html>
<head>
<title>SQL GROUP BY句 で グループ化する</title>
</head>
<body>
<h2>SQL GROUP BY句 で グループ化する</h2>
test038-1.asp<br>
<hr>
<b>cnt.mdb から訪問されたURLをグループ化して表示します。</b><br>
<hr>
<%
  'ADO DB Connection オブジェクトを作成する、英文そのままじゃん
  Set db=Server.CreateObject("ADODB.Connection")

  '.Provider？プロバイダー？通信会社？じゃなくって
  'データアクセスにはJet.OLEDB.4.0を使うことを設定
  db.Provider = "Microsoft.Jet.OLEDB.4.0"

  '次に、接続ＤＢの位置を渡すので、Server.MapPathで変換して渡す
  db.ConnectionString = Server.MapPath("cnt.mdb")

  'やっとデータベースを開ける
  db.open

  'お約束のレコードセットの作成 テーブル名logからURLを指定
  'GROUP BY URL で グループ化する
  Set rs = db.Execute("Select URL From log GROUP BY URL")

  'データの表示をテーブルで行う
  Response.Write "<TABLE Border='1'>"

  '見出しをバカっぽく、そのまま書き込む
  Response.Write "<TR>"
  Response.Write "<TD>No.</TD>"
  Response.Write "<TD>URL</TD>"
  Response.Write "</TR>"

  'ＥＯＦまでループ
  nCNT = 1
  Do While rs.EOF = False   '.EOFがFalseの間
    Response.Write "<TR>"   '内容を表示する
    Response.Write "<TD>" & nCNT & "</TD>"

    Response.Write "<TD><A HREF='" & rs.Fields.Item("URL") & "' Target='_blank'>"
    Response.Write rs.Fields.Item("URL") & "</A></TD>"

    Response.Write "</TR>" & Chr(13) & Chr(10)
    '次のレコードにポインタを移動する
    rs.MoveNext    'これを忘れると悲惨なことに、、、
    'カウンタを増やす
    nCNT = nCNT + 1
  Loop

  Response.Write "</TABLE>"  'テーブルは終わりです

  '開いていたレコードセットを閉じる
  rs.Close

  'データベースも閉じようよ
  db.Close

  'お行儀よくオブジェクトも開放しましょう（通常は自動的に解放されるけど）
  Set db = Nothing

%>
<hr>
終了です。<br>
<A HREF="test037-1.asp">cnt.mdb 最近訪問した人のデータ３０件を表示</A><br>
<br>
<% Call OUT_SRC("GROUP BY") 'ソースの表示関数を呼ぶ %>

</body>
</html>

ポイントは、
~~~~~~~~~~
  'お約束のレコードセットの作成 テーブル名logからURLを指定
  'GROUP BY URL で グループ化する
  Set rs = db.Execute("Select URL From log GROUP BY URL")
のＳＱＬ文です。
Select URL 　　取出すのは項目ＵＲＬ
 From log  　　テーブルはlogテーブル
  GROUP BY URL まとめるのはＵＲＬの値でまとめる
って感じです。

で、グループ化されたレコードセットが作成されるので、
あとは、中身を取出して表示しただけです。
http://www.ken3.org/cgi-bin/test/test038-1.asp
実行すると、私のＨＰ思ったよりページ数多い（笑）
本人も少しびっくり。

No. URL 
1 http://www.ken3.org/ 
2 http://www.ken3.org/24h/add24h.html 
3 http://www.ken3.org/asp/add-asp.html 
4 http://www.ken3.org/asp/iis-help.html 
5 http://www.ken3.org/asp/iis-win2000pro.html 
  ・
　・
　・
190 http://www.ken3.org/vba/iwind.html 
191 http://www.ken3.org/vba/object.html 
192 http://www.ken3.org/vba/select.html 

/*
 * ３．SQL GROUP BY句 で グループ化 Count(項目名)でレコード数を集計する
*/

さてと、ダラダラとＵＲＬが表示されました。
ほしい集計データとして、各ＵＲＬの訪問数を出したいですよね。
そんな時使用するのが、
集計関数のＣＯＵＮＴ関数です。

使い方、これも簡単で、

Select URL , Count(URL) AS XXX　　取出すのは項目ＵＲＬと集計値のXXX
 From log  　　テーブルはlogテーブル
  GROUP BY URL まとめるのはＵＲＬの値でまとめる

http://www.ken3.org/cgi-bin/test/test038-2.asp
<%@LANGUAGE=VBScript%>
<html>
<head>
<title>SQL GROUP BY句 で グループ化 Count関数で集計する</title>
</head>
<body>
<h2>SQL GROUP BY句 で グループ化 Count関数で集計する</h2>
test038-2.asp<br>
<hr>
<b>cnt.mdb から訪問されたURLをグループ化して集計します。</b><br>
<hr>
<%
  'ADO DB Connection オブジェクトを作成する、英文そのままじゃん
  Set db=Server.CreateObject("ADODB.Connection")

  '.Provider？プロバイダー？通信会社？じゃなくって
  'データアクセスにはJet.OLEDB.4.0を使うことを設定
  db.Provider = "Microsoft.Jet.OLEDB.4.0"

  '次に、接続ＤＢの位置を渡すので、Server.MapPathで変換して渡す
  db.ConnectionString = Server.MapPath("cnt.mdb")

  'やっとデータベースを開ける
  db.open

  'お約束のレコードセットの作成 テーブル名URLを指定
  'GROUP BY URL で グループ化する
  Set rs = db.Execute("Select URL, Count(URL) As URLCNT From log GROUP BY URL")

  'データの表示をテーブルで行う
  Response.Write "<TABLE Border='1'>"

  '見出しをバカっぽく、そのまま書き込む
  Response.Write "<TR>"
  Response.Write "<TD>No.</TD>"
  Response.Write "<TD>URL</TD>"
  Response.Write "<TD>訪問者</TD>"
  Response.Write "</TR>"

  'ＥＯＦまでループ
  nCNT = 1
  Do While rs.EOF = False   '.EOFがFalseの間
    Response.Write "<TR>"   '内容を表示する
    Response.Write "<TD>" & nCNT & "</TD>"

    Response.Write "<TD><A HREF='" & rs.Fields.Item("URL") & "' Target='_blank'>"
    Response.Write rs.Fields.Item("URL") & "</A></TD>"

    Response.Write "<TD ALIGN=RIGHT>" & rs.Fields.Item("URLCNT") & "人</TD>"

    Response.Write "</TR>" & Chr(13) & Chr(10)
    '次のレコードにポインタを移動する
    rs.MoveNext    'これを忘れると悲惨なことに、、、
    'カウンタを増やす
    nCNT = nCNT + 1
  Loop

  Response.Write "</TABLE>"  'テーブルは終わりです

  '開いていたレコードセットを閉じる
  rs.Close

  'データベースも閉じようよ
  db.Close

  'お行儀よくオブジェクトも開放しましょう（通常は自動的に解放されるけど）
  Set db = Nothing

%>
<hr>
終了です。<br>
<A HREF="test037-1.asp">cnt.mdb 最近訪問した人のデータ３０件を表示</A><br>
<br>

</body>
</html>

ポイントは、
~~~~~~~~~~
'GROUP BY URL で グループ化する
Set rs = db.Execute("Select URL, Count(URL) As URLCNT From log GROUP BY URL")
ここで、ＵＲＬの数をCount関数で集計しURLCNTと項目を作ってます。

あとは、
    Response.Write "<TD ALIGN=RIGHT>" & rs.Fields.Item("URLCNT") & "人</TD>"
と普通に使用してます。

http://www.ken3.org/cgi-bin/test/test038-2.asp
を実行すると、
No. URL 訪問者 
1 http://www.ken3.org/ 56人 
2 http://www.ken3.org/24h/add24h.html 8人 
3 http://www.ken3.org/asp/add-asp.html 13人 
4 http://www.ken3.org/asp/iis-help.html 24人 
  ・
　・
　・
と表示されます。

/*
 * ４．終わりの挨拶
*/

ＳＱＬ文でいろいろと処理ができます。
いろいろな切り口から、集計してみたいと思います。

素朴な疑問あったら、気軽に、
メール、掲示板に書き込んでくださいね。
掲示板 : http://www.ken3.org/asp/bbs-asp.html

ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。

----------------------------------
関連項目(ただのバックナンバー紹介)
----------------------------------
[No.27 SQL INSERT INTO文でデータの追加]
( http://www.ken3.org/backno/backno_asp06.html#27 を参照)
では、
SQLのINSERT INTO文を使って、データをＤＢに追加。

[No.28 SQL ORDER BYでデータの並べ替え]
( http://www.ken3.org/backno/backno_asp06.html#28 を参照)
では、
SQLのORDER BYを使ってデータの並べ替え、
ＤＥＳＣを追加して降順（逆順）を解説。

[No.37 訪問者のログをＭＤＢで管理]
( http://www.ken3.org/backno/backno_asp08.html#37 を参照)
では、
ＩＰアドレスＵＲＬをAccess MDBへ保存しました。

三流君の主なリンク先

[アクセスランキング] [サイトマップ] [リンク先・相互リンク先など]

ＡＳＰで遊ぶ、失敗する	掲示板	バックナンバー	登録と解除
三流プログラマー業務の愚痴	掲示板	バックナンバー	登録と解除
ＶＢＡで楽しくプログラミング	掲示板	バックナンバー	登録と解除
コンビニのオモテとウラ話	掲示板	バックナンバー	登録と解除
www.ken3.org(サイト内)からを利用して、を

Ken3の日記(weblog) -- [広告・副収入系] [プログラマー業務の愚痴] [VBA系の話題] [ASP系の話題] [コンビニ系ネタ] [その他]

SQL GROUP BY句でグループ化する

本文(発行内容)

フィードバック

評価・感想

三流君の主なリンク先

その他宣伝広告

SQL GROUP BY句 で グループ化する

本文(発行内容)

フィードバック

評価・感想

三流君の主なリンク先

その他 宣伝広告

SQL GROUP BY句でグループ化する

その他宣伝広告