window function help
От | Schnabel, Robert D. |
---|---|
Тема | window function help |
Дата | |
Msg-id | C20A71F48B88EF419C0DC4A551498446AE8CAFCA@UM-MBX-N03.um.umsystem.edu обсуждение исходный текст |
Ответы |
Re: window function help
|
Список | 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 likethis: 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 dois count the number of rows within +/- 20 of chr_pos (the current row). Given the above example, for chr_pos = 138 I wantthe 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
В списке pgsql-general по дате отправления: