如何查找每三年发布的视频游戏数量？

Question

假设我有一个表videogames，我想找到发布的游戏数量 从 1997 年开始，每隔 3 年进行一次。

视频游戏

videogameid	title	年份
1	GoldenEye 007	1997
2	Tomb Raider II	1997
3	Half-Life	1998
4	The Sims	2000
5	GTA (III)	2001
6	Kingdom Hearts	2003
7	魔兽世界	2004
8	ES4：湮没	2006
9	L.A. Noire	2011
10	Far Cry 3	2012
11	Diablo III	2012

从表中可以看出，预期输出应为 Year (1997-1999) = 3, Year (2000-2002) = 2, Year(2003 - 2005) = 2, Year(2006-2008) = 1, Year (2009 - 2011 ) = 1 and Year (2012-2014)= 2

这是我解决代码的尝试：

SELECT COUNT(videogameid) AS number_of_videogames
FROM videogames 
WHERE INTERVAL(1997,2,2,2,2,2,2)
GROUP BY YEAR;

出于某种原因，我得到了返回超过 100 ++ 行的答案，而每个间隔应该只有 6 行。

Answer 1

更新 2022-03-06

列出没有参考年份数字的总数似乎很奇怪:-)，但如果这就是您想要的，请尝试：

WITH recursive ReleaseYears AS (
  -- build list of years 
  SELECT MIN(`year`) AS YearNum
  FROM   VideoGames
  UNION ALL
  SELECT YearNum + 1
  FROM   ReleaseYears
  WHERE  YearNum <= ( SELECT MAX(`year`) + 2 FROM VideoGames) 
)
SELECT COUNT(vg.videogameid) AS Num
FROM   ( -- group year numbers in groups of 3 
         SELECT FLOOR((ROW_NUMBER() OVER(ORDER BY (SELECT YearNum)) -1) / 3) + 1 AS YearGroup
               , YearNum
         FROM   ReleaseYears
       ) y LEFT JOIN VideoGames vg ON y.YearNum = vg.`year`
GROUP BY YearGroup       
HAVING COUNT(vg.videogameid) > 0

结果：

<前>|数量 |
| --: |
| 3 |
| 2 |
| 2 |
| 1 |
| 1 |

---

原始答案

如果您想以“（最小-最大）”格式显示年份：

WITH recursive ReleaseYears AS (
  SELECT MIN(`year`) AS YearNum
  FROM   VideoGames
  UNION ALL
  SELECT YearNum + 1
  FROM   ReleaseYears
  WHERE  YearNum <= ( SELECT MAX(`year`) + 2 FROM VideoGames) 
)
SELECT YearGroup
       , CONCAT_WS('-', MIN(YearNum), MAX(YearNum)) AS YearRange
       , COUNT(vg.videogameid) AS Num
FROM   (
         SELECT FLOOR((ROW_NUMBER() OVER(ORDER BY (SELECT YearNum)) -1) / 3) + 1 AS YearGroup
               , YearNum
         FROM   ReleaseYears
       ) y LEFT JOIN VideoGames vg ON y.YearNum = vg.`year`
GROUP BY YearGroup       
HAVING COUNT(vg.videogameid) > 0
;

结果：

年级 |年份范围 |数量
--------：| :-------- | --:
        1 | 1997-1999 | 3
        2 | 2000-2002 | 2
        3 | 2003-2005 | 2
        4 | 2006-2008 | 1
        5 | 2009-2011 | 1
        6 | 2012-2014 | 2

db<>fiddle 此处

Answer 2

我认为你能得到的最简单的方法是使用递归 CTE 来生成你的年份，然后简单地将其外部连接到你的源数据并分组为 3 个，如下所示：

with recursive n as (
    select Min(year) yn, Ceiling((max(year)-Min(year))/3.0)*3 + min(year) maxyear, Min(year) minyear 
    from t
    union all
    select yn + 1, maxyear, minyear
    from n
    where yn < maxyear
)
select Min(yn) FromYear, Max(yn) toYear, Count(year) qty
from n
left join t on t.year=yn
group by floor((yn - minyear) /3);

演示DB<>Fiddle