Larry Martell grouping by the difference between values in rows
Jan 12, 2014; 14:17
Larry Martell
grouping by the difference between values in rows
I've been asked to do something that I do not think is possible in SQL.
I have a query that has this basic form:
SELECT a, b, c, d, AVG(e), STD(e), CONCAT(x, ',', y) as f FROM t GROUP BY a, b, c, d, f
x and y are numbers (378.18, 2213.797 or 378.218, 2213.949 or 10053.490, 2542.094).
The business issue is that if either x or y in 2 rows that are in the same a, b, c, d group are within 1 of each other then they should be grouped together. And to make it more complicated, the tolerance is applied as a rolling continuum. For example, if the x and y in a set of grouped rows are:
1 through 6 get combined because all their X values are within the tolerance of some other X in the set that's been combined. 7's Y value is within the tolerance of 2's Y, so that should be combined as well. 8 is not combined because neither the X or Y value is within the tolerance of any X or Y in the set that was combined.
In python I can easily parse the data and identify the rows that need to be combined, but then I've lost the ability to calculate the average and std. The only way I can think of to do this is to remove the grouping from the SQL and do all the grouping and aggregating myself. But this query often returns 20k to 30k rows after grouping. It could easily be 80k to 100k rows that I have to process if I remove the grouping and I think that will be very slow.
Anyone have any ideas?
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Jan 12
Peter Brawley Re: grouping by the difference between values in rows
Jan 12, 2014; 13:47
Peter Brawley
Re: grouping by the difference between values in rows
Jan 12
Larry Martell Re: grouping by the difference between values in rows
Jan 12, 2014; 22:13
Larry Martell
Re: grouping by the difference between values in rows
Jan 13
Peter Brawley Re: grouping by the difference between values in rows
Jan 13, 2014; 01:43
Peter Brawley
Re: grouping by the difference between values in rows
Jan 13
Larry Martell Re: grouping by the difference between values in rows
Jan 13, 2014; 13:31
Larry Martell
Re: grouping by the difference between values in rows
Jan 15
shawn l.green Re: grouping by the difference between values in rows
Jan 15, 2014; 18:06
shawn l.green
Re: grouping by the difference between values in rows
Jan 15
Larry Martell Re: grouping by the difference between values in rows
Jan 15, 2014; 21:42
Larry Martell
Re: grouping by the difference between values in rows
Jan 21
hsv Re: grouping by the difference between values in rows
Jan 21, 2014; 01:38
hsv
Re: grouping by the difference between values in rows
Jan 22
Takeshi Hashimoto Re: grouping by the difference between values in rows
Jan 22, 2014; 08:03
Takeshi Hashimoto
Re: grouping by the difference between values in rows
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!