0

I have a table that I use for statistical purposes.
Its columns are id and 1,2,3,..,31 and pivot.
This table gives the number of views on each day for the last 31 days.
1 gives the number of views for yesterday.
14 gives the number of views for 14 days ago.
etc ...
(pivot is just used to calculate the number of views)

I would use a cron job every day to update this table, but how would I go about "shifting" all the values to the side ( value column 15 would become value column 16; new value for column 1; delete value for column 31)

Dharman
  • 30,962
  • 25
  • 85
  • 135
Jackymamouth
  • 159
  • 2
  • 11

1 Answers1

0

  1. Define a table with only two columns — "date" and "views"
  2. INSERT a new row in the table with the view count for that day when the CRON job runs
  3. Modify your application query to read through this new table over a custom date range, which could be 31 days or anything else either — please have a look at this link to get an idea:
    MySQL Query - Records between Today and Last 30 Days

  4. Not really sure how pivot is being used here. However, I'm almost certain that if you're using it to store the sum of the views, it could as well be computed by using SUM() or GROUP BY without having to need a separate column in the table

  5. As far as data archival / removal is concerned, your daily CRON job could be modified to include a DELETE query (as the last step) which cleans up records older than a certain date. Again, you could use the link above to get your "target" date

.

I apologise that this might sound like a little too long a solution to what you've asked for. However, I feel, this approach should help you organise and maintain the table in question in a better way.

Community
  • 1
  • 1
Dhruv Saxena
  • 1,336
  • 2
  • 12
  • 29