Calculate Difference of Timestamps and skip NULL

Question

How to skip the First Row to be able calculate Average of Differents? "WHERE" Filter don't working (Error: Analytic Function not allowed in WHERE Clause)

1. Why i can skip the First Value with "557186" ?
2. Its possible to declare "Unit" to be able use this Variable later for "WHERE" ?

SELECT
  FORMAT_TIMESTAMP("%F", Timestamp) AS Date,
  FORMAT_TIMESTAMP("%R", Timestamp) AS Unit,
  LAG(FORMAT_TIMESTAMP("%R", Timestamp)) OVER (ORDER BY FORMAT_TIMESTAMP("%R", Timestamp)) AS CycleTimePreValue,
  CAST(FORMAT_TIMESTAMP("%s", Timestamp) AS INT64)-LAG(CAST(FORMAT_TIMESTAMP("%s", Timestamp) AS INT64)) OVER (ORDER BY CAST(FORMAT_TIMESTAMP("%s", Timestamp) AS INT64)) AS CycleTimePreValueSeconds,
FROM
  `wh-lr-sk.Quality.Retouren_IMPORT`
  # WHERE (CAST(FORMAT_TIMESTAMP("%s", Timestamp) AS INT64)-LAG(CAST(FORMAT_TIMESTAMP("%s", Timestamp) AS INT64)) OVER (ORDER BY CAST(FORMAT_TIMESTAMP("%s", Timestamp) AS INT64))) < 1000
ORDER BY
  Unit
LIMIT
  10

Answer 1

To "declare" unit you can use a CTE (common table expression).

with time_lags as (
SELECT
  FORMAT_TIMESTAMP("%F", Timestamp) AS Date,
  FORMAT_TIMESTAMP("%R", Timestamp) AS Unit,
  LAG(FORMAT_TIMESTAMP("%R", Timestamp)) OVER (ORDER BY FORMAT_TIMESTAMP("%R", Timestamp)) AS CycleTimePreValue,
  CAST(FORMAT_TIMESTAMP("%s", Timestamp) AS INT64)-LAG(CAST(FORMAT_TIMESTAMP("%s", Timestamp) AS INT64)) OVER (ORDER BY CAST(FORMAT_TIMESTAMP("%s", Timestamp) AS INT64)) AS CycleTimePreValueSeconds,
FROM
  `wh-lr-sk.Quality.Retouren_IMPORT`
)
SELECT
    *
FROM time_lags
WHERE Unit < 1000
AND CycleTimePreValue IS NOT NULL
ORDER BY
  Unit
LIMIT
  10

To remove the null values I just added add AND CycleTimePreValue IS NOT NULL to the where clause.