SQLでuniqコマンド的なことをやってみる

いや、DISTINCTではないのです。

諸事情あり、重複が存在する行から、重複した行だけを取り除いたものを取得したいなあと思ったわけです。

たとえば以下のようなテーブルtype_logsがあったとします。

created_at	type
2009-07-29 20:00:00	A
2009-07-30 12:00:00	A
2009-07-30 16:00:00	B
2009-07-30 20:00:00	C
2009-07-31 12:00:00	B
2009-07-31 16:00:00	B
2009-07-31 20:00:00	B
2009-08-01 12:00:00	C
2009-08-01 16:00:00	C

ここから、以下のようなデータを欲しいわけです。

	A
	B
	C
	B
	C

単に重複を取り除くだけであれば、

SELECT DISTINCT type FROM type_logs;

とやれば良いのだけど、それだと、

	A
	B
	C

みたいになってしまって、僕がやりたいこととは違う。

というわけで、あまり得意ではないSQL、いろいろ考えてやってみたのが以下です。

SELECT type FROM
  (
  SELECT
    created_at,
    type,
    (
      SELECT
        t1.type
      FROM
        type_logs AS t1
      WHERE
        t1.created_at < t2.created_at
      ORDER BY
        created_at DESC
      LIMIT 1
    ) AS previous_type
  FROM
    type_logs AS t2
  ORDER BY
    created_at
) as type_with_previous_type
WHERE
  type <> previous_type
  OR
  previous_type IS NULL
ORDER BY
  created_at
;

少し解説すると、

SELECT
  t1.type
FROM
  type_logs AS t1
WHERE
  t1.created_at < t2.created_at
ORDER BY
  created_at DESC
LIMIT 1

の部分で、「ひとつ前のtype」を取得します。

ので、

SELECT
  created_at,
  type,
  (
    SELECT
      t1.type
    FROM
      type_logs AS t1
    WHERE
      t1.created_at < t2.created_at
    ORDER BY
      created_at DESC
    LIMIT 1
  ) AS previous_type
FROM
  type_logs AS t2
ORDER BY
  created_at

は、

created_at	type	previous_type
2009-07-29 20:00:00	A
2009-07-30 12:00:00	A	A
2009-07-30 16:00:00	B	A
2009-07-30 20:00:00	C	B
2009-07-31 12:00:00	B	C
2009-07-31 16:00:00	B	B
2009-07-31 20:00:00	B	B
2009-08-01 12:00:00	C	B
2009-08-01 16:00:00	C	C

となります。

で、その集合に対して、typeがprevious_typeとは違うものになった行だけを、つまりいままでと違うtypeが出現した行だけを取得するという感じです。

type <> previous_type

だけだとprevious_typeがNULLの行が取れないので、

previous_type IS NULL

も追加する感じですね。

さて、そんなわけで書いてみましたが、さっきも書いたように、そんなにSQLには自信があるわけではありません。ので「もっとこうできるよ」的なナイスな通りすがりの人とかは、是非直してくれると良いような気がします。

書き忘れましたが、DBはPostgreSQL 8.1.11です(こういうの書き忘れるとえらい怒られたりする)。