简单生活

  • 最近

    ·

    最近参加了很多的线下的活动,包括了ACMUG、AWS 中国峰会、华为云HDC、IvorySQL & PostgreSQL生态大会,另外,还泡了一些杨梅酒、看了《长安的荔枝》,公司的产品“NineData”社区版发布了4.2.0。

    最近下线的活动很多,包括OceanBase、TiDB、各个云数据库厂商、各个数据库社区等,都在积极的组织一些社区活动,总的感受是,活动虽然很多,但开发者们对线下活动的热情是在减退的,而如果一场活动与AI关系不大,那么来现场的人一般是对这个技术的“真爱”。

    华为云的 HDC

    在上周末,受华为云数据库的朋友邀请,去参加华为云的 HDC 大会(开发者大会)。最近几年,和华为云的数据库合作比较多,也结识了很多华为云数据库团队的人。虽然数据库技术都是一样的,但每家公司都有着自己非常独特的环境与基因,有这自己不一样的风格。华为云数据库,感受着更多来自客户与一线的炮火,有着更接地气的拼劲。而整个公司因为在全球范围内的制裁压力,反而激发了更强的凝聚力。这次HDC大会上,华为云数据库发布了:GaussDB业务透明多写能力、“GaussDB Doer”一个面向华为云数据库的运维助手、TaurusDB for PostgreSQL

    此外,这次的HDC是在华为的松山湖园区,这是一个非常有特色的欧洲式建筑园区,随手拍了几张石雕,感受一下:

    前面的骑马的女神,大概是雅典娜
    拿着美杜莎之盾,大概是伯尔修斯
    经周陌认证,中间大概是波塞冬
    奥古斯都 屋大维

    ACMUG

    今年是 MySQL 30 年,这次成都的线下活动算是特别盛大的一次了,成都虽然有点远,但 MySQL 领域很多的有影响力的人去了。活动本身除了白天严肃的分享议题之外,下午、晚上大家随意闲聊各种八卦似乎要更有趣一些,这大概也是更多人参加的动力吧。

    亚马逊中国峰会

    这是因 NineData 赞助而去参加的活动,是以合作伙伴的身份参加的。Amazon的峰会在2015年的时候曾在上海参过一次,2018年还曾去过Vagas参加过一次re:Invent。Amazon 在全球云计算领域的地位依旧遥遥领先,但中国是一个特别的地方,确实很特别,无论是 Oracle 还是现在的 Amazon ,在全球大杀四方的时候,在中国却寸步难行,到底是谁的问题,一时难下结论,但这也确实给中国的厂商们留下一些时间和机会。

    云计算是现代应用非常底层的基础技术,而亚马逊作为一家美国的企业,要在中国开荒拓地,如果国际合作关系没有好转,未来大概是难有好转的。

    社区版发布4.2.0

    此外,这段时间,NineData 的社区版也发布了4.2.0,这是一个免费的(但不开源)数据库迁移同步工具,该免费版本中可以非常方便的帮助开发者完成诸如MySQL迁移、PostgreSQL迁移、Doris同步等工作。但如果是重要的生产环境或者需要长期运行的关键链路,则依旧建议考虑采购企业版。

    IvorySQL & PostgreSQL 生态大会

    这次大会主要由“瀚高”数据库团队在背后主办,是非常赞的活动,大会上有着关于 PostgreSQL 数据库方方面面的技术话题。这次参会,也认识了更多的 PostgreSQL 方向的开发者们。

    正如自己数年的感受一样:“PostgreSQL 在经历一场慢热的崛起”。从过去两三个月的两场收购(Neon、Crunchy Data)来看,在 AI 时代,PostgreSQL 依旧是在潮头的。

    杨梅酒

    最近几年,越来越体会到,杨梅是一种极为美味的水果了。杨梅大概在每年的6月初成熟,到了月底则已经逐渐下架,又因为其运输和保存都非常困难,也让这口美味,更显难得。江浙一带的杨梅种植技术大概是非常强的,这里的杨梅品种是非常独特的,甜中带着酸、酸中偷着甜,早上从台州一带的树上摘下,中午或晚上送到杭州,简单清洗一下,吃上十个八个,实为人间难得的美味。

    杨梅因为表面没有保护的表皮,所以其运输的难度比起荔枝要难数十倍。“杨贵妃”大概是没有尝过江浙一带的杨梅的,否则,则可能每年下江南一次。想运到长安或洛阳,不要说古代,即便是现在,都有一些困难的。

    喝酒这件事情,我大概是“人菜瘾大”的那类。在听说可以用杨梅泡酒后,从去年起就做了一些尝试。今年的杨梅酒已经按经验泡制、封存,约两个月后就可以品尝了。届时,如果感兴趣的,可以来我家“尝一尝”。

    长安的荔枝

    今年,一个多年未见的小学同学给我寄了一箱来自岭南一带的荔枝,放了半箱再公司,剩下的自己和家人吃了部分,上下楼的邻居也送了一些些。在此,代这些吃上荔枝的人一并感谢这位多年未见的同学。

    公众号久不更新,甚是心慌,记录如上,算是交代。

  • 随着 MySQL 对 JSON 类型的原生支持,操作 JSON 数据已变得非常高效与强大。在过去数年的版本中,MySQL 也在不断地增强 JSON 处理相关的功能。在 JSON 处理中需要非常频繁的使用“JSON Path” 语法,而这部分又是略微复杂的部分,本文将系统的介绍如何在 MySQL 中使用 JSON Path,包括语法规则、各种通配符用法、递归匹配等高级技巧,并通过丰富示例帮助开发者快速掌握。

    什么是 JSON Path?

    JSON Path 是一种表示法,用来描述如何在 JSON 文档中定位数据。类似于文件系统路径,JSON Path 指引着从 JSON 根节点出发,逐步深入结构内部。在 MySQL 中,几乎所有的 JSON相关的函数都会使用到,包括:JSON_EXTRACT()JSON_SET()JSON_REPLACE()JSON_REMOVE()JSON_CONTAINS()等。

    我们看到的场景的写法类似于:$.name$.colors[0]$.store**.price等。

    基础语法说明

    JSON Path的基础语法,遵循以下规则:

    • $:表示 JSON 文档的根节点。
    • .:用于访问对象中的属性。
    • ["key"]:另一种访问对象属性的方式,适合处理特殊字符的 key。
    • [index]:访问数组中的元素。
    • *:通配符,匹配所有子元素。
    • **:递归通配符,匹配所有嵌套层级的元素
    • [start to end]:数组范围选择

    JSON Path 基本示例

    示例表与示例数据

    创建带 JSON字段的表,并写入数据:

    CREATE TABLE t1 (
        id INT PRIMARY KEY AUTO_INCREMENT,
        data JSON
    );
    
    INSERT INTO t1 (data) VALUES
    ('{
      "name": "Alice",
      "age": 25,
      "email": "alice@example.com"
    }');

    提取对象字段

    这里使用基本的$.name引用根节点中属性为name的对象,示例如下:

    mysql> SELECT JSON_EXTRACT(data, '$.name') FROM t1;
    +------------------------------+
    | JSON_EXTRACT(data, '$.name') |
    +------------------------------+
    | "Alice"                      |
    +------------------------------+
    1 row in set (0.00 sec)

    也可以使用如下等价的写法data->'$.name'

    mysql> SELECT data->'$.name' FROM t1;
    +----------------+
    | data->'$.name' |
    +----------------+
    | "Alice"        |
    +----------------+

    访问数组元素

    初始化如下数据:

    -- truncate table t1;
    
    INSERT INTO t1 (data) VALUES
    ('{
      "colors": ["red", "green", "blue"]
    }');

    先访问colors属性,再查找该数组对象的第一个元素(注意:编号是0),故 JSON Path$.colors[0],示例如下:

    mysql> SELECT data->'$.colors[0]' AS first_color FROM t1;
    +-------------+
    | first_color |
    +-------------+
    | "red"       |
    +-------------+

    访问数组的范围

    除了像上述展示的,可以使用数值访问数组外,还可以使用0 to 1这样的语法表示一个范围,并访问数组中的多个元素:

    mysql> SELECT data->'$.colors[0 to 1]' AS first_color FROM t1;
    +------------------+
    | first_color      |
    +------------------+
    | ["red", "green"] |
    +------------------+
    
    mysql> SELECT data->'$.colors[1 to 1]' AS first_color FROM t1;
    +-------------+
    | ["green"]   |
    +-------------+
    
    mysql> SELECT data->'$.colors[1 to 2]' AS first_color FROM t1;
    +-------------------+
    | ["green", "blue"] |
    +-------------------+

    使用通配符

    准备示例数据

    为了展示相关的示例,这里先给出一个更为复杂的示例数据:

    CREATE TABLE t1 (
        id INT PRIMARY KEY AUTO_INCREMENT,
        data JSON
    );
    -- truncate table t1;
    
    INSERT INTO t1 (data) VALUES
    ('{
      "store": {
        "book": [
          {
            "category": "fiction",
            "title": "Harry Potter",
            "price": 29.99
          },
          {
            "category": "fiction",
            "title": "Lord of the Rings",
            "price": 49.99
          }
        ],
        "bicycle": {
          "color": "red",
          "price": 19.95
        }
      }
    }');

    使用通配符查询所有book

    则可以使用如下的搜索表达式:$.store.book[*]

    SELECT data->'$.store.book[*]' AS all_books 
    FROM t1;
    
    mysql> SELECT data->'$.store.book[*]' AS all_books
        -> FROM t1;
    +--------------------------------------------------------------+
    | all_books                                                    |
    +--------------------------------------------------------------+
    | [{"price": 29.99, "title"...}, {"price": 49.99, "title"...}] |
    +--------------------------------------------------------------+

    这里比较容易错误的写成:$.store.book.*$.store.book.[*]$.store.book*

    使用通配符递归查询

    列出所有书店中的书名

    依旧使用上述的数据,这里可以使用递归的通配符(**)查询结构中所有title属性的取值,则'$.store**.title'

    SELECT data->'$.store**.title' AS all_books 
    FROM t1;
    +---------------------------------------+
    | all_books                             |
    +---------------------------------------+
    | ["Harry Potter", "Lord of the Rings"] |
    +---------------------------------------+

    当然,也可以改成直接从“根”处开始递归查找,即$**.title

    mysql> SELECT data->'$**.title' AS all_books  FROM t1;
    +---------------------------------------+
    | all_books                             |
    +---------------------------------------+
    | ["Harry Potter", "Lord of the Rings"] |
    +---------------------------------------+

    类似的,我们还可以取出所有的价格:

    SELECT 
      data->'$.store**.price' AS book_prices 
    FROM t1;
    +-----------------------+
    | book_prices           |
    +-----------------------+
    | [29.99, 49.99, 19.95] |
    +-----------------------+

    小结

    熟悉 MySQL JSON Path Syntax 可以让开发者更加高效操作 JSON 数据。更多参考:

  • Oracle 云(即Oracle Cloud Infrastructure,简称OCI )的免费策略大概是所有云厂商中最为彻底与直接的。对于注册的账号,可以持续免费使用主要的云资源,包括虚拟主机(VM.Standard.E2.1.Micro)、数据库(1OCPU 16GB内存)等。

    相比于 AWS 的 一个月免费、GCP的 $300 代金券,OCI在免费策略上是最具诚意的云厂商。在使用方式上,也更具有诚意,不太因为使用超时、忘记关闭、规格选择错误等因素,而造成误收费,这些在AWS、GCP上都是很容易发生的。

    创建免费的数据库

    创建免费的 HeatWave MySQL 实例

    在创建数据库时,就可以选择“Always Free”选项,这时,在后续规格选择的时候,就不会误选成计费实例了。

    开启免费的 HeatWave 实例

    HeatWave是最近几年Oracle/MySQL最重要的发展方向,可以非常好的支持各种复杂与分析类的查询,很好的弥补了MySQL在分析能力上的短板。

    “Always Free” 也非常友好的支持了 HeatWave 相关的特性,可以让开发者非常好的体验HeatWave相关功能。

    创建实例账号

    创建数据库的管理员账号:

    查看免费实例

    在完成实例创建后,实例详情,可以看到“Always Free”标签:

    创建虚拟主机

    选择免费的虚拟主机规格

    这里需要注意,目前支持的免费规格,需要在分类“Virtual Machine->Specialty and previous generation”中选择:

    虚拟主机的基础选项

    查看实例状态

    在虚拟主机的实例列表页,可以查看该实例,并且看到免费标签“Always Free”:

  • Sysbench 是 MySQL 社交常用的压测工具,本文对 Sysbench 默认压测表大小进行分析,以帮助开发者了解该测试运行时的数据量大小。

    结论概述

    测试了 100万、500万数据,占用空间大小可以参考如下数据:

    sysbench 表大小预估单表记录表数量总空间预估
    场景 11,000,000102.3 GB
    场景 25,000,0001618.4 GB
    场景 310,000,0001636.32 GB

    生成数据命令

    这里使用如下的命令进行数据生成

    sysbench --mysql-host=... --mysql-db=sysbenchdb --db-driver=mysql \
             --mysql-user=... --mysql-password=...   \
             --table_size=1000000 --tables=10 prepare

    占用空间大小

    可以通过如下命令观察数据库中的表大小:

    MySQL [sysbenchdb]> show table status like '%sbtest1%'\G
    *************************** 1. row ***************************
               Name: sbtest1
             Engine: InnoDB
            Version: 10
         Row_format: Dynamic
               Rows: 986400
     Avg_row_length: 228
        Data_length: 225132544
    Max_data_length: 0
       Index_length: 16269312
          Data_free: 4194304
     Auto_increment: 1000001
        Create_time: 2025-03-01 05:57:45
        Update_time: 2025-03-01 05:57:45
         Check_time: NULL
          Collation: utf8mb4_0900_ai_ci
           Checksum: NULL
     Create_options:
            Comment:

    可以看到,单表约为 230 MB

    (225132544+16269312)/1024/1024 ~ 230 

    再通过数据库的物理文件观察大小:

    # ls -lah
    total 2.4G
    drwxr-x---  2 mysql mysql 4.0K Mar  1 14:04 .
    drwxr-x--x 11 mysql mysql 4.0K Mar  1 14:02 ..
    -rw-r-----  1 mysql mysql 240M Mar  1 14:04 sbtest10.ibd
    -rw-r-----  1 mysql mysql 240M Mar  1 14:00 sbtest1.ibd
    -rw-r-----  1 mysql mysql 240M Mar  1 14:01 sbtest2.ibd
    -rw-r-----  1 mysql mysql 240M Mar  1 14:01 sbtest3.ibd
    -rw-r-----  1 mysql mysql 240M Mar  1 14:02 sbtest4.ibd
    -rw-r-----  1 mysql mysql 240M Mar  1 14:02 sbtest5.ibd
    -rw-r-----  1 mysql mysql 240M Mar  1 14:03 sbtest6.ibd
    -rw-r-----  1 mysql mysql 240M Mar  1 14:03 sbtest7.ibd
    -rw-r-----  1 mysql mysql 240M Mar  1 14:03 sbtest8.ibd
    -rw-r-----  1 mysql mysql 240M Mar  1 14:04 sbtest9.ibd

    可以看到,单表大小约为 240 MB,与上述计算的 230 MB 并无太大差距。

    数据生成时间统计

    这里使用的是一台 Amazon RDS xlarge 规格的实例,存储类型为io1,iops规格为3000,在该环境下,大约18秒完成一个表的初始化,算是比较快的速度,即每秒写入约为5.5万记录。

    关于“标准测试”

    在“云数据库 MySQL 的对比测试”中,选择了4c16g的规格,压测时使用了10个记录数为100万的表,即按上述计算,数据量大小约为2.3GB。即,所有的数据均可以缓存再内存当中。这也是为什么该测试是一个 CPU 密集型测试的主要原因。

    如果考虑将数据量调整为 500万,即单表约为1.15GB,总集16个表,那么数据量就可能达到 18.4 GB,那么这个测试就可能成为一个IO密集型的测试,准确的说,可能是一个读IO密集型的测试。

    sysbench 表大小预估单表记录表数量总空间预估
    场景 11,000,000102.3 GB
    场景 25,000,0001618.4 GB

    更多统计

    Sysbench 500万数据大小
    bash-5.1# ls -l
    -rw-r----- 1 mysql mysql 1220542464 Mar 23 02:18 sbtest1.ibd
    mysql> show table status\G
    *************************** 1. row ***************************
               Name: sbtest1
             Engine: InnoDB
            Version: 10
         Row_format: Dynamic
               Rows: 4938540
     Avg_row_length: 205
        Data_length: 1017118720
    Max_data_length: 0
       Index_length: 0
          Data_free: 3145728
     Auto_increment: 5000001
        Create_time: 2025-03-23 02:17:49
        Update_time: 2025-03-23 02:17:49
         Check_time: NULL
          Collation: utf8mb4_0900_ai_ci
           Checksum: NULL
     Create_options:
            Comment:
    1 row in set (0.01 sec)

    可以看到,实际占用空间:1.137GB ~ 1220542464/1024/1024。那么,依次数据,16个大小约为 18.19 GB。与上述数据并无太大差距。

    Sysbench 1千万数据大小

    如下数据统计了,1000万数据大小。可以看到,总占用磁盘空间:2.27GB = 2436890624/1024/1024/1024

    bash-5.1# ls -l
    -rw-r----- 1 mysql mysql 2436890624 Mar 23 02:42 sbtest1.ibd
    mysql> show table status\G
    *************************** 1. row ***************************
               Name: sbtest1
             Engine: InnoDB
            Version: 10
         Row_format: Dynamic
               Rows: 9868349
     Avg_row_length: 220
        Data_length: 2179989504
    Max_data_length: 0
       Index_length: 0
          Data_free: 5242880
     Auto_increment: 10000001
        Create_time: 2025-03-23 02:41:25
        Update_time: 2025-03-23 02:41:25
         Check_time: NULL
          Collation: utf8mb4_0900_ai_ci
           Checksum: NULL
     Create_options:
            Comment:
    1 row in set (0.01 sec)
    
    mysql> select count(1) from sbtest1;
    +----------+
    | count(1) |
    +----------+
    | 10000000 |
    +----------+
    1 row in set (2.74 sec)
  • 一直以来都在较为系统对托管的 MySQL 进行性能测试(参考),本文较为系统将 Oracle Cloud 上托管 MySQL 的性能进行对比展示,可以帮助 Oracle Cloud 上的开发者较为系统了解其MySQL的性能情况。

    8.0.x 系列的性能对比

    这是当前的主要版本,也是当前的“稳定版”(LTS)。

    8.4.x 系列的性能对比

    这是当前另一个“稳定版”(LTS),也会是 8.0.x 系列之后另一个稳定版(LTS)。

    9.x.x 系列的性能对比

    该系列的版本为“创新版”。

    跨版本整体对比

    这里对比

    所有测试数据详情

    data202404_8036202409_8039202409_8402202409_9001202501_8040202501_8403202501_9102
    cpu_capacity103.3114.7111.393.7101.188.682

    测试实例配置信息

    shape=MySQL.4
    ha_type=Multi-FD
    preferred_ad=AP-TOKYO-1-AD-1
    region=tokyo
    storage_size=100

    小结

    整体上,在Oracle Cloud上,托管 MySQL 性能较为稳定,尤其是8.0.x系列,CPU资源也较为一致。从上述数据表格中,可以注意到 8.4.x系列和9.x系列,CPU资源略微要低一些。

  • 这是第二次 SQL 编程大赛[1],我依旧是评委之一,所以自己也尝试了独立完成该问题的挑战。这次大赛分为“普通挑战”和“进阶挑战”。其中普通挑战较为简单,本文主要讨论自己完成进阶挑战过程中的想法与思路。

    问题描述

    原始的问题,可以参考:NineData 第二届数据库编程大赛 用一条SQL秒杀火车票,本文仅考虑其中的“进阶挑战”。这里该“进阶挑战问题”复述如下。

    有如下两张表存放着乘客信息和列车信息,使用一条SQL给每个乘客分配一趟列车以及对应的座位号,需要注意,需要考虑进阶挑战的一些要求,比如,每趟列车可以发售10%的无座车票;车票需要有限发售有座车票,然后才开始发售无座车票。

    mysql> desc passenger;
    +-------------------+-------------+------+-----+
    | Field             | Type        | Null | Key |
    +-------------------+-------------+------+-----+
    | passenger_id      | varchar(16) | NO   | PRI |
    | departure_station | varchar(32) | NO   |     |
    | arrival_station   | varchar(32) | NO   |     |
    +-------------------+-------------+------+-----+
    mysql> desc train;
    +-------------------+-------------+------+-----+
    | Field             | Type        | Null | Key |
    +-------------------+-------------+------+-----+
    | train_id          | varchar(8)  | NO   | PRI |
    | departure_station | varchar(32) | NO   |     |
    | arrival_station   | varchar(32) | NO   |     |
    | seat_count        | int         | NO   |     |
    +-------------------+-------------+------+-----+

    示例数据如下:

    mysql> select * from passenger limit 3;
    +--------------+-------------------+-----------------+
    | passenger_id | departure_station | arrival_station |
    +--------------+-------------------+-----------------+
    | P00000001    | 上海              | 福州            |
    | P00000002    | 成都              | 成都            |
    | P00000003    | 乌鲁木齐          | 太原            |
    +--------------+-------------------+-----------------+
    mysql> select * from train limit 3;
    +----------+-------------------+-----------------+------------+
    | train_id | departure_station | arrival_station | seat_count |
    +----------+-------------------+-----------------+------------+
    | G1006    | 重庆              | 北京            |       1600 |
    | G1007    | 杭州              | 福州            |        600 |
    | G1008    | 济南              | 合肥            |        800 |
    +----------+-------------------+-----------------+------------+

    解题思路

    对乘客进行编号

    首先利用数据库的Windows Function功能对所有的乘客先分组再编号,具体的,按照“出发站”和“到达站”分组,然后在组内进行编号。次编号则为后续乘客车票分配的编号。例如,从 A 到 B 地,一共有 2420 个乘客。那么乘客的编号则是1…2420;再有乘客从 C 到 D 地,共有1800个乘客,则编号则为 1 … 1800。大概可以使用类似如下的 SQL 代码实现:

    ROW_NUMBER() over(PARTITION BY departure_station,arrival_station) as seq

    对列车进行排序和计算

    与乘客类似的,先按照出发和到达站点进行分组,并计算每个列车能够容纳的乘客数量,即座位数量的 1.1 倍。然后,在分组内进行“累加”计算,该累加计算,需算出每个列车能够运载乘客的起始序号和结束序号。例如,从 A 到 B地,共有列车 G01 和 G07 ,并分别有 600 和 1600 个座位。那么,经过上述的累加计算,列车 G01 能够运载的乘客编号应该是 1 到 660,而 G01 能够运载的乘客编号则为 661 到 2420 (即为 660 + 1600*110%)。

    上述计算也可以使用 Window Function来实现,参考实现如下:

            sum(seat_count*1.1)
              over (
                     PARTITION BY departure_station,arrival_station
                     ORDER BY train_id
                   ) as p_seat_to ,

    合并计算结果

    然后,将上述经过计算的乘客表和列车表进行 JOIN ,条件是 起始站和到达站相同,且乘客编号在列车编号之间。如果,乘客无法关联出列车,则表示无法分配列车。

    该方案的最终 SQL

    SELECT
      p_01.p_id,
      p_01.d_s,
      p_01.a_s,
      t_01.train_id as t_id,
      p_01.seq, -- passager seq from d_s to a_s
      t_01.seat_count,
      @p_seat_from := (t_01.p_seat_to-t_01.seat_count*1.1 + 1) as seat_from, -- train seat from(start index)
      t_01.p_seat_to as seat_to, -- train seat from(start index)
    
      if(p_01.seq >= p_seat_to-seat_count*0.1 + 1, "ti_no_seat","...") as ti_no_seat,
                    
      
      @seq_in_train := p_01.seq - @p_seat_from + 1  as seq_in_train, -- seq in the train
    
      @carriage_id := ceil(@seq_in_train/100) as t_carr_id, -- for carriage id 
    
      @row_id := ceil((@seq_in_train%100)/5) as row_id, -- row_id
    
      @seat_id := ceil((@seq_in_train%100)%5) seat_id  -- 0,1,2,3,4  A B C E F
    
    
    FROM
         (
           select
               ROW_NUMBER() over(PARTITION BY departure_station,arrival_station) as seq ,
               passenger_id as p_id,
               departure_station as d_s,
               arrival_station as a_s
           from
           passenger
         ) as p_01
    
         LEFT JOIN
    
        (
          select
            seat_count,
            sum(seat_count*1.1)
              over (
                     PARTITION BY departure_station,arrival_station
                     ORDER BY train_id
                   ) as p_seat_to ,
            train_id,
            departure_station as d_s ,
            arrival_station as a_s
          from
          train
        ) t_01
    
        ON
                p_01.seq >= p_seat_to-seat_count*1.1 + 1
            and p_01.seq <= p_seat_to
            and p_01.d_s =  t_01.d_s
            and p_01.a_s =  t_01.a_s

    上述实现的问题

    这样的实现,是可以完成相关的座位分配。但是,却会出现一个不合理的情况,即可能有车次的座位没有分配完,但是有一部分乘客却被分配到了无座的车次。比如,从A到B的车次,有两班,第一班车600个座位,第二版1600个座位,一共有 800 个乘客的话,那么这里分配自由度就比较高,比如这种情况依旧分配了 220 个无座的座位,是否是满足要求的。

    在最初,该赛题还未对外发布时,是没有该限制的。而后,发现该漏洞后,新增了一个规则,即需要先把有座的票优先分配,再分配无座的车票。

    考虑优先分配有座

    考虑优先分配有座的车票,再对上述实现进行一定程度的修改。

    重新考虑对列车的编号和计算

    对于每一趟列车X,构造一个虚拟列车X',该虚拟列车X'虚拟的负责所有的X列车的座票。而在给列车中计算起始和结束乘客编号时,则优先计算原列车的编号范围,在所有的原列车编号计算完成后,再计算X'的乘客编号范围。

    这里使用 CTEs 实现该表达如下:

    WITH 
      t_no_seat_virtual AS (
            select train_id as t_id,departure_station as d_s,arrival_station as a_s,seat_count, seat_count*0.1 as seat_count_no_seat 
            from train ),
      t_include_no_seat AS (
            select t_id,d_s ,a_s ,seat_count, "with_seat" as if_seat
            from t_no_seat_virtual
            union 
            select t_id,d_s ,a_s ,seat_count_no_seat, "no_seat" as if_seat
            from t_no_seat_virtual)
    SELECT * from t_include_no_seat ORDER BY t_id

    包含虚拟列车的表 t_include_no_seat

    然后把上面的实现中,表train替换成这里的 t_include_no_seat ,包含了额外的“虚拟列车”,完整的 SQL 如下:

    WITH
      t_no_seat_virtual AS (
            select train_id as t_id,departure_station as d_s,arrival_station as a_s,seat_count, seat_count*0.1 as seat_count_no_seat
            from train ),
      t_include_no_seat AS (
            select t_id,d_s ,a_s ,seat_count, 0 as if_no_seat
            from t_no_seat_virtual
            union
            select t_id,d_s ,a_s ,seat_count_no_seat, 1 as if_no_seat
            from t_no_seat_virtual)
    select
      seat_count,
      sum(seat_count)
        over (
               PARTITION BY d_s,a_s
               ORDER BY t_id,if_no_seat
             ) as p_seat_to ,
      t_id,
      d_s ,
      a_s,
      if_no_seat
    from
    t_include_no_seat

    返回的结果如下:

    +------------+-----------+------+--------+--------+------------+
    | seat_count | p_seat_to | t_id | d_s    | a_s    | if_no_seat |
    +------------+-----------+------+--------+--------+------------+
    |      600.0 |     600.0 | G109 | 上海   | 北京   |          0 |
    |       60.0 |     660.0 | G109 | 上海   | 北京   |          1 |
    |     1600.0 |    2260.0 | G40  | 上海   | 北京   |          0 |
    |      160.0 |    2420.0 | G40  | 上海   | 北京   |          1 |
    |     1600.0 |    4020.0 | G70  | 上海   | 北京   |          0 |
    |      160.0 |    4180.0 | G70  | 上海   | 北京   |          1 |
    |     1600.0 |    1600.0 | G113 | 上海   | 广州   |          0 |
    |      160.0 |    1760.0 | G113 | 上海   | 广州   |          1 |
    |     1600.0 |    3360.0 | G26  | 上海   | 广州   |          0 |
    |      160.0 |    3520.0 | G26  | 上海   | 广州   |          1 |
    |     1600.0 |    5120.0 | G48  | 上海   | 广州   |          0 |
    |      160.0 |    5280.0 | G48  | 上海   | 广州   |          1 |
    |     1600.0 |    1600.0 | G52  | 上海   | 成都   |          0 |
    |      160.0 |    1760.0 | G52  | 上海   | 成都   |          1 |
    |     1600.0 |    3360.0 | G8   | 上海   | 成都   |          0 |
    |      160.0 |    3520.0 | G8   | 上海   | 成都   |          1 |
    |     1600.0 |    1600.0 | G107 | 上海   | 武汉   |          0 |
    |      160.0 |    1760.0 | G107 | 上海   | 武汉   |          1 |
    |     1600.0 |    3360.0 | G17  | 上海   | 武汉   |          0 |
    |      160.0 |    3520.0 | G17  | 上海   | 武汉   |          1 |

    这里需要注意的是,编号的 ORDER BY 需要按照 if_no_seat,t_id 进行排序,这样就可以保障,优先分配有座位的位置。

    WITH
      t_no_seat_virtual AS (
            select train_id as t_id,departure_station as d_s,arrival_station as a_s,seat_count, seat_count*0.1 as seat_count_no_seat
            from train ),
      t_include_no_seat AS (
            select t_id,d_s ,a_s ,seat_count, 0 as if_no_seat
            from t_no_seat_virtual
            union
            select t_id,d_s ,a_s ,seat_count_no_seat, 1 as if_no_seat
            from t_no_seat_virtual)
    select
      sum(seat_count)
        over (
               PARTITION BY d_s,a_s
               ORDER BY     if_no_seat,t_id
             ) as p_seat_to ,
      seat_count,
      t_id,
      d_s ,
      a_s,
      if_no_seat
    from
    t_include_no_seat

    按照乘客序号分配座位

    与前述的实现相同,首先按照始发和到达站点将旅客表与“虚拟列车”表关联。如果自己序列落在某个列车的区间中就表示有座位。

    关于 row 的分配异常问题

    按照上述的分配,会将编号为 100 背书的人,分配为 0F,而正确的应该是 20 F。所以,需要额外处理该数值。具体的:

    ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)/5) 

    修改如下:

    IF( (p_01.seq-t_01.p_seat_to + t_01.seat_count)%100 = 0, "20" ,ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)/5))  

    这部分代码实现较为冗长,更好的方法是先计算偏倚值,然后使用字符串截取函数截取,而无需写这么多的CASE ... WHEN

    完整的SQL

    WITH 
      t_no_seat_virtual AS (
            select train_id as t_id,departure_station as d_s,arrival_station as a_s,seat_count, seat_count*0.1 as seat_count_no_seat 
            from train ),
      t_include_no_seat AS (
            select t_id,d_s ,a_s ,seat_count, 0 as if_no_seat
            from t_no_seat_virtual
            union 
            select t_id,d_s ,a_s ,seat_count_no_seat, 1 as if_no_seat
            from t_no_seat_virtual)
    
    SELECT
      p_01.p_id,
      p_01.d_s,
      p_01.a_s,
      t_01.t_id as t_id,
      p_01.seq, -- passager seq from d_s to a_s
      t_01.seat_count,
      t_01.if_no_seat,
      @p_seat_from := (t_01.p_seat_to-t_01.seat_count + 1) as seat_from, -- train seat from(start index)
      t_01.p_seat_to as seat_to, -- train seat from(start index)
      
      @seq_in_train := p_01.seq - @p_seat_from + 1  as seq_in_train, -- seq in the train
    
      @carriage_id := ceil(@seq_in_train/100) as t_carr_id, -- for carriage id 
    
      @row_id := ceil((@seq_in_train%100)/5) as row_id, -- row_id
    
      @seat_id := ceil((@seq_in_train%100)%5) seat_id,  -- 0,1,2,3,4  A B C E F
    
      CASE
        WHEN @seat_id = 1 THEN CONCAT(@row_id,"A")
        WHEN @seat_id = 2 THEN CONCAT(@row_id,"B")
        WHEN @seat_id = 3 THEN CONCAT(@row_id,"C")
        WHEN @seat_id = 4 THEN CONCAT(@row_id,"E")
        WHEN @seat_id = 0 THEN CONCAT(@row_id,"F")
        ELSE "ERROR"
      END as seat_index
    
    FROM
         (
           select
               ROW_NUMBER() over(PARTITION BY departure_station,arrival_station) as seq ,
               passenger_id as p_id,
               departure_station as d_s,
               arrival_station as a_s
           from
           passenger
         ) as p_01
    
         LEFT JOIN
    
        (
          select
            seat_count,
            sum(seat_count)
              over (
                     PARTITION BY d_s,a_s
                     ORDER BY     if_no_seat,t_id
                   ) as p_seat_to ,
            t_id,
            d_s ,
            a_s ,
            if_no_seat
          from
          t_include_no_seat
        ) t_01
    
        ON
                p_01.seq >= p_seat_to-seat_count + 1
            and p_01.seq <= p_seat_to
            and p_01.d_s =  t_01.d_s
            and p_01.a_s =  t_01.a_s

    修正SQL

    最后按照题目要求,对输出结果做一些修正。具体的:

    • 按要求,如果分配座位为无座的,则在车厢号展示为””,座位号显示”无座”。
    • 删除中间计算结果,为了保证性能,就不在外面再套一层了,事实上,套一层可读性会更好
    WITH 
      t_no_seat_virtual AS (
            select train_id as t_id,
                   departure_station as d_s,
                   arrival_station as a_s,
                   seat_count, seat_count*0.1 as seat_count_no_seat 
            from train ),
      t_include_no_seat AS (
            select t_id,d_s ,a_s ,seat_count, 0 as if_no_seat
            from t_no_seat_virtual
            union 
            select t_id,d_s ,a_s ,seat_count_no_seat, 1 as if_no_seat
            from t_no_seat_virtual)
    
    SELECT
      p_01.p_id,
      p_01.d_s,
      p_01.a_s,
      t_01.t_id as t_id,
      p_01.seq, -- passager seq from d_s to a_s
      t_01.seat_count,
      t_01.if_no_seat,
      @p_seat_from := (t_01.p_seat_to-t_01.seat_count + 1) as seat_from, -- train seat from(start index)
      t_01.p_seat_to as seat_to, -- train seat from(start index)
      
      @seq_in_train := p_01.seq - @p_seat_from + 1  as seq_in_train, -- seq in the train
    
      -- @carriage_id := ceil(@seq_in_train/100) as t_carr_id, -- for carriage id 
    
      IF(if_no_seat, "" , @carriage_id := ceil(@seq_in_train/100) ) as t_carr_id,
    
      @row_id := ceil((@seq_in_train%100)/5) as row_id, -- row_id
    
      @seat_id := IF( !isnull(t_01.t_id) and if_no_seat,-1,ceil((@seq_in_train%100)%5)) as seat_id,  -- 0,1,2,3,4  A B C E F
    
      CASE
        WHEN @seat_id = 1  THEN CONCAT(@row_id,"A")
        WHEN @seat_id = 2  THEN CONCAT(@row_id,"B")
        WHEN @seat_id = 3  THEN CONCAT(@row_id,"C")
        WHEN @seat_id = 4  THEN CONCAT(@row_id,"E")
        WHEN @seat_id = 0  THEN CONCAT(@row_id,"F")
        WHEN @seat_id = -1 THEN "无座"
        ELSE NULL
      END as seat_index
    
    FROM
         (
           select
               ROW_NUMBER() over(PARTITION BY departure_station,arrival_station) as seq ,
               passenger_id as p_id,
               departure_station as d_s,
               arrival_station as a_s
           from
           passenger
         ) as p_01
    
         LEFT JOIN
    
        (
          select
            seat_count,
            sum(seat_count)
              over (
                     PARTITION BY d_s,a_s
                     ORDER BY     if_no_seat,t_id
                   ) as p_seat_to ,
            t_id,
            d_s ,
            a_s ,
            if_no_seat
          from
          t_include_no_seat
        ) t_01
    
        ON
                p_01.seq >= p_seat_to-seat_count + 1
            and p_01.seq <= p_seat_to
            and p_01.d_s =  t_01.d_s
            and p_01.a_s =  t_01.a_s

    正确性验证

    这个 SQL 的正确性并不好验证。而事实上,只要能够正确的完成这条SQL,基本上就已经打败了80%的选手了。如果性能再有一些优化,基本上已经是前10%的选手。

    这里从以下几个方面对SQL正确性做初步验证:

    • 对于每一个系列(始发站和到达站相同)的车次进行统计,统计座位数量和旅客数量,然后看实际分配情况是否符合
    • 手动检车车厢、座位号分配的情况
    • 检查某个系列,无座和有座的乘客数量

    座位供需统计

    座位的供需情况有如下三种:(a) 座票供应充值 (b) 加上无座后供应充值 (c) 供应不足。完整的供需计算如下:

    
    case #1
    ti_supply |__________________________|___________|
    ti_needed |<------------------->|
    
    case #2
    ti_supply |__________________________|___________|
    ti_needed |<------------------------------>|
    
    case #3
    ti_supply |__________________________|___________|
    ti_needed |<-------------------------------------------->|
    
    -- ti_ always short for tickets
    -- p_  always short for passager
    -- t_  always short for train
    select
        p_needed.d_s,
        p_needed.a_s,
        ti_needed,
        ti_supply,
        if( ti_needed > ti_supply, ti_needed - ti_supply , 0 )  as p_without_ti,
        if( ti_needed > ti_supply/1.1 ,  round(ti_supply/1.1,0) , ti_needed ) as p_with_ti_with_seat,
        if( ti_needed <= ti_supply/1.1 ,   -- case #1
            0,
            if( ti_needed <= ti_supply,    -- case #2
                round(ti_needed - ti_supply/1.1,0) ,
                ti_supply/11               -- case #3
              )
          ) as p_with_ti_without_seat
    from
      (
        select
          -- passenger_id as p_id,
          departure_station as d_s,
          arrival_station as a_s,
          count(1) as ti_needed
        from
          passenger
        group by
          departure_station,arrival_station
      ) p_needed
      ,
      (
        select
          -- train_id t_id,
          departure_station as d_s ,
          arrival_station as a_s,
          1.1*sum(seat_count) as ti_supply
        from
          train
        group by
          departure_station,arrival_station
      ) t_supply
    WHERE
            p_needed.d_s = t_supply.d_s
        and p_needed.a_s = t_supply.a_s

    返回的供需表如下:

    +--------+--------+-----------+-----------+--------------+---------------------+------------------------+
    | d_s    | a_s    | ti_needed | ti_supply | p_without_ti | p_with_ti_with_seat | p_with_ti_without_seat |
    +--------+--------+-----------+-----------+--------------+---------------------+------------------------+
    | 上海   | 北京   |      2460 |    4180.0 |            0 |                2460 |                      0 |
    | 上海   | 广州   |      2406 |    5280.0 |            0 |                2406 |                      0 |
    | 上海   | 成都   |      2421 |    3520.0 |            0 |                2421 |                      0 |
    | 上海   | 武汉   |      2454 |    7700.0 |            0 |                2454 |                      0 |
    | 上海   | 深圳   |      2388 |    7040.0 |            0 |                2388 |                      0 |
    | 北京   | 上海   |      2381 |    1760.0 |        621.0 |                1600 |              160.00000 |
    | 北京   | 广州   |      2448 |    3520.0 |            0 |                2448 |                      0 |
    | 北京   | 成都   |      2384 |    3520.0 |            0 |                2384 |                      0 |
    | 北京   | 杭州   |      2478 |    3520.0 |            0 |                2478 |                      0 |
    | 北京   | 武汉   |      2404 |    5940.0 |            0 |                2404 |                      0 |
    | 北京   | 深圳   |      2342 |    3520.0 |            0 |                2342 |                      0 |
    | 广州   | 上海   |      2339 |    4180.0 |            0 |                2339 |                      0 |
    | 广州   | 北京   |      2368 |    1760.0 |        608.0 |                1600 |              160.00000 |
    | 广州   | 成都   |      2332 |    3520.0 |            0 |                2332 |                      0 |
    | 广州   | 杭州   |      2407 |    5280.0 |            0 |                2407 |                      0 |
    | 广州   | 武汉   |      2320 |    3520.0 |            0 |                2320 |                      0 |
    | 广州   | 深圳   |      2352 |    1760.0 |        592.0 |                1600 |              160.00000 |
    | 成都   | 上海   |      2422 |    4180.0 |            0 |                2422 |                      0 |
    | 成都   | 北京   |      2318 |    5940.0 |            0 |                2318 |                      0 |
    | 成都   | 广州   |      2450 |    1760.0 |        690.0 |                1600 |              160.00000 |
    | 成都   | 杭州   |      2343 |    5280.0 |            0 |                2343 |                      0 |
    | 成都   | 武汉   |      2415 |    5280.0 |            0 |                2415 |                      0 |
    | 成都   | 深圳   |      2364 |    2420.0 |            0 |                2200 |                    164 |
    | 杭州   | 北京   |      2389 |    1760.0 |        629.0 |                1600 |              160.00000 |
    | 杭州   | 成都   |      2370 |    1760.0 |        610.0 |                1600 |              160.00000 |
    | 杭州   | 深圳   |      2387 |   10560.0 |            0 |                2387 |                      0 |
    | 武汉   | 上海   |      2323 |    5280.0 |            0 |                2323 |                      0 |
    | 武汉   | 北京   |      2453 |    5280.0 |            0 |                2453 |                      0 |
    | 武汉   | 广州   |      2395 |   10560.0 |            0 |                2395 |                      0 |
    | 武汉   | 成都   |      2337 |    1760.0 |        577.0 |                1600 |              160.00000 |
    | 武汉   | 杭州   |      2428 |    3520.0 |            0 |                2428 |                      0 |
    | 武汉   | 深圳   |      2390 |    5280.0 |            0 |                2390 |                      0 |
    | 深圳   | 上海   |      2251 |    3520.0 |            0 |                2251 |                      0 |
    | 深圳   | 北京   |      2309 |    7040.0 |            0 |                2309 |                      0 |
    | 深圳   | 成都   |      2341 |    3520.0 |            0 |                2341 |                      0 |
    | 深圳   | 杭州   |      2412 |     660.0 |       1752.0 |                 600 |               60.00000 |
    | 深圳   | 武汉   |      2329 |   10120.0 |            0 |                2329 |                      0 |
    +--------+--------+-----------+-----------+--------------+---------------------+------------------------+

    SQL 计算返回结果统计

    先使用 CREATE TABLE t_ret ...将结果集存储一个中间的临时表。

    然后,再计算 SQL返回结果表中的统计数据:

    SELECT 
      d_s,a_s,
      CASE
        WHEN ISNULL(seat_index)  THEN "p_without_ti"
        WHEN seat_index = "无座"  THEN "p_with_ti_without_seat"
        ELSE "p_with_ti_with_seat"
      END as p_status,
      COUNT(1)
    FROM t_ret
    GROUP BY d_s,a_s , p_status

    返回:

    +--------+--------+------------------------+----------+
    | d_s    | a_s    | p_status               | COUNT(1) |
    +--------+--------+------------------------+----------+
    | 上海   | 北京   | p_with_ti_with_seat    |     2460 |
    | 上海   | 广州   | p_with_ti_with_seat    |     2406 |
    | 上海   | 成都   | p_with_ti_with_seat    |     2421 |
    | 上海   | 杭州   | p_without_ti           |     2373 |
    | 上海   | 武汉   | p_with_ti_with_seat    |     2454 |
    | 上海   | 深圳   | p_with_ti_with_seat    |     2388 |
    | 北京   | 上海   | p_with_ti_with_seat    |     1600 |
    | 北京   | 上海   | p_with_ti_without_seat |      160 |
    | 北京   | 上海   | p_without_ti           |      621 |
    | 北京   | 广州   | p_with_ti_with_seat    |     2448 |
    | 北京   | 成都   | p_with_ti_with_seat    |     2384 |
    | 北京   | 杭州   | p_with_ti_with_seat    |     2478 |
    | 北京   | 武汉   | p_with_ti_with_seat    |     2404 |
    | 北京   | 深圳   | p_with_ti_with_seat    |     2342 |
    | 广州   | 上海   | p_with_ti_with_seat    |     2339 |
    | 广州   | 北京   | p_with_ti_with_seat    |     1600 |
    | 广州   | 北京   | p_with_ti_without_seat |      160 |
    | 广州   | 北京   | p_without_ti           |      608 |
    | 广州   | 成都   | p_with_ti_with_seat    |     2332 |
    | 广州   | 杭州   | p_with_ti_with_seat    |     2407 |
    | 广州   | 武汉   | p_with_ti_with_seat    |     2320 |
    | 广州   | 深圳   | p_with_ti_with_seat    |     1600 |
    | 广州   | 深圳   | p_with_ti_without_seat |      160 |
    | 广州   | 深圳   | p_without_ti           |      592 |
    | 成都   | 上海   | p_with_ti_with_seat    |     2422 |
    | 成都   | 北京   | p_with_ti_with_seat    |     2318 |
    | 成都   | 广州   | p_with_ti_with_seat    |     1600 |
    | 成都   | 广州   | p_with_ti_without_seat |      160 |
    | 成都   | 广州   | p_without_ti           |      690 |
    | 成都   | 杭州   | p_with_ti_with_seat    |     2343 |
    | 成都   | 武汉   | p_with_ti_with_seat    |     2415 |
    | 成都   | 深圳   | p_with_ti_with_seat    |     2200 |
    | 成都   | 深圳   | p_with_ti_without_seat |      164 |
    | 杭州   | 上海   | p_without_ti           |     2376 |
    | 杭州   | 北京   | p_with_ti_with_seat    |     1600 |
    | 杭州   | 北京   | p_with_ti_without_seat |      160 |
    | 杭州   | 北京   | p_without_ti           |      629 |
    | 杭州   | 广州   | p_without_ti           |     2401 |
    | 杭州   | 成都   | p_with_ti_with_seat    |     1600 |
    | 杭州   | 成都   | p_with_ti_without_seat |      160 |
    | 杭州   | 成都   | p_without_ti           |      610 |
    | 杭州   | 武汉   | p_without_ti           |     2353 |
    | 杭州   | 深圳   | p_with_ti_with_seat    |     2387 |
    | 武汉   | 上海   | p_with_ti_with_seat    |     2323 |
    | 武汉   | 北京   | p_with_ti_with_seat    |     2453 |
    | 武汉   | 广州   | p_with_ti_with_seat    |     2395 |
    | 武汉   | 成都   | p_with_ti_with_seat    |     1600 |
    | 武汉   | 成都   | p_with_ti_without_seat |      160 |
    | 武汉   | 成都   | p_without_ti           |      577 |
    | 武汉   | 杭州   | p_with_ti_with_seat    |     2428 |
    | 武汉   | 深圳   | p_with_ti_with_seat    |     2390 |
    | 深圳   | 上海   | p_with_ti_with_seat    |     2251 |
    | 深圳   | 北京   | p_with_ti_with_seat    |     2309 |
    | 深圳   | 广州   | p_without_ti           |     2387 |
    | 深圳   | 成都   | p_with_ti_with_seat    |     2341 |
    | 深圳   | 杭州   | p_with_ti_with_seat    |      600 |
    | 深圳   | 杭州   | p_with_ti_without_seat |       60 |
    | 深圳   | 杭州   | p_without_ti           |     1752 |
    | 深圳   | 武汉   | p_with_ti_with_seat    |     2329 |
    +--------+--------+------------------------+----------+

    从上述的两个结果对比来看,挑选了几个来看,数据是一致的。比如,“深圳->杭州”,上面表格计算得

    | 深圳   | 杭州   |      2412 |     660.0 |       1752.0 |                 600 |               60.00000 |

    对比:

    | 深圳   | 杭州   | p_with_ti_with_seat    |      600 |
    | 深圳   | 杭州   | p_with_ti_without_seat |       60 |
    | 深圳   | 杭州   | p_without_ti           |     1752 |

    这里的 600、60、1752也是一致的。

    最后按照输出进行调整

    WITH
      t_no_seat_virtual AS (
        select
          train_id as t_id,
          departure_station as d_s,
          arrival_station as a_s,
          seat_count,
          seat_count*0.1 as seat_count_no_seat
        from train
      ),
      t_include_no_seat AS (
        select t_id,d_s ,a_s ,seat_count, 0 as if_no_seat
        from t_no_seat_virtual
        union
        select t_id,d_s ,a_s ,seat_count_no_seat, 1 as if_no_seat
        from t_no_seat_virtual
      )
    SELECT
      p_01.p_id,         -- output 01
      p_01.d_s,          -- output 02
      p_01.a_s,          -- output 03
      t_01.t_id as t_id, -- output 04
      IF(
          if_no_seat,
          "" ,
          ceil((p_01.seq-t_01.p_seat_to + t_01.seat_count)/100)
      ) as t_carr_id, -- output 05
    
      CASE IF( !isnull(t_01.t_id) and if_no_seat,-1,ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)%5))
        WHEN 1  THEN CONCAT( ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)/5) ,"A")
        WHEN 2  THEN CONCAT( ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)/5) ,"B")
        WHEN 3  THEN CONCAT( ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)/5) ,"C")
        WHEN 4  THEN CONCAT( ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)/5) ,"E")
        WHEN 0  THEN CONCAT( IF( (p_01.seq-t_01.p_seat_to + t_01.seat_count)%100 = 0, "20" ,ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)/5)) ,"F")
        WHEN -1 THEN "无座"
        ELSE NULL
      END as seat_index   -- output 06
    FROM
      (
        select
          ROW_NUMBER() over(PARTITION BY departure_station,arrival_station) as seq ,
          passenger_id as p_id,
          departure_station as d_s,
          arrival_station as a_s
        from
        passenger
      ) as p_01
    
      LEFT JOIN
    
      (
        select
          seat_count,
          sum(seat_count)
            over (
                   PARTITION BY d_s,a_s
                   ORDER BY     if_no_seat,t_id
                 ) as p_seat_to ,
          t_id,
          d_s ,
          a_s ,
          if_no_seat
        from
        t_include_no_seat
      ) t_01
    
      ON
            p_01.seq >= p_seat_to-seat_count + 1
        and p_01.seq <= p_seat_to
        and p_01.d_s =  t_01.d_s
        and p_01.a_s =  t_01.a_s
    ORDER BY p_01.p_id

    赛题与数据

    原始的比赛题目参考:https://www.ninedata.cloud/sql_train2024 这里仅讨论其中的“进阶挑战”。

    表定义(MySQL)

    CREATE DATABASE `game_ticket`;
    
    use game_ticket;
    
    CREATE TABLE `passenger` (
      `passenger_id` varchar(16) NOT NULL,
      `departure_station` varchar(32) NOT NULL,
      `arrival_station` varchar(32) NOT NULL,
      PRIMARY KEY (`passenger_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    CREATE TABLE `train` (
      `train_id` varchar(8) NOT NULL,
      `departure_station` varchar(32) NOT NULL,
      `arrival_station` varchar(32) NOT NULL,
      `seat_count` int NOT NULL,
      PRIMARY KEY (`train_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    赛题数据

    为了方便调试,这里保持了一份CSV的输入如下,供测试使用:

    相关阅读