Window function trouble
От | Harald Fuchs |
---|---|
Тема | Window function trouble |
Дата | |
Msg-id | pud42mahnr.fsf@srv.protecting.net обсуждение исходный текст |
Список | pgsql-sql |
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?
В списке pgsql-sql по дате отправления: