CODEWITHSUNDEEP
phpmysqlarrayssumaverage
user1489795
user1489795

Reputation: 5

MYSQL: Ordering by the average of an array

So I have an imploded array in a mysql table that is basically just a sequence of numbers (ex. 1,5,3,1,4,5) and I was wondering if it was possible to order them by the average (or even the sum if that were possible) of the sets of numbers in the table

Upvotes: 1

Views: 230

Answers (2)

eggyal
eggyal

Reputation: 125855

Instead of storing your numbers in a delimited string, take advantage of MySQL's relational capabilities and normalise your data: store your numbers as (key, value) pairs in a separate table that relates a foreign key (i.e. that of your existing table) to a single number in the list. If order is important, include an additional column to indicate the number's position within the list.

CREATE TABLE `newtable` (
  `key`   INT,
  `value` INT,
  FOREIGN KEY (`key`) REFERENCES `existingtable` (`key`)
)

Then you need only join the tables together, GROUP BY the key in your existing table and ORDER BY the AVG() or SUM() of the values in the new table; you can even reclaim the comma-separated list if so desired using MySQL's GROUP_CONCAT() function:

SELECT   `existingtable`.*, GROUP_CONCAT(`newtable`.`value`) AS `values`
FROM     `existingtable` LEFT JOIN `newtable` USING (`key`)
GROUP BY `key`
ORDER BY AVG(`newtable`.`value`) -- or use SUM() if preferred

Upvotes: 9

spencer7593
spencer7593

Reputation: 108390

As others have mentioned, it's not clear what your table looks like, or the data in you table looks like. It's not at all clear what an "imploded array" looks like. This is where an EXAMPLE would really help you get the answer you are looking for.

But let's go with the "worst case" here, and assume you've got a string containing those values you want to average. Like this:

CREATE TABLE foo (str VARCHAR(1000));
INSERT INTO foo VALUES ('1,5,3,1,4,5');
INSERT INTO foo VALUES ('2.8,4.2');
INSERT INTO foo VALUES ('bar');
INSERT INTO foo VALUES (' 0, -2, 3');

It's possible to create a function that returns an average of the values in your "imploded array" string. As an example:

DELIMITER $$
CREATE FUNCTION `do_average`(p_str VARCHAR(2000))
RETURNS DOUBLE
BEGIN
  DECLARE c INT;
  DECLARE i INT;
  DECLARE s VARCHAR(2000);
  DECLARE v DOUBLE;
  SET c = 0;
  SET v = 0;
  SET s = CONCAT(p_str,',');
  SET i = INSTR(s,',');
  WHILE i > 0 DO
    SET v = v + SUBSTR(s,1,i);
    SET c = c + 1;
    SET s = SUBSTR(s,i+1,2000);
    SET i = INSTR(s,',');
  END WHILE;
  RETURN v / NULLIF(c,0);
END$$
DELIMITER ;

We can demonstrate this working:

SELECT f.str
     , do_average(f.str) AS davg
  FROM foo f
 ORDER BY do_average(f.str)

str                       davg  
-----------  -------------------
bar                            0
 0, -2, 3      0.333333333333333
1,5,3,1,4,5     3.16666666666667
2.8,4.2                      3.5

Note that with this function, we're using MySQL's implicit conversion of strings to numbers, so empty strings, or invalid numbers are going to be converted to zero, and that zero is going to get added and counted in computing the average.

A do_sum function would be nearly identical, just return the total rather than the total divided by the count.

sqlfiddle example here http://sqlfiddle.com/#!2/4391b/2/0

Upvotes: 1

Related Questions