[三流君] −−＞ [ＡＳＰで遊ぶ、失敗する] −−＞ [バックナンバー一覧]
−−＞ No.039 SQL GROUP BY句 で グループ化 集計結果を並べ替えする

SQL GROUP BY句 で グループ化 集計結果を並べ替えする

本文(発行内容)

＜SQL GROUP BY句 で グループ化 集計結果を並べ替えする＞
こんにちは、三流プログラマーのＫｅｎ３です。

前回、グループ化して集計を取りました。
今回は、その集計結果で並べ替えをします。
＊少しハマりました。笑ってください。。。

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


/*
 * １．ＳＱＬがうまく作成できずにハマる（笑）
*/

前回の、[No.38 SQL GROUP BY句 で グループ化する]
( http://www.ken3.org/backno/backno_asp08.html#38 を参照)
で、

cnt.mdb (Access DB)
テーブル名:log
IP
URL
referrer
WriteTime
からＵＲＬでグループ化して、訪問者数をカウントしました。

SQL GROUP BY句 で グループ化 Count(項目名)でレコード数を集計する
http://www.ken3.org/cgi-bin/test/test038-2.asp
無事、ＵＲＬ単位でデータが集計されました。

No. URL 訪問者 
1 http://www.ken3.org 2人 
2 http://www.ken3.org/ 148人 
3 http://www.ken3.org/24h/add24h.html 13人 
4 http://www.ken3.org/asp/add-asp.html 46人 
　・
　・
230 http://www.ken3.org/vba/excel-help.html 32人 
231 http://www.ken3.org/vba/iwind.html 28人 
232 http://www.ken3.org/vba/object.html 96人 

とＵＲＬの順に表示されました。

これを訪問者の多い順に並べ直したいと思い、
並べ替えだろ、そんなのＯＲＤＥＲ　ＢＹでＯＫだから、
  'GROUP BY句 で グループ化 Count関数で集計 ORDER BY句で並べ替え
  strSQL = "Select URL, Count(URL) As URLCNT "
  strSQL = strSQL & "From log "
  strSQL = strSQL & "GROUP BY URL "
  strSQL = strSQL & "ORDER BY URLCNT DESC"
と、
ORDER BY URLCNT DESC
Count(URL) As URLCNTで作成した項目を使いました。

あれ？動作しない、、、なぜだろう、、、
~~~~~~~~~~~~~~~~~~~~~
いろいろとやるが思うように進まなかった。


/*
 * ２．集計関数をそのまま書くとＯＫです。
*/


答えはかんたんでした。
~~~~~~~~~~~~~~~~~~~~~
  strSQL = "Select URL, Count(URL) As URLCNT "
  strSQL = strSQL & "From log "
  strSQL = strSQL & "GROUP BY URL "
  strSQL = strSQL & "ORDER BY Count(URL) DESC"

と、
ORDER BY Count(URL) DESC を指定したら動作しました。
~~~~~~~~~~~~~~~~~~~~~~~~
う〜ん、、まぁいっか。

http://www.ken3.org/cgi-bin/test/test039-1.asp
でテストできます。

<%@LANGUAGE=VBScript%>
<html>
<head>
<title>GROUP BY句 で グループ化 Count関数で集計 ORDER BY句で並べ替え</title>
</head>
<body>
<h2>GROUP BY句 で グループ化 Count関数で集計 ORDER BY句で並べ替え</h2>
test039-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

  'お約束のレコードセットの作成 テーブル名URLを指定
  'GROUP BY句 で グループ化 Count関数で集計 ORDER BY句で並べ替え
  strSQL = "Select URL, Count(URL) As URLCNT "
  strSQL = strSQL & "From log "
  strSQL = strSQL & "GROUP BY URL "
  strSQL = strSQL & "ORDER BY Count(URL) DESC"

  Set rs = db.Execute(strSQL)

  'データの表示をテーブルで行う
  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>


/*
 * ３．Date関数の値を使用して、当日分をグループ化集計
*/


さてと、全体から訪問者の多い順に表示することができました。
次は、当日に来た来場者の数を表示してみたいと思います。

Date関数で現在の日付を取出して、それを条件にしてみたいと思います。

  strSQL = "Select URL, Count(URL) As URLCNT "
  strSQL = strSQL & "From log "
  strSQL = strSQL & "WHERE WriteTime >= #" & Date & "# " '日付は#で囲う
  strSQL = strSQL & "GROUP BY URL "
  strSQL = strSQL & "ORDER BY Count(URL) DESC"

現在のシステム日付をDate関数で受け取り、
ＳＱＬ文のＷｈｅｒｅ句で指定します。

ポイントは、日付型なので＃＃で囲むことです。

http://www.ken3.org/cgi-bin/test/test039-2.asp
でテストできます、確認して見て下さい。＊ページの人気も含めて（笑)

<%@LANGUAGE=VBScript%>
<html>
<head>
<title>Date関数の値を使用して、当日分をグループ化集計</title>
</head>
<body>
<h2>Date関数の値を使用して、当日分をグループ化集計</h2>
test039-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句 で グループ化 Count関数で集計 ORDER BY句で並べ替え
  strSQL = "Select URL, Count(URL) As URLCNT "
  strSQL = strSQL & "From log "
  strSQL = strSQL & "WHERE WriteTime >= #" & Date & "# " '日付は#で囲う
  strSQL = strSQL & "GROUP BY URL "
  strSQL = strSQL & "ORDER BY Count(URL) DESC"

  Set rs = db.Execute(strSQL)

  Response.Write "発行したＳＱＬ文は[<b>" & strSQL & "</b>]です。<br><br>"

  'データの表示をテーブルで行う
  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>


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


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

素朴な疑問あったら、気軽に、
メール、掲示板に書き込んでくださいね。
掲示板 : 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へ保存しました。

[No.38 SQL GROUP BY句 で グループ化する]
( http://www.ken3.org/backno/backno_asp08.html#38 を参照)
では、
URLでグループ化して、集計関数Count(項目名)で集計しました。

フィードバック

ASP系の→[掲示板]←を覗く、質問を書き込む

評価・感想

No.039を読んだ満足度(評価)は？
5満足（参考になった）
4まぁまぁ（一部参考になった）
3普通（どちらとも言えない）
2なんかなぁ（期待と違った）
1不満（読んで損した気分）
 作者に感想・質問を送る場合は下記に気軽に書いてください
あなたのお名前（ニックネーム） さん

作者からの返信は、 不用 E-mail で受信したい
＊質問・感想はメルマガで紹介する場合があります

三流君の主なリンク先

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

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

その他 宣伝広告