Let's say I have a table (with millions of rows)
cat1|cat2|value A | B| 10 ...
and I want to group the data by
cat1 and also split the grouping into 10 (at least roughly) equal partitions of the data.
The purpose of the random grouping is for the machine learning technique of cross-validation. (It's similar to splitting subjects into a control group and experimental group.)
What I would imagine to use is the following:
select cat1, r, sum(value) from t group by cat1, floor(rand()*10) as r
which doesn't work.
One way which I believe would work would be simply to insert a new column with rand into my table and then group appropriately on that.
Is there any way of doing this grouping otherwise?