从一个游戏SQL的优化说起‐2 - l1t1/note GitHub Wiki

WITH 
-- 将108张牌分配给4个人，用轮流、顺序分配的方案
tmp0 AS (
    SELECT 
        a.i AS category,
        b.i AS seq,
        SUBSTRING(c.j, (a.i * 27) + b.i, 1) AS chr,
        ASCII(chr) - 126982 AS n,
        (n - 1) // 9 AS hs,
        CASE n % 9 WHEN 0 THEN 9 ELSE n % 9 END AS pd
    FROM 
        range(4) a(i),
        range(1, 28) b(i),
        games c
),
-- 第一步，能凑成对子、2张顺子情况
tmp1 AS (
    SELECT 
        a.*,
        MIN(b.seq) FILTER (WHERE a.category = b.category AND a.hs = b.hs AND a.pd = b.pd AND b.seq > a.seq) AS dz,
        MIN(b.seq) FILTER (WHERE a.category = b.category AND a.hs = b.hs AND a.pd = b.pd + 1 AND b.seq > a.seq) AS sz1,
        MIN(b.seq) FILTER (WHERE a.category = b.category AND a.hs = b.hs AND a.pd = b.pd - 1 AND b.seq > a.seq) AS sz2
    FROM 
        tmp0 a
    LEFT JOIN 
        tmp0 b ON a.category = b.category AND a.hs = b.hs
    GROUP BY 
        a.category, a.seq, a.chr, a.n, a.hs, a.pd
    ORDER BY 
        a.category, a.seq
),
-- 第二步，加能凑成三张顺子的情况
tmp2 AS (
    SELECT 
        a.*,
        MIN(b.seq) FILTER (WHERE a.category = b.category AND a.hs = b.hs AND a.pd = b.pd + 2 AND b.seq > a.seq AND sz1 IS NOT NULL) AS sz11,
        MIN(b.seq) FILTER (WHERE a.category = b.category AND a.hs = b.hs AND a.pd = b.pd - 2 AND b.seq > a.seq AND sz2 IS NOT NULL) AS sz22,
        MIN(b.seq) FILTER (WHERE a.category = b.category AND a.hs = b.hs AND a.pd = b.pd AND b.seq > a.dz AND dz IS NOT NULL) AS sz
    FROM 
        tmp1 a
    LEFT JOIN 
        tmp0 b ON a.category = b.category AND a.hs = b.hs
    GROUP BY 
        a.category, a.seq, a.chr, a.n, a.hs, a.pd, a.dz, a.sz1, a.sz2
    ORDER BY 
        a.category, a.seq
),
-- 生成所有可以形成3张组合的牌
tmp_3z AS (
    SELECT 
        category, 
        LIST_SORT([seq, sz1, sz11]) AS n 
    FROM 
        tmp2 
    WHERE 
        sz11 IS NOT NULL
    UNION  
    SELECT 
        category, 
        LIST_SORT([seq, sz2, sz22]) AS n 
    FROM 
        tmp2 
    WHERE 
        sz22 IS NOT NULL
    UNION 
    SELECT 
        category, 
        LIST_SORT([seq, dz, sz]) AS n 
    FROM 
        tmp2 
    WHERE 
        sz IS NOT NULL
),
-- 生成所有对子组合
tmp_dz AS (
    SELECT 
        category, 
        LIST_SORT([seq, dz]) AS n 
    FROM 
        tmp1 
    WHERE 
        dz IS NOT NULL
),
-- 生成所有可能的组合
tmp_all AS (
    SELECT 
        a.category,
        a.n AS sz1,
        b.n AS sz2,
        c.n AS sz3,
        d.n AS dz,
        LIST_REVERSE(LIST_SORT(ARRAY_DISTINCT(
            ARRAY_CONCAT(a.n, ARRAY_CONCAT(b.n, ARRAY_CONCAT(c.n, d.n)))
        ))) AS n
    FROM 
        tmp_3z a 
    LEFT JOIN 
        tmp_3z b ON a.category = b.category
    LEFT JOIN 
        tmp_3z c ON a.category = c.category
    LEFT JOIN 
        tmp_dz d ON a.category = d.category
    WHERE 
        a.n < b.n AND b.n < c.n
        AND ARRAY_LENGTH(ARRAY_DISTINCT(ARRAY_CONCAT(a.n, ARRAY_CONCAT(b.n, ARRAY_CONCAT(c.n, d.n))))) = 11
),
-- 用最少摸牌次数能胡牌的组合
tmp_min AS (
    SELECT 
        MIN(n) AS n 
    FROM 
        tmp_all
),
-- 最终结果
final_result AS (
    SELECT 
        STRING_AGG(a.chr, '' ORDER BY a.chr) AS ret
    FROM 
        tmp1 a
    JOIN 
        tmp_min m ON a.seq IN (SELECT UNNEST(m.n))
    JOIN 
        tmp_all b ON b.n = m.n AND a.category = b.category
)
SELECT * FROM final_result;

│ 🀌🀍🀎🀔🀕🀖🀙🀙🀚🀛🀜 │

Run Time (s): real 0.046 user 0.108000 sys 0.020000

WITH 
games as(with t as (select chr(126983+i::int) pic from generate_series(0,26)t(i))select 2, string_agg(pic,''order by pic)j from t,generate_series(1,4) )