SQL文の作成について

質問

下記のようなテーブルがあります．

テーブル名：logdata
フィールド：
１）日付: timestamp with time zone
２）発信元IPアドレス： inet
２）ポート番号: 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アドレスは特に考慮せず，
１時間ごとの各ポートへのアクセス数を出力させたいのですが，
どのようなSQLを書けばよろしいでしょうか？

回答No.5

chukenkenkou

ところで最終的なレポートを、どのような方法・アプリで見るつもりでしょうか？
横に多数展開されたレポートは、非常に見づらいですよ？
縦軸はポート番号、横軸は時刻にした方が見やすいと思うし、この方法ならSQL
だけでも実現可能です。というか、#2で既に回答済でしたね。

回答No.4

chukenkenkou

SQLだけで、横に展開する項目数を可変にすることは非常に困難です。
予め最大項目数を決めて固定にしておけば可能かも知れませんが、数千～数万といった単位だと、SQL文の長さの制限に掛かったり、性能が出せないSQLになります。

#1でも書きましたが、凝った表示を実現したいなら、ストアドプロシジャを使うか、アプリケーション側で編集するしかありません。

回答No.3

chukenkenkou

殆ど答えともいえる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

chukenkenkou

クロス集計ですね。
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 の知識がだいぶ深まりました．ありがとうございます．

教えていただいたコマンドに非常に満足しているのですが（特に２番目），
つぎのような結果を返す 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

chukenkenkou

具体的に、何が分からないのでしょうか？

ここは「仕様を提示して、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 ポート番号

上記コマンドを日付と時刻をずらしながら実行していくのは骨が折れます．

１時間当たりのポート別アクセス数を見やすい形で出力する SQL 文を書くには
どういった知識が必要になってくるか教えていただけないでしょうか？