SQL（Postgresql）で曜日や週で集計する

　ふと、Postgresqlで曜日や週の集計ができんのかなーと思って、検索してみたところ、あった。過去にも何度も思ったことがあるはずだが）、探し方が悪かったのだろう。postgresql7.2のころから、extract関数というのがあって日付から世紀 century や日 day や十年 decade やら曜日 dow がとりだせる。「extract 関数は日付/時刻の値から年とか時刻などの部分フィールドを抽出します」だそうですよ！（がーん！知らなかった。。。）詳細は「日付関数演算子」などで、postgresqlのドキュメントで探してください。バージョンにはご注意。。

曜日をとりだす例。

select extract(dow from timestamp '2001-02-16 20:38:40');

この場合、 '2001-02-16 20:38:40'をtimestampにして、そこからDOWで曜日をとりだしている。どぅ？どゅ？

勉強用のテーブルをつくる。idはキーがいるかなと思って設定した。注文日と数量なやつ。（英語へのツッコミは慈悲の心をもって。。。）

CREATE TABLE orders
(
  id serial NOT NULL,
  order_date timestamp with time zone,
  amount integer,
  CONSTRAINT orders_pkey PRIMARY KEY (id)
)

order_dateの曜日を求める。

select 
 order_date
,extract(dow from order_date)
,amount
from orders

すると、曜日が、0〜6（日曜日が 0）で求めることができる。（曜日があっているかどうかはカレンダーを見よ！）
ここでぐいっと日本語の曜日で表示したい場合

select 
 order_date
,extract(dow from order_date) 
,(array['日','月','火','水','木','金','土'])[extract(dow from order_date) + 1]
,amount
from orders
order by order_date

など。配列をつくって何番目か、という塩梅なのだが、ARRAYは添字が1かららしいので、プラス１している。

ほぉほぉ。

これは

SELECT (ARRAY['日','月','火','水','木','金','土'])[EXTRACT(DOW FROM CAST('2012/01/25' AS DATE)) + 1]

を参考にしたのだけど、このサイトをどこでみたか忘れてしまった。。すいませぬ。。。

曜日ごとに集計する。
それっぽくするために日付の条件を追加。

select 
 extract(dow from order_date) as day_of_the_week
,(array['日','月','火','水','木','金','土'])[extract(dow from order_date) + 1]
,sum(amount)
from orders
where order_date between '2011/11/01' and '2011/12/01'
group by day_of_the_week
order by day_of_the_week

これで、曜日ごとの集計ができる。

次は週で集計。

参考：ログイン - yanor.net/wiki／ありがたや。。。

まずは、週のはじめを決める。日曜日か月曜日か仕様が求める方（うげ）。それをどうやって求めるか。
日曜日の場合

select
 s1.order_date
,s1.amount
,s1.order_date - day_of_the_week as hogeday
from
(
	select 
	 date(order_date) as order_date
	,amount
	,cast(extract(dow from order_date) as int ) - 0 as day_of_the_week
	from orders
	where order_date between '2011/11/01' and '2011/12/01'
	order by day_of_the_week
)s1
order by order_date

上記の場合、hogeday のところに、order_dateの週の日曜日の日付がでてくる。
月曜日にする場合は、真ん中ぐらいの day_of_the_week で「- 0」と書いてあるところを「- 1」にすればよし。
曜日の数字はゼロからはじまるので、自分の曜日の数字を自分の日付からマイナスすると、日曜日（あるいは月曜日）の数字がでる。注意点は、order_date を timestampにしていたので、そのままだと「日付 - 日数」がいきなり使えない。なので dateにしている。。。あ、cast文法とかあるんすね！！（万年初心者）

週の頭で集計する。

select
 sum(s1.amount)
,s1.order_date - day_of_the_week as hogeday
from
(
	select 
	 date(order_date) as order_date
	,amount
	,cast(extract(dow from order_date) as int ) - 0 as day_of_the_week
	from orders
	where order_date between '2011/11/01' and '2011/12/01'
	order by day_of_the_week
)s1
group by hogeday
order by hogeday

できたー。

だがしかし。ちょっとした見た目の罠がある。

たとえば、日付の範囲指定を2011/11の一ヶ月にしていると、最初の週の週の頭は、10月の日付がでてきてしまう。抽出条件は11月なのに、10月も出てくる。。なので、検索結果を表示する場合は、最初の週の頭の月が変わっている場合は、範囲指定の開始の日付をぶっこんでしまうのが、お客さん？に何かいわれる可能性は低いかも。いやそもそもしかしだよ、週ごとに集計する場合、検索条件がどうなるかってーのはありますが。まま、そこらへんは決めごとなので好きにして。（←だめエンジニアはこういう言い方をする）

上記以外に書き方はあると思いまっするよ。

おまけ。

手動でテストデータをつくるのにせこせこINSERT文を作ったのだが

insert into orders(order_date, amount)
select
 current_timestamp + '-42 days'
,11
;

なりゆきでこんなSQLになった。'-42 days' のところと amountに入る数字「11」を色々かえた。最初のうち、42日前にするのに、さんざん「current_timestamp - '42 days'」と書いて怒られまくったよ。常に「プラス」で日付の数字の前に、マイナス書くらしいっすよ。（たくさん作るならプログラムからつくるべきだも）