SQL query to calculate the cumulative number of trips for each date

Question

I have a hive table called bikeshare_trips with the following schema

+---------------------+------------+----------------------------------------------------+--+
|      col_name       | data_type  |                      comment                       |
+---------------------+------------+----------------------------------------------------+--+
| trip_id             | int        | numeric id of bike trip                            |
| duration_sec        | int        | time of trip in seconds                            |
| start_date          | string     | start date of trip with date and time, in PST      |
| start_station_name  | string     | station name of start station                      |
| start_station_id    | int        | numeric reference for start station                |
| end_date            | string     | end date of trip with date and time, in PST        |
| end_station_name    | string     | station name for end station                       |
| end_station_id      | int        | numeric reference for end station                  |
| bike_number         | int        | id of bike used                                    |
| zip_code            | string     | Home zip code of subscriber (customers can choose to manually enter zip at kiosk however data is unreliable) |
| subscriber_type     | string     | Subscriber can be annual or 30-day member, Customer can be 24-hour or 3-day member |
+---------------------+------------+----------------------------------------------------+--+

and some data example

944732  2618    09/24/2015 17:22:00 Mezes   83  09/24/2015 18:06:00 Mezes   83  653 94063   Customer
984595  5957    09/24/2015 18:12:00 Mezes   83  10/25/2015 19:51:00 Mezes   83  52  nil Customer
984596  5913    09/24/2015 18:13:00 Mezes   83  10/25/2015 19:51:00 Mezes   83  121 nil Customer
1129385 6079    09/24/2015 10:33:00 Mezes   83  03/18/2016 12:14:00 Mezes   83  208 94070   Customer
1030383 5780    2015-09-30 10:52:00 Mezes   83  12/06/2015 12:28:00 Mezes   83  44  94064   Customer
1102641 801 02/23/2016 12:25:00 Mezes   83  02/23/2016 12:39:00 Mezes   83  174 93292   Customer
969490  255 2015-09-30 19:02:00 Mezes   83  10/13/2015 19:07:00 Mezes   83  650 94063   Subscriber
1129386 6032    03/18/2016 10:33:00 Mezes   83  03/18/2016 12:13:00 Mezes   83  155 94070   Customer
947105  1008    2015-09-30 12:57:00 Mezes   83  09/26/2015 13:13:00 Mezes   83  157 94063   Subscriber
1011650 60  11/16/2015 18:54:00 Mezes   83  11/16/2015 18:55:00 Mezes   83  35  94124   Subscriber

Each row of the table corresponds to a different bike trip, and I want to calculate the cumulative number of trips for each date in 2015.

the expected output would be

trip_date               num_trips                cumulative_trips  
2015-09-24              4                        4                
2015-09-30              3                        7                
2015-11-16              1                        8

I'm trying this using analytic function and subqueries, but I don't get it, any help would be appreciate, thanks in advance

Gordon Linoff · Accepted Answer · 2020-05-07T14:12:21.477

2

You can use aggregation and window functions:

select to_date(UNIX_TIMESTAMP(start_date,"MM/dd/yyyy HH:mm")) as dte, count(*),
       sum(count(*)) over (order by min(start_date))
from bikeshare_trips
where YEAR(FROM_UNIXTIME(UNIX_TIMESTAMP(start_date,"MM/dd/yyyy HH:mm"))) = 2015 
group by to_date(UNIX_TIMESTAMP(start_date,"MM/dd/yyyy HH:mm"))
order by dte;

You might need a subquery in Hive:

select dte, cnt, sum(cnt) over (order by dte)
from (select to_date(UNIX_TIMESTAMP(start_date,"MM/dd/yyyy HH:mm")) as dte, count(*) as cnt           
      from bikeshare_trips
      where YEAR(FROM_UNIXTIME(UNIX_TIMESTAMP(start_date,"MM/dd/yyyy HH:mm"))) = 2015 
      group by to_date(start_date)
     ) b
order by dte;

edited May 07 '20 at 14:12

answered May 06 '20 at 16:46

Gordon Linoff

1,242,037
58
646
786

It didn't work, it gave me some errors: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies. But thank you for your effort. I think I found out a way. – Chema May 07 '20 at 11:05
@Chema . . . This should work fine. Perhaps Hive requires a subquery, so I'll add that in. – Gordon Linoff May 07 '20 at 11:20
Last query you did could work, the problem is start_date because is a string timestamp and to_date(timestamp) doesn't work because timestamp doesn't have the right format. I tried this TO_DATE(from_unixtime(UNIX_TIMESTAMP('10/25/2015 12:45:00',"MM/dd/yyyy HH:mm"))) and it seems to be working – Chema May 07 '20 at 11:34
and you missed the condition: WHERE YEAR(FROM_UNIXTIME(UNIX_TIMESTAMP(start_date,"MM/dd/yyyy HH:mm"))) = 2015 – Chema May 07 '20 at 11:46
1

please make the changes in your code and I will approve your solution, thanks – Chema May 07 '20 at 11:50

score 0 · Answer 2 · answered May 06 '20 at 16:45

0

A correlated subquery might be one option here:

SELECT
    trip_date,
    num_trips,
    (SELECT SUM(t2.num_trips) FROM yourTable t2
     WHERE t2.trip_date <= t1.trip_date) AS cumulative_trips
FROM yourTable t1
ORDER BY
    trip_date;

answered May 06 '20 at 16:45

Tim Biegeleisen

502,043
27
286
360

SQL query to calculate the cumulative number of trips for each date

2 Answers2