From: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
---|---|
To: | Nicolas Beuzeboc <nicolasb(at)norchemlab(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Special grouping on sorted data. |
Date: | 2008-09-23 04:27:25 |
Message-ID: | 48D8702D.7010308@postnewspapers.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Nicolas Beuzeboc wrote:
> Hi,
>
> I was looking for a simple solution to this problem. I can't find a way
> to group on b and n by just collapsing sequential n's (identical n's
> right next to each other) the sorting condition is the timestamp.
>
> b | n | stamp
> ----------------------------------------
> A | 1 | 2008-09-20 06:07:47.981445 [1]
> A | 1 | 2008-09-20 06:08:13.294306 [1]
> A | 1 | 2008-09-20 06:12:02.046596 [1]
> A | 2 | 2008-09-20 06:12:26.267786 [2]
> A | 2 | 2008-09-20 06:12:47.750429 [2]
> A | 1 | 2008-09-20 06:13:12.152512 [3]
> A | 2 | 2008-09-20 06:13:39.052528 [4]
> A | 2 | 2008-09-20 06:14:12.875389 [4]
> B | 1 | 2008-09-20 06:14:29.963352 [5]
> B | 1 | 2008-09-20 06:14:52.247307 [5]
> B | 3 | 2008-09-20 06:15:13.358151 [6]
> B | 3 | 2008-09-20 06:15:44.307792 [6]
> B | 3 | 2008-09-20 06:16:17.32131 [6]
> B | 2 | 2008-09-20 06:16:44.030435 [7]
> B | 2 | 2008-09-20 06:17:00.140907 [7]
> C | 1 | 2008-09-20 06:17:50.067258 [8]
> C | 1 | 2008-09-20 06:18:22.280218 [8]
> C | 1 | 2008-09-20 06:18:41.661213 [8]
> C | 1 | 2008-09-20 06:19:07.920327 [8]
> C | 3 | 2008-09-20 06:19:26.166675 [9]
> C | 2 | 2008-09-20 06:19:46.459439 [10]
> C | 2 | 2008-09-20 06:20:04.634328 [10]
I'd be tempted to use a set-returning PL/PgSQL function to process an
input set ordered by stamp and return a result whenever the (b,n) pair
changed. I'm sure there's a cleverer set-oriented approach, but it's
eluding me at present.
You need a way to express the notion of "contiguous runs of (b,n)" which
doesn't really exist in (set-oriented) SQL.
> Here I give an example of the output I'm looking for, And I can find a
> way to do that in crystal report, but I would like postgresql to send it
> that way. If the next n is different create a new row.
I suspect that Crystal Reports may be pulling the whole data set from
PostgreSQL then doing its processing client-side.
Try turning on query logging in the server and running your report. See
what SQL Crystal Reports actually executes.
--
Craig Ringer
From | Date | Subject | |
---|---|---|---|
Next Message | Louis-David Mitterrand | 2008-09-23 08:18:30 | Re: exclusion query |
Previous Message | Craig Ringer | 2008-09-23 03:54:18 | Re: Multi-line text fields |