CODEWITHSUNDEEP
phpmysqlarraysstatistics
Jachym
Jachym

Reputation: 485

Advanced statistics in PHP and MySQL

I have a slight problem. I have a dataset, which contains values measured by a weather station, which I want to analyze further using MySQL database and PHP. Basically, the first column of the db contains the date and the other columns temperature, humidity, pressure etc. Now, the problem is, that for the calculation of the mean, st.dev., max, min etc. it is quite simple. However there are no build-in commands for other parameters which I need, such as kurtosis etc.

What I need is for example to calculate the skewness, mean, stdev etc. for the individual months, then days etc. For the build-in functions it is easy, for example finding some of the parameters for the individual months would be:

SELECT AVG(Temp), STD(Temp), MAX(Temp)
FROM database
GROUP BY YEAR(Date), MONTH(Date)

Obviously I cannot use this for the more advanced parameters. I thought about ways of achieving this and I could only think of one solution. I manually wrote a function, which processes the values and calculates the things such as kurtosis using the particular formulae. But, what that means is that I would need to create arrays of data for each month, day, etc. depending on what I am currently calculating. So for example, i would first need to take the data and split it into arrays lets say Jan11, Feb11, Mar11...... and each array would contain the data for that month. Then I would apply the function on those arrays and create new variables with the result (lets say kurtosis_jan11, kurtosis_feb11 etc.)

Now to my question. I need help with the splitting of data. The problem is that I dont know in advance which month the data starts and which it ends, so I cannot set fixed variables for this. The program first has to check the first month and then create new array for each month, day etc. until it reaches the last record. And for each it would create the array.

That of course would be maybe one solution but if anyone has any other ideas about how to go around this problem I would very much appreciate your help.

Upvotes: 1

Views: 463

Answers (2)

Momin
Momin

Reputation: 868

SELECT AVG(Temp), STD(Temp), MAX(Temp)
FROM database
GROUP BY YEAR(Date), MONTH(Date)

having date between date_from and date_to

I think you want a group of data in between a data range.

Upvotes: 1

Alex Barroso
Alex Barroso

Reputation: 859

You can do more complex queries to achieve this. Here are some examples http://users.drew.edu/skass/sql/ , including Skew

Upvotes: 1

Related Questions