« 在宅勤務のお供「BROOK'Sコーヒー」 | メイン | SoftBankの携帯ウィジェットはJavaScript »

2008年11月21日

重複している行から条件に一致した最初の一行を抽出したい(DISTINCT ON)

ログデータの集計などで、条件に一致した最初の一行(最初に現れた一行)のみ抽出したい場合が多々ある。普通であれば、自己結合・サブクエリーなどを使用するが、PostgreSQLであれば、「DISTINCT ON」を使った方が直感的にすっきり書けるし、パフォーマンスも(たぶん)いい(未検証)。

PostgreSQL 8.3.4文書 : SELECT

---
DISTINCT ON ( expression [, ...] )は各行集合の中で、指定した式が等しいと評価した最初の行のみを保持します。

(中略)

例えば、次の例は各地点の最新の気象情報を取り出します。

SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;
---

想定しているのは、このような一部データが欠落しているロギングデータ。location項目が重複しており、11時の時点の金沢のデータがないというのがやっかいだ。

locationtimereport
札幌9時はれ
東京9時くもり
金沢9時くもり
福岡9時あめ
札幌10時はれ
東京10時くもり
金沢10時あめ
福岡10時あめ
札幌11時くもり
東京11時あめ
福岡11時くもり

と紹介してみたものの、なかなか知識の適応は難しい...。頭の片隅でも、ということで。

余談になるが、サブクエリーも使えない場合の代替案としては、

(1)特定の制約がある項目を含んだ一時テーブルを作成して、INSERTしながら重複を抑制...。

(2)プログラム側のハッシュ配列で、既に任意条件であるキーが存在するか検査をして、重複を抑制...。

(3)ハッシュすら使わず、for文でループ回してif文で重複検査...。

うーん、(3)はリアルで見たことがあり、もう愕然としたのをよく覚えている。