sql,tsql,sql-server-2008-r2,aggregate-functions,percentile

I like to do these calculations directly, using row_number()/rank() and window functions. The built-in functions are useful, but they don't actually save that much effort: SELECT id, MIN(CASE WHEN seqnum >= 0.9 * cnt THEN x END) as percentile_90 FROM (select t.*, row_number() over (partition by id order by x)...

sql,oracle,join,nested-queries,percentile

Try this query, it gave me desired output for your examples. select distinct sttr.operation, tss.val1, tss.val2, tss.val3, percentile_disc(0.9) within group (order by sttr.val asc) over (partition by sttr.operation) as "90th Percentile" from sttr join exe on exe.tstart < sttr.time and sttr.time < exe.tend join tss on sttr.operation = tss.step where...

excel,excel-formula,percentile

In Excel 2010 or later versions you can use AGGREGATE function for this, AGGREGATE has an option to ignore error values, so you can use this formula =AGGREGATE(16,6,A1:A10,0.9) See help for AGGREGATE function for more details but 16 denotes the function type - in this case PERCENTILE.INC (the equivalent of...

python,statistics,median,percentile,wolframalpha

You have 7 numbers which you are attempting to split into quartiles. Because 7 is not divisible by 4 there are a couple of different ways to do this as mentioned here. Your way is the first given by that link, wolfram alpha seems to be using the third. Numpy...

It's always a good idea to read the help pages: > centiles(h,xvar=abdom$x, cent=c(10,50,90), points=FALSE) % of cases below 10 centile is 8.688525 % of cases below 50 centile is 50.16393 % of cases below 90 centile is 90 ...

r,histogram,percentile,quantile

Still difficult to figure out what you are trying to accomplish, but this is my best guess: # create reproducible example - you already have this... set.seed(1) df <- data.frame(Y=sample(0:1,100000,replace=T), TOTA=runif(100000,0,18938000)) na <- sample(1:100000,5000) # 5% NA df[na,]$TOTA <- NA # you start here... breaks <- c(0,10^(2:6), 5938000, 10938000, 15938000,...

This may be helpful. Using group_by, ntile in dplyr, and your ifelse statement, I came up with the following. library(dplyr) group_by(mydf, vch1, vbin1) %>% mutate(check = ntile(vint1, 100), out = ifelse(check > 99, "cat3", ifelse(between(check, 95, 99), "cat2", ifelse(between(check, 90, 95), "cat1", "N")))) %>% ungroup() # A part of the...

sql,database,ms-access,percentile

Solved what was needed by implementing the following: SELECT Point, MAX(Values) FROM Table WHERE Values IN( SELECT TOP 75 PERCENT Values FROM Table dummy WHERE dummy.Report = "Ammonia" AND Table.Report=dummy.Report ORDER BY dummy.Values ASC ) GROUP BY Point ...

If you want to ROUND the results, you can calculate your own value to use in TOP (granted, this means that you need to first count the rows of your table, but it's the only way that I can think of doing this, since there isn't a setting for this):...

See if this makes sense. The 90th percentile of a normal distribution with mean and sd of 'smn' and 'ssd' is qnorm(.9, smn, ssd): So this seems to deliver (somewhat) sensible results, albeit not the full hack of centiles that I suggested: plot(h$xvar, qnorm(.9, fitted(h), h$sigma.fv)) (Note the massive overplotting...

Your approach is correct , but prctile expects percentages (between 0 and 100). So: a = prctile(tcIED.', 90); Or, equivalently, use quantile with proportions (between 0 and 1): a = quantile(tcIED.', 0.9); For example, with your data you get >> a(1) ans = 6190 which means that about 90% of...

for 50% (halfway) the formula is simply result = (a + b) / 2 in general for a generic percentage k the formula is result = (a*(100-k) + b*k) / 100 ...

Tae-Sung Shin is correct. Percentile from Histogram is the best way to do this.

This is how I did it. I changed a few of the variable names to make the context clearer. var dataSet = new List<double> { 1, 2, 3, 2 }; double denominator = dataSet.Count - 1; var uniqueValues = dataSet.Distinct(); var vp = dataSet.Select(value => new VP { Value =...

I think the datatypes of Cnt and Rnk are INT and hence the result gives you the least value of INT type which is 0. Try casting Cnt and Rnk to FLOAT (cast(Cnt - Rnk) as FLOAT)/CAST(Cnt as FLOAT) ...

Simulate data (reproducibly): set.seed(1001) mydata <- data.frame( age = sample(5:75, 500, replace=TRUE)) mydata <- transform(mydata, yvar = rnorm(500, age, 20)) Since the LMS method typically appears to be based on variants of the Box-Cox transformation, which requires positive values, a simpler way to do this would be to use quantile...

(After changing "value" to "param") ddf = data.frame(gp1, gp2, param) ddf$category <- with(ddf, ave(param, gp1,gp2, FUN=function(x) x > quantile(x,.95) ) ) > ddf gp1 gp2 param category 1 2 a 20 0 2 2 a 16 0 3 1 a 12 0 4 1 b 16 0 5 3 b...

python,python-2.7,matplotlib,boxplot,percentile

To draw the box plot using just the percentile values and the outliers ( if any ) I made a customized_box_plot function that basically modifies attributes in a basic box plot ( generated from a tiny sample data ) to make it fit according to your percentile values. The customized_box_plot...