A function that partitions a result set and computes something within that partition without rolling up the data in that partition.
4
votes
1answer
68 views
How do I get the aggregate of a window function in Postgres?
I have a table containing a two columns of permutations/combinations of integer arrays, and a third column containing a value, like so:
CREATE TABLE foo
(
perm integer[] NOT NULL,
combo integer[] ...
10
votes
4answers
267 views
How can I write windowing query which sums a column to create discrete buckets?
I have a table which includes a column of decimal values, such as this:
id value size
-- ----- ----
1 100 .02
2 99 .38
3 98 .13
4 97 .35
5 96 .15
6 95 .57
7 94 .25
...
0
votes
1answer
20 views
Using PL/R for clustering geometries
I have a Postgres/PostGIS database of geography points which I'm trying to cluster; meaning I want to group and count them by proximity. I have had first success with the kmeans Postgresql extension, ...
2
votes
1answer
58 views
SQL Windowing function to create a running total
I need some help with windowing functions.
I have been playing around with sql 2012 windowing functions recently. I know that you can calculate the sum within a window and the running total within a ...
0
votes
1answer
34 views
Filter on a window function without writing an outer SELECT statement
Since window functions cannot be included in the WHERE clause of the inner SELECT, is there another method that could be used to write this query without the outer SELECT statement? I'm using Oracle. ...
4
votes
1answer
77 views
How to get a row_number to have the behavior of dense_rank
I have a stored procedure will be used to page through data. One of the requirements of the procedure is to have a parameter that will be used to sort two columns of data, the sorting should be used ...
0
votes
1answer
147 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
297 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 ...
7
votes
6answers
595 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
148 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
115 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
78 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
139 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
238 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
298 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 ...
