A function that partitions a result set and computes something within that partition without rolling up the data in that partition.

learn more… | top users | synonyms

0
votes
1answer
28 views

PostgreSQL extending UPDATE query

I have the following update query to populate a table storing user stats users: int (pk) user_id date last_visit int (fk) local_site sites: int (pk) site_id site_stats: int (fk) site_id ...
0
votes
1answer
39 views

row_number over partition at Version < Firebird 3

I´m currently at the end. Given facts: ID ID2 FROM TO COSTS 4985 501671 2013-07-25 00:00:00 2013-12-30 00:00:00 17.8 4985 501671 2013-09-05 00:00:00 ...
4
votes
1answer
92 views

“Ranking” values in an ORDER BY clause?

I have the following table: Table "public.employee_employee" Column | Type | Modifiers ...
0
votes
1answer
42 views

How can I get the latest five scores per user per subject?

There is a Postgres table in our system called "scores" which records test scores. It has test takers (users of our system), scores from 0-10, depts and dates each time a test was taken, like this: ...
1
vote
2answers
41 views

Nearest value for each foreign key

I have a table with a primary key, timestamp, value, foreign key. I would like to find the nearest value to a timestamp for each of the foreign keys in a list. Unfortunately, the gap from the given ...
1
vote
1answer
71 views

Retrieve title from rows with a maximum count in other columns

Given the following table archived: CREATE TABLE archived ( sid serial -- row id showurl text -- can be: btg, tmwwt, gcaf, emda title text reference text -- title id ...
1
vote
1answer
119 views

Select row at the nth percentile

I have two tables, table1 and table2. Let the two tables contain date, id and latency column. I have a simple query that performs a join on the two tables and returns a set of rows: Select ...
1
vote
1answer
78 views

numbering rows consecutively for a number of tables

I have a number of tables, all containing attributes aid,bid,cid, and xid of type integer, other attributes may differ. For every (given) table T, I would like to order the rows according to ...
2
votes
3answers
1k views

In Microsoft SQL Server 2008, syntax generates the error “The Parallel Data Warehouse (PDW) features are not enabled.”

I have the following virtual column generated from an aggregate over a sorted partition, MIN(picture_id) OVER ( PARTITION BY [360_set] ORDER BY picture_id ASC ) However, when I execute that, I get ...
4
votes
1answer
514 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
574 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 ...
1
vote
1answer
187 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, ...
4
votes
1answer
264 views

Running total to the previous row

I need some help with windowing functions. I know you can calculate the sum within a window and the running total within a window. But is it possible to calculate the previous running total i.e. the ...
1
vote
1answer
110 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
160 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 ...
1
vote
1answer
1k 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 ...
4
votes
2answers
3k 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
1k 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
305 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
201 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
149 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
163 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
385 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, ...
5
votes
2answers
456 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 ...