mysql累积聚合

SELECT
  a.empid,
  a.ordermonth,a.qty AS thismonth,
  SUM(b.qty) AS total,
  CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avg
FROM emporders a 
INNER JOIN emporders b
    ON a.empid=b.empid
    AND b.ordermonth <= a.ordermonth
WHERE DATE_FORMAT(a.ordermonth,‘%Y‘)=‘2015‘ AND DATE_FORMAT(b.ordermonth,‘%Y‘)=‘2015‘
GROUP BY a.empid,a.ordermonth,a.qty
HAVING total<1000
ORDER 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 avg
FROM emporders a 
INNER JOIN emporders b
    ON a.empid=b.empid
    AND b.ordermonth <= a.ordermonth
WHERE DATE_FORMAT(a.ordermonth,‘%Y‘)=‘2015‘ AND DATE_FORMAT(b.ordermonth,‘%Y‘)=‘2015‘
GROUP BY a.empid,a.ordermonth,a.qty
HAVING total-a.qty < 1000
ORDER BY a.empid,a.ordermonth

SELECT
  a.empid,
  a.ordermonth,a.qty AS thismonth,
  SUM(b.qty) AS total,
  CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avg
FROM emporders a 
INNER JOIN emporders b
    ON a.empid=b.empid
    AND b.ordermonth <= a.ordermonth
WHERE DATE_FORMAT(a.ordermonth,‘%Y‘)=‘2015‘ AND DATE_FORMAT(b.ordermonth,‘%Y‘)=‘2015‘
GROUP BY a.empid,a.ordermonth,a.qty
HAVING total-a.qty < 1000 AND total >= 1000
ORDER BY a.empid,a.ordermonth

运行结果如下

mysql累积聚合

标签：