年の初めに過去を振り返る
去年の年末は、と言うと随分と遠い昔のようだが、実際は昨日。 除夜の鐘を聞いてすぐに寝てしまったので、朝は平日のいつも通りに目が覚めた。 で、せっかく早く目が覚めたのだから初日の出でも見ようかとベランダに出てみたのだが。
ちょっと早過ぎたようで、外はまだ夜と言った方がしっくりくる暗さ。 東の空はわずかに明るい程度。 振り返れば、西の空の低いところに月が出ていた。 初月の入りか。
寒いので一旦家の中に入り、しばらく待って出直し。 今度はちょうどいいタイミングだったようで、初日の出を最初からじっくり見ることができた。 体は芯から冷えたけど。
初詣の行列。 寒い中に長時間並んで、一体何を願うのか。
普段買い物の行き帰りに使う道がこの状態なので、今日は駅の裏側から。 買い物袋をぶら下げて浅川沿いを歩くのは、ちょっと新鮮だった。
小計と累計
年末のいつだったか、入社3年目ぐらいの若手がデータベース上のデータを集計していたのだが、その方法があまりにもあんまりだった。 今日から昨日、昨日から一昨日、と期間を変えながらselectを繰り返し、それぞれの結果をExcelに手入力して資料を作っていたのだ。
「それ、システム運用開始まで繰り返すの？」
「いや、結構大変なので、とりあえずあと1年分ぐらいにしようと思います」
「大変なのはそんなやり方してるからだろ。 数えてるのは、日毎とか月毎の発生件数なんだよね？」
「そうです」
「だったら、日とか月でgroup byしてcountしたら？ ほら、こんな感じで」
「あっ、僕が1時間かけてやってきたことが一瞬で… しかも全期間…」
「まあ知らなかったならしょうがないけど、俺らシステム屋なんだからさ。 パラメータが違うだけの同じ作業を1時間繰り返すんなら、何か自動化を考えようよ」
そんな説教くさいことを言うついでに 「ちょっと工夫すると累計もとれるよ」 と言っておいたのだが。
その時に想定していたちょっとの工夫って、集計結果の自己結合だった。
仮にeventsテーブルに発生日occured_dateを持つレコードが登録されているとして、こんな感じ。
with count_by_date as (
select occured_date
, count(*) cnt
from events
group by occured_date
)
select a.occured_date
, a.cnt daily_count
, sum(b.cnt) total_count
from count_by_date a
inner join count_by_date b
on a.occured_date >= b.occured_date
group by a.occured_date, a.cnt
order by a.occured_date, a.cnt
これで発生日毎の集計値とその日までの累計値が取れる。
でもこれ、得られる結果は正しいんだけど、それだけなんだよな。 自己結合はウィンドウ関数がまだ一般的じゃなかった時代の名残だし、withがあったりして無駄に複雑。 全然ちょっとじゃない。
ウインドウ関数を使って同じ結果を得るには、例えばこんな感じ。
select distinct
occured_date
, sum(1) over (partition by occured_date) daily_count
, sum(1) over (order by occured_date) total_count
from events
order by occured_date
もう圧倒的に簡単。
何故こんなことを書いてるかというと、今日、退屈な正月番組を見ている最中にふと年末のことを思い出して、ついでに自己結合じゃなくてウインドウ関数を使えば圧倒的に簡単だってことに気が付いたから。 偉そうに 「知らなかったならしょうがないけど」 なんて投げた言葉はブーメランだった。 知ってはいても使うべき場面で思いつかないなら、知らないと同じだし。
年末に投げたブーメランに新年早々後頭部を撃たれて思う。 今年はちょっと頑張ろう。