Re: window function help

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of David Johnston
Sent: Thursday, April 03, 2014 11:09 AM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] window function help

Andy Colson wrote
> On 4/3/2014 10:27 AM, Schnabel, Robert D. wrote:
>> 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
>>
>
> 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)

Same concept as mine - but I'm not sure where the "212" came from and you did not duplicate the "187" that was present in the original.

The OP wanted to show the duplicate row - which yours does and mine does not
- but depending on how many duplicates there are having to run the same effective query multiple times knowing you will always get the same result seems inefficient. Better to query over a distinct set of values and then, if needed, join that back onto the original dataset.

David J.

Thanks. I had considered this strategy initially but didn't actually try it because I figured it would be too slow and I knew from previous experience with window functions that they are much faster than queries of this nature. My largest chromosome has about 6M position and this ran in 69 seconds which is acceptable since I'll only be doing this infrequently. I should be able to handle it form here. Thanks again for pointing me in the right direction.

Bob