重複している行から条件に一致した最初の一行を抽出したい(DISTINCT ON)
ログデータの集計などで、条件に一致した最初の一行(最初に現れた一行)のみ抽出したい場合が多々ある。普通であれば、自己結合・サブクエリーなどを使用するが、PostgreSQLであれば、「DISTINCT ON」を使った方が直感的にすっきり書けるし、パフォーマンスも(たぶん)いい(未検証)。
---
DISTINCT ON ( expression [, ...] )は各行集合の中で、指定した式が等しいと評価した最初の行のみを保持します。
(中略)
例えば、次の例は各地点の最新の気象情報を取り出します。
SELECT DISTINCT ON (location) location, time, report FROM weather_reports ORDER BY location, time DESC;---
想定しているのは、このような一部データが欠落しているロギングデータ。location項目が重複しており、11時の時点の金沢のデータがないというのがやっかいだ。
location | time | report |
札幌 | 9時 | はれ |
東京 | 9時 | くもり |
金沢 | 9時 | くもり |
福岡 | 9時 | あめ |
札幌 | 10時 | はれ |
東京 | 10時 | くもり |
金沢 | 10時 | あめ |
福岡 | 10時 | あめ |
札幌 | 11時 | くもり |
東京 | 11時 | あめ |
福岡 | 11時 | くもり |
と紹介してみたものの、なかなか知識の適応は難しい...。頭の片隅でも、ということで。
余談になるが、サブクエリーも使えない場合の代替案としては、
(1)特定の制約がある項目を含んだ一時テーブルを作成して、INSERTしながら重複を抑制...。
(2)プログラム側のハッシュ配列で、既に任意条件であるキーが存在するか検査をして、重複を抑制...。
(3)ハッシュすら使わず、for文でループ回してif文で重複検査...。
うーん、(3)はリアルで見たことがあり、もう愕然としたのをよく覚えている。