1
\$\begingroup\$

Problem

So what problem am I trying to solve? Given that we have a list of IDstrings, with an associated region, I want to produce all the different combinations of IDStrings possible for a specific region.

DB Fiddle

See the fullworking DB-Fiddle :

https://www.db-fiddle.com/f/mHyWsoRjoQwESHtuLiFQ2y/0

Approach

How did I solve it currently?

  1. I did a group by and group_concat. So for a specific region I get an entry like a,b,c.
  2. But the purpose is to get all combinations and sub-combinations of a,b,c - so e.g. : Combinations:

    [a,b] - [a,c] - [b,c] - [a,b,c] - etc...

    1. So what I did was: split the csv list into an actual table. You get a temp table with 3 rows: a , b , c ,

    2. then you generate select statements with self joins.

    3. select * from temp a, temp b produces:

    [a, a] [a, b] [a , c] [b , a] [etc...]

    1. Then I get rid of the lines with the same element but also you need to produce multiple select statements. Essentially: 2 self-joins , 3 self-joins , n self-joins ,

All these are inserted into the results table.

Intro

So as an exercise to train with mysql stored procedures after 10 years, I answered the following question on stackoverflow: https://stackoverflow.com/questions/46828262/mysql-query-expanding-data-into-new-table/46829411#46829411

But I am feeling that this stored procedure is too clunky and inefficient.

Some comments and review will be appreciated. Thanks!

Code

#Init Stuff

    create table testing(
      idString varchar(20),
      region varchar(20)
    );

    insert into testing values("abcdefg","123456");
    insert into testing values("bcdefgh","123456");
    insert into testing values("cdefghi","123456");
    insert into testing values("defghij","456789");
    insert into testing values("efghijk","456789");
    insert into testing values("fghijkl","456789");


    # Run the below all together or split up as requested


DROP PROCEDURE IF EXISTS `sp_split`;
DROP PROCEDURE IF EXISTS `getCombinations`;
drop table result;
drop table temp;

CREATE TABLE IF NOT EXISTS `temp` (
  `col` VARCHAR(100) NOT NULL
) ENGINE=MEMORY;

CREATE TABLE IF NOT EXISTS `result` (
  `idStrings` VARCHAR(100) NOT NULL,
  `region` VARCHAR(100) NOT NULL,
  `count` VARCHAR(100) NOT NULL
) ENGINE=MEMORY;

create view  IF NOT EXISTS groupByBugWorkaroundView as select   region as region, count(distinct idString) as countInt, group_concat(idString) as csv from testing group by region;

delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_split`(IN toSplit text, IN target char(255))
  BEGIN
    # Temp table variables
    SET @tableName = 'tmpSplit';
    SET @fieldName = 'variable';

    #select toSplit;

    # Dropping table
    SET @sql := CONCAT('DROP TABLE IF EXISTS ', @tableName);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;


    # Creating table
    SET @sql := CONCAT('CREATE TABLE IF NOT EXISTS ', @tableName, ' (', @fieldName, ' VARCHAR(1000))');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;


    # Preparing toSplit
    SET @vars := toSplit;
    SET @vars := CONCAT("('", REPLACE(@vars, ",", "'),('"), "')");

    delete from tmpSplit;

    # Inserting values
    SET @sql := CONCAT('INSERT INTO ', @tableName, ' VALUES ', @vars);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    # Returning record set, or inserting into optional target
    IF target IS NULL THEN
      SET @sql := CONCAT('SELECT TRIM(`', @fieldName, '`) AS `', @fieldName, '` FROM ', @tableName);
    ELSE
      SET @sql := CONCAT('INSERT INTO ', target, ' SELECT TRIM(`', @fieldName, '`) FROM ', @tableName);
    END IF;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

  END
;;

#-------------------------------------------------------------------------------

CREATE PROCEDURE getCombinations()
  BEGIN

    DECLARE countInt INT DEFAULT 0;
    DECLARE csv varchar(100);
    DECLARE region varchar(100);
    DECLARE v_last_row_fetched INT;
    declare v_counter int unsigned default 1;

    DECLARE counter cursor for select  * from groupByBugWorkaroundView;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET  v_last_row_fetched=1;

    SET SESSION sql_mode = CONCAT(@@sql_mode, ',PIPES_AS_CONCAT');

    delete from result;
    delete from tmpSplit;
    COMMIT;

    open counter;
    c1_loop: loop
      fetch counter into region , countInt, csv ;

      IF  v_last_row_fetched=1 THEN LEAVE c1_loop; END IF;

      CALL sp_split(csv, null);

      #       delete from temp;
      #       insert into temp select * from tempSplit;
      #       commit;

      set @q0 = concat('insert into result select t0.variable ');
      set @q1 = concat(' from tmpSplit t0');
      set @q3 = concat(' where 1=1 ');
      set @q4 = ',\''  || region || ' \'' ;

      #-- DEBUG
      #       set @tempLog = concat('insert into templog values (\'',@q1);
      #       set @tempLog = concat(@q1, ' '');');
      #       PREPARE stmt1 FROM @tempLog;
      #       EXECUTE stmt1;
      #       DEALLOCATE PREPARE stmt1;
      #-- END DEBUG

      set v_counter = 1;
      while v_counter < countInt do

        set @q1 := concat(@q1,  ' ,tmpSplit t');
        set @q1 := concat(@q1 ,  v_counter);

        set @q0 := concat(@q0, '|| \',\' || t');
        set @q0 := concat(@q0, v_counter);
        set @q0 := concat(@q0, '.variable');

        set @q3 := concat(@q3, ' AND not t');
        set @q3 := concat(@q3, v_counter-1);
        set @q3 := concat(@q3, '.variable = ');
        set @q3 := concat(@q3, ' t');
        set @q3 := concat(@q3, v_counter);
        set @q3 := concat(@q3, '.variable ');


        set v_counter=v_counter+1;

        set @q5 = ',\''  || v_counter || '\'' ;

         set @q9 := concat('', '');
         set @q9 := concat( @q0, @q4);
         set @q9 := concat( @q9, @q5);
         set @q9 := concat( @q9, @q1);
         set @q9 := concat( @q9, @q3);
         select @q9 as debug;

          PREPARE stmt1 FROM @q9;
          EXECUTE stmt1;
          DEALLOCATE PREPARE stmt1;
          commit;

      end while;

      delete from temp; commit;

    end loop c1_loop;
    close counter;

  END;

DELIMITER ;

CALL getCombinations();
select distinct idStrings, region, count from result;

References

https://gist.github.com/avoidwork/3749973

Updated Code - October 26, 2017:

#Will not work without the following command/Setting:
SET SESSION sql_mode = CONCAT(@@sql_mode, ',PIPES_AS_CONCAT');

DROP PROCEDURE IF EXISTS `sp_split`;
DROP PROCEDURE IF EXISTS `getCombinations`;
drop table result;
drop table temp;

CREATE TABLE IF NOT EXISTS `temp` (
  `col` VARCHAR(100) NOT NULL
) ENGINE=MEMORY;

CREATE TABLE IF NOT EXISTS `result` (
  `idStrings` VARCHAR(100) NOT NULL,
  `region` VARCHAR(100) NOT NULL,
  `count` VARCHAR(100) NOT NULL
) ENGINE=MEMORY;

create view  IF NOT EXISTS groupByBugWorkaroundView as select   region as region, count(distinct idString) as countInt, group_concat(idString) as csv from testing group by region;

delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_split`(IN toSplit text, IN target char(255))
  BEGIN
    # Temp table variables
    SET @tableName = 'tmpSplit';
    SET @fieldName = 'variable';

    #select toSplit;

    # Dropping table
    SET @sql := CONCAT('DROP TABLE IF EXISTS ', @tableName);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;


    # Creating table
    SET @sql := CONCAT('CREATE TABLE IF NOT EXISTS ', @tableName, ' (', @fieldName, ' VARCHAR(1000))');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;


    # Preparing toSplit
    SET @vars := toSplit;
    SET @vars := CONCAT("('", REPLACE(@vars, ",", "'),('"), "')");

    delete from tmpSplit;

    # Inserting values
    SET @sql := CONCAT('INSERT INTO ', @tableName, ' VALUES ', @vars);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    # Returning record set, or inserting into optional target
    IF target IS NULL THEN
      SET @sql := CONCAT('SELECT TRIM(`', @fieldName, '`) AS `', @fieldName, '` FROM ', @tableName);
    ELSE
      SET @sql := CONCAT('INSERT INTO ', target, ' SELECT TRIM(`', @fieldName, '`) FROM ', @tableName);
    END IF;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

  END
