我有一个用户事件表。我有用户 ID 和日期。我需要根据日期范围获取用户有事件的季度，即:

日期范围:2011-08-01 - 2012-07-31

Q1 - Aug-Oct
Q2 - Nov-Jan
Q3 - Feb-Apr
Q4 - May-Jul

我玩过 QUARTER mysql 中的函数，但它只返回基于一月作为年初的季度:

SELECT QUARTER(a.Date), a.Date
FROM activity AS a
WHERE a.InstitutionNumber = '000000000075' 
AND a.HWA = '001372EADBC4'
AND (a.Date between '2011-08-01' and '2012-07-31')
GROUP BY QUARTER(a.Date), a.HWA

返回:

QUARTER Date
1   2012-01-11
3   2011-08-01
4   2011-10-03

我希望能够在没有重叠的情况下列出每个 HWA 有事件的季度。我的意思是，如果基于 2011 年 8 月至 1012 年 7 月的日期，HWA 在第 1、2 和 3 季度使用，我希望它将该 HWA 列为第 3 个季度。

更新:

从下面的回答中，我现在已经回答了我提出的问题。 我现在更复杂了。一些 HWA 是其他 HWA 的替代品。在这些情况下，它们具有相同的 groupID 以表明它们已链接。在这些情况下，我需要取消重叠的季度。澄清一下，如果两个 HWA 在第 2 季度使用并且它们具有相同的 groupID，那么我只需要计算其中一个的第 2 季度(这无关紧要)。下面是我现在使用的查询和我得到的输出:

 SELECT act.HWA, count(act.HWA), m.Status, m.GroupID
FROM (
    SELECT 
      a.HWA, a.Date,
      CASE 
        WHEN MONTH(a.Date) BETWEEN 8 AND 10 THEN 'Q1'
        WHEN (MONTH(a.Date) BETWEEN 11 AND 12 OR MONTH(a.Date) = 1) THEN 'Q2'
        WHEN MONTH(a.Date) BETWEEN 2 AND 4 THEN 'Q3'
        WHEN MONTH(a.Date) BETWEEN 5 AND 7 THEN 'Q4'
      END AS quarter,
      CASE
        WHEN MONTH(a.Date) <= 7 THEN YEAR(a.Date) - 1 
        ELSE EXTRACT(YEAR_MONTH from a.Date)
      END AS quarteryear,
      COUNT(*) AS num_activites
    FROM activity a
    WHERE
      a.InstitutionNumber = '000000000075'  
      AND (a.HWA = '001372EADBC4' OR a.HWA = '180373E241DB' OR a.HWA = '180373E23DE7')
      AND (a.Date between '2011-08-01' and '2012-07-31')
    GROUP BY
      quarter,
      a.HWA
) act, machine m
where act.HWA = m.HWA
group by act.HWA

返回这个:

HWA         quarters  status   groupid

001372EADBC4    3    deleted    59970
180373E23DE7    2     online    59970
180373E241DB    1    deleted    59970

这 3 个 HWA 都共享 quarter 3 和相同的 groupID。其中只有一个必须包含 Q3。从上面可以看出，3 个 HWA 总共有 6 个季度。如果您从其中两个中删除第 3 季度，我们总共有 4 个季度用于所有 3 个 HWA，这应该是这样的。任何进一步的帮助都会很棒。

最佳答案

您可以GROUP BY用CASE派生的列按月分隔季度并处理年份，如果月份<=七月，减去1从 year="" 构建从="" 8="" 月开始的="">

SELECT 
  a.Date,
  CASE 
    WHEN MONTH(a.Date) BETWEEN 8 AND 10 THEN 'Q1'
    WHEN (MONTH(a.Date) BETWEEN 11 AND 12 OR MONTH(a.Date) = 1) THEN 'Q2'
    WHEN MONTH(a.Date) BETWEEN 2 AND 4 THEN 'Q3'
    WHEN MONTH(a.Date) BETWEEN 5 AND 7 THEN 'Q4'
  END AS quarter,
  CASE
    /* Make a year that starts with August so quarters appear to be all in the same year */
    WHEN MONTH(a.Date) <= 7 THEN YEAR(a.Date) - 1 
    ELSE YEAR(a.Date) 
  END AS quarteryear,
  COUNT(*) AS num_activites
FROM Activity a
WHERE
  a.InstitutionNumber = '000000000075'  
  AND a.HWA = '001372EADBC4'
  AND (a.Date between '2011-08-01' and '2012-07-31')
/* Groupings over the quarter and fake year for aggregates */
GROUP BY
  quarter, 
  quarteryear

更新:

重读你的问题，我意识到这不是你想要的确切输出。以上将为您提供每个季度的事件数，但您真正想要的是有事件的季度数。为此，您可以将上述部分包装在子查询中，并在其上获取 COUNT():

SELECT quarteryear, COUNT(*)
FROM (
    SELECT 
      a.Date,
      CASE 
        WHEN MONTH(a.Date) BETWEEN 8 AND 10 THEN 'Q1'
        WHEN (MONTH(a.Date) BETWEEN 11 AND 12 OR MONTH(a.Date) = 1) THEN 'Q2'
        WHEN MONTH(a.Date) BETWEEN 2 AND 4 THEN 'Q3'
        WHEN MONTH(a.Date) BETWEEN 5 AND 7 THEN 'Q4'
      END AS quarter,
      CASE
        /* Make a year that starts with August so quarters appear to be all in the same year */
        WHEN MONTH(a.Date) <= 7 THEN YEAR(a.Date) - 1 
        ELSE YEAR(a.Date) 
      END AS quarteryear,
      COUNT(*) AS num_activites
    FROM Activity a
    WHERE
      a.InstitutionNumber = '000000000075'  
      AND a.HWA = '001372EADBC4'
      AND (a.Date between '2011-08-01' and '2012-07-31')
    /* Groupings over the quarter and fake year for aggregates */
    GROUP BY
      quarter, 
      quarteryear
) qtrsubcounts
GROUP BY quarteryear

关于mysql - SQL语句根据日期范围查找季度，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/11768881/