postgresqlの値の変化点を知りたい

Question

ip | os |0| 1 |2| 3 |4 |5 |6 |7 |8 |9 |10|11|12|13|14|15 |16 |17 |18 |
111.111.111.111 | Mac OS X | | | | | | | | | | | | | | | | 1 | 1 | 1 | 1 |
222.222.222.222 | Mac OS X | | | | | | | | | | | | | | | | 1 | 1 | 1 | 1 |
333.333.333.333 | Linux 2.6 | | | | | | | 1| 1| 1| 1| | | | | | | | | |

このようなデータベースをpostgresqlで作成しています。
目的は例えば333.333.333.333のipアドレスであればLinuxが何時間連続で動いているかという値を取得することです。
このデータベースどういうものかというと例であればipアドレスが111.111.111.111のPCで動いているOSは何かということが書いてあります。
１時間ごとにtest.shを実行しキャプチャしてきた通信からOS推定を行っています。
その結果をcsvファイルに保存しostableというtableに入力しています。
なのでテーブルの列名の数字は0時から23時を表しています。(横が入りきらずここでは18まで書いてます。)
そして15時に実行した時に111.111.111.111のOSがMac OS Xなら15の列のところに1をinsertします。もうすでに1が立っていたら1の値を同じ１という値に更新しています。
実行プログラムは下記の通りです。

<test.sh>
DATE3=date -v-1H +"%H"

while read row; do
ip_tmp=$(echo ${row} | cut -d , -f 3)
os_tmp=$(echo ${row} | cut -d , -f 4)

/usr/local/bin/psql -U nakakukitatsuya -d nmapresult < test.sql -v col="$DATE3" -v os_tmp="$os_tmp" -v ip_tmp="$ip_tmp"
done < /Users/nakakukitatsuya/Downloads/p0f-master/hoge.csv

<test.sql>
update ostable set :"col"=1 where os=:'os_tmp';
insert into ostable (ip, os, :"col")
select :'ip_tmp', :'os_tmp', 1
where not exists (select * from ostable where os= :'os_tmp');

目的は1が出てきた時間から1がなくなった時間の差を取ることで特定のOSの連続起動時間の値を取得することです。

もし何かいい方法ご存知であればご教授いただけると助かります。

Comments

[sazi]

例えば断続的に起動、(0,1,0,1,1,0)みたいな場合はどうするのですか？最大を取得？

[ygtygtygt]

全ての時間を取得しようとしています。

Answer 1

連続性を後から検索するのでは無くupdate時に前の時刻に1を足す様にする。前項目pcolはシェルで渡す。

update ostable set :"col"=coalesce(:"pcol",0)+1 where os=:'os_tmp';

マシン毎に後は横を縦にして最大値を求められる。　どうしても1である必要が有れば忘れてください。

Comments

[ygtygtygt]

回答ありがとうございます。
そういう考え方があったんですね。思いつきませんでした。試してみたところうまくいきました。本当に助かりました。ありがとうございます。