Re: window function help

On 4/3/2014 10:27 AM, Schnabel, Robert D. wrote:
> 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
lookslike 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
todo is count the number of rows within +/- 20 of chr_pos (the current row).  Given the above example, for chr_pos =
138I 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
allrows.  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
>
>
>
>

Don't think a window function is needed, how about this:

select chr_pos, (
   select count(*)
   from mutant b
   where b.chr_pos between a.chr_pos-20 and a.chr_pos+20
   )
from mutant a;

Here's what I get.  I dont remember if "between" is inclusive on both
sides or not, but you can change it to suit your needs.

This is the answer I got, which is different than yours, but I think its
right.


  chr_pos | count
---------+-------
      138 |     2
      140 |     2
      163 |     2
      174 |     4
      187 |     3
      188 |     4
      208 |     5
      210 |     4
      212 |     4
      213 |     4
(10 rows)


-Andy