赛季累计数据对比统计
下面给你一套可直接改用的脚本方案,覆盖 SQL 取数 → Python 统计 → 对比输出,假设场景是球员/队伍的赛季累计数据对比(可换成销售、运营等任意累计指标)。

需求拆解
“赛季累计数据对比”通常要回答:
- 每个对象(球员/队伍)赛季累计值是多少?
- 与上赛季或联盟平均对比,涨跌幅多少?
- 排名变化如何?
- 输出成表/图,便于复盘。
SQL 取数脚本(以球员赛季累计为例)
-- 赛季累计基础表:按球员+赛季聚合
WITH season_agg AS (
SELECT
player_id,
player_name,
season,
team,
COUNT(*) AS games_played,
SUM(points) AS total_points,
SUM(rebounds) AS total_rebounds,
SUM(assists) AS total_assists,
SUM(minutes) AS total_minutes,
ROUND(AVG(points), 2) AS avg_points,
ROUND(SUM(points) / NULLIF(SUM(minutes),0) * 36, 2) AS pts_per36
FROM game_logs
WHERE season IN (:current_season, :prev_season)
GROUP BY player_id, player_name, season, team
)
SELECT * FROM season_agg;
要点:
NULLIF防除零;- 用
SUM/COUNT做累计,AVG做场均; - 一次取两个赛季,后续在 Python 里做对比。
Python 统计对比脚本
import pandas as pd
# 1. 读取数据(替换为你的 SQL 结果或 CSV)
df = pd.read_sql(sql, conn) # 或 pd.read_csv("season_stats.csv")
CUR, PREV = 2024, 2023
# 2. 拆分当前/上赛季
cur = df[df.season == CUR].set_index("player_id")
prev = df[df.season == PREV].set_index("player_id")
# 3. 合并对比
metrics = ["total_points", "total_rebounds", "total_assists", "avg_points"]
cmp = cur[["player_name"] + metrics].join(
prev[metrics], rsuffix="_prev", how="left"
)
# 4. 计算涨跌幅与排名
for m in metrics:
cmp[f"{m}_diff"] = cmp[m] - cmp[f"{m}_prev"]
cmp[f"{m}_pct"] = (cmp[m] / cmp[f"{m}_prev"] - 1).round(3)
cmp["rank_cur"] = cmp["total_points"].rank(ascending=False, method="min")
cmp["rank_prev"] = cmp["total_points_prev"].rank(ascending=False, method="min")
cmp["rank_change"] = cmp["rank_prev"] - cmp["rank_cur"] # 正数=上升
# 5. 输出
cmp = cmp.sort_values("total_points", ascending=False)
cmp.to_csv("season_compare.csv", encoding="utf-8-sig")
print(cmp.head(10)[["player_name","total_points","total_points_prev",
"total_points_pct","rank_change"]])
可视化(可选,matplotlib)
import matplotlib.pyplot as plt
top = cmp.head(10)
x = range(len(top))
plt.figure(figsize=(10,5))
plt.bar(x, top["total_points"], label="本赛季")
plt.bar(x, top["total_points_prev"], alpha=0.6, label="上赛季")
plt.xticks(x, top["player_name"], rotation=45)
plt.ylabel("累计得分"); plt.legend(); plt.tight_layout()
plt.savefig("season_compare.png", dpi=150)
中文显示需设置:
plt.rcParams['font.sans-serif']=['SimHei']
输出结果示例
| 球员 | 本赛季累计 | 上赛季累计 | 同比 | 排名变化 |
|---|---|---|---|---|
| A | 1850 | 1600 | +15.6% | ↑2 |
| B | 1720 | 1780 | -3.4% | ↓1 |
| C | 1650 | 1200 | +37.5% | ↑5 |
可扩展点
- 对比维度:换成“队伍 vs 联盟平均”“本周 vs 上周累计”只需改
groupby和 join 键; - 归一化:出场数不同的对象,用
per36或per_game更公平; - 异常过滤:加
games_played >= 10避免小样本失真; - 自动化:把 SQL + Python 包成
cron定时任务,每日/每周刷新对比表。
需要我按你的具体数据结构(表名、字段、对比对象)改写成可直接运行的版本吗?告诉我字段即可。
版权声明:本站文章为原创整理,转载请注明出处。