乐透开奖不同行列号码共现频次查询实现需求

实现思路 #

• 第一步：为每一期开奖记录生成唯一标识，将每行6个号码的宽表结构转换为单号码行的长表结构
• 第二步：基于同开奖期的号码，生成所有大小≥2的无序号码组合（强制按号码大小排序拼接，避免相同组合因顺序不同被判定为不同值）
• 第三步：按组合分组统计出现次数，过滤出出现次数≥2的结果输出

具体实现方案 #

方案1：SQL实现（以MySQL为例） #

直接基于数据库表查询，适合大数据量场景：

WITH numbered AS (
    -- 为每一期开奖记录生成唯一ID
    SELECT 
        ROW_NUMBER() OVER () AS draw_id,
        number_1, number_2, number_3, number_4, number_5, number_6
    FROM `list`
),
unpivoted AS (
    -- 宽表转长表，每一期的每个号码单独占一行
    SELECT draw_id, number_1 AS num FROM numbered
    UNION ALL SELECT draw_id, number_2 AS num FROM numbered
    UNION ALL SELECT draw_id, number_3 AS num FROM numbered
    UNION ALL SELECT draw_id, number_4 AS num FROM numbered
    UNION ALL SELECT draw_id, number_5 AS num FROM numbered
    UNION ALL SELECT draw_id, number_6 AS num FROM numbered
),
combinations AS (
    -- 生成所有无重复顺序的号码组合，覆盖2-6个号码的所有组合形式
    SELECT 
        a.draw_id,
        GROUP_CONCAT(DISTINCT b.num ORDER BY b.num SEPARATOR ',') AS num_combination
    FROM unpivoted a
    JOIN unpivoted b ON a.draw_id = b.draw_id AND a.num < b.num
    GROUP BY a.draw_id, a.num
)
-- 统计频次并过滤符合要求的结果
SELECT 
    num_combination,
    COUNT(DISTINCT draw_id) AS `TARGET`
FROM combinations
GROUP BY num_combination
HAVING `TARGET` >= 2
ORDER BY `TARGET` DESC;

方案2：Python Pandas实现 #

适合小数据量灵活处理，逻辑更直观：

import pandas as pd
from itertools import combinations

# 此处替换为你自己的数据库连接，从表中读取原始数据
df = pd.read_sql("SELECT * FROM `list`", con=your_db_connection)

combo_counter = {}
# 遍历每一期开奖记录生成所有组合并计数
for _, row in df.iterrows():
    # 先排序确保同组合的字符串拼接结果唯一
    nums = sorted([row[f'number_{i}'] for i in range(1,7)])
    # 生成所有长度≥2的组合
    for combo_len in range(2,7):
        for combo in combinations(nums, combo_len):
            combo_str = ','.join(map(str, combo))
            combo_counter[combo_str] = combo_counter.get(combo_str, 0) + 1

# 输出符合要求的结果
for combo, cnt in combo_counter.items():
    if cnt >=2:
        print(f"{combo} -> TARGET: {cnt}")

两种方案针对你提供的样例数据，都会输出预期结果：

28,36,55,57 -> TARGET: 2
30,40 -> TARGET: 2

内容的提问来源于stack exchange，提问作者CoinAnalyzer