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
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 |