mysql年初至今聚合

年初至今聚合和滑动聚合类似，不同的地方仅在于统计的仅为当前一年的聚合。唯一的区别体现在下限的开始位置上。在年初至今的问题中，下限为该年的第一天，而滑动聚合的下限为N个月的第一天。因此，年初至今的问题的解决方案如下图所示，得到的结果

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 avg
FROM emporders a 
INNER JOIN emporders b
    ON a.empid=b.empid
    AND b.ordermonth >= DATE_FORMAT(a.ordermonth, ‘%Y-01-01‘)
    AND b.ordermonth <= a.ordermonth
    AND DATE_FORMAT(b.ordermonth,‘%Y‘)=‘2015‘ 
GROUP BY a.empid,a.ordermonth,a.qty
ORDER BY a.empid,a.ordermonth

mysql年初至今聚合

标签：