Planning code for streaks
I don't need to run commands just yet. I can propose a solution with example code snippets in Python and SQL to calculate consecutive double-digit scoring streaks of at least 3. Since the request is a bit ambiguous, I think I should clarify and provide a general template code for checking the streaks.
I'll keep things concise and simple, including a Python function that uses pandas to compute streaks by players based on game dates, plus a SQL approach using a window function.Preparing filtering examples
I see that I can show how to filter for "连续三场" or "至少三场," which likely indicates they want at least 3 consecutive streaks.
First, I should ask: 你想要什么：数据查询/新闻/代码? Then, I'll provide a ready-to-use SQL and Python snippet. It might also be helpful to present a pure SQL example, specifically in PostgreSQL, using a trick with boolean to int for computing the streaks—grouping by summing where breaks occur, utilizing a cumulative sum approach.Explaining SQL approach for streaks
I’m detailing an approach for filtering the game stats to identify scoring streaks. The example SQL code starts by creating a temporary table, defining a 'break_flag' for scores below 10.
Then, using a cumulative sum, it identifies groups of games. The next step summarizes the results by counting qualifying games and determining their start and end dates.
I need to be careful since the method relies on break groups; non-qualifying rows increment the group ID, meaning it could affect how we count consecutive qualifying scores.Exploring filtering methods for points
Filtering for points >= 10 helps retain only the qualifying rows, but I noticed that group IDs remain the same through each run. An alternative approach could be using the LAG function to identify reset points, allowing us to assign groups using a cumulative sum of the resets.
I want to provide solutions for both PostgreSQL and MySQL while also including a pandas version. In pandas, I would sort the dataframe, create a flag for scores, and use cumulative sums to identify groups. However, I need to ensure the reset mechanism and group IDs function correctly across players.Finalizing streak computation
The reset flag will be 0 for qualifying rows and 1 for non-qualifying ones, leading to a cumulative sum that maintains a constant group ID within a qualifying run. It's crucial to reset the group ID per player, which is handled by using groupby.
To filter the results for the streaks, I’ll group by player and the group ID, aggregating to find the start and end dates along with the length. I can also differentiate between lengths of exactly 3 and those of 3 or more, but I think providing both scenarios might be helpful.
Let’s implement this using a clean code style.
你想要的是哪一种？
- 列表/查询：最近哪些CBA球员完成“连续三场得分上双”
- 代码/脚本：给你一段 SQL 或 Python 来从你自己的比赛数据里统计“连续≥3场10+分”球员
- 解释/定义：规则口径、边界情况（加时、替补出场等）
如果你要统计代码，下面给你即插即用的方案（任选其一）。
Python（pandas）
- 输入表字段示例：
player_id, game_date, points - 统计每位球员所有“连续≥10分”的区间，并筛选长度≥3
import pandas as pd
def streaks_10plus(df: pd.DataFrame, min_len: int = 3) -> pd.DataFrame:
# 需要列: player_id, game_date, points
df = df.copy()
df['game_date'] = pd.to_datetime(df['game_date'])
df = df.sort_values(['player_id', 'game_date'])
df['ok'] = df['points'] >= 10
# 累计非达标标记，作为“分段组号”，在每个球员内递增
df['grp'] = df.groupby('player_id')[~df['ok']].cumsum()
streaks = (
df[df['ok']]
.groupby(['player_id', 'grp'])
.agg(
start_date=('game_date', 'min'),
end_date=('game_date', 'max'),
games=('ok', 'size'),
total_points=('points', 'sum'),
avg_points=('points', 'mean'),
)
.reset_index(drop=True)
)
return streaks[streaks['games'] >= min_len].sort_values(['player_id', 'start_date'])
# 用法：
# result = streaks_10plus(df, min_len=3)
# 若要“恰好3场”，把最后一行条件改为 streaks['games'] == 3
PostgreSQL
- 表：
game_stats(player_id, game_date, points, game_id?)
WITH base AS (
SELECT
player_id,
game_date,
points,
CASE WHEN points >= 10 THEN 0 ELSE 1 END AS break_flag
FROM game_stats
),
seg AS (
SELECT
player_id,
game_date,
points,
SUM(break_flag) OVER (PARTITION BY player_id ORDER BY game_date
ROWS UNBOUNDED PRECEDING) AS grp
FROM base
),
streaks AS (
SELECT
player_id,
MIN(game_date) AS start_date,
MAX(game_date) AS end_date,
COUNT(*) AS games,
SUM(points) AS total_points,
AVG(points) AS avg_points
FROM seg
WHERE points >= 10
GROUP BY player_id, grp
)
SELECT * FROM streaks
WHERE games >= 3 -- 改成 = 3 表示“恰好三场”
ORDER BY player_id, start_date;
MySQL 8+
WITH base AS (
SELECT
player_id,
game_date,
points,
CASE WHEN points >= 10 THEN 0 ELSE 1 END AS break_flag
FROM game_stats
),
seg AS (
SELECT
player_id,
game_date,
points,
SUM(break_flag) OVER (PARTITION BY player_id ORDER BY game_date) AS grp
FROM base
),
streaks AS (
SELECT
player_id,
MIN(game_date) AS start_date,
MAX(game_date) AS end_date,
COUNT(*) AS games,
SUM(points) AS total_points,
AVG(points) AS avg_points
FROM seg
WHERE points >= 10
GROUP BY player_id, grp
)
SELECT * FROM streaks
WHERE games >= 3
ORDER BY player_id, start_date;
需要我按你的实际表结构改写，或直接跑一遍帮你导出名单吗？如果是想要当季CBA的具体球员名单，也可以说下赛季范围和时间区间，我来整理。
Copyright 2024 九游（JY）中国官方网站 - 九游最新版游戏娱乐平台 All Rights by 九游