0

Lets say that I have a table that looks like

1 | Test   | Jan 10, 2017  
...

10000 | Test   | Jan 20, 2030

and I want to bucket the records in the table based on the 2nd column with a set amount of 10 buckets regardless of the values of the dates. All I require is that each bucket covers a time range of equal length.

I understand that I could do something with

GROUP BY
    YEAR(datefield),
    MONTH(datefield),
    DAY(datefield),
    HOUR(datefield),

and subtract the largest datefield with the smallest datefield and divide by 10 to get the time length covered in each bucket. However, is there already built-in functionality in MySQL that would do this as doing the manual subtraction and division might lead to smaller edge cases. Am I on the right track by doing the subtraction and division for bucketing into a constant number of buckets?

Andy
  • 942
  • 1
  • 10
  • 23
  • http://rpbouman.blogspot.com/2008/07/calculating-nth-percentile-in-mysql.html – shawnt00 Mar 09 '15 at 04:24
  • http://stackoverflow.com/questions/1057027/calculating-percentile-rank-in-mysql – shawnt00 Mar 09 '15 at 04:27
  • If you're really just splitting the results buckets that are based on one tenth of the time span then it's probably easier to just do the date math as you're describing. In that case you're not evenly dividing the rows into equal-size buckets so it's a different problem. – shawnt00 Mar 09 '15 at 04:31

0 Answers0