基于日期范围查找季度的SQL语句

Question

基于日期范围查找季度的SQL语句

6

我有一个用户活动表格，其中包含用户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月至2012年7月日期的第1、2和3个季度中使用，我希望将其列为该HWA的3个季度。

更新：

根据下面的答案，我现在已经回答了我提出的问题。我有一个进一步的复杂情况。有些HWAs是其他HWAs的替代品。在这些情况下，它们具有相同的groupID以显示它们是链接的。在这些情况下，我需要取消重叠的季度。澄清一下，如果两个HWAs在第二季度使用，并且它们具有相同的groupID，则我只需要计算其中一个的第二季度（这无关紧要）。以下是我现在使用的查询及其输出：

 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

三个硬件设备共享第三季度和相同的组ID。只需要其中一个设备计算第三季度即可。从上面可以看出，这三个硬件设备总共有6个季度。如果您从其中两个设备中删除第三季度，则所有三个设备将仅剩下4个季度，这是正确的做法。如需进一步帮助，请告知。

- aaron

你想按财政年度还是日历年度划分季度？根据你的描述，似乎你不想按照日历年度。 - Justin Skiles

@jskiles1 季度在顶部列出。一个不寻常的集合，从八月开始。 - Michael Berkowski

我想要基于财政年度，从八月份开始，就像Michael说的那样。 - aaron

2个回答

0

我知道这个回复有点晚了，但我没有看到这个作为解决方案，而且它更简单。甚至可以将其设置为函数。

choose(datepart(M,a,Date),'Q2','Q3','Q3','Q3','Q4','Q4','Q4','Q1','Q1','Q1','Q2','Q2')

- Tom

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Michael Berkowski · Accepted Answer

您可以使用 CASE 构造一个新列来按月份分组并处理年份，如果月份小于或等于七月，则从年份中减去一来构建从八月开始的年份（仅用于分组目的）。以下是可能适用的代码示例：

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