puppuli puppuli - 5 months ago 12
MySQL Question

Count of view week by week

I'm looking to plot the number of views on an investment every week from creation date to current date.
enter image description here

Every time someone visits an investment, I'm inserting it to a table with

investment_id
,
user_id
and
timestamp(datetime)
.

Table looks like this:

investment_id


investment_id user_id Time_stamp
499 233 2015-01-22 09:00:42
499 256 2015-01-21 09:00:42
499 275 2015-01-20 09:00:42
499 233 2015-01-14 09:00:42
499 233 2015-01-14 09:00:42
499 233 2015-01-10 09:00:42
499 273 2015-01-06 09:00:42
499 347 2015-01-02 09:00:42
499 343 2015-01-01 09:00:42
499 344 2015-01-01 09:00:42


So for investment_id 499 created on 2015-01-01 00:00:00, the result will be as below:


Week Views
1 4
2 3
3 2
4 1


Found a work around:

SELECT (CASE (ceiling(datediff(Time_stamp,"2015-01-01 00:00:00")/7)) WHEN 0 THEN 1
ELSE (ceiling(datediff(Time_stamp,"2015-01-01 00:00:00")/7)) END) as weeks,
count(Investment_Id) as viewCount
from log_table where Investment_Id =499 group by weeks

Answer

Needed is a temporary/virtual table like this:

+ ------------- + ---------- + ---------- + ---------- +
| investment_id | week_start | week_end   | weeknumber |
+ ------------- + ---------- + ---------- + ---------- +
| 499           | 2015-01-02 | 2015-01-09 | 1          |
| 499           | 2015-01-09 | 2015-01-16 | 2          |
| 499           | 2015-01-16 | 2015-01-23 | 3          |
| 499           | 2015-01-23 | 2015-01-30 | 4          |
+ ------------- + ---------- + ---------- + ---------- +

This can be accomplished with a kind of generate series:

SELECT
    499 investment_id,
    @wstart := @startdate + INTERVAL @wseq * 7 DAY week_start,
    @wend := @wstart + INTERVAL 7 DAY week_end,
    @wseq := @wseq + 1 weeknumber
FROM
    information_schema.collations
CROSS JOIN
    (SELECT @startdate := (SELECT MIN(DATE(time_stamp)) 
     FROM log_table
     WHERE investment_id = 499), @wseq := 0) u
HAVING
    week_start <= CURRENT_DATE

The cross join is to initiate the user variables, with a table with enough rows you can generate series from that user variables. information_schema.collations is always available and has over 200 rows.

If you put this in a subquery the table with investment views can be joined on the investent_id and the time_stamp between week_start and week_end.

This will result in:

SELECT
    s.weeknumber,
    COUNT(v.user_id) views
FROM
    (
    SELECT
        499 investment_id,
        @wstart := @startdate + INTERVAL @wseq * 7 DAY week_start,
        @wend := @wstart + INTERVAL 7 DAY week_end,
        @wseq := @wseq + 1 weeknumber
    FROM
        information_schema.collations
    CROSS JOIN
        (SELECT @startdate := (SELECT MIN(DATE(start_date))
         FROM log_table
         WHERE investment_id = 499), @wseq := 0) u
    HAVING
        weekstart <= CURRENT_DATE
    ) s
LEFT JOIN
    log_table v
    ON s.investment_id = v.investment_id
    AND v.time_stamp >= s.week_start
    AND v.time_stamp < s.week_end
GROUP BY s.weeknumber
Comments