Hive 1.2.1에 다음 데이터가 있습니다 (실제 데이터 세트는 훨씬 더 크지 만 데이터 구조는 비슷합니다).
id radar_id car_id datetime
1 A21 123 2017-03-08 17:31:19.0
2 A21 555 2017-03-08 17:32:00.0
3 A21 777 2017-03-08 17:33:00.0
4 B15 123 2017-03-08 17:35:22.0
5 B15 555 2017-03-08 17:34:05.0
6 B15 777 2017-03-08 20:50:12.0
7 C09 777 2017-03-08 20:55:00.0
8 A21 123 2017-03-09 11:00:00.0
9 C11 664 2017-03-09 11:10:00.0
10 A21 123 2017-03-09 11:12:00.0
11 A21 555 2017-03-09 11:12:10.0
12 B15 123 2017-03-09 11:14:00.0
13 B15 555 2017-03-09 11:20:00.0
14 A21 444 2017-03-09 10:00:00.0
15 C09 444 2017-03-09 10:20:00.0
16 B15 444 2017-03-09 10:05:00.0
가장 빈번한 루트 2 개를 얻고 싶습니다. 경로는에 radar_id의해 정렬 된 시퀀스입니다 datetime. 다음과 같은 결과를 얻고 싶습니다.
route frequency
A21->B15 2
A21->B15-C09 1
빈도는 차량 (고유하지 않음, 고려할 필요 없음 car_id)이 하루에 경로를 통과하는 평균 횟수입니다. 경로의 A21->B15경우 빈도는 2입니다. 왜냐하면 3 번 라이드 2017-03-08와 1 번 라이드가 있기 때문 입니다 2017-03-09. 차량 123이 A21->A21->B15날짜에 경로 를 수행하는 것이 중요 합니다 2017-03-09. 와 동일하지 않습니다 A21->B15. 그래서 저는 초기 레이더에서 낮 동안 그 차량을 포착 한 최종 레이더까지의 경로를 고려하고 싶습니다.
라이드가 23:55에 시작되고 00:22에 끝나는 경우는 두 개의 다른 경로로 간주되어야합니다.
Hive 1.2.1을 사용하여 어떻게 할 수 있습니까?
최신 정보:
답에서 제안한 것처럼,이 쿼리를 테스트하지만, route이 포함되어 있지 않습니다 ->. 경로의 값은 000021또는 0450001등입니다.
df = sqlContext.sql("select regexp_replace(route,'(?<=^|->)\\d{5}','') as route " +
",count(*) / min(days) as frequency " +
"from (select concat_ws('->',sort_array(collect_list(radarids))) as route " +
",count(distinct dt) over() as days " +
"from (select car_id " +
",to_date(datetime) as dt " +
",concat(printf('%05d',row_number() over " +
"(partition by car_id,to_date(datetime) " +
"order by to_unix_timestamp(datetime))),cast(radarid as string)) as radarids " +
"from mytable " +
") t " +
"group by car_id " +
",dt " +
") t " +
"group by route " +
"order by frequency desc " +
"limit 5")
select regexp_replace(route,'(?<=^|->)\\d{5}','') as route
,count(*) / min(days) as frequency
from (select concat_ws('->',sort_array(collect_list(radar_ids))) as route
,count(distinct dt) over() as days
from (select car_id
,to_date(datetime) as dt
,concat(printf('%05d',row_number() over (partition by car_id,to_date(datetime) order by datetime)),radar_id) as radar_ids
from mytable
) t
group by car_id
,dt
) t
group by route
order by frequency desc
limit 2
;
+---------------+-----------+
| route | frequency |
+---------------+-----------+
| A21->B15 | 1.5 |
+---------------+-----------+
| A21->B15->C09 | 1.0 |
+---------------+-----------+
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다