Início / dba / Perguntas / 292522
Accepted
kn3l
Asked: 2021-06-01 00:23:58 +0800 CST

Significado de "linhas entre 15 anteriores e 15 seguintes"

  • 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

Alguém poderia me explicar o significado de rows between 15 preceding and 15 following?

sql-server window-functions

2 respostas

  1. Best Answer

    Isso significa que avg(num_trips)é calculado não em todas as linhas, mas em 31 linhas (máximo) - 15 linhas adjacentes com trip_datemenor que na linha atual, linha atual e 15 linhas adjacentes com trip_datemaior que na linha atual. Obviamente, quando há menos de 15 linhas de qualquer lado, menos linhas são consideradas no cálculo.

    Um exemplo:

    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;

    valor | média 2
----: | -----:
    1 | 2.0000
    2 | 2,5000
    3 | 3.0000
    4 | 4.0000
    5 | 4,5000
    6 | 5.0000

    Vamos pegar a linha 3. O quadro define que devemos pegar as linhas de 2 anteriores até 2 seguintes. As "2 linhas anteriores" para a linha 3 são a linha 1, "2 seguintes" é a linha 5. Assim, AVGé calculado para todas as 5 linhas entre 1 e 5 inclusive, e a média é 3.

    Vamos pegar a linha 2. As "2 linhas anteriores" para a linha 1 são a única linha 1, "2 seguintes" é a linha 4. Assim, AVGé calculado para 4 linhas de 1 a 4, e a média é 2,5.

    Mais um exemplo:

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

    valor | média 2
----: | -----:
    1 |   nulo
    2 | 1.0000
    3 | 1,5000
    4 | 2,5000
    5 | 3,5000
    6 | 4,5000

    Vamos pegar a linha 3. O quadro define que devemos pegar as linhas de 2 anteriores até 1 anterior. A linha "2 anteriores" é a linha 1, "1 anterior" é a linha 2. Assim, AVGé calculado para 2 linhas de 1 a 2 e a média é 1,5.

    Vamos pegar a linha 1. A linha não é "2 precedente" e "1 precedente". Portanto, não há linhas para AVGcálculo e a saída é NULL.

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

    • 5

  2. rows between 15 preceding and 15 following

    Esta cláusula especifica/limita as linhas sobre as quais a média dessa linha é calculada.

    Na sua consulta avg_num_tripsencontra-se uma média corrente do número de viagens (num dia), calculada, não sobre todas as linhas, mas apenas sobre o máximo de 31 linhas, nomeadamente as 15 linhas anteriores à data da viagem, a própria linha (linha actual ) e as 15 linhas que seguem a data da viagem.

    Consulte a documentação: Cláusula SELECT - OVER (Transact-SQL)

    • 3

relate perguntas