;;

#-------------------------------------------------------------------------------

CREATE PROCEDURE getCombinations()
  BEGIN

    DECLARE countInt INT DEFAULT 0;
    DECLARE csv varchar(100);
    DECLARE region varchar(100);
    DECLARE v_last_row_fetched INT;
    declare v_counter int unsigned default 1;

    DECLARE counter cursor for select  * from groupByBugWorkaroundView;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET  v_last_row_fetched=1;

    SET SESSION sql_mode = CONCAT(@@sql_mode, ',PIPES_AS_CONCAT');

    delete from result;
    delete from tmpSplit;
    COMMIT;

    open counter;
    c1_loop: loop
      fetch counter into region , countInt, csv ;

      IF  v_last_row_fetched=1 THEN LEAVE c1_loop; END IF;

      CALL sp_split(csv, null);

      #       delete from temp;
      #       insert into temp select * from tempSplit;
      #       commit;

      set @q0 = concat('insert into result select t0.variable ');
      set @q1 = concat(' from tmpSplit t0');
      set @q3 = concat(' where 1=1 ');
      set @q4 = ',\''  || region || ' \'' ;

      #-- DEBUG
      #       set @tempLog = concat('insert into templog values (\'',@q1);
      #       set @tempLog = concat(@q1, ' '');');
      #       PREPARE stmt1 FROM @tempLog;
      #       EXECUTE stmt1;
      #       DEALLOCATE PREPARE stmt1;
      #-- END DEBUG

      set v_counter = 1;
      while v_counter < countInt do

        set @q1 := concat(@q1,  ' ,tmpSplit t');
        set @q1 := concat(@q1 ,  v_counter);

        set @q0 := concat(@q0, '|| \',\' || t');
        set @q0 := concat(@q0, v_counter);
        set @q0 := concat(@q0, '.variable');

        #Necessary joins
        set @q3 := concat(@q3, ' AND not t');
        set @q3 := concat(@q3, v_counter-1);
        set @q3 := concat(@q3, '.variable = ');
        set @q3 := concat(@q3, ' t');
        set @q3 := concat(@q3, v_counter);
        set @q3 := concat(@q3, '.variable ');

        #Alphabetical order
        set @q3 := concat(@q3, ' AND not t');
        set @q3 := concat(@q3, v_counter-1);
        set @q3 := concat(@q3, '.variable > ');
        set @q3 := concat(@q3, ' t');
        set @q3 := concat(@q3, v_counter);
        set @q3 := concat(@q3, '.variable ');

        set v_counter=v_counter+1;

        set @q5 = ',\''  || v_counter || '\'' ;

        set @q9 := concat('', '');
        set @q9 := concat( @q0, @q4);
        set @q9 := concat( @q9, @q5);
        set @q9 := concat( @q9, @q1);
        set @q9 := concat( @q9, @q3);
        select @q9 as debug;

        PREPARE stmt1 FROM @q9;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
        commit;

      end while;

      delete from temp; commit;

    end loop c1_loop;
    close counter;

  END;

DELIMITER ;

CALL getCombinations();
select distinct idStrings, region, count from result order by region, count  desc;
\$\endgroup\$
  • 1
    \$\begingroup\$ @Mast done! Question has been updated. \$\endgroup\$ – Menelaos Bakopoulos Oct 24 '17 at 9:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.