SQL 语句，用于根据日期范围查找季度

Question

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

日期范围： 2011-08-01 -

2012-07-31

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

我已经在 mysql 中使用了 QUARTER 函数，但这只返回基于一月作为年初的季度：

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 具有活动的季度列为计数，而不会重叠。我的意思是，如果 HWA 在基于 2011 年 8 月至 1012 年 7 月日期的第 1、2 和 3 季度中使用，我希望它将其列为该 HWA 的季度 3。

---

更新：

从下面的答案中，我现在回答了我所说的问题。我现在有进一步的复杂情况。一些 HWA 是其他 HWA 的替代品。在这些情况下，它们具有相同的组 ID 以显示它们是链接的。在这些情况下，我需要取消重叠的季度。澄清一下，如果两个 HWA 在第 2 季度有使用量并且它们具有相同的组 ID，那么我只需要计算其中一个的第 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 共享第 3 季度和相同的组 ID。其中只有一个必须包含 Q3 计数。从上面可以看到，在 6 个 HWA 中总共有 3 个季度。如果您从其中两个中删除第 3 季度，我们总共有 4 个季度用于所有 3 个 HWA，这就是它应该的样子。任何进一步的帮助都会很棒。

Answer 1

您可以GROUP BY派生的带有CASE的列，以按月分隔季度并处理年份，如果月份<= 7月，则从年份中减去1以构建从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

Answer 2

我知道这

有点老了，但我不认为这是一个解决方案，它更简单。 甚至可以将其设置为功能。

选择(日期

部分(M，a，日期(，"Q2"，"Q3"，"Q3"，"Q3"，"Q4"，"Q4"，"Q4"，"Q1"，"Q1"，"Q1"，"Q2"，"Q2"(