如何在Group By查询中获取每场比赛首末得分的时间及对应球员

我有一组比赛事件数据，需要按**Location（场地）和Date（日期）**分组，针对每场比赛仅筛选Event = 'Point Scored'的事件，获取首个得分的时间、球员，以及最后一个得分的时间、球员，最终输出每场比赛一行的汇总数据，格式如下：

Location,     Date,    1st Point Time, 1st Point Player, Last Point Time, Last Point Player
---
Indoor Hall   01-Jan-22      10:13:00      Player 4              11:02:00        Player 7

目前我用5个查询实现，但速度极慢：

• 2个查询：分别按Location和Date分组，获取'Point Scored'事件的Time列MIN和MAX值；
• 2个查询：将上述结果左连接主数据集，关联Location、Date、Time和Player得到首尾得分球员；
• 1个查询：合并前两步的结果。

想请教：能不能在聚合查询中直接获取对应球员名称，或者有没有更高效的单/少步骤方案？

原始数据样本 #

可以用窗口函数或者精简的聚合+关联方案，大幅减少查询次数和数据扫描量，效率远高于原来的5步方案：

方案1：窗口函数（推荐，仅扫描一次数据） #

利用ROW_NUMBER()窗口函数，按场地、日期分组，对得分事件分别按时间升序、降序排号，然后提取排号为1的记录，最后合并成一行：

WITH scored_events AS (
    SELECT 
        Location,
        Date,
        Time,
        Player,
        -- 按时间升序排，第一个得分排第1
        ROW_NUMBER() OVER (PARTITION BY Location, Date ORDER BY Time ASC) AS rn_first,
        -- 按时间降序排，最后一个得分排第1
        ROW_NUMBER() OVER (PARTITION BY Location, Date ORDER BY Time DESC) AS rn_last
    FROM your_table_name
    WHERE Event = 'Point Scored'
)
SELECT 
    se_first.Location,
    se_first.Date,
    se_first.Time AS "1st Point Time",
    se_first.Player AS "1st Point Player",
    se_last.Time AS "Last Point Time",
    se_last.Player AS "Last Point Player"
FROM scored_events se_first
JOIN scored_events se_last 
    ON se_first.Location = se_last.Location 
    AND se_first.Date = se_last.Date
WHERE se_first.rn_first = 1 
    AND se_last.rn_last = 1;

方案2：聚合子查询+关联（兼容无窗口函数的SQL版本） #

先通过聚合获取每场的首尾得分时间，再关联主表获取对应球员，仅需2次关联：

WITH game_time_bounds AS (
    SELECT 
        Location,
        Date,
        MIN(Time) AS first_point_time,
        MAX(Time) AS last_point_time
    FROM your_table_name
    WHERE Event = 'Point Scored'
    GROUP BY Location, Date
)
SELECT 
    gtb.Location,
    gtb.Date,
    gtb.first_point_time AS "1st Point Time",
    fp.Player AS "1st Point Player",
    gtb.last_point_time AS "Last Point Time",
    lp.Player AS "Last Point Player"
FROM game_time_bounds gtb
JOIN your_table_name fp 
    ON gtb.Location = fp.Location 
    AND gtb.Date = fp.Date 
    AND gtb.first_point_time = fp.Time 
    AND fp.Event = 'Point Scored'
JOIN your_table_name lp 
    ON gtb.Location = lp.Location 
    AND gtb.Date = lp.Date 
    AND gtb.last_point_time = lp.Time 
    AND lp.Event = 'Point Scored';

方案优势 #

两种方案都只需要1-2次数据扫描，避免了原方案多次查询和连接的开销。如果你的SQL引擎支持窗口函数，方案1效率最高；如果是老版本引擎，方案2比原5步方案精简太多，速度会显著提升。

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