实用脚本统计长短传比例如何分布？

wen 实用脚本 2026-10-22 2

统计长短传比例分布的实用脚本

下面给你一套可落地的脚本方案，适用于日志/埋点/交易流水中的"长传/短传"（如足球传球、网络数据包、消息体大小等）比例统计与分布分析，假设你的数据表中已有 pass_type（或 length）字段。

实用脚本统计长短传比例如何分布？

核心统计口径先明确

指标	定义
长传数	`length >= threshold`（如足球 30m，网络包 1500B）
短传数	`length < threshold`
长短传比例	`long_count / short_count`（或占比 `long/(long+short)`）
分布	按时间窗口 / 球员 / 会话 / 分桶统计比例分布（直方图、分位数）

SQL 版（最通用，适合数仓/MySQL/ClickHouse）

整体长短传比例

SELECT
    SUM(CASE WHEN length >= 30 THEN 1 ELSE 0 END) AS long_cnt,
    SUM(CASE WHEN length <  30 THEN 1 ELSE 0 END) AS short_cnt,
    ROUND(SUM(CASE WHEN length >= 30 THEN 1 ELSE 0 END) * 1.0
          / NULLIF(SUM(CASE WHEN length < 30 THEN 1 ELSE 0 END), 0), 3) AS long_short_ratio,
    ROUND(SUM(CASE WHEN length >= 30 THEN 1 ELSE 0 END) * 1.0
          / COUNT(*), 3) AS long_ratio
FROM passes
WHERE dt = '2024-01-01';

按球员/维度分组

SELECT
    player_id,
    COUNT(*)                                            AS total,
    SUM(length >= 30)                                   AS long_cnt,   -- PostgreSQL/ClickHouse 布尔求和
    SUM(length <  30)                                   AS short_cnt,
    ROUND(SUM(length >= 30)::numeric / NULLIF(SUM(length < 30), 0), 3) AS ratio
FROM passes
GROUP BY player_id
ORDER BY ratio DESC;

比例分布（直方图，按每场/每会话算比例再分桶）

WITH per_game AS (
    SELECT
        game_id,
        SUM(CASE WHEN length >= 30 THEN 1 ELSE 0 END) * 1.0
        / COUNT(*) AS long_ratio
    FROM passes
    GROUP BY game_id
)
SELECT
    CASE
        WHEN long_ratio < 0.1 THEN '0-10%'
        WHEN long_ratio < 0.2 THEN '10-20%'
        WHEN long_ratio < 0.3 THEN '20-30%'
        WHEN long_ratio < 0.5 THEN '30-50%'
        ELSE '50%+'
    END        AS bucket,
    COUNT(*)   AS games,
    ROUND(COUNT(*) * 1.0 / SUM(COUNT(*)) OVER (), 3) AS pct
FROM per_game
GROUP BY bucket
ORDER BY bucket;

Python 版（pandas，适合离线分析 + 画图）

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
# 假设 df 有列: player_id, game_id, length
THRESHOLD = 30
df = pd.read_csv("passes.csv")
df["is_long"] = (df["length"] >= THRESHOLD).astype(int)
# 1) 整体比例
total = len(df)
long_cnt = df["is_long"].sum()
short_cnt = total - long_cnt
print(f"长传 {long_cnt} / 短传 {short_cnt} = 比例 {long_cnt/short_cnt:.3f}")
print(f"长传占比 {long_cnt/total:.3f}")
# 2) 按球员
by_player = df.groupby("player_id")["is_long"].agg(
    total="size", long_cnt="sum"
)
by_player["short_cnt"] = by_player["total"] - by_player["long_cnt"]
by_player["ratio"] = by_player["long_cnt"] / by_player["short_cnt"].replace(0, np.nan)
by_player["long_pct"] = by_player["long_cnt"] / by_player["total"]
print(by_player.sort_values("ratio", ascending=False).head(10))
# 3) 比例分布（每场长传占比直方图）
per_game = df.groupby("game_id")["is_long"].mean()
per_game.hist(bins=[0, .1, .2, .3, .5, .7, 1], edgecolor="k")
plt.xlabel("长传占比"); plt.ylabel("场次数"); plt.title("长短传比例分布")
plt.savefig("ratio_dist.png", dpi=150)
# 4) 分位数输出
print(per_game.quantile([.1, .25, .5, .75, .9]))