时间戳

首页 > 解决方案 > MySQL - 为分组生成序列号

mysql - MySQL - 为分组生成序列号

问题描述

我得到了一个用于填充 MySQL 数据库的源文件。我使用 Python 脚本来清理和转换数据。我试图解决的问题的一个小例子如下所示。

源文件

Area                        | Team    |
---------------------------------------
NORTH EAST - CO.DURHAM BL   | Winters |
NORTH EAST - NEWCASTLE GT   | Summers |
......                                |
SOUTH EAST - SOUTHAMPTON BI | Winters |

阶段 2 在清理等之后转换为 DB 临时表。

ID | Region     | City        | State     | Team    |
----------------------------------------------------|
1  | NORTH EAST | DURHAM      | BL        | Winters |
2  | NORTH EAST | NEWCASTLE   | GT        | Summers |
......                
80 | SOUTH EAST | SOUTHAMPTON | BI        | Winters |

第 3 阶段 - 为每个区域分组生成区域 ID

ID | Region ID  | Region     | City        | State     | Team    |
-------------------------------------------|-----------|---------|
1  | 1          | NORTH EAST | DURHAM      | BL        | Winters |
2  | 1          | NORTH EAST | NEWCASTLE   | GT        | Summers |
......                
80 | 10         | SOUTH EAST | SOUTHAMPTON | BI        | Winters |

是否可以为mysql中的分组生成序列号？例如，在上面的场景中，是否可以生成RegionID列使得 NORTH EAST 区域分组为 1，SOUTH EAST 分组为 10 等等。这将如何实现？

注意有没有办法自动生成序列？例如，区域 id 列中的值是否可以自动生成而无需在区域表中手动指定 id？

标签： mysql

解决方案

CREATE TABLE regions (
  region_id INT PRIMARY KEY
  region varchar(10),
  key(region)
);

INSERT INTO regions VALUES
(1, 'NORTH EAST'), (10,'SOUTH EAST'), etc...

UPDATE db_staging_table AS d
JOIN regions AS r USING (region)
SET d.region_id = r.region_id;

如果您使用的是 MySQL 8.0，您可以这样做：

WITH cte (region, region_id) AS (
  SELECT region, ROW_NUMBER() OVER() 
  FROM db_staging_table 
  GROUP BY region
)
UPDATE db_staging_table AS d JOIN cte ON d.region = cte.region 
SET d.region_id = cte.region_id;

推荐阅读