Skip to Content
0

Counting pairs meetings from logs (HANA)

Feb 06, 2017 at 04:16 PM

17

avatar image

I have a log containing approx 1.5 million records. Each record has a user name, location and time_stamp.

I need to count the number of times a pair of users were in the same location at the same time (within 1200 sec).

This is a snippet of my code which seems to generate billions of records and causes a memory problem and the script to crash.

SELECT T1."USERNAME"AS user1, T2."USERNAME"AS user2

FROM "schema"."VIEW_logs" T1, "schema"."VIEW_logs" T2

WHERE T1."USERNAME" < T2."USERNAME"

AND T1."STATION_ID" = T2."STATION_ID"

AND ABS(T1."TIME_STAMP" - T2."TIME_STAMP") < 1200

Any assistance would be appreciated.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers