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 など)を出したいです。よろしくお願いします。

回答の条件
  • URL必須
  • 1人2回まで
  • 登録:2006/02/09 18:09:32
  • 終了:--

回答(3件)

id:birdie-brain No.1

birdie-brain回答回数40ベストアンサー獲得回数42006/02/09 20:33:16

ポイント70pt

縦横が可変の表を抽出するにはクロス集計が必要になりますが、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|

id:kamiochiai

うーん・・・

無いかな・・・

2006/02/10 13:50:51
id:birdie-brain No.2

birdie-brain回答回数40ベストアンサー獲得回数42006/02/09 20:43:03

前の回答ですが、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);

id:kamiochiai

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

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 の量も変わってきますし、いまいち汎用性が薄いのです。

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

2006/02/10 13:59:55
id:takeshijoe No.3

takeshijoe回答回数3ベストアンサー獲得回数02006/02/10 14:24:48

ポイント70pt

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

でも(ためしてみてないですが)↓の感じで上手く動けば、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;

id:kamiochiai

ふむふむ・・

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

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

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

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

2006/02/10 16:53:19

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません