Datensätze nach timestamp gruppieren

Hallo zusammen,

ich brauche mal wieder den Rat der SQL-Experten :slight_smile:
Ich habe in einer Tabelle eine Reihe von Datensätzen, die ich gerne statistisch nach Tagen/Moanten/Jahren auswerten möchte. Die Tabelle hat folgenden Aufbau:

CREATE TABLE activity
(
  id bigserial NOT NULL,
  mtime timestamp without time zone DEFAULT now(),
  object_id bigint,
  action character varying(1),
  remark character varying(4096),
  CONSTRAINT activity_pkey PRIMARY KEY (id)
)

Im Feld “object_id” wird ein Fremdschlüssel einer anderen Tabelle gespeichert.
Im Feld “action” wird ein “v” für view oder ein “r” für request gespeichert.

Was ich gerne herausfinden möchte ist, wieviele Views hatte ich für ein Objekt und wieviele Requests. Und das ganze zeitlich nach Tagen/Monaten/Jahren verdichtet.
Ich habe bereits versucht, wie in der Doku unter http://www.postgresql.org/docs/9.2/static/functions-datetime.html mit

EXTRACT, date_part

beschrieben die Daten zu erhalten, breche mir dabei aber irgendwie die Finger.
Es wäre superklasse, wenn sich diesem Punkt jmd. annehmen könnte.

Besten Dank, euch ein schönes WE.

LG Andreas



test=# create table murphy(ts timestamp, action  char(1));CREATE TABLE                                              
Time: 1,106 ms                                            
test=*# insert into murphy select now()+random()*10000*'1minute'::interval, case when random() > 0.5 then '1' else '0' end from generate_Series(1,1000);                                                                                                                                              
INSERT 0 1000                                                                                                                                      
Time: 3,855 ms                                                                                                                                     
test=*# select date_trunc('day', ts), action, count(action) from murphy group by 1,2 order by 1,2;
     date_trunc      | action | count
---------------------+--------+-------
 2013-04-12 00:00:00 | 0      |    21
 2013-04-12 00:00:00 | 1      |    19
 2013-04-13 00:00:00 | 0      |    68
 2013-04-13 00:00:00 | 1      |    73
 2013-04-14 00:00:00 | 0      |    74
 2013-04-14 00:00:00 | 1      |    72
 2013-04-15 00:00:00 | 0      |    68
 2013-04-15 00:00:00 | 1      |    78
 2013-04-16 00:00:00 | 0      |    55
 2013-04-16 00:00:00 | 1      |    66
 2013-04-17 00:00:00 | 0      |    70
 2013-04-17 00:00:00 | 1      |    69
 2013-04-18 00:00:00 | 0      |    61
 2013-04-18 00:00:00 | 1      |    83
 2013-04-19 00:00:00 | 0      |    67
 2013-04-19 00:00:00 | 1      |    56
(16 rows)

Time: 1,432 ms
test=*# select date_trunc('hour', ts), action, count(action) from murphy group by 1,2 order by 1,2 limit 10;
     date_trunc      | action | count
---------------------+--------+-------
 2013-04-12 17:00:00 | 0      |     1
 2013-04-12 18:00:00 | 0      |     3
 2013-04-12 18:00:00 | 1      |     5
 2013-04-12 19:00:00 | 0      |     4
 2013-04-12 19:00:00 | 1      |     4
 2013-04-12 20:00:00 | 0      |     2
 2013-04-12 20:00:00 | 1      |     4
 2013-04-12 21:00:00 | 0      |     3
 2013-04-12 21:00:00 | 1      |     2
 2013-04-12 22:00:00 | 0      |     4
(10 rows)

Time: 1,607 ms
test=*#

Wow, beeindruckend…!
Werde das morgen gleich mal testen und gebe Bescheid.

Schon jetzt vielen Dank und einen schönen Abend in die Runde.

LG Andreas

Naja, bei gleichem Vornamen muß man sich doch helfen, oder?

Andreas

Ich weiß nicht, ob das für dich nicht auch nach hinten losgehen könnte smile

Dank dir!

LG Andreas

Habe die Query etwas modifiziert und was soll ich sagen… Rennt wie sau :wink:
Ohne diesen Anstoß hätte ich das wohl auf Client-Seite gemacht.
Besten Dank, schönes WE euch!

LG Andreas