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

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

本文(発行内容)

＜SQL文 HAVING句でGroup化されたレコードに条件設定する＞
こんにちは、三流プログラマーのＫｅｎ３です。

今回は、
SQL Select文でグループ化したデータに対して、
条件を付ける HAVING句 を使用してみます。

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

/*
 * １．今回のキッカケ
*/

ログのカウント処理でグループ化していろいろと集計を行っていました。
http://www.ken3.org/cgi-bin/test/test039-1.asp
で、発行する（した）ＳＱＬ文は
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人 

と、集計されて表示されています。

全てのデータ表示も必要なのですが、
人気のある上位ページだけチェックしたいので、
訪問者数５０人以上のある程度訪問者の居るページを表示させたいと思いました。

さて、どうしようかなぁ。
単純なのは、
  'ＥＯＦまでループ
  nCNT = 1
  Do While rs.EOF = False   '.EOFがFalseの間
の表示のループで、訪問者数をチェックして、ループを抜ければできそうです。

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

それだと少し面白くないので、ＳＱＬ文で探してみました
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)の降順でレコードを作成

作成はこんな感じのＳＱＬを作成、それを発行します。
グループ化されてできたレコードに
HAVING 条件
~~~~~~~~~~~
と書くことによって、グループ化されたレコードを絞ることができます。

使い方はいつものＳＱＬ文字列の作成に入れて、

  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
でテスト可能です。動きを見て下さい。
ＳＱＬ文しか変わってないけど、下記がソースです。
＊逆に言うとＳＱＬ文を変えると、定型パターンでいろいろとできますね。
  ＳＱＬのマジシャンと呼ばれるくらいになりたいですよね。

<%@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句で訪問者５０人以上を表示</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 & "HAVING Count(URL) >= 50 "  'グループ化集計50人以上
  strSQL = strSQL & "ORDER BY Count(URL) DESC"

  'オマケで画面にデータ表示
  Response.Write "<hr>発行する（した）ＳＱＬ文は<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>"

  'ＥＯＦまでループ
  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">ＵＲＬでグループ化した集計値を全て表示</A><br>
<A HREF="test037-1.asp">cnt.mdb 最近訪問した人のデータ３０件を表示</A><br>
<br>

</body>
</html>

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

今回は、
ＳＱＬ文のHAVING句でGroup化されたレコードに条件設定する
のサンプル解説でした。

ＳＱＬ文の参考になれば幸いです。

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

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

----------------------------------
関連項目(ただのバックナンバー紹介)
----------------------------------

[No.39 SQL GROUP BY句 で グループ化 集計結果を並べ替えする]
http://www.ken3.org/backno/backno_asp08.html#39
では、ＵＲＬをグループ化して訪問者数を数えてました。

フィードバック

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

評価・感想

三流君の主なリンク先

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

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

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

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

本文(発行内容)

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

/* * １．今回のキッカケ */

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

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

フィードバック

評価・感想

三流君の主なリンク先

その他 宣伝広告

その他宣伝広告