W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.
www.w3schools.com
If you step through this your query knowledge will greatly improve. Very well structured and understandable.
If I take elapsed times I can find things like the median, mode, variance, and distribution of the elapsed times. These things are usually far from normally distributed. Likely you have lots of short events and a few outlier long term events. A distribution of the data is far more telling of what is happening.
Hi @MajP, I am sorry to bother you again, I been busy analysising away, thanks to all your help. I have even managed to adjust the avg elapsed by adjusting the queries slightly, which I would not have been able to do without your guidance, Thanks.
I have checked the access functions and can see by changing your SQL code from avg to MAX, MIN, STD and VAR. However, could you help with getting some of the other statistical functions as you suggested median, mode, variance, and distribution of the elapsed times, please.
I am heading out for the weekend, I can look at this later. Use the PM feature to remind me if I forget. However, the MIN, MAX, and STD should be very easy to do. If you look at qryAvg just replaces Avg with the other keywords
Code:
SELECT qryElapsed.userName, Avg(qryElapsed.Elapsed) AS AvgOfElapsed
FROM qryElapsed
GROUP BY qryElapsed.userName
SELECT qryElapsed.userName, Min(qryElapsed.Elapsed) AS MinOfElapsed
FROM qryElapsed
GROUP BY qryElapsed.userName
SELECT qryElapsed.userName, Max(qryElapsed.Elapsed) AS MaxOfElapsed
FROM qryElapsed
GROUP BY qryElapsed.userName
SELECT qryElapsed.userName, STD(qryElapsed.Elapsed) AS StandardDeviationOfElapsed
FROM qryElapsed
GROUP BY qryElapsed.userName
I have another function I can post later that does percentiles. This will be very beneficial for distributions. Or you drop this into excel and build your histograms there. Then you can show the graphical distribution of elapsed times.
But here is my point that AVG is meaningless. This is an ugly historgram because it is very hard to do a historgram when you have huge outliers. But this is the distribution for 1050 you have a few events below 100. A lot between 100 and 400 and huge outlier of 69K. This means IMO talking about the average is meaningless. Most users will never see anything near the average.
So throwing out the one outlier I get something that is reasonable.
So I can tell the boss the average is around 4K, but that is not what users would expect to see.
"Boss, this is because we had one bad case taking 69K. But as you can see the biggest group is at 100 with a cluster between 100 and 250. There is another cluster of events between 400 and 500 with only one extreme outlier over 900."
This is why I never believe any stats quoted in the news. It is very correct to say the Avg is 4k, it is also completely meaningless without further discussion. Stats may be correct, it is the interpretation that is not.
I am heading out for the weekend, I can look at this later. Use the PM feature to remind me if I forget. However, the MIN, MAX, and STD should be very easy to do. If you look at qryAvg just replaces Avg with the other keywords
Code:
SELECT qryElapsed.userName, Avg(qryElapsed.Elapsed) AS AvgOfElapsed
FROM qryElapsed
GROUP BY qryElapsed.userName
SELECT qryElapsed.userName, Min(qryElapsed.Elapsed) AS MinOfElapsed
FROM qryElapsed
GROUP BY qryElapsed.userName
SELECT qryElapsed.userName, Max(qryElapsed.Elapsed) AS MaxOfElapsed
FROM qryElapsed
GROUP BY qryElapsed.userName
SELECT qryElapsed.userName, STD(qryElapsed.Elapsed) AS StandardDeviationOfElapsed
FROM qryElapsed
GROUP BY qryElapsed.userName
I have another function I can post later that does percentiles. This will be very beneficial for distributions. Or you drop this into excel and build your histograms there. Then you can show the graphical distribution of elapsed times.
But here is my point that AVG is meaningless. This is an ugly historgram because it is very hard to do a historgram when you have huge outliers. But this is the distribution for 1050 you have a few events below 100. A lot between 100 and 400 and huge outlier of 69K. This means IMO talking about the average is meaningless. Most users will never see anything near the average. View attachment 94763
So throwing out the one outlier I get something that is reasonable. View attachment 94767
So I can tell the boss the average is around 4K, but that is not what users would expect to see.
"Boss, this is because we had one bad case taking 69K. But as you can see the biggest group is at 100 with a cluster between 100 and 250. There is another cluster of events between 400 and 500 with only one extreme outlier over 900."
This is why I never believe any stats quoted in the news. It is very correct to say the Avg is 4k, it is also completely meaningless without further discussion. Stats may be correct, it is the interpretation that is not.
Thanks a million @MajP, you are right I'll have deal with the outliers for the ML side of the analysis (more fun ), also going to run it through a DLN. I appreciate the time and the patience, and the great explanations. I am transfering most of the query analysis results to an excel sheet at the mo but the more worthwhile factors I can generate, the better.
Have a great weekend! and thanks again for the help, I pester you during the week, if posting the percentiles slips you mind
www.w3schools.com
), also going to run it through a DLN. I appreciate the time and the patience, and the great explanations. I am transfering most of the query analysis results to an excel sheet at the mo but the more worthwhile factors I can generate, the better.
