mysql - 按季度对日期范围内的记录进行计数

Question

我不确定表达这个问题的正确方法是什么，但我希望这是有道理的。我有一个疑问:

SELECT 
     c.ID,
     c.Name,
     c.DateAdded,
     c.LastContact
FROM 
     computers AS c
WHERE 
     LENGTH(DateAdded) > 1
ORDER BY 
     DateAdded ASC;

返回结果:

ID  Name    DateAdded           LastContact
1   MUTHER  9/2/2012 1:23       5/17/2016 13:57
5   VGER    12/19/2012 10:44    5/17/2016 13:56
7   HELPER  1/13/2013 18:09     5/17/2016 13:57
9   JARVIS  1/15/2013 0:28      7/31/2015 0:20
12  PHLFS1  1/17/2013 18:41     2/17/2013 13:37

我可以通过以下查询计算季度添加计算机的日期:

SELECT 
     YEAR(DateAdded) AS YEAR, 
     QUARTER(DateAdded) AS QUARTER, 
     COUNT(ComputerID) AS 'added-during-qtr'
FROM 
     computers
WHERE 
     LENGTH(DateAdded) > 1
GROUP BY 
     YEAR(DateAdded), 
     QUARTER(DateAdded)
ORDER BY 
     YEAR(DateAdded), 
     QUARTER(DateAdded)

这给了我激活时间的计数:

year    quarter added-during-qtr
2012    3       1
2012    4       1
2013    1       3

但是不他们在本季度是否活跃，这才是我真正想要的:

year    quarter active-during-qtr
2012    3       1
2012    4       2
2013    1       5
2013    2       4

我不确定我是否以正确的方式提出问题，但在一天结束时，我想计算在 DateAdded 和 LastContact 之间“事件”的计算机数量，按年份和顺序排序四分之一。理想情况下，无需在 case 语句中手动添加范围。提前致谢。

Answer 1

仍然无法理解您为何对 2013 年第二季度有所期待。

但这是我的尝试:

http://sqlfiddle.com/#!9/ce133d/7

SELECT 
  p.year,
  p.quarter,
  COUNT(DISTINCT c.id)
FROM (
  SELECT 
  YEAR(DateAdded) AS YEAR, 
  QUARTER(DateAdded) AS QUARTER,
  CONCAT(YEAR(DateAdded),QUARTER(DateAdded)) period
    FROM computers
  WHERE LENGTH(DateAdded) > 1
  GROUP BY YEAR(DateAdded), QUARTER(DateAdded)
  ORDER BY YEAR(DateAdded), QUARTER(DateAdded)
  ) p #periods
LEFT JOIN computers c
ON p.period>=CONCAT(YEAR(c.DateAdded),QUARTER(c.DateAdded))
GROUP BY p.period

更新根据您最后的评论，可以进行此修改:

http://sqlfiddle.com/#!9/0c323e/5

SELECT 
  p.year,
  p.quarter,
  COUNT(DISTINCT c.id)
FROM (
  SELECT 
    y.year,
    q.quarter,
    CONCAT(y.year,q.quarter) period
  FROM (SELECT 2012 AS year UNION SELECT 2013 ) y 
  LEFT JOIN (SELECT 1 AS quarter UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) q
  ON 1
  ) p #periods
LEFT JOIN computers c
ON p.period>=CONCAT(YEAR(c.DateAdded),QUARTER(c.DateAdded))
  AND p.period<=CONCAT(YEAR(c.LastContact),QUARTER(c.LastContact))
GROUP BY p.period

如果您需要限制时间段，可以在 GROUP BY p.period 之前添加 WHERE 子句，如下所示:

WHERE p.period<='20132'

http://sqlfiddle.com/#!9/0c323e/7