Ingenious!
I actually think, however, there was a subtle bug in, though I see you fixed it.
The line:
- row_number() over () as d
needs to be:
- row_number() over (order by i asc) as d
I discovered this when working your code into my application. I got
very, very wei
On 2013-10-24 17:09, Robert James wrote:
On 10/22/13, Elliot wrote:
It looks like you already found a solution, but here's one with a CTE. I
cobbled this together from an older query I had for doing something
similar, for which I unfortunately lost the original source of this
approach. Also, th
On 10/22/13, Elliot wrote:
> It looks like you already found a solution, but here's one with a CTE. I
> cobbled this together from an older query I had for doing something
> similar, for which I unfortunately lost the original source of this
> approach. Also, this implies that there is something t
Ok thanks for this precision Merlin.
Seems like aggregates are way more powerful than I thought.
Obviously I need a lot more reading about custom aggregates before fully
understanding it.
Elliot's query is pure SQL so obviously very cool !
It could be improved at the margin, and aggregates/funct
> 2013/10/22 Merlin Moncure
>> > With a standard loop, I loop n times, and each times I only need the
>> > current
>> > row plus the previous row which I put in memory, thus O(n).
>>
>> For posterity, the above is incorrect. Since the aggregate is ordered
>> through the window function, it gets e
Wow, this is an excellent discussion - and I must admit, a bit beyond
my abilities. Is there a consensus as to the best approach to adopt?
Is Elliot's the best?
On 10/22/13, Rémi Cura wrote:
> OK,
> just out of pure curiosity,
> is it always the case or is it due to this particular aggregate?
>
OK,
just out of pure curiosity,
is it always the case or is it due to this particular aggregate?
Cheers,
Rémi-C
2013/10/22 Merlin Moncure
> On Tue, Oct 22, 2013 at 9:43 AM, Rémi Cura wrote:
> > Thanks again for the precision !
> >
> > I still don't understand perfectly. We call the aggregate
On Tue, Oct 22, 2013 at 9:43 AM, Rémi Cura wrote:
> Thanks again for the precision !
>
> I still don't understand perfectly. We call the aggregate n times, and each
> time we compute the aggregate, using (potentially) n rows, thus becoming (at
> most) O(n*n).
>
> With a standard loop, I loop n tim
Hmm exactly what I was thinking !
Thank you a lot, I spend many hours thinking about this and this solution
is very nice.
Cheers,
Rémi-C
2013/10/22 Merlin Moncure
> On Tue, Oct 22, 2013 at 10:01 AM, Elliot
> wrote:
> > On 2013-10-21 20:38, Robert James wrote:
> >>
> >> I have a table of even
On Tue, Oct 22, 2013 at 10:01 AM, Elliot wrote:
> On 2013-10-21 20:38, Robert James wrote:
>>
>> I have a table of event_id, event_time. Many times, several events
>> happen in a row. I'd like a query which replaces all of those events
>> with a single record, showing the count.
>>
>> Eg: Take A
On 2013-10-21 20:38, Robert James wrote:
I have a table of event_id, event_time. Many times, several events
happen in a row. I'd like a query which replaces all of those events
with a single record, showing the count.
Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return: A,3; B,1; C,1; A,1;
D,1; A,
Thanks again for the precision !
I still don't understand perfectly. We call the aggregate n times, and each
time we compute the aggregate, using (potentially) n rows, thus becoming
(at most) O(n*n).
With a standard loop, I loop n times, and each times I only need the
current row plus the previou
On Tue, Oct 22, 2013 at 9:09 AM, Rémi Cura wrote:
>
> Thanks for this good example Merlin !
>
> I didn't know you could use variable inside custom aggregates, and this
> allow to solve the problem!
>
> In my own problem I couldn't use aggregates because
> _as it output at most one row, it would ha
Thanks for this good example Merlin !
I didn't know you could use variable inside custom aggregates, and this
allow to solve the problem!
In my own problem I couldn't use aggregates because
_as it output at most one row, it would have mean a lots of useless
computation (as in this example I guess
On Tue, Oct 22, 2013 at 8:41 AM, Rémi Cura wrote:
> héhé,
> nice snipping Merlin !
>
> I guess you are almost there, output is still wrong (should be) (
>> Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return: A,3; B,1; C,1; A,1;
>> D,1; A,2; D,2; B,1; C,2
> )
>
> I don't understand enough to make the
héhé,
nice snipping Merlin !
I guess you are almost there, output is still wrong (should be) (
> Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return: A,3; B,1; C,1; A,1;
> D,1; A,2; D,2; B,1; C,2
)
I don't understand enough to make the modifications =)
Cheers,
Rémi-C
2013/10/22 hubert depesz luba
On pon, paź 21, 2013 at 08:38:52 -0400, Robert James wrote:
> I have a table of event_id, event_time. Many times, several events
> happen in a row. I'd like a query which replaces all of those events
> with a single record, showing the count.
>
> Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return:
On Tue, Oct 22, 2013 at 8:16 AM, Rémi Cura wrote:
> 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 :
>
>
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 a
On 10/22/13, Rémi Cura 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 = prev
Hey,
I tried something very similar to compute generalized union of numeric
range (which was innapropriate, anyway).
My conclusion were that it's not possible using windows function as you
need either a memory (windows function are not allowed in update) or
iterations to propagate information (win
Robert James wrote
> I have a table of event_id, event_time. Many times, several events
> happen in a row. I'd like a query which replaces all of those events
> with a single record, showing the count.
>
> Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return: A,3; B,1; C,1; A,1;
> D,1; A,2; D,2; B,1;
22 matches
Mail list logo
