mysql自定义周度数据

业务要求把本周三到下周三的数据作为一周的数据处理 计算自定义周里的平均值和周三的值

SELECT 
    t.end_date as 时间,
    g.gm,
    t.gmavg as 供煤平均值,
    g.hm,
    t.hmavg as 耗煤平均值,
    g.kc,
    t.kcavg as 库存平均值

FROM ghc g,
(
  SELECT
     max(end_date) as  end_date,
     FORMAT(AVG(gm),2) as gmavg,
     FORMAT(AVG(hm),2) as hmavg,
     FORMAT(AVG(kc),2) as kcavg,
      (
        (
              rowno+7 - MOD(CURDATE() ,7)-3
          )DIV 7
-- 这个数据必须多几个给最后一期使用 减的数字跟 多的天数有关
     )AS groupNum
 FROM
    (SELECT @rowno:=@rowno+1 as rowno,r.* from ghc r,(select @rowno:=0) t where DATE_FORMAT(end_date,'%Y-%m-%d')>='2013-02-02' and  DATE_FORMAT(end_date,'%Y-%m-%d')<='2018-08-27' ORDER by end_date ASC) t
 GROUP BY
     groupNum  ORDER BY end_date desc ) t where g.end_date = t.end_date order by t.end_date desc

 

表名：ghc

列: gm kc hm三个数据列 end_date 时间列

转载于:https://my.oschina.net/u/3322291/blog/3072801