mysql - 在 MySql 的滑动时间窗口内运行每个 id 的总数

Question

我需要一个查询，它可以告诉我昨天、前 2 天和前 3 天的给定 idTest 的 idObj 数。

2 天前标记的 idObj 数应包括昨天标记的 idObj，即它只应显示昨天和 2 天前标记的 idObj 数。3天前标记的idOjb数量应包括昨天、2天前和3天前标记的idObj。

我还需要列的名称作为日期，而不是“DayBeforeYest”和“TwoDayBefore”。

下面是我要找的:

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

此处，在 2013 年 6 月 29 日，idObj 计数包括那些仅在 2013 年 6 月 29 日被标记的 idObj。在 2013-06-28，idObj 计数包括在 2013-06-29 和 2013-06-28 标记的那些 idObj。在 2013 年 6 月 27 日，idObj 计数包括那些已被标记的 idObj2013-06-29、2013-06-28 和 2013-06-27。因此，与昨天相比，3 天前列的 idObj 计数会更少。

查询

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);

下面是我提出的查询，但它只计算每个 idTest 在第 2 天和第 3 天标记的 idObj 的数量。它不考虑前几天标记的 idObj。它也不以日期格式显示列名称。

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;

谢谢!

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

编辑:

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;

对于给定的示例，输出应如下所示:

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

在 2013 年 6 月 30 日，对于 idTest 104，我们有 1 个 idObj 4433。在 2013 年 6 月 29 日，对于 idTest 104，我们有 1 个 idObj 4433，它也在 2013 年 6 月 30 日为 idTest 104。在 2013-06-28 上，对于 idTest 104，我们有 1 个 idObj 3439，对于 idTest 104，它不在 2013-06-30 或 2013-06-29 中。因此，您将看到 104 的行值为 1 1 0。

在 2013 年 6 月 30 日，对于 idTest 105，我们有 1 个 idObj 3345。在 2013 年 6 月 29 日，对于 idTest 105，我们有 1 个 idObj 3345，它也在 2013 年 6 月 30 日的 idTest 105 中。在 2013-06-28 对于 idTest 105，我们有 1 个 idObj 3345，它也在 2013-06-30 和 2013-06-29。因此，您会看到行值为 1 1 1。

等等……

在2013-06-28，统计idObj，它应该存在于2013-06-28、213-06-29、2013-06-30。在 2013-06-29 上，要计算 idObj，它应该出现在 2013-06-29 和 2013-06-30 中。2013-06-30，要统计idObj，它应该出现在2013-06-30。

Answer 1

已更新要将列名设为日期，您必须使用动态 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;

示例输出:

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

Here is SQLFiddle demo

To make life easier on client (calling) side you can wrap this code into a stored procedure

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

然后使用它

CALL sp_test_report();

这是 SQLFiddle 演示