Re: window function help

Andy Colson wrote
> On 4/3/2014 11:09 AM, David Johnston wrote:
>> 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.
>>
>
>
>
> > 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.
>
> Ah, data entry error. I didn't even notice. Oops.
>
> > The OP wanted to show the duplicate row - which yours does and mine
> does not
>
> Did you post a sql statement? I didn't seem to get it.
>
> > - but depending on how many duplicates there are having to run the same
>
> Agreed. If there are a lot of dups, we could probably speed this up.
>
> -Andy

My original seems to be held up for some reason...

Let me try again:

WITH val (value) AS (
VALUES (138),(140),(163),(174),(187),(187),(188),(208),(210),(213)
)
SELECT value, (SELECT count(*) FROM val AS valcheck WHERE valcheck.value
BETWEEN src.value - 20 AND src.value + 20)
FROM (
SELECT DISTINCT value FROM val
) src
ORDER BY 1;

--
View this message in context: http://postgresql.1045698.n5.nabble.com/window-function-help-tp5798526p5798565.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.