SQL-запрос для расчета кумулятивного количества поездок на каждую дату

У меня есть таблица улья под названием bikeshare_trips со следующей схемой

+---------------------+------------+----------------------------------------------------+--+
|      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 |
+---------------------+------------+----------------------------------------------------+--+

и пример данных

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

Каждая строка таблицы соответствует разному велопутешествию, и я хочу рассчитать совокупное количество поездок для каждой даты в 2015 году.

ожидаемый результат будет

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

Я пытаюсь использовать аналитическую функцию и подзапросы, но не понимаю, любая помощь будет признательна, заранее спасибо


sql hive hiveql aggregate-functions analytic-functions
person Chema    schedule 06.05.2020    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Вы можете использовать агрегатные и оконные функции:

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;

Вам может понадобиться подзапрос в 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;
person Gordon Linoff    schedule 06.05.2020
comment
Это не сработало, это дало мне несколько ошибок: FAILED: SemanticException Не удалось разбить вызовы окон на группы. По крайней мере, 1 группа должна зависеть только от входных столбцов. Также проверьте циклические зависимости. Но спасибо за ваши усилия. Думаю, я нашел способ. - person Chema; 07.05.2020
comment
@Chema. . . Это должно работать нормально. Возможно, для Hive требуется подзапрос, поэтому я добавлю его. - person Gordon Linoff; 07.05.2020
comment
Последний запрос, который вы сделали, мог сработать, проблема в start_date, потому что это строковая метка времени, а to_date (timestamp) не работает, потому что метка времени имеет неправильный формат. Я пробовал этот TO_DATE (from_unixtime (UNIX_TIMESTAMP ('25.10.2015 12: 45: 00', MM / dd / yyyy HH: mm))), и, похоже, он работает - person Chema; 07.05.2020
comment
и вы пропустили условие: WHERE YEAR (FROM_UNIXTIME (UNIX_TIMESTAMP (start_date, MM / dd / yyyy HH: mm))) = 2015 - person Chema; 07.05.2020
comment
пожалуйста, внесите изменения в свой код, и я одобряю ваше решение, спасибо - person Chema; 07.05.2020

arrow_upward
0
arrow_downward

Одним из вариантов здесь может быть коррелированный подзапрос:

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;
person Tim Biegeleisen    schedule 06.05.2020