主页 / dba / 问题 / 292522
Accepted
kn3l
Asked: 2021-06-01 00:23:58 +0800 CST

“前 15 行和后 15 行之间的行”的含义

  • 772
WITH trips_by_day AS
                      (
                      SELECT DATE(trip_start_timestamp) AS trip_date,
                          COUNT(*) as num_trips
                      FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
                      WHERE trip_start_timestamp >= '2016-01-01' AND trip_start_timestamp < '2018-01-01'
                      GROUP BY trip_date
                      ORDER BY trip_date
                      )
                      SELECT trip_date,
                          avg(num_trips)
                          OVER (
                               order by trip_date
                               rows between 15 preceding and 15 following
                               ) AS avg_num_trips
                      FROM trips_by_day

谁能给我解释一下的意思rows between 15 preceding and 15 following？

sql-server window-functions

2 个回答

  1. Best Answer

    这意味着avg(num_trips)不是在所有行上计算，而是在 31 行（最大）上计算 - 15 个相邻行trip_date小于当前行、当前行和 15 个相邻行trip_date大于当前行。当然，当任何一侧的行数少于 15 行时，计算中的行数就会减少。

    一个例子：

    CREATE TABLE test (value INT);
INSERT INTO test VALUES (1), (2), (3), (4), (5), (6);

    SELECT value,
       AVG(value) OVER (ORDER BY value ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) avg2
FROM test;

    价值 | 平均2
----: | -----：
    1 | 2.0000
    2 | 2.5000
    3 | 3.0000
    4 | 4.0000
    5 | 4.5000
    6 | 5.0000

    让我们取第 3 行。框架定义我们应该从前面的 2 行到后面的 2 行。第 3 行的“前 2”行是第 1 行，“后 2”是第 5 行。因此AVG计算 1 到 5 之间的所有 5 行，平均值为 3。

    我们以第 2 行为例。第 1 行的“前 2”行是唯一的第 1 行，“后 2”是第 4 行。因此AVG计算从 1 到 4 的 4 行，平均值为 2.5。

    再举一个例子：

    SELECT value,
       AVG(value) OVER (ORDER BY value ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) avg2
FROM test;

    价值 | 平均2
----: | -----：
    1 |   无效的
    2 | 1.0000
    3 | 1.5000
    4 | 2.5000
    5 | 3.5000
    6 | 4.5000

    让我们取第 3 行。框架定义我们应该从前面的 2 到前面的 1 取行。“前2”行是第1行，“前1”是第2行。所以AVG从1到2计算2行，平均值为1.5。

    让我们以第 1 行为例。“前 2 行”和“前 1 行”都不是。所以没有行AVG计算，输出为NULL。

    https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=eac1f05b4df491f2620c196062c2ab37

    • 5

  2. rows between 15 preceding and 15 following

    此子句指定/限制计算该行平均值的行。

    在您的查询avg_num_trips中是旅行次数（一天）的运行平均值，不是针对所有行计算的，而是仅针对最多 31 行，即旅行日期之前的 15 行、行本身（当前行) 和旅行日期之后的 15 行。

    请参阅文档：SELECT - OVER 子句 (Transact-SQL)

    • 3

相关问题