如何根据日期汇总上个月的统计信息？（在where条件中添加cas

需求

每个月初需要基于队列使用资源统计上个月整个月的资源使用报表，现资源使用信息是存储在mysql中的，如何基于一条SQL实现这个功能呢？

分析

想实现的功能细分了下，其实可以总结成这样：

• 如果今天为这个月的第一天，则汇总出上个月一个月的资源使用情况；
• 否则则汇总本月，自月初到本天的资源使用情况；

这个是一个在Where条件中添加case when的需求，在链接(https://stackoverflow.com/questions/206484/sql-switch-case-in-where-clause)中学习到，这个确实可以做的，但更易于接受是通过OR做处理，其可以理解成：

• dayofmonth(curdate())=1 and 查询条件=上个月；
• dayofmonth(curdate())!=1 and 查询条件=本月；

将上述两个集合加起来就可以实现这个需求了：

(dayofmonth(curdate())=1 and (`QUEUE_RESOURCE_FEE_USAGE_V2`.`created_time` > (curdate() + interval (-(dayofmonth(curdate())) + 1) day) - interval 1 month))) or (dayofmonth(curdate())!=1 and (`QUEUE_RESOURCE_FEE_USAGE_V2`.`created_time` > (curdate() + interval (-(dayofmonth(curdate())) + 1) day)))

实现

好久没写sql，最终的sql如下：

CREATE VIEW `v2_portal_resource_current_month` AS
select
  date_format(`QUEUE_RESOURCE_FEE_USAGE_V2`.`created_time`,'%Y-%m-%d') AS `date`,
  curdate() AS `curdate`,
  `QUEUE_RESOURCE_FEE_USAGE_V2`.`allocated_vcores`,
  `QUEUE_RESOURCE_FEE_USAGE_V2`.`allocated_gb`,
  `QUEUE_RESOURCE_FEE_USAGE_V2`.`queue_name` AS `queue_name`,
  case `QUEUE_RESOURCE_FEE_USAGE_V2`.`cluster_id`
     when 1 then 'cluster1'
      when 2 then 'cluster2'
      when 3 then 'cluster3'
      else 'UNKNOWN'
  end as `cluster_name`
from `QUEUE_RESOURCE_FEE_USAGE_V2`
where
  ((`QUEUE_RESOURCE_FEE_USAGE_V2`.`created_time` < curdate())
  and (`QUEUE_RESOURCE_FEE_USAGE_V2`.`queue_class`=3)
  and ((dayofmonth(curdate())=1 and (`QUEUE_RESOURCE_FEE_USAGE_V2`.`created_time` > (curdate() + interval (-(dayofmonth(curdate())) + 1) day) - interval 1 month))) or (dayofmonth(curdate())!=1 and (`QUEUE_RESOURCE_FEE_USAGE_V2`.`created_time` > (curdate() + interval (-(dayofmonth(curdate())) + 1) day))))
group by `QUEUE_RESOURCE_FEE_USAGE_V2`.`queue_name`,`QUEUE_RESOURCE_FEE_USAGE_V2`.`cluster_id`,date_format(`QUEUE_RESOURCE_FEE_USAGE_V2`.`created_time`,'%Y-%m-%d')
order by `QUEUE_RESOURCE_FEE_USAGE_V2`.`created_time`,`QUEUE_RESOURCE_FEE_USAGE_V2`.`cluster_id`