To: pgsql-sql@postgresql.org
From: Harald Fuchs <hari.fuchs@gmail.com>
Subject: Window function trouble
Date: Thu, 10 Dec 2009 18:00:08 +0100
Lines: 56
Message-ID: <pud42mahnr.fsf@srv.protecting.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.2 (gnu/linux)
Cancel-Lock: sha1:OACeY0CWdXgxi/TP71fYZOuqZEs=

I have a table like this:

  CREATE TABLE tbl (
    host text NOT NULL,
    adr ip4 NOT NULL,
    usr text NOT NULL
  );

(ip4 is from the ip4r contrib module)
and I want the number of entries per address and per user:

  SELECT adr, usr, count(*)
  FROM tbl
  WHERE host = ?
    AND adr <<= ?
  GROUP BY adr, usr
  ORDER BY adr, usr

That's pretty basic stuff and returns something like this:

  adr1 usr1_1 cnt1_1
  adr1 usr1_2 cnt1_2
  adr1 usr1_3 cnt1_3
  adr2 usr2_1 cnt2_1
  ...

But I want the address to be NULL if it's the same as the address of
the previous row. I came up with this:

  SELECT CASE lag(adr) OVER (ORDER BY adr)
         WHEN adr THEN NULL
         ELSE adr
         END AS myaddr,
         usr, count(*)
  FROM tbl
  WHERE host = ?
    AND adr <<= ?
  GROUP BY adr, usr
  ORDER BY adr, usr

This returns something like

  adr1 usr1_1 cnt1_1
  NULL usr1_2 cnt1_2
  NULL usr1_3 cnt1_3
  adr2 usr2_1 cnt2_1
  ...

what's exactly what I want.  But when I don't name the CASE expression
(i.e. I delete "AS myaddr"), I get the following:

  adr1 usr1_1 cnt1_1
  adr2 usr2_1 cnt2_1
  ...

The other users for one address are gone.  Does anyone know why?