SQLの時間データを15分単位で集計できますか？
- SQLの時間データを15分単位で集計する方法について質問です。
- 具体的なテーブルのデータを例に挙げ、集計結果の形式を示しています。
- アイデアや方法があれば教えていただきたいです。
- ベストアンサー
SQLの時間データを15分単位で集計できますか？
SQLで質問させてください。 現在、datetime型の項目があるのですが、これを15分単位で集計したいのです。具体的には以下のようなテーブルとなっています。 時間 値 ―――――――――――――――― 2009/1/1 15:02:00 1 2009/1/1 15:04:00 1 2009/1/1 15:16:00 1 2009/1/1 15:31:00 1 2009/1/1 15:48:00 1 2009/1/1 16:04:00 1 これを次のような形に集計できないものでしょうか？ 年月 時間 数 ―――――――――――――――― 2009/1/1 15:00 2 2009/1/1 15:15 1 2009/1/1 15:30 1 2009/1/1 15:45 1 2009/1/1 16:00 1 何かアイデアがございましたら、よろしくお願いします。
- SQL Server
- 回答数1
- ありがとう数4
- みんなの回答 （1）
- 専門家の回答
質問者が選んだベストアンサー
SQL Serverは2000か2005ですよね？ 「分」の部分を15で割って切り捨て、15倍すれば目的は達せます。 SELECT CONVERT(varchar,時間,111) 年月, RIGHT('00'+CONVERT(varchar,DATEPART(hh,時間)),2) +':'+RIGHT('00'+CONVERT(varchar,ROUND(DATEPART(mi,時間)/15,0,1)*15),2) 時刻, COUNT(*) 数 FROM テーブル GROUP BY CONVERT(varchar,時間,111), DATEPART(hh,時間),ROUND(DATEPART(mi,時間)/15,0,1)
関連するQ&A
- 分単位でログ集計するためカウントする方法
アクセスログの分析のため月ごとで、データをlog_siteというテーブルに入れ込んでいます。(データ量は約4500万件) ※実際はもっと項目(リクエストURL、HTTPステータス、UAなど)がありますが、 ここでは省略しています。 +----+-----------------+--------------------+----------+----+------+ + No | IP | DATETIME | YYYYMMDD | HH | HHMM | +----+-----------------+--------------------+----------+----+------+ + 1 | XXX.XXX.XXX.XXX | 2012-11-14 18:50:11| 20121114 | 18 | 1850 | + 2 | XXX.XXX.XXX.XXX | 2012-11-14 11:44:04| 20121114 | 11 | 1144 | + 3 | XXX.XXX.XXX.XXX | 2012-11-14 17:16:06| 20121114 | 17 | 1716 | + 4 | XXX.XXX.XXX.XXX | 2012-11-03 16:47:01| 20121103 | 16 | 1647 | + 5 | XXX.XXX.XXX.XXX | 2012-11-05 19:35:18| 20121105 | 19 | 1935 | + 6 | XXX.XXX.XXX.XXX | 2012-11-03 16:52:39| 20121103 | 16 | 1652 | + 7 | XXX.XXX.XXX.XXX | 2012-11-04 15:19:40| 20121104 | 15 | 1519 | + 8 | XXX.XXX.XXX.XXX | 2012-11-12 12:34:58| 20121112 | 12 | 1234 | + 9 | XXX.XXX.XXX.XXX | 2012-11-09 09:50:53| 20121109 | 09 | 0950 | + 10 | XXX.XXX.XXX.XXX | 2012-11-09 13:08:53| 20121109 | 13 | 1308 | + 11 | XXX.XXX.XXX.XXX | 2012-11-08 06:57:31| 20121108 | 06 | 0657 | + 12 | XXX.XXX.XXX.XXX | 2012-11-13 11:12:14| 20121113 | 11 | 1112 | + 13 | XXX.XXX.XXX.XXX | 2012-11-05 10:22:42| 20121105 | 10 | 1022 | +----+-----------------+--------------------+----------+----+------+ 各項目は以下のように定義しています。 `No` int(11) NOT NULL AUTO_INCREMENT, `IP` varchar(15) DEFAULT NULL, `DATETIME` datetime DEFAULT NULL, `YYYYMMDD` varchar(8) DEFAULT NULL, `HH` varchar(2) DEFAULT NULL, `HHMM` varchar(4) DEFAULT NULL, やりたいこととしては、1分または1時間単位でアクセスを集計しグラフを書こうとしています。 グラフに対しては、43,56,35,・・,5といった状態で渡せばグラフを書いてくれるのですが、 そのデータを取り出すため以下のようなSQLを書いて、各1分単位でそのカウントをして見ました。 select HHMM, count(IP) from log_site where YYYYMMDD='20121101'and ( (HHMM='0000')or(HHMM='0001')or(HHMM='0002')・・・・(HHMM='2359') )group by HHMM; +------+-------+ + HHMM | count | +------+-------+ + 0001 | 43 | + 0002 | 56 | + 0003 | 35 | (----省略----) + 2359 | 5 | +------+-------+ アクセス時間については、DATETIMEの項目にデータを入れているのですが 1分ずつ、1時間ずつ集計するのに日時や秒の情報が邪魔だったので YYYYMMDD、HH、HHMMといった内容で情報を持たせています。 実行した結果、取得できるのに300-400秒必要なのですが、 1日を取得するだけで、5-7分要するのは、なんとかならないでしょうか？ 最低でも3日、最高で7日間ぐらいのデータは取得したいと考えているため、 この時間はかかりすぎなのです。 やはりログデータ量が約4500万件あるという多さから予め日ごとで集計された 数字を別テーブルに持たせることを考えていたのですが、 引っ掛けたいリクエストURLや、HTTPステータス、UAなど他の項目でも アクセス傾向が知りたいため、予め日ごとで集計された結果を別テーブルに持つ ということは出来ないので、予め集計された結果を持っておく訳にも行かず困っています。 もし今の状況でどのようなＳＱＬだったら、データをスムースに取得できるのでしょうか？ またスムーズにデータを取得するためにデータ構造の見直しが必要としたら どのように変えて、かつどのようにｓｑｌを組んで取得すればよいのでしょうか？ （仮に、日ごとでテーブルを作った場合は1日あたり150万程度になりますが 日をまたぐ場合は、どのようなSQLになるのでしょうか？） もっといい方法があれば教えていただければと思います。 よろしくお願いいたします。
- 締切済み
- MySQL
- 集計のSQLをお教え下さい
初心者ですよろしくお願いします。 以下の様なテーブルがあり、年月と場所コードで 件数を出したいと考えております。 申し訳ありませんが、SQLの記述をお教え下さい。 年月日 ｜ 場所コード ｜ 20060510 1000 20060511 1000 20060511 2000 20060512 2000 20060601 1000 20060601 2000 ・ ・ ↓ 年月日 ｜ 場所コード ｜ count 200605 1000 2 200605 2000 2 200606 1000 1 200606 2000 1 年月日を年月にして集計したいと考えています。 select 年月日,場所コード,count(*) from table group by substring(年月日 from 1 for 6) ではうまくいきませんでした・・・ よろしくお願いします。
- ベストアンサー
- MySQL
- 数件単位の集計のSQLについて
数件単位で合計した集計をしたいのですが、良い案が思い浮かびません。 例えば、100件のレコードがあった場合、ID.1～10までの合計、 ID.11～20までの合計、・・ID.91～100までの合計といった感じです。 ○集計対象のテーブル ID 値 ---------------------------- 1 10,000 2 9,000 3 8,000 : 99 100 100 10 ○集計結果イメージ ID 値 ---------------------------- (1～10の合計値) 50,000 (11～20の合計値) 25,000 ： (91～100の合計値) 5,000 以上、宜しくお願い致します。
- ベストアンサー
- Oracle
- レコード集計のSQLについて
お世話になります。 SQL Serverに関する質問です。 以下の内容を満たすSQLを作成したいのですが、 方法がわからず困っています。 (内容) *************************************************************** ・Ａテーブル、Ｂテーブルには、以下のレコードが格納されています。 [Aテーブル] | key_code | code1 | code2 | code3 | money | person | ← カラム名 ---------------------------------------------- | 001 | 1 | 1 | 1 | 1000 | 10 | ---------------------------------------------- | 001 | 1 | 2 | 1 | 3000 | 10 | ---------------------------------------------- | 001 | 1 | 1 | 1 | 1000 | 10 | ---------------------------------------------- [Bテーブル] | key_code | cd1 | cd2 | cd3 | cd4 | cd5 | ・・・・・ | cd10 | ← カラム名 ------------------------------------------------------------ | 001 | 01 | 02 | 10 | 30 | 51 | ・・・・・ | 90 | ------------------------------------------------------------ | 002 | 01 | 02 | 03 | 10 | 40 | ・・・・・ | 55 | ------------------------------------------------------------ ・Aテーブルの key_code とBテーブルの key_code を関連付けて、 Ｂテーブルのcd1～cd10に格納されている各コード単位に集計した Aテーブルのmoney及びpersonの値を取得したい。 例えば、 key_code = '000' でAテーブルとＢテーブルを関連付けして、 Ｂテーブルのcd(1～10) = '10' で集計した Aテーブルの money と personの 値を求めたい。 **************************************************************** 初歩的な質問で申し訳ございませんが、 どなたかご教授願えませんでしょうか？ よろしくお願いいたします。
- 締切済み
- SQL Server
- 集計のSQL文について
SQLでの集計方法について質問です。 Oracle8iを使用しています。 あるテーブルに以下のようなレコードがあります。 ID 回数 値 1 1 100 1 2 200 1 3 300 2 1 150 2 2 550 .... この登録されている値をそのIDごとに、その回数まで回数ごとにサマリーした結果を出力したいのですが どうすればよいでしょうか。 ID 回数 値 1 1 100 ・・・(100) 1 2 300 ・・・(100+200) 1 3 600 ・・・(100+200+300) 2 1 150 ・・・(150) 2 2 700 ・・・(150+550) 宜しくお願いします
- ベストアンサー
- Oracle
- SQLで集計について分からないことがあります。
SQLで集計について分からないことがあります。 まず、次のようなテーブルがあったとして 日付 店舗コード ブランドコード 金額 ---------------------------------------- 09-01 | 401101 | 01 | 10000 09-02 | 401101 | 01 | 20000 09-03 | 401101 | 01 | 30000 09-04 | 401101 | 01 | 40000 09-01 | 401101 | 02 | 10000 09-02 | 401101 | 02 | 20000 09-03 | 401101 | 02 | 30000 09-04 | 401101 | 02 | 40000 09-01 | 401101 | | 11111 09-02 | 401101 | | 22222 09-03 | 401101 | | 33333 09-04 | 401101 | | 44444 ブランドコードが'Null'の金額をブランドコードが'01'の金額に 日付をキーにして集計したいんですが、 可能でしょうか？ なにか、良い方法があればお願いします。
- ベストアンサー
- Oracle
- 集計のSQLについて
皆さんの知恵をお借りしたいと思っています。 どなたか、良い案がある方お願いいたします。 以下のテーブルがあります。 （売上テーブル） 品名 金額 購入者 ---------------------------- 鉛筆 20 Ａさん 鉛筆 40 Ｂさん 鉛筆 30 Ｂさん 鉛筆 30 鉛筆 10 上のテーブルで、品名・購入者を集計キーとして、 金額を集計したい場合、 SELECT 品名,購入者,SUM(金額) FROM 売上テーブル GROUP BY 品名,購入者 というSQL文で, 品名 金額 購入者 ---------------------------- 鉛筆 20 Ａさん 鉛筆 70 Ｂさん 鉛筆 40 という結果が得られると思いますが・・・、 これを、 品名 金額 購入者 ---------------------------- 鉛筆 20 Ａさん 鉛筆 70 Ｂさん 鉛筆 30 鉛筆 10 という結果になるようなSQL文を作りたいのです。 購入者が入っているものについては、品名・購入者で集計して、 購入者が空欄のものは集計しないようにしたいと思っています。 UNIONを使えばできるとは思うのですが、 それ以外の方法で、1回のSQL文でできる方法があれば知りたいと思っています。 よろしくお願いします。
- ベストアンサー
- Oracle
- PL/SQLの戻り値について
現在正規化されていないテーブルを、PL/SQLを使って集計したいと考えています。以下のような構造にしたいと思うのですが、可能でしょうか? １．正規化されていないテーブルのデータを正規化されたテーブル（テンポラリーテーブル）へ転記 ２．テンポラリーテーブルテーブルのデータを集計（GROUP BYなどで） ３．２の結果を戻す 上記１～３までをすべてPL/SQLで実行し、その結果を取得したいです。 なお取得する結果としては、 キー,集計値1,集計値2 ・ ・ ・ というような構造で、複数レコードあります。 つまり、通常のSELECT文で返ってくる値を、PL/SQLで戻したいと考えています。
- 締切済み
- Oracle
- SQL、特にOralce 8iに関して、データの集計に関する質問です。
SQL、特にOralce 8iに関して、データの集計に関する質問です。 以下のようなデータがあったとします。 [開始年月(int)] [終了年月(int)] [値] 201001 201004 1 201003 201007 2 201005 201010 3 201007 201101 4 このとき以下のような感じで、重複している月で値を合算し、一番値が大きな月を求めるには、どうすればよいでしょうか。 １ ２ ３ ４ ５ ６ ７ ８ ９ 101112 (月) ■■■■＿＿＿＿＿＿＿＿ ＿＿■■■■■＿＿＿＿＿ ＿＿＿＿■■■■■■＿＿ ＿＿＿＿＿＿■■■■■■ ↓↓↓↓↓↓↓↓↓↓↓↓ １ １ ３ ３ ５ ５ ９ ７ ７ ７ ４ ４ (計) ←この場合 7月が一番大きかった できればPL/SQLなしで解決したいと思っております。 ご教示お願いいたします。
- 締切済み
- Oracle
- 1回のSQLで集計結果順にソートする方法を教えてください
PHP4＋MySQLです。 やりたいことがあるのですが、いいSQLが考えつきません。 お知恵を拝借できれば幸いです。 以下のようなテーブルがあるとします。 ID｜ITEMid｜DATE 1｜ 232｜2009-06-14 2｜ 156｜2009-06-14 3｜ 156｜2009-06-14 4｜ 232｜2009-06-14 5｜ 232｜2009-06-14 6｜ 567｜2009-06-14 これを次のように集計したいのです。 （ITEMidの登場回数順にソートして、実際の回数も出す） 232:3 156:2 567:1 サブクエリの使えない環境で、1回のSQLで集計することはできるでしょうか。できる場合実際のSQL文を教えていただければ幸いです。 テンポラリテーブル使えば自分でもできそうな気がするので、できれば使わない想定でお願いします。
- ベストアンサー
- MySQL
お礼
お早いアドバイスありがとうございます。 イメージどうりの集計ができました！ 2009/01/01 15:00 2 2009/01/01 15:15 1 2009/01/01 15:30 1 2009/01/01 15:45 1 2009/01/01 16:00 1 ありがとうございました。