mysql - 如何在MySQL中选择6个月的数据，分组在下个月的22日和21日之间

Question

我有一个表，其中包含每天使用的数据量，它看起来像这样:

date       | bytes
------------------
2014-01-1  | 12345 
2014-01-2  | 56789
2014-01-3  | 78901
...
2014-02-1  | 12345
2014-02-2  | 56789
2014-02-3  | 78901
...

我需要做的是获取最近 6 个月的总计，但是该月必须从该月的 22 天开始，并在下个月的 21 天结束。当月应该从 22 日开始到今天结束。

我能想到的最好的结果是以下，问题是 - 它非常困惑，并且似乎没有给出正确的结果。

SELECT monthname(`date`),sum(`bytes`) 
FROM `trafficDaily` 
WHERE `date` between STR_TO_DATE( CONCAT(  "22,", MONTH( NOW( ) )-6 ,  ",", YEAR( NOW( ) ) ) ,  "%d,%m,%Y" ) 
and STR_TO_DATE( CONCAT(  "21,", MONTH( NOW( ) ) ,  ",", YEAR( NOW( ) ) ) ,  "%d,%m,%Y" ) 
group by month(DATE_SUB(`date`, INTERVAL 21 DAY)) 
order by `date`

预先感谢您的帮助。

Answer 1

您可以通过使用用户定义的变量来跟踪月份的变化，即在您的案例中，月份从 21 日开始

SELECT 
MONTHNAME(STR_TO_DATE(group_day, '%m')) month_name ,
SUM(`bytes`) `sum` 
 FROM (
SELECT *,
 @changemonth:= CASE
    WHEN DAY(`date`) > 21 
    THEN @month
    WHEN MONTH(`date`) <> @month
    THEN @month
    ELSE @month - 1
  END  group_day,
@month:= MONTH(`date`)
FROM
  t ,(SELECT @changemonth:=0,
      @month:= (SELECT MONTH(`date`) FROM t
      WHERE `date` > NOW() - INTERVAL 6 MONTH ORDER BY `date` LIMIT 1) aa
     ) tt
  WHERE `date` > NOW() - INTERVAL 6 MONTH 
  ORDER BY `date`
    )  a 
GROUP BY group_day

Demo for last 3 months

根据评论进行编辑，适用于 1 月份位于过去 6 个月期间的情况

SELECT 
MONTHNAME(
  STR_TO_DATE(
    CASE WHEN group_day < 1 
    THEN 12 ELSE group_day                       
    END, '%m'
  )
) month_name ,
SUM(`bytes`) `sum` 
 FROM (
SELECT *,
 @changemonth:= CASE
    WHEN DAY(`date`) > 21 
    THEN @month
    WHEN MONTH(`date`) <> @month
    THEN @month
    ELSE @month - 1
  END  group_day,
@month:= MONTH(`date`)
FROM
  t ,(SELECT @changemonth:=0,
      @month:= (SELECT MONTH(`date`) FROM t
      WHERE `date` > NOW() - INTERVAL 6 MONTH ORDER BY `date` LIMIT 1) aa
     ) tt
  WHERE `date` > NOW() - INTERVAL 6 MONTH 
  ORDER BY `date`
    )  a 
GROUP BY group_day

Demo with January