-1

I have a following table structure:

page_id   view_count    date
1         30            2018-08-30
1         33            2018-08-31
1         1             2018-09-01
1         5             2018-09-02
...

View count is reset on 1st of every month, and it's current value is stored on a daily basis, so on 31st of August it was increased by 3 (because 33-30).

What I need to do is to retrieve the view count (difference) between two dates through SQL query. To retrieve view count between two dates in same month would be simple, by just subtracting bigger date with the lower date, but retrieving between two dates that are in different months is what's not sure to me how to achieve. If I wanted to retrieve data between 2018-08-13 and 2018-09-13 I would have to get difference between 2018-08-31 and 2018-08-13, and add it to the value of 2018-09-13.

Also, I would like to do it for all page_id at once, between the same dates if possible within a single query.

Salman A
  • 262,204
  • 82
  • 430
  • 521
Viktor
  • 23
  • 7
  • 3
    why don't you store both `total_view_count` (never resetted) and `view_count` in your table? So you can easily perform queries even between different months and grouped by `page_id` too – Marco Sep 04 '18 at 14:12
  • Possible duplicate of [PHP: Return all dates between two dates in an array](https://stackoverflow.com/questions/4312439/php-return-all-dates-between-two-dates-in-an-array) – Hamza Zafeer Sep 04 '18 at 14:38
  • @HamzaZafeer Question is for MySQL, not PHP. For all we know it could be programmed in Python. – coladict Sep 04 '18 at 14:41
  • @Marco I wish I could do that, but I'm limited to work with what I have. I currenctly have a table that stores only current view count, this table is what I have made as a helper table to be able to retrieve that difference. Reason for storing a sum is that I will have about 6k rows stored daily in that table, so I will be deleting any data older than 1 year and a total_view_count would get to a point of getting to an int overflow at some point. These numbers are just for example, they're much bigger than this. – Viktor Sep 04 '18 at 14:43

1 Answers1

0

assuming that the counter is unique per page and that the page_id counter is inserted daily into the table, I think that such a solution would work

The dates are based on the example, and should be replaced by the relevant parameters

SELECT
    v1.view_count + eom.view_count - v2.view_count
FROM
    view_counts v1
    INNER JOIN view_counts v2 ON v2.page_id = v1.page_id AND v2.`date` = '2018-08-13'
    INNER JOIN view_counts eom ON v2.page_id = v.page_id AND eom.`date` = LAST_DAY(DATE_ADD(v.`date`, INTERVAL -1 MONTH))
WHERE
    `date` = '2018-09-13'
Guy Louzon
  • 1,175
  • 9
  • 19
  • Thank you so much, this helped a lot! Altho, this only works if the dates are in different months, if used for the same month no result would be given. – Viktor Sep 04 '18 at 19:37
  • You're welcome, and you're right, but that was your question... I think you should write 2 queries and shift between them, depending on the dates' months – Guy Louzon Sep 04 '18 at 19:58