undefined | Better HN

0 pointsozgune14y ago0 comments

It's doable, but the paradigm doesn't naturally fit into SQL. Users typically need to use sub-selects and self-joins for this kind of "A", "B" & "C" analysis; and that introduces inefficiencies.

0 comments

3 comments · 1 top-level

bkj12314y ago· 2 in thread

This is where a good analytical (as opposed to operational) data model comes into play. Something less normalized that is intuitive and efficient to use.

benbjohnson14y ago

Event data is difficult to denormalize within a relational database. You can group events by actor (e.g. the user performing the events) and stuff it all into a single row. You'll get great performance but you'll have to custom process your data which is in a custom format. SQL functions will become useless.

You can also store every event as a row and pull it out through Hadoop to process (as another commenter mentioned) but you're going to get huge performance bottlenecks just in extracting the data from the database, serializing it, transferring to another server for processing, and then deserializing it. Not to mention that MapReduce is batch-oriented so it's not going to be real-time.

bgilroy2614y ago

In a typical data environment for an event table, you can have a user_history table with the unique_primary_key, timestamp, user_id, attribute_column_name, Previous_value, and Subsequent_value.

If you want to know what users who did X and later did Y, couldn't you select all of the users who did X in a subquery and then find out how many of those user_id's match user_id's of people who did Y, where the timestamp on Y is between the X's timestamp and X's timestamp + some_predetermined_amount_of_time?

I am out of my depth, but isn't there some database best practice for tracking user session start and stop times?

Edit: I wrote this comment before I saw a different comment of yours above, which I think answers my question.

j / k navigate · click thread line to collapse