I am looking for advice on storing rows of data.
What I have a bunch of users (id), and data about their activities. Each user can perform a type of activity (type_id). And then about 20 columns of integer data related to the type of activity.
This next bit is slightly wrong. Originally I said: I currently fetch the data and store it once per hour, so I have a unique index over 3 columns (id, type_id, timestamp) + the 20 integers.
But actually I currently fetch and store the data every 15 mins, but only keep the last record for each hour. I have been doing this with "on duplicate key update"
The data for type_id might not change for hours, days, weeks or even never again. Some users have 3 or 4 types, others 20 or more types of activities. In a typical hour I am adding 1000's of rows of data, most of which are duplicates (eg when sleeping none of the users data changes but I still store a row for each type_id for each hour, each row contains the 20 identical integers)
I need to look at this data and generate graphs and other reports so I am storing all the data even though it is mostly duplicates but I think I am looking for a way to do the reverse of "insert or on duplicate key update" where I only insert a new row if the integer data is different for each type_id for each user id.
If I could do that, can I then use SQL to fill in the blanks when I select data for a time period? Buy that I mean if users A type B has not changed since January I don't want to store rows for them in February and March, but still be able to select and get data for those months. In other words select the last row before a given timestamp?
Are there better ways to store and retrieve this data?