CODEWITHSUNDEEP
sqldatabasesqliteselectwindow-functions
skmathur
skmathur

Reputation: 1637

Cumulative Sum Partition by Order by SQLite3

I'm trying to sessionize some rides data, and I've created a flag for each ride (each row is a ride in this dataset). I've made a table called flags that looks something like this:

ride_id | rider_id | ride_request_datetime | rider_request_number | new_booking_flag
-------------------------------------------------------------------------------------
100       Sue        2019-05-09 05:27:00      1                     1
101       Sue        2019-05-09 05:27:18      2                     0
102       Sue        2019-05-10 11:00:09      3                     1
203       Joe        2020-03-09 05:27:18      1                     1
204       Joe        2020-03-09 05:36:00      2                     0

Based on some criteria of the ride_request_datetime, I've created the column new_booking_flag. Now, I want to sum the values on new_booking_flag, partitioning by rider_id and ordering by rider_request_number -- in order to get a column that represents "booking_request_number"

I've tried the following code:

select
  ride_id,
  rider_id,
  ride_request_datetime,
  rider_request_number,
  new_booking_flag,
  sum(new_booking_flag) over (partition by rider_id order by rider_request_number)
from flags
limit 50;

And SQLite3 is giving me this error: Error: near "(": syntax error

Upvotes: 0

Views: 1069

Answers (1)

GMB
GMB

Reputation: 222582

Your query is correct and should work, provided that you are running it against a SQLite database that supports window functions (they were introduced in version 3.25).

In earlier versions, one option is to emulate the window sum with a correlated subquery:

select
    ride_id,
    rider_id,
    ride_request_datetime,
    rider_request_number,
    new_booking_flag,
    (   
        select sum(f1.new_booking_flag) 
        from flags f1 
        where 
            f1.rider_id = f.rider_id 
            and f1.rider_request_number <= f.rider_request_number
    ) booking_request_number
from flags f
limit 50;

For performance with this query, consider an index on (rider_id, rider_request_number, new_booking_flag).

Upvotes: 3

Related Questions