SHOW:
|
|
- or go back to the newest paste.
| 1 | ||
| 2 | - | /* AVG */ |
| 2 | + | with boobies as (select Rate, ntile(20) over(order by Rate) as n, count(*) over(order by rate) as x from RateCalculatorLookups) |
| 3 | - | -- AVG(Rate) OVER (PARTITION BY group_Id) AS AvgRate |
| 3 | + | --select * from boobies |
| 4 | - | -- 2 89,9090909090909 |
| 4 | + | /* |
| 5 | - | -- 3 58,3333333333333 |
| 5 | + | rate, n, x |
| 6 | NULL 1 3 | |
| 7 | - | /* NTILE */ |
| 7 | + | NULL 1 3 |
| 8 | - | with truncatedSet as (select Rate, ntile(10) over(order by Rate) as n from RateCalculatorLookups where rate is not null and group_id = 3) |
| 8 | + | NULL 2 3 |
| 9 | - | select * from truncatedSet |
| 9 | + | 10 2 4 |
| 10 | - | -- 50 1 -- gets removed |
| 10 | + | 14 3 5 |
| 11 | - | -- 50 2 |
| 11 | + | 15 3 6 |
| 12 | - | -- 50 3 |
| 12 | + | 20 4 7 |
| 13 | - | -- 50 4 |
| 13 | + | 37 4 8 |
| 14 | - | -- 50 5 |
| 14 | + | 42 5 9 |
| 15 | - | -- 100 6 -- should be removed |
| 15 | + | 44 5 10 |
| 16 | 45 6 11 | |
| 17 | - | select avg(Rate) from truncatedSet where n between 2 and 19 |
| 17 | + | 50 6 16 |
| 18 | - | -- 60 |
| 18 | + | 50 7 16 |
| 19 | 50 7 16 | |
| 20 | 50 8 16 | |
| 21 | 50 8 16 | |
| 22 | 51 9 18 | |
| 23 | 51 9 18 | |
| 24 | 55 10 20 | |
| 25 | 55 10 20 | |
| 26 | 56 11 21 | |
| 27 | 58 11 22 | |
| 28 | 59 12 23 | |
| 29 | 65 13 25 | |
| 30 | 65 14 25 | |
| 31 | 73 15 26 | |
| 32 | 85 16 27 | |
| 33 | 87 17 28 | |
| 34 | 91 18 29 | |
| 35 | 100 19 30 | |
| 36 | 900 20 31*/ | |
| 37 | select avg(Rate) from boobies where (x >= 20 and n between 2 and 19) or (x < 20 and n between 2 and x-1) | |
| 38 | -- 52,8888888888889 |
