计算视图中的字段

Question

我的 MariaDB 数据库中有下表“数字”：

id	number
1	25
2	41
3	3
4	73
5	38
6	41
7	12
8	14

现在我想创建一个视图，让我看到：

• 第 1 列：所有可能的数字（不同）
• 第 2 列：该数字出现的次数
• 第 3 列：最后一个 ID 与该数字中的 ID 之间的 ID 数量。

这将导致这个视图：

numberoccurrences	IDdifferences	1
3	1	5
12	1	1
14	1	0
25	1	7
38	1	3
41	2	2
73	4	所以

我开始于：

SELECT DISTINCT number FROM numbers 

然后在同一个表上进行左连接来计数，但是使查询变得非常慢（并且它没有按照我想要的方式工作）。您知道如何解决这个问题吗？预先非常感谢！

Answer 1

您可以按数字分组并使用 COUNT() 聚合函数来获取列 occurrences 和 MAX() 窗口函数来获取列 >IDdifferences：

SELECT number,
       COUNT(*) occurrences,
       MAX(MAX(id)) OVER () - MAX(id) IDdifferences
FROM numbers
GROUP BY number
ORDER BY number;

如果 ids 之间存在间隙：

SELECT number,
       COUNT(*) occurrences,
       MAX(MAX(rn)) OVER () - MAX(rn) IDdifferences
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn FROM numbers) t
GROUP BY number
ORDER BY number;

请参阅 演示。