mysql滑動彙總

標籤：

滑動彙總是按順序對滑動視窗範圍內的資料進行彙總的操作。下累積彙總不同，滑動彙總並不是統計開始計算的位置到當前位置的資料。

這裡以 統計最近三個月中員工第月訂單情況為例來介紹滑動彙總。滑動彙總和累積彙總解決方案的主要區別在於串連的條件不同。滑動彙總條件不再是b.ordermonth <= a.ordermonth,而應該是b.ordermonth大於前三個月的月份，並且小於當前月份。因此滑動彙總的解決方案的SQL語句如下

SELECT  a.empid,  DATE_FORMAT(a.ordermonth, ‘%Y-%m‘) AS ordermonth,  a.qty AS thismonth,  SUM(b.qty) AS total,  CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avgFROM emporders a INNER JOIN emporders b    ON a.empid=b.empid    AND b.ordermonth > DATE_ADD(a.ordermonth, INTERVAL -3 MONTH)    AND b.ordermonth <= a.ordermonthWHERE DATE_FORMAT(a.ordermonth,‘%Y‘)=‘2015‘ AND DATE_FORMAT(b.ordermonth,‘%Y‘)=‘2015‘GROUP BY a.empid,DATE_FORMAT(a.ordermonth, ‘%Y-%m‘),a.qtyORDER BY a.empid,a.ordermonth

運行結果如下該解決方案返回的是三個月為一個周期的滑動彙總，但是每個使用者包含前兩個月並且未滿3個月的彙總。如果只希望返回滿3個月的彙總，不返回未滿3個月的彙總，可以使用HAVING過濾器進行過濾，過濾的條件為MIN(b.ordermonth)=DATE_ADD(a.ordermonth, INTERVAL -2 MONTH),例如

SELECT  a.empid,  a.ordermonth AS ordermonth,  a.qty AS thismonth,  SUM(b.qty) AS total,  CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avgFROM emporders a INNER JOIN emporders b    ON a.empid=b.empid    AND b.ordermonth > DATE_ADD(a.ordermonth, INTERVAL -3 MONTH)    AND b.ordermonth <= a.ordermonthWHERE DATE_FORMAT(a.ordermonth,‘%Y‘)=‘2015‘ AND DATE_FORMAT(b.ordermonth,‘%Y‘)=‘2015‘ AND a.empid=1GROUP BY a.empid,DATE_FORMAT(a.ordermonth, ‘%Y-%m‘),a.qtyHAVING MIN(b.ordermonth)=DATE_ADD(a.ordermonth, INTERVAL-2 MONTH)ORDER BY a.empid,a.ordermonth

運行結果如下

mysql滑動彙總