Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
Hi | | ericomtxmacbookpro.local | *E85DC00A0137C6171923BE35EDD809573FB3AB4F | mysql DELETE FROM mysql.user WHERE user=''; mysql FLUSH PRIVILEGES; maybe helps? Cheers -- Claudio
Re: grouping by the difference between values in rows
Hello Larry, On 1/13/2014 2:43 AM, Peter Brawley wrote: On 2014-01-12 9:13 PM, Larry Martell wrote: On Sun, Jan 12, 2014 at 2:47 PM, Peter Brawley peter.braw...@earthlink.net wrote: On 2014-01-12 1:17 PM, Larry Martell wrote: 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: row 1: 1.5, 9.5 row 2: 2.4, 20.8 row 3: 3.3, 40.6 row 4: 4.2, 2.5 row 5: 5.1, 10.1 row 6: 6.0, 7.9 row 7: 8.0, 21.0 row 8: 100, 200 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? Could you compute the row-to-row values write them to a temp table, then run the SQL that incorporates that result column? I thought of temp tables, but I could not come up with a way to use them for this. How can I apply the x/y tolerance grouping in sql? Run the query you showed, saving the result to a temp table. In an sproc or your preferred app language, do the row-to-row processing to generate a new column in the temp table from the biz rules you outlined, now query the revised temp table as desired. This is a very interesting problem but I am not sure what to do with a grouping. To me this appears to be a two-dimensional, nearest-neighbor-type sorting problem. The way you outline it you could end up with all of the elements in your analysis plane in a single group depending on how closely they are to each other. Are long strings of points and blobs (clusters of points) in your 2-D search space (your plane is defined by your {a,b,c,d} tuple) acceptable results of your re-grouping process? If I had to draw this out graphically, you are stamping each of your {x,y} points with a 2x2 boundary box (each point is in intersection in the middle) and you want to know which sets of those boxes form a contiguous intersection area (they overlap). Is that what you are looking for? One technique would be to start with Peter's suggestion. Then reprocess that list to generate an acyclic directed graph (by eliminating the reverse matches from your set), then tracing down the tree. If point 2 links to point 7,then 7 also links to 2 (both would be found by your nearness test. Eliminate any matches where the first point appears after the second point from your list. Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: grouping by the difference between values in rows
On Wed, Jan 15, 2014 at 4:06 PM, shawn l.green shawn.l.gr...@oracle.com wrote: Hello Larry, On 1/13/2014 2:43 AM, Peter Brawley wrote: On 2014-01-12 9:13 PM, Larry Martell wrote: On Sun, Jan 12, 2014 at 2:47 PM, Peter Brawley peter.braw...@earthlink.net wrote: On 2014-01-12 1:17 PM, Larry Martell wrote: 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: row 1: 1.5, 9.5 row 2: 2.4, 20.8 row 3: 3.3, 40.6 row 4: 4.2, 2.5 row 5: 5.1, 10.1 row 6: 6.0, 7.9 row 7: 8.0, 21.0 row 8: 100, 200 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? Could you compute the row-to-row values write them to a temp table, then run the SQL that incorporates that result column? I thought of temp tables, but I could not come up with a way to use them for this. How can I apply the x/y tolerance grouping in sql? Run the query you showed, saving the result to a temp table. In an sproc or your preferred app language, do the row-to-row processing to generate a new column in the temp table from the biz rules you outlined, now query the revised temp table as desired. This is a very interesting problem but I am not sure what to do with a grouping. To me this appears to be a two-dimensional, nearest-neighbor-type sorting problem. The way you outline it you could end up with all of the elements in your analysis plane in a single group depending on how closely they are to each other. Are long strings of points and blobs (clusters of points) in your 2-D search space (your plane is defined by your {a,b,c,d} tuple) acceptable results of your re-grouping process? If I had to draw this out graphically, you are stamping each of your {x,y} points with a 2x2 boundary box (each point is in intersection in the middle) and you want to know which sets of those boxes form a contiguous intersection area (they overlap). Is that what you are looking for? One technique would be to start with Peter's suggestion. Then reprocess that list to generate an acyclic directed graph (by eliminating the reverse matches from your set), then tracing down the tree. If point 2 links to point 7,then 7 also links to 2 (both would be found by your nearness test. Eliminate any matches where the first point appears after the second point from your list. Thanks much for the reply Shawn. Unfortunately this project was put on the back burner. When I get back to it I'll be sure to try the suggestions given here and I will report back as to what worked. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql