人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

SQL文ででの集計関数の使い方を悩んでいます。環境はPostgreSQL8.1です。
create table click(
serialid serial,
ipaddr text,
date timestamp not null default current_timestamp
)
上記のテーブルがあります。

検索結果して
2006/1/1 | 1/2 | 1/3 |
-------------+----+----+----+
AA.BB.CC.DD | 1 | 5 | 6 |
EE.FF.GG.HH | 2 | 3 | 4 |
: : : :

このような表を作ろうと思っています。
これを1回のSQLで行うことは可能でしょうか?
列は、ひと月分(2月であれば 2/1〜2/28 など)を出したいです。よろしくお願いします。

●質問者: kamiochiai
●カテゴリ:ウェブ制作
✍キーワード:2006 28 AA BB CC
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● birdie-brain
●70ポイント

縦横が可変の表を抽出するにはクロス集計が必要になりますが、PostgreSQL単独ではクロス集計をサポートしていないので、SQLだけで直接上記のような表を作成することはできないと思います。


この場合は、SQLで日付(1か月分)およびIPアドレスごとの件数を集計した上で、その結果をSQL以外の手段(PHP等)で縦横の表に整形するのが良いかと思います。


SQL文:

select

ipaddr,

date_trunc(’day’, date) as date,

count(*) as click

from

click

where

date_trunc(’month’, date) = ’2006-01-01’;


検索結果:

ipaddr | date | click |

----------+---------+------+

AA.BB.CC.DD |2006-01-01 | 1|

AA.BB.CC.DD |2006-01-02 | 5|

EE.FF.GG.HH |2006-01-01 | 2|

EE.FF.GG.HH |2006-01-02 | 3|

◎質問者からの返答

うーん・・・

無いかな・・・


2 ● birdie-brain
●0ポイント

前の回答ですが、group by句が抜けていたような気が。。。


select

ipaddr,

date_trunc(’day’, date) as date,

count(*) as click

from

click

where

date_trunc(’month’, date) = ’2006-01-01’

group by

ipaddr, date_trunc(’day’, date);

◎質問者からの返答

この質問をしてから自分で考えた力技もあるんですけど、

SELECT

ipaddr,

count( CASE WHEN date >= ’2006-01-01’ AND date < ’2006-01-02’ THEN TRUE END) AS day1,

count( CASE WHEN date >= ’2006-01-02’ AND date < ’2006-01-03’ THEN TRUE END) AS day2,

count( CASE WHEN date >= ’2006-01-03’ AND date < ’2006-01-04’ THEN TRUE END) AS day3,

count( CASE WHEN date >= ’2006-01-04’ AND date < ’2006-01-05’ THEN TRUE END) AS day4,

::

::

count( CASE WHEN date >= ’2006-01-31’ AND date < ’2006-02-01’ THEN TRUE END) AS day31

FROM

click

GROUP BY

ipaddr

;

こんな感じので

この場合だと 月によってCASE WHEN の量も変わってきますし、いまいち汎用性が薄いのです。

これをもっと美しく書くことは出来ないかなと思っているんですが。。。


3 ● takeshijoe
●70ポイント

わたしも考えましたが、同じような感じにしかならなかったですよ。

でも(ためしてみてないですが)↓の感じで上手く動けば、1か所直すだけですむかも。


select ipaddr,

count(case when to_char(date, ’YYYYMMDD’) like ’%01’ then 1 else null end) as ’01’,

count(case when to_char(date, ’YYYYMMDD’) like ’%02’ then 1 else null end) as ’02’,

count(case when to_char(date, ’YYYYMMDD’) like ’%03’ then 1 else null end) as ’03’,

count(case when to_char(date, ’YYYYMMDD’) like ’%04’ then 1 else null end) as ’04’,

count(case when to_char(date, ’YYYYMMDD’) like ’%05’ then 1 else null end) as ’05’,

count(case when to_char(date, ’YYYYMMDD’) like ’%06’ then 1 else null end) as ’06’,

count(case when to_char(date, ’YYYYMMDD’) like ’%07’ then 1 else null end) as ’07’,

count(case when to_char(date, ’YYYYMMDD’) like ’%08’ then 1 else null end) as ’08’,

count(case when to_char(date, ’YYYYMMDD’) like ’%09’ then 1 else null end) as ’09’,

count(case when to_char(date, ’YYYYMMDD’) like ’%10’ then 1 else null end) as ’10’,

count(case when to_char(date, ’YYYYMMDD’) like ’%11’ then 1 else null end) as ’11’,

count(case when to_char(date, ’YYYYMMDD’) like ’%12’ then 1 else null end) as ’12’,

count(case when to_char(date, ’YYYYMMDD’) like ’%13’ then 1 else null end) as ’13’,

count(case when to_char(date, ’YYYYMMDD’) like ’%14’ then 1 else null end) as ’14’,

count(case when to_char(date, ’YYYYMMDD’) like ’%15’ then 1 else null end) as ’15’,

count(case when to_char(date, ’YYYYMMDD’) like ’%16’ then 1 else null end) as ’16’,

count(case when to_char(date, ’YYYYMMDD’) like ’%17’ then 1 else null end) as ’17’,

count(case when to_char(date, ’YYYYMMDD’) like ’%18’ then 1 else null end) as ’18’,

count(case when to_char(date, ’YYYYMMDD’) like ’%19’ then 1 else null end) as ’19’,

count(case when to_char(date, ’YYYYMMDD’) like ’%20’ then 1 else null end) as ’20’,

count(case when to_char(date, ’YYYYMMDD’) like ’%21’ then 1 else null end) as ’21’,

count(case when to_char(date, ’YYYYMMDD’) like ’%22’ then 1 else null end) as ’22’,

count(case when to_char(date, ’YYYYMMDD’) like ’%23’ then 1 else null end) as ’23’,

count(case when to_char(date, ’YYYYMMDD’) like ’%24’ then 1 else null end) as ’24’,

count(case when to_char(date, ’YYYYMMDD’) like ’%25’ then 1 else null end) as ’25’,

count(case when to_char(date, ’YYYYMMDD’) like ’%26’ then 1 else null end) as ’26’,

count(case when to_char(date, ’YYYYMMDD’) like ’%27’ then 1 else null end) as ’27’,

count(case when to_char(date, ’YYYYMMDD’) like ’%28’ then 1 else null end) as ’28’,

count(case when to_char(date, ’YYYYMMDD’) like ’%29’ then 1 else null end) as ’29’,

count(case when to_char(date, ’YYYYMMDD’) like ’%30’ then 1 else null end) as ’30’,

count(case when to_char(date, ’YYYYMMDD’) like ’%31’ then 1 else null end) as ’31’

from click

where date between ’2006-01-01’ and ’2006-01-31’

group by ipaddr;

◎質問者からの返答

ふむふむ・・

なるほど、手っ取り早く使えそうです。

私の書いたほうはクエリ全体の処理時間を極力短く出来るように考えていたので、型変換が無いほうが処理速度は速かったです。

いつかPostgreSQLでもクロス集計が使えたらよいなあ。

ありがとうございました。

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