1

I have a large MySQL Table containing about 15 Million rows of web log data. Depending on the IP, useragent and site_id (different sections on the website) I want to create user sessions. The session delimiter should be 30 minutes. So if a unique user (same IP and useragent) is inactive for 30 minutes or if he visits a different site area (2, 3, or 4) a new session starts.

I found a very good blog entry, that describes the general procedure: http://randyzwitch.com/sessionizing-log-data-sql/ But I can't implement this 1:1 because MySQL doesn't support window functions and I'm not proficient enough with MySQL to figure it out on my own, e.g. with GROUP_CONCAT().

Here is anonymized example data on sqlfiddle: http://www.sqlfiddle.com/#!9/a8b1f/1

It would be really great if you could help me.

I already posted this question on Stack Overflow.

0
0

You've provided your own answer - MySQL doesn't have window-functions (AKA analytic functions). My advice to you would be to switch to PostgreSQL for that reason alone.

Also, PostgreSQL supports SET operators, CHECK constraints, GIS, calculated fields (although MariaDB does support these), and CTEs (Common Table Expressions, AKA the WITH clause) - although see Justin Swanhart's answer here.

1
  • Thank you. I thought i could avoid the migration to PostgreSQL. But now I managed the migration and now I'm able to use all the benefits. – LarsVegas Nov 12 '15 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.