Re: Count of records in a row

[Rémi Cura <remi.cura@xxxxxxxxx>]

Hey,

when using a for you implicitly use a cursor (I think), 

so this is the same, use FOR if you like it more.
It should be *very* fast to write ! 

As I wrote, relational algebra can handle it, but it is not practically feasible :

If you just execute 3 times the query I wrote, you will have your answer.
It is 3 times because the biggest sequence is A A A A.

That's the problem, your number of execution depends on the max size of sequence.

The problems boils down to this : the answer for one row depends on the answer of the previous row, the row before , etc.

You could succeed with ordering by id in a windows function, and in this window function order by new_id and putting null to the end, but such nested windows functions calls are not allowed.

Nevertheless if you find something purely relational please keep me posted !

Cheers,

Rémi-C

2013/10/22 Robert James <srobertjames@xxxxxxxxx>

On 10/22/13, Rémi Cura <remi.cura@xxxxxxxxx> wrote:
> But it is immensely easier and sometimes mandatory to use instead
> a plpgsql function using cursor (or cursors).
>
> It would be something like that in plpgsql :
>
> cursor on table of letter ordered
> accum = 0;
> loop on rows of table ordered
>
> if letter = previous letter, new_id = accum
> else accum ++ ; new_id = accum
>
> old letter = new_letter
> new letter = next letter;
>
> end of loop,

Shouldn't it be possible to do that with a FOR loop without a cursor?

It might be that procedural is the way to go.  But I still believe
that relational algebra can handle this, even without a window
function.  Something like:

SELECT event e, COUNT(
    SELECT event oe ... WHERE oe.event_time > e.event_time AND NOT EXISTS (
             SELECT event te WHERE te.event_time > e.event_time AND
te.event_time < oe.event_time))

.