如何用SQL检测保龄球比赛中的分瓶（Split）情况？

嘿，这个问题我刚看到的时候也卡了一下——毕竟总不能把所有可能的分瓶组合都硬编码进SQL里吧？先把USBC的分瓶定义捋清楚，再一步步拆解怎么实现。

首先，先明确分瓶的核心判定规则（来自USBC）：

分瓶是指第一投后剩余立瓶满足以下所有条件：

1. 头瓶（1号瓶）已经被击倒；
2. 存在至少一组立瓶，要么它们之间有已击倒的瓶（比如7号和9号立着，中间的8号已倒），要么它们正前方有已击倒的瓶（比如5号和6号立着，最前方的1号已倒）。

接下来，我们要处理的核心问题是：如何把pinsHit字段里的逗号分隔瓶号转换成可操作的集合，再基于规则判断是否为分瓶。下面提供两种可行的思路，你可以根据自己使用的SQL方言调整。

思路一：用预定义规则表匹配 #

这种方法适合规则明确且很少变动的场景，我们先创建一个辅助表来定义所有分瓶的判定组合，再关联查询匹配。

步骤1：创建分瓶规则表 #

先把USBC定义的分瓶组合和对应条件存入辅助表：

-- 创建辅助表：存储分瓶判定规则
CREATE TABLE split_criteria (
    group_id INT,
    standing_pin1 INT, -- 组合中的第一个立瓶
    standing_pin2 INT, -- 组合中的第二个立瓶
    rule_type VARCHAR(20), -- 规则类型：between（中间有倒瓶）/ ahead（前方有倒瓶）
    required_down_pins VARCHAR(50) -- 需要已击倒的瓶号（逗号分隔）
);

-- 插入规则数据，覆盖常见分瓶场景
INSERT INTO split_criteria VALUES
-- 规则1：立瓶之间有倒瓶
(1, 7, 9, 'between', '8'),
(2, 3, 10, 'between', '8,9'),
(3, 4, 6, 'between', '5'),
(4, 2, 4, 'between', '3'),
(5, 6, 8, 'between', '7'),
(6, 3, 5, 'between', '4'),
-- 规则2：立瓶正前方有倒瓶
(7, 2, 3, 'ahead', '1'),
(8, 5, 6, 'ahead', '1'),
(9, 7, 10, 'ahead', '1,2,3');

步骤2：编写检测SQL #

以PostgreSQL为例，我们用CTE拆分字符串、计算剩余立瓶，再匹配规则：

WITH split_pins AS (
    -- 拆分pinsHit为单个瓶号
    SELECT
        gameid,
        frame,
        throwNum,
        pinsHit,
        UNNEST(STRING_TO_ARRAY(pinsHit, ','))::INT AS hit_pin
    FROM bowling_throws
    WHERE throwNum = 1 -- 只检测第一投
),
remaining_pins AS (
    -- 计算每个frame的剩余立瓶
    SELECT
        gameid,
        frame,
        throwNum,
        pinsHit,
        ARRAY(
            SELECT pin FROM GENERATE_SERIES(1,10) pin 
            WHERE pin NOT IN (SELECT hit_pin FROM split_pins sp WHERE sp.gameid = bp.gameid AND sp.frame = bp.frame)
        ) AS remaining_pin_array
    FROM split_pins bp
    GROUP BY gameid, frame, throwNum, pinsHit
),
candidate_frames AS (
    -- 筛选符合基础条件的记录：头瓶已倒，剩余立瓶≥2
    SELECT *
    FROM remaining_pins
    WHERE 1 = ANY(STRING_TO_ARRAY(pinsHit, ',')::INT[])
        AND ARRAY_LENGTH(remaining_pin_array, 1) >= 2
)
-- 最终检测是否为分瓶
SELECT
    cf.gameid,
    cf.frame,
    cf.pinsHit,
    cf.remaining_pin_array,
    CASE WHEN EXISTS (
        SELECT 1
        FROM split_criteria sc
        WHERE sc.standing_pin1 = ANY(cf.remaining_pin_array)
            AND sc.standing_pin2 = ANY(cf.remaining_pin_array)
            AND (
                -- 匹配between规则：中间有至少一个已击倒的瓶
                (sc.rule_type = 'between' AND EXISTS (
                    SELECT 1
                    FROM UNNEST(STRING_TO_ARRAY(sc.required_down_pins, ','))::INT req_pin
                    WHERE req_pin = ANY(STRING_TO_ARRAY(cf.pinsHit, ',')::INT[])
                ))
                OR
                -- 匹配ahead规则：前方所有要求的瓶都已击倒
                (sc.rule_type = 'ahead' AND NOT EXISTS (
                    SELECT 1
                    FROM UNNEST(STRING_TO_ARRAY(sc.required_down_pins, ','))::INT req_pin
                    WHERE req_pin NOT IN (SELECT hit_pin FROM split_pins sp WHERE sp.gameid = cf.gameid AND sp.frame = cf.frame)
                ))
            )
    ) THEN 'Split' ELSE 'Not Split' END AS is_split
