[三流君] −−> [ASPで遊ぶ、失敗する] −−> [バックナンバー一覧]
−−> No.043 SQL文 HAVING句でGroup化されたレコードに条件設定する

SQL文 HAVING句でGroup化されたレコードに条件設定する



本文(発行内容)


<SQL文 HAVING句でGroup化されたレコードに条件設定する>

こんにちは、三流プログラマーのKen3です。 今回は、 SQL Select文でグループ化したデータに対して、 条件を付ける HAVING句 を使用してみます。 何かの参考となれば幸いです。

/* * 1.今回のキッカケ */

ログのカウント処理でグループ化していろいろと集計を行っていました。 http://www.ken3.org/cgi-bin/test/test039-1.asp で、発行する(した)SQL文は Select URL, Count(URL) As URLCNT From log GROUP BY URL ORDER BY Count(URL) DESC No. URL 訪問者 1 http://www.ken3.org/backno/backno_vba_mokuji.html 548人 2 http://www.ken3.org/backno/backno_vba12.html 344人 3 http://www.ken3.org/backno/backno_vba01.html 238人 4 http://www.ken3.org/ 207人 ・  ・  ・ 177 http://www.ken3.org/backno/backno_guchi11.html 1人 178 http://www.ken3.org/cgi-bin/test/test001.asp 1人 179 http://www.ken3.org/backno/hosoku/008/ 1人 180 http://www.ken3.org/backno/backno_guchi31.html 1人 と、集計されて表示されています。 全てのデータ表示も必要なのですが、 人気のある上位ページだけチェックしたいので、 訪問者数50人以上のある程度訪問者の居るページを表示させたいと思いました。 さて、どうしようかなぁ。 単純なのは、 'EOFまでループ nCNT = 1 Do While rs.EOF = False '.EOFがFalseの間 の表示のループで、訪問者数をチェックして、ループを抜ければできそうです。

/* * 2.SQL文 HAVING句でGroup化されたレコードに条件設定する */

それだと少し面白くないので、SQL文で探してみました SQL SELECT文にHAVING句があって、 GROUP BY でグループ化されたレコードに対して条件を付けられます。 Select URL, Count(URL) As URLCNT -- URLとURLをCount関数で集計した項目URLCNT From log -- テーブルはlogから GROUP BY URL -- URLでグループ化 HAVING Count(URL) >= 50 -- Count(URL)が50以上のデータに絞り込む ORDER BY Count(URL) DESC -- Count(URL)の降順でレコードを作成 作成はこんな感じのSQLを作成、それを発行します。 グループ化されてできたレコードに HAVING 条件 ~~~~~~~~~~~ と書くことによって、グループ化されたレコードを絞ることができます。 使い方はいつものSQL文字列の作成に入れて、 strSQL = "Select URL, Count(URL) As URLCNT " strSQL = strSQL & "From log " strSQL = strSQL & "GROUP BY URL " strSQL = strSQL & "HAVING Count(URL) >= 50 " strSQL = strSQL & "ORDER BY Count(URL) DESC" でSQL文の文字列を作成、 Set rs = db.Execute(strSQL) で発行、レコードセットを作り表示しました。 http://www.ken3.org/cgi-bin/test/test043-1.asp でテスト可能です。動きを見て下さい。 SQL文しか変わってないけど、下記がソースです。 *逆に言うとSQL文を変えると、定型パターンでいろいろとできますね。 SQLのマジシャンと呼ばれるくらいになりたいですよね。 <%@LANGUAGE=VBScript%> <html> <head> <title>SQL文 HAVING句でGroup化されたレコードに条件設定する</title> </head> <body> <h2>SQL文 HAVING句でGroup化されたレコードに条件設定する</h2> test043-1.asp<br> <hr> cnt.mdb から訪問されたURLをグループ化して集計、降順に表示<br> <b>HAVING句で訪問者50人以上を表示</b><br> <hr> <% 'ADO DB Connection オブジェクトを作成する、英文そのままじゃん Set db=Server.CreateObject("ADODB.Connection") '.Provider?プロバイダー?通信会社?じゃなくって 'データアクセスにはJet.OLEDB.4.0を使うことを設定 db.Provider = "Microsoft.Jet.OLEDB.4.0" '次に、接続DBの位置を渡すので、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 & "HAVING Count(URL) >= 50 " 'グループ化集計50人以上 strSQL = strSQL & "ORDER BY Count(URL) DESC" 'オマケで画面にデータ表示 Response.Write "<hr>発行する(した)SQL文は<br><B><font color='green'>" Response.Write strSQL & "</font></B><br>です<hr>" 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>" 'EOFまでループ 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="test039-1.asp">URLでグループ化した集計値を全て表示</A><br> <A HREF="test037-1.asp">cnt.mdb 最近訪問した人のデータ30件を表示</A><br> <br> </body> </html>

/* * 3.終わりの挨拶 */

今回は、 SQL文のHAVING句でGroup化されたレコードに条件設定する のサンプル解説でした。 SQL文の参考になれば幸いです。 素朴な疑問あったら、気軽に、 メール、掲示板に書き込んでくださいね。 掲示板 : http://www.ken3.org/asp/bbs-asp.html ASP、VBScript勉強中の三流プログラマーのKen3でした。 ---------------------------------- 関連項目(ただのバックナンバー紹介) ---------------------------------- [No.39 SQL GROUP BY句 で グループ化 集計結果を並べ替えする] http://www.ken3.org/backno/backno_asp08.html#39 では、URLをグループ化して訪問者数を数えてました。


ページフッター

ここまで、読んでいただきどうもです。目的の情報が見つかったか?少々心配しつつ、、、

三流君へ メッセージを送る

感想や質問・要望・苦情など 三流君へメッセージを送る。
返信例 XXXXさんへ
下記のフォームからメッセージを送ることができます。


あなたのお名前(ニックネーム):さん
返信は?:

アドレス:に返事をもらいたい
感想や質問↓:


(感想や質問・要望・苦情はHPで記事に載せることがあります。)

種類別のリンク や 広告など

[三流君(TOP ken3.org へ戻る)] / [ASPで遊ぶ、失敗する] / [ASP記事 バックナンバー目次]


Blogとリンク:[三流君のMemo別館]/ [ASP 三流君のソースコード置き場]/ [Ken3Video YouTubeで動画解説]
広告:

気になった ジャンル ↓を選択してください。

まぁ、基本はデータの受け取りかなぁ。
・[Form等を使用したデータのやり取り]・・・ASPと言っても、HTMLの入力フォームからデータを受け取ります。POSTやGETでやりとりを押さえますか。

次は、データの入出力 で ADOを使った(ADOで接続) と SQLの解説を少々
・[ADOでMdbファイルを使う]・・・MDBと接続して、簡単な追加・更新・削除を行った。
・[ADOでExcelと接続してみた]・・・.xlsと接続してSQLを使ってみた。
・[ADOでCSVと接続してみた]・・・.CSV テキストを読み出した。※更新・削除はできません

広告:



DBが使えるので、あまり使用しないけど、普通のテキストファイル処理
・[テキストファイル処理]・・・ファイルを開いて、書き込む。1行読み込みなどを軽く

VBScriptでFormat関数が無いなど、微妙にVBAと違うけど
[VBScript関数関係の説明]・・・少し、処理を書いてみた。
[その他処理サンプル]・・・あまり良いサンプル作れなかったけど。。。
何かの参考となれば幸いです。



[三流君(TOP ken3.org へ戻る)] / [ASPで遊ぶ、失敗する] / [ASP記事 バックナンバー目次]


広告: