1

I'm stuck, this job seems too complicated for me I hope in some goodness help here!

So this is my table views:

+---+--------------------+
| id|                time|
+---+--------------------+
| 1 | 2016-10-06 10:13:11|
| 2 | 2016-10-07 15:33:31|
| 3 | 2016-10-08 20:13:13|
| 4 | 2016-10-09 09:16:21|
| 5 | 2016-10-10 18:23:17|
| . |                 ...|
+---+--------------------+

I need to select in array the rows count of each day for the last 2 weeks and compare them (so I need to compare monday of current week and previous week).

So I tried:

SELECT 'last_week' AS week, COUNT(*) AS rows 
    FROM `views`
    WHERE `time` >= CURDATE() - INTERVAL  6 DAY 
      AND `time` < CURDATE() + INTERVAL  1 DAY
  UNION ALL
    SELECT 'previous_week' AS week, COUNT(*) AS rows 
    FROM `views`
    WHERE `time` >= CURDATE() - INTERVAL 13 DAY
      AND `time` < CURDATE() - INTERVAL  6 DAY;

But it counts rows of each weeks and not single days. Yes, I can add eg. AND DAYOFWEEK(time) = 7; to the query, but in this case I have to declared it for seven times.

Basically I need something like:

+---------------+--------------------+
|          week | 1| 2| 3| 4| 5| 6| 7|
+---------------+--+--+--+--+--+--+--+
|     last_week | 1| 5| 3|15|12| 1| 3|
| previous_week |10|15|34|22|55|78|99|
+---------------+--+--+--+--+--+--+--+

Where 1 is Monday, 7 Sunday.

1
  • I would do the following: Use GROUP BY to get the counts per day; use date arithmetic to find week number and day of week; check the results. Then I would apply "pivoting" code to the the day of week across the top.
    – Rick James
    Oct 18 '16 at 3:34
2

PRELIMINARY DATE MATH

Here are two expressions you need to understand

mysql> SET @this_past_monday = CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND;
Query OK, 0 rows affected (0.01 sec)

mysql> SET @prev_two_mondays = @this_monday - INTERVAL 2 WEEK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CURDATE(),@this_past_monday,@prev_two_mondays;
+------------+---------------------+---------------------+
| CURDATE()  | @this_past_monday   | @prev_two_mondays   |
+------------+---------------------+---------------------+
| 2016-10-11 | 2016-10-10 00:00:00 | 2016-09-26 00:00:00 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

GOAL OF THIS ANSWER

You need to combine two queries

  • generate a temp table with the dates based on these two Mondays
  • retrieve counts from views table based on these two Mondays

QUERY #1

Generate all dates from Jan 1st last year to 1000 days later, extracting all dates spanning 2 weeks before this past Monday

SELECT dt FROM
(SELECT MAKEDATE(YEAR(NOW()) - 1,H*100+T*10+U) dt FROM
(SELECT 0 H UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
(SELECT 0 T UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
(SELECT 0 U UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U
ORDER BY dt) A
WHERE dt >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND - INTERVAL 2 WEEK
AND   dt <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND;

QUERY #1 EXECUTED

mysql> SELECT dt FROM
    -> (SELECT MAKEDATE(YEAR(NOW()) - 1,H*100+T*10+U) dt FROM
    -> (SELECT 0 H UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
    -> (SELECT 0 T UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
    -> (SELECT 0 U UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U
    -> ORDER BY dt) A
    -> WHERE dt >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND  - INTERVAL 2 WEEK
    -> AND   dt <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND;
+------------+
| dt         |
+------------+
| 2016-09-26 |
| 2016-09-27 |
| 2016-09-28 |
| 2016-09-29 |
| 2016-09-30 |
| 2016-10-01 |
| 2016-10-02 |
| 2016-10-03 |
| 2016-10-04 |
| 2016-10-05 |
| 2016-10-06 |
| 2016-10-07 |
| 2016-10-08 |
| 2016-10-09 |
+------------+
14 rows in set (0.00 sec)

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2016-10-11 |
+------------+
1 row in set (0.00 sec)

mysql>

QUERY #2

SELECT DATE(`time`) dt,COUNT(1) RowCount FROM views
WHERE `time` >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND - INTERVAL 2 WEEK
AND   `time` <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND
GROUP BY DATE(`time`);

COMBINATION

LEFT JOIN Query #1 and Query #2

SELECT A.dt,IFNULL(B.RowCount,0) RowsCounted FROM
(SELECT dt FROM
(SELECT MAKEDATE(YEAR(NOW()) - 1,H*100+T*10+U) dt FROM
(SELECT 0 H UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
(SELECT 0 T UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
(SELECT 0 U UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U
ORDER BY dt) A
WHERE dt >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND  - INTERVAL 2 WEEK
AND   dt <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND) A
LEFT JOIN
(SELECT DATE(`time`) dt,COUNT(1) RowCount FROM views
WHERE `time` >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND - INTERVAL 2 WEEK
AND   `time` <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND
GROUP BY DATE(`time`)) B
USING (dt);

REASON FOR COMBINATION / LEFT JOIN

In the event there are no rows for a particular date, you want Query #1 to still show a date but present 0 as the number of rows

GIVE IT A TRY !!!

NOTE : I will leave the gory details of presenting the data in your capable hands

2
  • Really thanks for your time and this useful answer! Oct 11 '16 at 22:01
  • Why have ` + INTERVAL 0 SECOND? Sure, it turns a DATE` into the equivalent DATETIME, but why is that needed?
    – Rick James
    Oct 18 '16 at 3:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.