南大通用GBase 8a实现查找演员评分最高的几部电影业务

本文通过GBase 8a数据库实现查找演员评分最高的1到多部电影业务，提供分析过程和具体SQL。

参考

GBase 8a 模糊查询和正则函数regexp_replace、regexp_like

背景

已知目标演员表和影片表，需要查找这些演员得分最高的影片。其中影片表里的演员内容，是逗号分割多位演员的。

演员表

我们【故意】混进来一个叫刘德的人。

gbase> select * from actor;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 刘德华    |
|    2 | 成龙      |
|    3 | 刘德      |
+------+-----------+
3 rows in set (Elapsed: 00:00:00.00)

影片表

其中演员部分，是【可能】包含多位的，用逗号分割。

gbase> select * from film;
+------+-----------------------------+----------------------------+-------+
| id   | filmname                    | names                      | score |
+------+-----------------------------+----------------------------+-------+
|    1 | 刘德华9.1分影片             | 刘德华                     |   9.1 |
|    2 | 刘德华主演9.2分影片         | 刘德华,周星驰              |   9.2 |
|    3 | 成龙主演9.0分影片           | 刘德华,成龙,周星驰         |   9.0 |
|    4 | 成龙主演9.4分影片           | 成龙,周星驰                |   9.4 |
+------+-----------------------------+----------------------------+-------+
4 rows in set (Elapsed: 00:00:00.00)

分析

此业务的难点，是影片的演员有多位，所有如果可以列转行就简单了，也就是相同的电影，每个演员一行。也可以满足范式的要求，单独建1个电影演员表，每个电影的每个演员一行。

如果不转，则可以用like模糊匹配的方式，需要注意的是人名的区分，避免出现‘张三丰’包含‘张三'这种情况。可以采用

精确匹配 OR 首位匹配 OR 中间匹配 OR 末尾匹配

其中首位匹配是名字后面带逗号，中间匹配是前后有逗号，末尾匹配是只有前面有逗号。

运行样例

拿到演员参加的所有电影

注意其中的RLIKE部分，正则匹配多种情况。

select a.name,b.filmname,b.names,b.score 
from actor a 
left join film b on b.names rlike concat(
  '^',a.name,'$',  '|',
  '^',a.name,',',  '|',
  ',',a.name,',',  '|',
  a.name,'$');


+-----------+-----------------------------+----------------------------+-------+
| name      | filmname                    | names                      | score |
+-----------+-----------------------------+----------------------------+-------+
| 刘德华    | 刘德华9.1分影片             | 刘德华                     |   9.1 |
| 刘德华    | 刘德华主演9.2分影片         | 刘德华,周星驰              |   9.2 |
| 刘德华    | 成龙主演9.0分影片           | 刘德华,成龙,周星驰         |   9.0 |
| 成龙      | 成龙主演9.0分影片           | 刘德华,成龙,周星驰         |   9.0 |
| 成龙      | 成龙主演9.4分影片           | 成龙,周星驰                |   9.4 |
| 刘德      | NULL                        | NULL                       |  NULL |
+-----------+-----------------------------+----------------------------+-------+
6 rows in set (Elapsed: 00:00:00.00)

采用ROW_NUMBER根据得分排序

gbase> select x.*,row_number() over(partition by name order by score desc) rownum from (select a.name,b.filmname,b.names,b.score  from actor a  left join film b on b.names rlike concat(   '^',a.name,'$',  '|',   '^',a.name,',',  '|',   ',',a.name,',',  '|',   a.name,'$'))x order by name,rownum;
+-----------+-----------------------------+----------------------------+-------+--------+
| name      | filmname                    | names                      | score | rownum |
+-----------+-----------------------------+----------------------------+-------+--------+
| 刘德      | NULL                        | NULL                       |  NULL |      1 |
| 刘德华    | 刘德华主演9.2分影片         | 刘德华,周星驰              |   9.2 |      1 |
| 刘德华    | 刘德华9.1分影片             | 刘德华                     |   9.1 |      2 |
| 刘德华    | 成龙主演9.0分影片           | 刘德华,成龙,周星驰         |   9.0 |      3 |
| 成龙      | 成龙主演9.4分影片           | 成龙,周星驰                |   9.4 |      1 |
| 成龙      | 成龙主演9.0分影片           | 刘德华,成龙,周星驰         |   9.0 |      2 |
+-----------+-----------------------------+----------------------------+-------+--------+
6 rows in set (Elapsed: 00:00:00.01)

按照排名获得结果

排名最高的1部电影

gbase> select * from (select x.*,row_number() over(partition by name order by score desc) rownum from (select a.name,b.filmname,b.names,b.score  from actor a  left join film b on b.names rlike concat(   '^',a.name,'$',  '|',   '^',a.name,',',  '|',   ',',a.name,',',  '|',   a.name,'$'))x )y where rownum<=1 order by name,rownum;
+-----------+-----------------------------+---------------------+-------+--------+
| name      | filmname                    | names               | score | rownum |
+-----------+-----------------------------+---------------------+-------+--------+
| 刘德      | NULL                        | NULL                |  NULL |      1 |
| 刘德华    | 刘德华主演9.2分影片         | 刘德华,周星驰       |   9.2 |      1 |
| 成龙      | 成龙主演9.4分影片           | 成龙,周星驰         |   9.4 |      1 |
+-----------+-----------------------------+---------------------+-------+--------+
3 rows in set (Elapsed: 00:00:00.00)

排名最高的2部电影

gbase> select * from (select x.*,row_number() over(partition by name order by score desc) rownum from (select a.name,b.filmname,b.names,b.score  from actor a  left join film b on b.names rlike concat(   '^',a.name,'$',  '|',   '^',a.name,',',  '|',   ',',a.name,',',  '|',   a.name,'$'))x )y where rownum<=2 order by name,rownum;
+-----------+-----------------------------+----------------------------+-------+--------+
| name      | filmname                    | names                      | score | rownum |
+-----------+-----------------------------+----------------------------+-------+--------+
| 刘德      | NULL                        | NULL                       |  NULL |      1 |
| 刘德华    | 刘德华主演9.2分影片         | 刘德华,周星驰              |   9.2 |      1 |
| 刘德华    | 刘德华9.1分影片             | 刘德华                     |   9.1 |      2 |
| 成龙      | 成龙主演9.4分影片           | 成龙,周星驰                |   9.4 |      1 |
| 成龙      | 成龙主演9.0分影片           | 刘德华,成龙,周星驰         |   9.0 |      2 |
+-----------+-----------------------------+----------------------------+-------+--------+
5 rows in set (Elapsed: 00:00:00.01)