FROM candidate_frames cf;

思路二：用瓶的坐标位置动态判断 #

这种方法更灵活，不用硬编码所有分瓶组合，而是通过保龄球瓶的物理坐标关系来动态判定。

步骤1：创建瓶的坐标表 #

先给每个瓶定义x-y坐标（模拟保龄球道上的排列）：

CREATE TABLE pin_coords (
    pin INT PRIMARY KEY,
    x INT, -- 横向坐标
    y INT  -- 纵向坐标（y=0是最前方的头瓶）
);

INSERT INTO pin_coords VALUES
(1,0,0),
(2,-1,1),
(3,1,1),
(4,-2,2),
(5,0,2),
(6,2,2),
(7,-3,3),
(8,-1,3),
(9,1,3),
(10,3,3);

步骤2：编写检测SQL #

还是以PostgreSQL为例，通过坐标关系判断分瓶：

WITH split_pins AS (
    -- 拆分pinsHit为单个瓶号
    SELECT
        gameid,
        frame,
        throwNum,
        pinsHit,
        UNNEST(STRING_TO_ARRAY(pinsHit, ','))::INT AS hit_pin
    FROM bowling_throws
    WHERE throwNum = 1
),
remaining_pins AS (
    -- 计算每个frame的剩余立瓶
    SELECT
        gameid,
        frame,
        throwNum,
        pinsHit,
        ARRAY(
            SELECT pin FROM pin_coords 
            WHERE pin NOT IN (SELECT hit_pin FROM split_pins sp WHERE sp.gameid = bp.gameid AND sp.frame = bp.frame)
        ) AS remaining_pin_array
    FROM split_pins bp
    GROUP BY gameid, frame, throwNum, pinsHit
),
candidate_frames AS (
    -- 筛选符合基础条件的记录
    SELECT *
    FROM remaining_pins
    WHERE 1 = ANY(STRING_TO_ARRAY(pinsHit, ',')::INT[])
        AND ARRAY_LENGTH(remaining_pin_array, 1) >= 2
),
remaining_pin_pairs AS (
    -- 生成剩余立瓶的所有两两组合（避免重复）
    SELECT
        cf.gameid,
        cf.frame,
        cf.pinsHit,
        cf.remaining_pin_array,
        pc1.pin AS pin1,
        pc1.x AS x1,
        pc1.y AS y1,
        pc2.pin AS pin2,
        pc2.x AS x2,
        pc2.y AS y2
    FROM candidate_frames cf
    CROSS JOIN UNNEST(cf.remaining_pin_array) pc1_pin
    JOIN pin_coords pc1 ON pc1.pin = pc1_pin
    CROSS JOIN UNNEST(cf.remaining_pin_array) pc2_pin
    JOIN pin_coords pc2 ON pc2.pin = pc2_pin
    WHERE pc1.pin < pc2.pin
)
-- 最终检测是否为分瓶
SELECT DISTINCT
    gameid,
    frame,
    pinsHit,
    remaining_pin_array,
    CASE WHEN EXISTS (
        SELECT 1
        FROM remaining_pin_pairs rpp
        WHERE rpp.gameid = cf.gameid AND rpp.frame = cf.frame
            AND (
                -- 情况1：同一纵向层的立瓶，之间有已击倒的瓶
                (rpp.y1 = rpp.y2 AND ABS(rpp.x1 - rpp.x2) >= 2 AND EXISTS (
                    SELECT 1
                    FROM pin_coords pc_mid
                    WHERE pc_mid.y = rpp.y1
                        AND pc_mid.x BETWEEN LEAST(rpp.x1, rpp.x2) + 1 AND GREATEST(rpp.x1, rpp.x2) - 1
                        AND pc_mid.pin IN (SELECT hit_pin FROM split_pins sp WHERE sp.gameid = rpp.gameid AND sp.frame = rpp.frame)
                ))
                OR
                -- 情况2：同一纵向层的立瓶，前方有已击倒的瓶
                (rpp.y1 = rpp.y2 AND EXISTS (
                    SELECT 1
                    FROM pin_coords pc_ahead
                    WHERE pc_ahead.y < rpp.y1
                        AND pc_ahead.pin IN (SELECT hit_pin FROM split_pins sp WHERE sp.gameid = rpp.gameid AND sp.frame = rpp.frame)
                ))
            )
    ) THEN 'Split' ELSE 'Not Split' END AS is_split
FROM candidate_frames cf;

两种思路对比 #

• 预定义规则表：实现简单，适合规则固定的场景，但需要维护规则表，新增分瓶场景时要手动添加规则。
• 坐标动态判断：更灵活，不用硬编码组合，能自动适配所有符合USBC定义的分瓶情况，但需要理解坐标逻辑，SQL稍复杂。

你可以根据自己的实际需求选择对应的方法，另外不同SQL方言的字符串拆分函数不同（比如MySQL用JSON_TABLE，SQL Server用STRING_SPLIT），需要对应调整拆分部分的代码。

备注：内容来源于stack exchange，提问作者John Sly