A function that partitions a result set and computes something within that partition without rolling up the data in that partition.
0
votes
1answer
47 views
Select first row (grouping) + add aggregate function
First have a look at this question on StackOverflow.
I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query.
How can I combine the ...
0
votes
2answers
62 views
MySQL and window functions
It seems that MySQL does not support window functions.
E.g. the simple: COUNT(*) OVER() AS cnt does not work.
What I am not sure is if this applies to commercial version as well (I assume the ...
6
votes
6answers
370 views
Find “n” consecutive free numbers from table
I have some table with numbers like this (status is either FREE or ASSIGNED)
id_set number status
-----------------------
1 000001 ASSIGNED
1 000002 FREE
1 000003 ...
4
votes
2answers
110 views
Select longest continuous sequence
I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specifc field.
Consider the following table:
lap_id (Serial), lap_no (int), car_type ...
1
vote
2answers
94 views
Count events on “user based” sliding window
So, if I wanted to know how many events a certain user bought in certain timeframe windows. I will use 7 for the example, but the query should accept this number of days as a parameter, which will be ...
2
votes
1answer
68 views
sorting groups of related rows by average values while keeping the groups together
I'm using PostgreSQL 8.4, but would like a standard SQL solution if possible.
Consider the following table.
corrmodel=# SELECT * from data limit 1;
id | datasubgroup_id | datafile_id | ...
1
vote
1answer
134 views
Working of window functions and idea window size for window function
I am not able to understand the concept of window functions. How exactly they work and what are the pros and cons of such technique?
I have read that using limit and offset is slow but its still ...
2
votes
3answers
214 views
What is the simplest way of excluding the current row from the result of an analytic?
I want to do something like the following:
with w as ( select level as foo,
decode(level,8,1,mod(level,4))*100 as bar
from dual connect by level<9 )
select foo, ...
4
votes
2answers
269 views
Window functions cause awful execution plan when called from a view with external parametrized 'where' clause
I had this issue long time ago, I found a workaround which suited me and forgot about it.
But now there's that question on SO so I'm willing to bring this problem up.
There's a view that joins few ...
