Running total on one identifier in slip time window in MySql

Question

I need a request that can tell me the idObj number for a given idTest yesterday, 2 days before and 3 days before.

The idObj number marked 2 days before should include the idObj flag Yesterday, i.e. it should show only the idObj number marked yesterday, and also 2 days before. The idOjb number marked 3 days before should include idObj flagged Yesterday, 2 days before and 3 days before.

I also need the column name as the date instead of "DayBeforeYest" and "TwoDayBefore".

Below I am looking for:

idTest 2013-06-29 2013-06-28 2013-06-27 104 9 7 5 105 7 6 2 106 5 3 0 

Here, in 2013-06-29, idObj counts includes those idObjs that were tagged only on 2013-06-29. In 2013-06-28, the idObj number includes those idObjs that were noted on 2013-06-29 and 2013-06-28. In 2013-06-27, the number of idObj includes those idObj that were marked on 2013-06-29, 2013-06-28 and 2013-06-27. Thus, 3 days ago, the column will have less idObj counts compared to yesterday.

Request

 create table tblTest (dateFact date, idTest int, idObj int); insert into tblTest values ('2013-06-29', 104, 4433), ('2013-06-29', 105, 3345), ('2013-06-29', 106, 5543), ('2013-06-28', 104, 4433), ('2013-06-28', 105, 3345), ('2013-06-28', 106, 4356), ('2013-06-27', 104, 3439), ('2013-06-07', 105, 3345), ('2013-06-07', 106, 8955); 

Below is the query I came up with, but it just counts the number of idObj tagged on 2nd and 3rd day on idTest. It does not take into account idObj noted in previous days. It also does not display the column name in date format.

 select idTest, max(Yest) as Yest, max(DayBeforeYest) as DayBeforeYest, max(TwoDayBefore) as TwoDayBefore from ( (select idTest, count(idObj) as Yest, 0 as DayBeforeYest, 0 as TwoDayBefore from tblTest where dateFact =date_sub(curdate(), interval 1 day) group by idTest) union (select idTest, 0 as Yest, count(idObj) DayBeforeYest, 0 as TwoDayBefore from tblTest where dateFact = date_Sub(curdate(), interval 2 day) group by idTest) union (select idTest, 0 as Yest, 0 as DayBeforeYest , count(idObj) TwoDayBefore from tblTest where dateFact = date_sub(curdate(), interval 3 day) group by idTest) )x group by idTest; 

Thanks!

======================================

Edited by:

 create table tblTest (dateFact date, idTest int, idObj int); INSERT INTO tblTest select CURDATE() - INTERVAL 1 DAY, 104, 4433 UNION ALL SELECT CURDATE() - INTERVAL 1 DAY, 105, 3345 UNION ALL SELECT CURDATE() - INTERVAL 1 DAY, 106, 5543 UNION ALL SELECT CURDATE() - INTERVAL 2 DAY, 104, 4433 UNION ALL SELECT CURDATE() - INTERVAL 2 DAY, 105, 3345 UNION ALL SELECT CURDATE() - INTERVAL 2 DAY, 106, 4356 UNION ALL SELECT CURDATE() - INTERVAL 3 DAY, 104, 3439 UNION ALL SELECT CURDATE() - INTERVAL 3 DAY, 105, 3345 UNION ALL SELECT CURDATE() - INTERVAL 3 DAY, 106, 8955; 

In this example, the output should be as follows:

 idTest 2013-06-30 2013-06-29 2013-06-28 104 1 1 0 105 1 1 1 106 1 0 0 

In 2013-06-30 for idTest 104 we have 1 idObj 4433. In 2013-06-29 for idTest 104 we have 1 idObj 4433, which is also in 2013-06-30 for idTest 104. In 2013-06- 28 for idTest 104 we have 1 idObj 3439, which is not located on 2013-06-30 or 2013-06-29 for idTest 104. Therefore, you will see that the string values ​​for 104 are 1 1 0.

In 2013-06-30 for idTest 105 we have 1 idObj 3345. In 2013-06-29 for idTest 105 we have 1 idObj 3345, which is also located in 2013-06-30 idTest 105. In 2013-06-28 for idTest 105 we have 1 idObj 3345, which is also located on 2013-06-30 and 2013-06-29. Therefore, you will see that the string values ​​are 1 1 1.

And so on...

In 2013-06-28, in order to calculate idObj, he must be present on 2013-06-28, 213-06-29, 2013-06-30. In 2013-06-29, in order to calculate idObj, he must be present on 2013-06-29 and 2013-06-30. In 2013-06-30, in order to calculate idObj, he must be present on 2013-06-30.

Answer 1

UPDATED For column names to be dates, you must use dynamic SQL.

 SET @sql = CONCAT( 'SELECT idTest , SUM(d1) `', DATE_FORMAT(CURDATE() - INTERVAL 1 DAY, '%Y-%m-%d'), '`, SUM(d2 = 1 AND d1 = 1) `', DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, '%Y-%m-%d'), '`, SUM(d3 = 1 AND d2 = 1 AND d1 = 1) `', DATE_FORMAT(CURDATE() - INTERVAL 3 DAY, '%Y-%m-%d'), '` FROM ( SELECT idTest ,idObj ,SUM(CASE WHEN dateFact = CURDATE() - INTERVAL 1 DAY THEN 1 ELSE 0 END) d1 ,SUM(CASE WHEN dateFact = CURDATE() - INTERVAL 2 DAY THEN 1 ELSE 0 END) d2 ,SUM(CASE WHEN dateFact = CURDATE() - INTERVAL 3 DAY THEN 1 ELSE 0 END) d3 FROM tblTest WHERE dateFact BETWEEN CURDATE() - INTERVAL 3 DAY AND CURDATE() - INTERVAL 1 DAY GROUP BY idTest, idObj ) q GROUP BY idTest'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; 

Output Example:

  |  IDTEST |  2013-07-02 |  2013-07-01 |  2013-06-30 |
 -------------------------------------------------
 |  104 |  1 |  1 |  0 |
 |  105 |  1 |  1 |  1 |
 |  106 |  1 |  0 |  0 |

Here is the SQLFiddle demo

To make life easier on the client (caller) side, you can transfer this code to a stored procedure

 DELIMITER $$ CREATE PROCEDURE sp_test_report() BEGIN -- put above mentioned code here END$$ DELIMITER ; 

And then use it

 CALL sp_test_report(); 

Here is the SQLFiddle demo