[三流君] −−＞
[ＡＳＰで遊ぶ、失敗する] −−＞
[バックナンバー一覧]
−−＞ No.040 MDBへのSQL文 Format関数使用
MDBへのSQL文 Format関数使用
本文(発行内容)
＜MDBへのSQL文 Format関数使用＞
こんにちは、三流プログラマーのＫｅｎ３です。
今回は、ＳＱＬ文の中で関数を使ってみます。
＊少しハマりました。笑ってください。。。
何かの参考となれば幸いです。
/*
* １．日付別にデータをカウントしたくて
*/
前回まで、GROUP BYで、グループ化して、データを集計してました。
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
とＳＱＬ文を発行して、訪問者のＵＲＬ単位の集計をとりました。
日付別のアクセス数をカウントしたくて、少し悩みました。
悩みの理由は、テーブルの構造が、
cnt.mdb
テーブル名:log
IP
URL
referrer
WriteTime
と項目があって、WriteTimeに2003/03/23 13:20:23と時刻まで入ってます。
そのままグループ化できないしなぁ、、、
/*
* ２．ADO から MDB へのアクセス(SQL文)でFormat関数使用
*/
なんて、深く考えていたのですが、
SQL文の中でFormat関数が使えるみたいなので、使ってみました。
えっ、過去のメルマガで、VBScriptでは使えないって言ってたよね？
In message "[ASPで遊ぶ No.019] - 001行目と表示したくてRight関数を使う",
>/*
> * ２．あれ？Format関数って使えないの？
>*/
>
>VBAも少し知ってる私は、
>有名なFormat関数を使用して、
>Format(カウンター,"000")
>にすればＯＫだと思い軽く修正しました。
>
>Do While ts.AtEndOfStream = False '*6ファイルの終端になってない間ループ
> strDATA = ts.ReadLine 'ファイルからデータを一行を読む
> nLINE = nline + 1 '行カウンタを＋１する
> Response.Write Format(nLINE,"000") & "行目 " '*1行番号の表示
> Response.Write strDATA '読み込んだデータを表示
> Response.Write "<BR>" '改行のタグを入れる
> '行カウンタが５、１０、１５、、５で／って余りが０なら
> '＜ＨＲ＞タグを挿入する
> If nLINE Mod 5 = 0 Then
> Response.Write "<HR>" 'タグを入れる
> End If
>Loop
>
>の表示部分を、
> Response.Write Format(nLINE,"000") & "行目 " '*1行番号の表示
>に変えました、余裕でしょ。
>
>あれ？
>
>エラー タイプ
>Microsoft VBScript 実行時エラー (0x800A000D)
>型が一致しません。: 'Format'
>/test/test019-1.asp, line 26
>
>ウソでしょ？エラーだよ。
>なんで？
>
>何でジャねぇダロ三流君、
>ＡＳＰ（ＶＢＳｃｒｉｐｔ）に、
>Ｆｏｒｍａｔ関数は無いんだよ。存在しないの。
>
>またまたぁ。
>と
>思い、少し調べてみると、やはり無いみたい。
---
と解説してたのに？
あっ、VBScriptでは無いんだけど、MDBへのSQL文では使えるみたいです。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
下記のようなＳＱＬを作りました。
strSQL = "Select Format(WriteTime, 'YYYYMMDD') AS YYYYMMDD, Count(*) AS CNT"
strSQL = strSQL & " From log"
strSQL = strSQL & " GROUP BY Format(WriteTime, 'YYYYMMDD')"
普通にFormat(WriteTime, 'YYYYMMDD')と使ってます。
http://www.ken3.org/cgi-bin/test/test040-1.asp
でテスト可能です。
<%@LANGUAGE=VBScript%>
<html>
<head>
<title>ADO から MDB へのアクセスでFormat関数使用</title>
</head>
<body>
<h2>ADO から MDB へのアクセスでFormat関数使用</h2>
test040-1.asp<br>
<hr>
<b>YYYYMMDDでデータをまとめて集計表示します。</b><br>
<font color="red">GROUP BY Format(WriteTime, 'YYYYMMDD')でまとめてみた。</font><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
'FORMAT関数を使用して、テーブル名logからデータを集計する
'ＳＱＬ文を発行する
strSQL = "Select Format(WriteTime, 'YYYYMMDD') AS YYYYMMDD, Count(*) AS CNT"
strSQL = strSQL & " From log"
strSQL = strSQL & " GROUP BY Format(WriteTime, 'YYYYMMDD')"
'オマケで画面にデータ表示
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>日付</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>" & rs.Fields.Item("YYYYMMDD") & "</TD>"
Response.Write "<TD ALIGN='RIGHT'>" & rs.Fields.Item("CNT") & "</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>
</body>
</html>
http://www.ken3.org/cgi-bin/test/test040-1.asp
の出力結果は、
NO. 日付 閲覧ページ数
1 20030316 384
2 20030317 1528
3 20030318 1555
4 20030319 1621
5 20030320 1484
6 20030321 609
7 20030322 516
8 20030323 173
みたいな感じで集計・表示してます。
なんだぁ、Format関数使えるのかぁ、、、って感じですね。
/*
* ３．当日の時間帯別アクセス数を表示してみる
*/
あまり意味のない集計なのですが、
当日の時間帯別のアクセス数を表示してみます。
発行するＳＱＬ文は、
~~~~~~~~~~~~~~~~~~
Select Format(WriteTime, 'HH') AS HH, Count(*) AS CNT
From log
Where WriteTime >= #2003/03/23#
GROUP BY Format(WriteTime, 'HH')
で、
strSQL = "Select Format(WriteTime, 'HH') AS HH, Count(*) AS CNT"
strSQL = strSQL & " From log"
strSQL = strSQL & " Where WriteTime >= #" & Date & "#" '当日を条件に
strSQL = strSQL & " GROUP BY Format(WriteTime, 'HH')"
で作成しました。
ポイントは、
Format(WriteTime, 'HH')で時間の取りだし
と
" Where WriteTime >= #" & Date & "#"の当日指定の部分かなぁ。
http://www.ken3.org/cgi-bin/test/test040-2.asp
でテスト可能です。（あんまり訪問者居ないなぁ（笑））
<%@LANGUAGE=VBScript%>
<html>
<head>
<title>当日の時間帯別アクセス数を表示してみる SQLでFormat関数使用</title>
</head>
<body>
<h2>当日の時間帯別アクセス数を表示してみる SQLでFormat関数使用</h2>
test040-2.asp<br>
<hr>
<b>当日の時間帯別アクセス数を表示してみる</b><br>
<font color="red">GROUP BY Format(WriteTime, 'HH')でまとめてみた。</font><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
'FORMAT関数を使用して、テーブル名logからデータを時間帯別に集計する
'ＳＱＬ文を発行する
strSQL = "Select Format(WriteTime, 'HH') AS HH, Count(*) AS CNT"
strSQL = strSQL & " From log"
strSQL = strSQL & " Where WriteTime >= #" & Date & "#" '当日を条件に
strSQL = strSQL & " GROUP BY Format(WriteTime, 'HH')"
'オマケで画面にデータ表示
Response.Write "<hr>発行する（した）ＳＱＬ文は<br><B><font color='green'>"
Response.Write strSQL & "</font></B><br>です<hr>"
Set rs = db.Execute(strSQL)
'データの表示をテーブルで行う
Response.Write Date & "の時間帯別のアクセス集計<br>"
Response.Write "<TABLE Border='1'>"
'見出しをバカっぽく、そのまま書き込む
Response.Write "<TR>"
Response.Write "<TD>時間帯</TD>"
Response.Write "<TD>閲覧ページ数</TD>"
Response.Write "</TR>"
'ＥＯＦまでループ
Do While rs.EOF = False '.EOFがFalseの間
Response.Write "<TR>" '内容を表示する
Response.Write "<TD>" & rs.Fields.Item("HH") & "時〜</TD>"
Response.Write "<TD ALIGN='RIGHT'>" & rs.Fields.Item("CNT") & "</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>
</body>
</html>
http://www.ken3.org/cgi-bin/test/test040-2.asp
の実行結果
2003/03/23の時間帯別のアクセス集計
時間帯 閲覧ページ数
00時〜 27
01時〜 14
・
・
・
12時〜 13
13時〜 28
みたいな感じで表示されます。
/*
* ４．終わりの挨拶
*/
ＳＱＬ文でいろいろと処理ができます。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
いろいろな切り口から、集計してみたいと思います。
今日、少し驚いたのは、MDBへのSQL文でFormat関数が使えたことでした。
素朴な疑問あったら、気軽に、
メール、掲示板に書き込んでくださいね。
掲示板 : http://www.ken3.org/asp/bbs-asp.html
ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。
----------------------------------
関連項目(ただのバックナンバー紹介)
----------------------------------
[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系の→[掲示板]←を覗く、質問を書き込む
評価・感想
三流君の主なリンク先
[アクセスランキング]
[サイトマップ]
[リンク先・相互リンク先など]
Ken3の日記(weblog) --
[広告・副収入系]
[プログラマー業務の愚痴]
[VBA系の話題]
[ASP系の話題]
[コンビニ系ネタ]
[その他]
その他 宣伝広告