Search code examples
sqlgoogle-bigquerywindow-functions

How to group in BigQuery with window functions?

I'm trying to find the range of values for events within each visit_id in a data set, where each visit_id is returned once. I'm using this code:

SELECT 
visit_id
, min(event_start_datetime) over (partition by visit_id)
, max(event_start_datetime) over (partition by visit_id)
, max(event_start_datetime) over (partition by visit_id) - min(event_start_datetime) over (partition by visit_id)
FROM `table`

Right now it's returning each visit_id many times, which makes me think I should Group By that column, though when I do that it gives me an error, due to the window functions.

I know it's something very simple but what am I missing?

Thanks!

Solution

  • It sounds like you should be using GROUP BY here with aggregation rather than using aggregate window functions:

    SELECT
    visit_id,
    MIN(event_start_datetime) AS min_event_start_datetime,
    MAX(event_start_datetime) AS max_event_start_datetime,
    MAX(event_start_datetime) - MIN(event_start_datetime) AS delta
FROM yourTable
GROUP BY visit_id;