Re: Select

fritz-bayer@xxxxxx wrote: 
Hello,

I have the following table, which stores simple HTTP requests:

   Column    |           Type           | Modifiers
--------------+--------------------------+-----------
 ip           | character varying(254)   |
 host         | character varying(254)   | not null
 uri          | text                     | not null
 referer      | text                     |
 agent        | text                     |
 created      | timestamp with time zone |

I would like to list all UNIQUE requests for a period of time and group
them by the date.

Non unique requests are those, which have the same ip, the same host,
same uri, referer, user agent and occur within +/- 1 second.

Can anybody help me to figure out the quers? I have only managed to
select NON unique requests and group and oder them by day using the
following sql statement:

select to_char(created, 'YYYY-MM-DD') as d, count(*) from user_requests
where host='www.examplehost.com' and created >= '2005-08-10' and
created <= '2005-08-18' group by d order by d;

How do I have to modify this statement so that it won't show records
having the same  ip, host, uri, referer and agent occurring in the same
second (better would be +/-1)?

Fritz


Untested. (And you have to change abs(created1-created2)<1 second

SELECT to_char(a.created,'YYYY-MM-DD') as d, count(*) FROM (SELECT created FROM user_requests u1 JOIN user_requests u2 USING (ip, host, uri, referer, agent) WHERE ABS(u1.created-u2.created)<1 second GROUP BY ip, host, uri, referer, agent HAVING count(*)>1) as a GROUP BY d ORDER BY d

Regards
Stefan
.