mysql累積彙總

標籤：

累積彙總為彙總從序列內第一個元素到當前元素的資料，如為每個員工返回每月開始到現在累積的訂單數量和平均訂單數量

行號問題有兩個解決方案，分別是為使用子查詢和使用串連。子查詢的方法通常比較直觀，可讀性強。但是在要求進行彙總時，子查詢需要為每個彙總掃描一次資料，而串連方法通常只需要掃描一次就可以得到結果。下面的查詢使用串連來得到結果

SELECT  a.empid,  a.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 <= a.ordermonthGROUP BY a.empid,a.ordermonth,a.qtyORDER BY a.empid,a.ordermonth

如果只是查詢2015年的累積訂單，可以加上以where條件

WHERE DATE_FORMAT(a.ordermonth,‘%Y‘)=‘2015‘ AND DATE_FORMAT(b.ordermonth,‘%Y‘)=‘2015‘

運行結果如下此外可能還需要篩選資料，例如只需要返回每個員工到達某一目標之前每月訂單的情況。這裡假設統計每個員工的合計訂單數量達到1000之前的累積情況。這裡可以使用HAVING過濾器來完成查詢

SELECT  a.empid,  a.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 <= a.ordermonthWHERE DATE_FORMAT(a.ordermonth,‘%Y‘)=‘2015‘ AND DATE_FORMAT(b.ordermonth,‘%Y‘)=‘2015‘GROUP BY a.empid,a.ordermonth,a.qtyHAVING total<1000ORDER BY a.empid,a.ordermonth

這裡並沒有統計到達到1000時該月的情況，如果要進行統計，則情況又有點複雜。如果指定了total <= 1000，則只有該月訂單數量正好為1000才進行統計，否則不會對該月進行統計。因此這個問題的過濾，可以從另外一個方面來考慮。當累積累積訂單小於1000時，累積訂單與上個月的訂單之差是小於1000的，同時也能對第一個訂單數量超過1000的月份進行統計。故該解決方案的SQL語句如下

SELECT  a.empid,  a.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 <= a.ordermonthWHERE DATE_FORMAT(a.ordermonth,‘%Y‘)=‘2015‘ AND DATE_FORMAT(b.ordermonth,‘%Y‘)=‘2015‘GROUP BY a.empid,a.ordermonth,a.qtyHAVING total-a.qty < 1000ORDER BY a.empid,a.ordermonth

運行結果如下如果只想返回達到累積訂單數為1000的當月資料，不返回之前的月份，則可以對上述SQL語句進一步過濾，再添加累積訂單數量大於等於1000的條件。該問題的SQL語句如下，

SELECT  a.empid,  a.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 <= a.ordermonthWHERE DATE_FORMAT(a.ordermonth,‘%Y‘)=‘2015‘ AND DATE_FORMAT(b.ordermonth,‘%Y‘)=‘2015‘GROUP BY a.empid,a.ordermonth,a.qtyHAVING total-a.qty < 1000 AND total >= 1000ORDER BY a.empid,a.ordermonth

運行結果如下

mysql累積彙總