window function help

From: "Schnabel, Robert D(dot)" <schnabelr(at)missouri(dot)edu>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: window function help
Date: 2014-04-03 15:27:53
Message-ID: C20A71F48B88EF419C0DC4A551498446AE8CAFCA@UM-MBX-N03.um.umsystem.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I'm trying to figure out how to count the number of rows within a fixed range of the current row value. My table looks like this:

SELECT chr_pos
FROM mutations_crosstab_9615_99
WHERE bta = 38
LIMIT 10

chr_pos
138
140
163
174
187
187
188
208
210
213

chr_pos is integer and represents the base pair position along a chromosome.

It looks to me like a window function would be appropriate but I cannot figure out the correct syntax. What I want to do is count the number of rows within +/- 20 of chr_pos (the current row). Given the above example, for chr_pos = 138 I want the count of rows between 118 and 158. For chr_pos 187 I want the count of rows between 167 and 207 etc for all rows. The result I'm looking for should look like the following:

chr_pos,num_variants
138,2
140,2
163,2
174,4
187,4
187,4
188,4
208,6
210,3
213,1

Is there a way to do this with a window function? Any help would be appreciated.

Thanks
Bob

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kasahara Tatsuhito 2014-04-03 15:31:22 Re: size of interval type
Previous Message Torsten Förtsch 2014-04-03 15:19:56 Re: Pagination count strategies