DBから単位時間毎にデータ数を集計し、csv形式で出力
データ概要とやりたいこと
以下のようなテーブルがある
id | time(timestamp型) | text |
---|---|---|
----------------- | 2012-11-16 16:41:09+09 | @minya2sen それじゃあ、適当な時間に呼んでおくれ (((o(*゚▽゚*)o))) |
----------------- | 2012-11-16 17:05:03+09 | ★プロのパーソナルトレーナー監修★アメリカの最新理論を取り入れた2ヶ月でマイナス10キロを目指す!http://t.co/0VLMFARg |
----------------- | 2012-11-16 16:45:21+09 | れっつごー(((o(*゚▽゚*)o))) |
----------------- | 2012-11-16 16:41:09+09 | 【生放送】外配信 Inter BEE 2012─ 音と映像と通信のプロフェッショナル展 ─ 国際放送機器展 in 幕張メッセ 9 20分 を開始しました。 http://t.co/YkRuJPeq #lv115557930 |
----------------- | 2012-11-16 16:45:21+09 | Flicker、秩父第二十五番久昌寺のセットへ、一巡目(2009年冬)に撮った弁天池の凍結と御手判の画像を追加。 http://t.co/N9jm2saR |
----------------- | 2012-11-16 16:41:09+09 | @04x12 あれやばすぎ! |
----------------- | 2012-11-16 16:45:21+09 | バトリオでキラやレアが出て喜んでるガキ相手に「頼む!譲ってくれ!」なんて言うなよ |
----------------- | 2012-11-16 16:53:19+09 | RT @yuzi07090710: おれ、罰ゲームを考えるのが昔から得意だった。大学のとき、コンビニに入ってストッキングをレジに出して『試着できますか?』って聞く罰を考えた。3回やって全部おれが負けた。 |
----------------- | 2012-11-16 16:49:25+09 | RT @kinitwi: 見て、この男と女の違い。『女は一ヶ月で四回性格が変わる』ホルモン的にこんなに違うんだって男には理解してほしい yamadaalice_bot http://t.co/wfayxsJe |
----------------- | 2012-11-16 17:05:03+09 | RT @soraotto: ムービック商品情報:K クッション 3,150円 @movic_jp http://t.co/KrKQqD1N あっあたまおかしい |
----------------- | 2012-11-16 16:57:14+09 | RT @niftynews: ジェットスターに厳重注意へ http://t.co/h2UsvVTx #niftynews |
これを、↓のような形(時間:分 | データ数)で集計したい。(今回の例は同日のデータしか扱ってないので特に問題はないが、実際には平均を出す必要があると思うが、今回の備忘録では対象外。)
time | count |
---|---|
00:00 | 6591 |
00:10 | 5509 |
00:20 | 5654 |
00:30 | 5200 |
00:40 | 4021 |
00:50 | 1638 |
01:00 | 1030 |
01:10 | 268 |
集計
↓のSQLで目的のデータ整形はできる。
WITH tmp AS ( SELECT id AS id, extract('hour' from time) || ':' || trunc(extract('minute' from time) / 10 ) * 10 AS time \ FROM table_name ) SELECT time::time, count(id) FROM tmp GROUP BY time ORDER BY time;
少し補足をすると、extractでtimeから時間と分の値を取得。今回は10分刻みで集計したかったから、分を10で割った商をまた10倍することで目的のデータのtimeを作ってる(truncは切り捨て)。
これらで得た値を || で連結してる。ここで出力されるデータ型は文字列なので、次にSELECTするときにはtime型で解釈するよう time::timeとしている。
データ数の集計は、idの数を調べれば良い(重複がないという条件で)と思うので、idの数を調べる。
ファイルに出力
COPY ( WITH tmp AS ( SELECT id AS id, extract('hour' from time) || ':' || trunc(extract('minute' from time) / 10 ) * 10 AS time \ FROM table_name ) SELECT time::time, count(id) FROM tmp GROUP BY time ORDER BY time ) TO '/tmp/record_table.csv' CSV;
SQLの自下げってどれくらいが読みやすいんだろうなー?