对公用数据的逗号分隔值进行分组

Question

我有一个表，col1 id int，col2作为varchar（逗号分隔值），第3列用于为它们分配组。表格看起来像

  col1          col2     group
..............................
       1        2,3,4       
       2        5,6        
       3        1,2,5
       4        7,8
       5        11,3
       6        22,8

这只是真实数据的样本，现在我必须为它们分配一个组no，这样输出看起来像

  col1          col2       group
..............................
       1        2,3,4       1
       2        5,6         1
       3        1,2,5       1
       4        7,8         2
       5        11,3        1
       6        22,8        2

分配组号的逻辑是，col2中字符串的每个类似逗号分隔值都必须与col2中的每个where相同，其中"2"在那里，它必须是相同的组号，但复杂的是2、3、4在一起，所以如果在col2的任何where中找到，它们都将被分配相同的组。主要部分是col2中的2,3,4和1,2,5都有2，所以所有int 1,2,3,4,5都必须分配相同的组号。在col2上尝试了与匹配的存储过程，但没有得到所需的结果

大多数imp（我不能使用规范化，因为我负担不起从有数百万记录的原始表中创建新表），甚至规范化在我的上下文中也没有帮助。

---

迄今为止已实现。。。。。。我设置了组列自动递增，然后编写了以下程序：-

BEGIN
  declare cil1_new,col2_new,group_new int;
  declare done tinyint default 0;
  declare group_new varchar(100);
  declare cur1 cursor for select col1,col2,`group` from company ; 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
  open cur1;
  REPEAT
  fetch cur1 into col1_new,col2_new,group_new;
  update company set group=group_new where
  match(col2) against(concat("'",col2_new,"'"));
  until  done end repeat;
  close cur1;
  select * from company;
END

这个过程正在运行，没有syntex错误，但问题是我没有执行所需的结果。

Answer 1

这是可能的，但我不确定在你的大桌子上需要多长时间。我假设您可以创建一个新表，其中包含所有组，并且在填充组列时有数字。

此外，这不能在活动表上运行。这是不可能写的，所以这不是我设计的限制。想一想，如果添加一个值为7和"6,7"的新行，会发生什么，这将桥接组1和2，并且所有工作都必须删除。

每次向表中添加内容时，都需要重新运行此过程。如果这是不可接受的，请运行一次，然后用维护值并在需要时合并组的触发器替换它。

这是程序。它可以从一些模块化中受益，但它是有效的。我采用了Jay-Pipes split_string函数并将其包含在内。

首先是DDL和一些测试数据

CREATE TABLE `company` (
  `col1` int(11) DEFAULT NULL,
  `col2` varchar(100) DEFAULT NULL,
  `grp` int(11) DEFAULT NULL
);
CREATE TABLE `groups` (
  `number` int(11) NOT NULL DEFAULT '0',
  `grp` int(11) NOT NULL DEFAULT '0',
  `processed` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`number`,`grp`),
  KEY `grp` (`grp`)
);
insert into company (col1, col2) values 
(1,'2,3,4'),       
(2,'5,6'),        
(3,'1,2,5'),
(4,'7,8'),
(5,'11,3'),
(6,'22,8');

现在程序

use test;
drop procedure if exists group_it;
delimiter //
create procedure group_it ()
begin                        
  declare current_group int default 0;
  declare ids varchar(100);
  -- clear out all data from before
  update company set grp = null;
  truncate groups;
  main: loop                                
    -- take one unmapped (new group)
    set ids := null;
    select col2 into ids from company where grp is null limit 1;
    if ids is null then
      leave main;
    end if;
    set current_group := current_group + 1;
    --  put each value into groups table and mark as unprocessed
    call split_string(ids, ',');
    insert into groups select value, current_group, false from SplitValues;
    -- while unprocessed value in groups
    begin
      declare unprocessed int;
      unprocessed: loop
        set unprocessed = null;
        select number
          into unprocessed
          from groups
         where not processed
         limit 1;
        if unprocessed is null then
          leave unprocessed;
        end if;
        begin
          -- find all rows in company that matches this group
          declare row_id int;
          declare ids2 varchar(100);
          declare cur2_done boolean;
          declare cur2 cursor for
            select col1, col2 
              from company
             where col2 regexp concat('^', unprocessed, '$')
                or col2 regexp concat('^', unprocessed, ',')
                or col2 regexp concat(',', unprocessed, '$')
                or col2 regexp concat(',', unprocessed, ',');
          declare continue handler for not found set cur2_done := true;
          open cur2;    
          numbers: loop
            set cur2_done := false;
            fetch cur2 into row_id, ids2; 
            if cur2_done then
                close cur2;
                leave numbers;
            end if;
            update company set grp = current_group where col1 = row_id;
            --  add all new values to groups marked as unprocessed
            call split_string(ids2, ',');   
            insert ignore into groups select value, current_group, false from SplitValues;
          end loop numbers;
          update groups set processed = true where number = unprocessed;
        end;
      end loop unprocessed;
    end;
  end loop main;
end//
delimiter ;         

我是Jay Pipes split_string

DELIMITER //
DROP PROCEDURE IF EXISTS split_string //
CREATE PROCEDURE split_string (
IN input TEXT
, IN `delimiter` VARCHAR(10)
)
SQL SECURITY INVOKER
COMMENT
'Splits a supplied string using using the given delimiter,
placing values in a temporary table'
BEGIN
DECLARE cur_position INT DEFAULT 1 ;
DECLARE remainder TEXT;
DECLARE cur_string VARCHAR(1000);
DECLARE delimiter_length TINYINT UNSIGNED;
DROP TEMPORARY TABLE IF EXISTS SplitValues;
CREATE TEMPORARY TABLE SplitValues (
value VARCHAR(1000) NOT NULL PRIMARY KEY
) ENGINE=MyISAM;
SET remainder = input;
SET delimiter_length = CHAR_LENGTH(delimiter);
WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO
SET cur_position = INSTR(remainder, `delimiter`);
IF cur_position = 0 THEN
SET cur_string = remainder;
ELSE
SET cur_string = LEFT(remainder, cur_position - 1);
END IF;
IF TRIM(cur_string) != '' THEN
INSERT INTO SplitValues VALUES (cur_string);
END IF;
SET remainder = SUBSTRING(remainder, cur_position + delimiter_length);
END WHILE;
END //
DELIMITER ;