![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?5a7ff87)
下記のようなテーブルがあります.
テーブル名:logdata
フィールド:
1)日付: timestamp with time zone
2)発信元IPアドレス: inet
2)ポート番号: integer
具体例はつぎのようなものです.
2008/01/14 00:00:00, xxx.xxx.xxx.xxx, 445
2008/01/14 00:00:01, xxx.xxx.xxx.xxx, 135
・
・
・
2008/01/18 23:59:59, xxx.xxx.xxx.xxx, 445
今回,発信元IPアドレスは特に考慮せず,
1時間ごとの各ポートへのアクセス数を出力させたいのですが,
どのようなSQLを書けばよろしいでしょうか?
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
ところで最終的なレポートを、どのような方法・アプリで見るつもりでしょうか?
横に多数展開されたレポートは、非常に見づらいですよ?
縦軸はポート番号、横軸は時刻にした方が見やすいと思うし、この方法ならSQL
だけでも実現可能です。というか、#2で既に回答済でしたね。
No.4
- 回答日時:
SQLだけで、横に展開する項目数を可変にすることは非常に困難です。
予め最大項目数を決めて固定にしておけば可能かも知れませんが、数千~数万といった単位だと、SQL文の長さの制限に掛かったり、性能が出せないSQLになります。
#1でも書きましたが、凝った表示を実現したいなら、ストアドプロシジャを使うか、アプリケーション側で編集するしかありません。
No.3
- 回答日時:
殆ど答えともいえるSQLは、既に提示済です。
ほんの少し変更するだけで期待した結果を得られるのですが、自分で試行錯誤する気はないのでしょうか?
やる気を感じられない人に教えるのは、教える側としても教え甲斐がありません。。。
<SQL例>
select
to_char(日付,'YYYY-MM-DD') as YYYYMMDD,
to_char(日付,'HH24') as HH,
sum(case when ポートNo=123 then 1 else 0 end) as "port123",
sum(case when ポートNo=333 then 1 else 0 end) as "port333",
sum(case when ポートNo=555 then 1 else 0 end) as "port555"
from logdata
group by YYYYMMDD,HH
order by YYYYMMDD,HH
存在しない日時のデータは、当然、検索結果として得られません。もし、データが存在しない日時も0件で表示したいなら、#1でも触れましたが、日付(あるいは時間も)を管理するカレンダー表を作り、LEFT JOINする必要があります。
PostgreSQLには独自機能として、集合を返すgenerate_seriesという関数があり、この関数でも日付や時刻を生成することは可能ですが、性能的にはカレンダー表を作り、適切なインデクスを定義した方がいいと思います。
=====余談=====
こういったサイトで今回のような質問をする場合、表の定義(列構成とデータ型)、表のデータ、得たい結果を明記した方が、回答を得やすいです。さらには、表の定義や格納データ等をSQLで示し、回答者側がすぐにテストできるような形で掲載すれば、回答者側の負担も減り、すぐにテストできますから、多くの人から早く回答を得られる可能性があります。また、バージョンも明記しましょう。
また、「他人にSQL等を作ってもらうこと」を許している「はてな」といったサイトもあります。ポイントを購入し、回答者にポイントでお礼する仕組みですが、それだけに回答もしっかりしたものを得られます。
この回答への補足
教えていただいた SQL はすぐに考えついたのですが,
この書き方ですと sum(case when ポートNo= 以下略 を
自分で何番のポートにアクセスがあったかどうかを調べ,
それらをすべて手で打たなければならないと思います.
アクセスが検知されているポート(数千~数万あります)を
重複しないように横に展開する方法はありますでしょうか?
余談について:
いただいたアドバイスはごもっともだと思います.
反省し,今後につなげていきたいと考えております.
No.2
- 回答日時:
クロス集計ですね。
SQL例を示します。
<ポート番号毎に、時間帯(2時間)別のアクセス数を得るSQL例>
select
ポートNo,
sum(case when extract(hour from 日付) between 0 and 1 then 1 else 0 end) as "0-",
sum(case when extract(hour from 日付) between 2 and 3 then 1 else 0 end) as "2-",
sum(case when extract(hour from 日付) between 4 and 5 then 1 else 0 end) as "4-",
sum(case when extract(hour from 日付) between 6 and 7 then 1 else 0 end) as "6-",
sum(case when extract(hour from 日付) between 8 and 9 then 1 else 0 end) as "8-",
sum(case when extract(hour from 日付) between 10 and 11 then 1 else 0 end) as "10-",
sum(case when extract(hour from 日付) between 12 and 13 then 1 else 0 end) as "12-",
sum(case when extract(hour from 日付) between 14 and 15 then 1 else 0 end) as "14-",
sum(case when extract(hour from 日付) between 16 and 17 then 1 else 0 end) as "16-",
sum(case when extract(hour from 日付) between 18 and 19 then 1 else 0 end) as "18-",
sum(case when extract(hour from 日付) between 20 and 21 then 1 else 0 end) as "20-",
sum(case when extract(hour from 日付) between 22 and 23 then 1 else 0 end) as "22-"
from logdata
group by ポートNo
order by ポートNo
<日付、ポート番号毎に、時間帯(2時間)別のアクセス数を得るSQL例>
select
to_char(日付,'YYYY-MM-DD') as YYYYMMDD,
ポートNo,
sum(case when extract(hour from 日付) between 0 and 1 then 1 else 0 end) as "0-",
sum(case when extract(hour from 日付) between 2 and 3 then 1 else 0 end) as "2-",
sum(case when extract(hour from 日付) between 4 and 5 then 1 else 0 end) as "4-",
sum(case when extract(hour from 日付) between 6 and 7 then 1 else 0 end) as "6-",
sum(case when extract(hour from 日付) between 8 and 9 then 1 else 0 end) as "8-",
sum(case when extract(hour from 日付) between 10 and 11 then 1 else 0 end) as "10-",
sum(case when extract(hour from 日付) between 12 and 13 then 1 else 0 end) as "12-",
sum(case when extract(hour from 日付) between 14 and 15 then 1 else 0 end) as "14-",
sum(case when extract(hour from 日付) between 16 and 17 then 1 else 0 end) as "16-",
sum(case when extract(hour from 日付) between 18 and 19 then 1 else 0 end) as "18-",
sum(case when extract(hour from 日付) between 20 and 21 then 1 else 0 end) as "20-",
sum(case when extract(hour from 日付) between 22 and 23 then 1 else 0 end) as "22-"
from logdata
group by YYYYMMDD,ポートNo
order by YYYYMMDD,ポートNo
この回答への補足
お蔭様で SQL の知識がだいぶ深まりました.ありがとうございます.
教えていただいたコマンドに非常に満足しているのですが(特に2番目),
つぎのような結果を返す SQL も教えていただければ幸いです.
YYYY-MM-DD HH | port_1 | port_2 | port_3 | ... | port_k
2008-01-19 00 | 10 | 9 | 12 | ....
2008-01-19 01 | 0 | 2 | 3 | ...
2008-01-19 02 | 1 | 4 | 3 | ...
・
・
・
No.1
- 回答日時:
具体的に、何が分からないのでしょうか?
ここは「仕様を提示して、SQLを作ってもらう」サイトではなく、「分からない部分を具体的に示して質問する」ことが利用規約になっています。
得たい結果が不明ですが、提示された条件だけなら、以下のようなSQLで結果を得られます。
select port_no,date_trunc('hour',acs_tmst) as tmst,count(*) as acs_cnt
from logdata
group by port_no,tmst
order by port_no,tmst
「データが存在しない日時も、0件で表示したい」といった要件があるなら、カレンダー表を作成しておき、ジョインするといった方法になります。
また、凝った表示方法をしたいなら、SQLだけではなく、アプリケーション側やストアドプロシジャで実装した方が、処理は軽くなると思います。
この回答への補足
>ここは「仕様を提示して、SQLを作ってもらう」サイトではなく、「分からない部分を具体的に示して質問する」ことが利用規約になっています。
すみません.
現在,私は bash から手打ちでコマンドを打っているのですが,
コマンドを改善して負担をなるべく軽くしたいと思っています.
$ psql -c "select ポート番号, count (*)
from ログデータ
where between '日付 00:00:00' and '日付 00:59:59'
group by ポート番号
order by ポート番号
上記コマンドを日付と時刻をずらしながら実行していくのは骨が折れます.
1時間当たりのポート別アクセス数を見やすい形で出力する SQL 文を書くには
どういった知識が必要になってくるか教えていただけないでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- ネットワーク コマンドプロンプトで、コマンドの飛ばし方を教えてください。 ネットワークにデータを飛ばす(copy) 4 2022/10/19 11:21
- その他(パソコン・周辺機器) チャレンジタッチ3 改造 アプリインストールできない 1 2022/07/01 14:43
- その他(Microsoft Office) パワークエリの複数ファイルのデータ統合について 3 2022/07/14 17:06
- UNIX・Linux bash のファイルの読み込み方についてご質問 3 2023/05/15 20:40
- その他(メールソフト・メールサービス) ドメイン間違えでエラー送信メールが来ない 1 2022/05/10 18:44
- 確定申告 確定申告しなかった場合の期末商品棚卸高の扱い 2 2022/06/11 21:24
- その他(IT・Webサービス) EXCELVBAでシートを他のブックにコピーする命令を出すと毎回存在しないシート名の問合わせがある 1 2022/05/07 15:12
- 数学 数独の次の一手 2 2022/08/31 17:17
- Excel(エクセル) EXCELVBAでシートを他のブックにコピーする命令を出すと毎回存在しないシート名の問合わせがある 2 2022/05/07 17:16
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
直近1ヶ月に誕生日を迎える社員...
-
<SQL>重複しているデータの場合...
-
SQLで、同じ値が何回連続す...
-
項目以外の文字列は連結できま...
-
NULLは表示順最下位
-
文字2桁、3桁交じりの文字列...
-
SQLローダーCSV取込で、囲み文...
-
select文の実行結果に空白行を...
-
2つの列が同じ値の行を取得するSQL
-
1の行を固定した上でVBAを用い...
-
テーブルの最後(最新)のレコー...
-
ファイルの漢数字の順番につい...
-
AccessのSQL文で1件のみヒット...
-
単一グループのグループ関数で...
-
レコードの登録順がおかしい
-
where句中のtrim関数について
-
小数点以下桁数が最大の値に小...
-
count関数の値をwhere句で使用...
-
月末日のみソートしたい
-
並べ替えについて
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
複数のテーブルから値を合計出...
-
<SQL>重複しているデータの場合...
-
SQLです教えてくださいお願いし...
-
SQLです。下記の問合せを行うク...
-
下記の問合せを行うクエリを、 ...
-
SQL 重複しないJoinの仕方を教...
-
最新の日付と2番目の日付のデー...
-
mysqlに格納されている日付のデ...
-
DISTINCTROWについてです。
-
SQLで、同じ値が何回連続す...
-
直近1ヶ月に誕生日を迎える社員...
-
NULLは表示順最下位
-
MySQLで日付(date)型のdefaul...
-
MySQLで特定のグループの上位3...
-
MySQLのdatetime型に0月0日を格納
-
「8月の第3土日」をデータベ...
-
型変換
-
日付検索で0001-01-01 00:00:00...
-
Mysqlで最新の日付を持つデータ...
-
NULLの値ってupdateで評価され...
おすすめ情報