Navicat 博客

按组列出的前 N 个查询 2020 年 5 月 14 日,由 Robert Gravelle 撰写

前N个查询是按降序获取按值排序的前几条记录的查询。 通常,这些是使用TOP或LIMIT子句完成的。 问题是,前N个结果集被限制为表中的最高值,而没有任何分组。 GROUP BY子句可以帮助您解决此问题,但仅限于每个组的单项最高结果。 如果您想要每个类别的前5名,GROUP BY本身将无济于事。 这并不意味着它无法完成。 实际上,在今天的博客中,我们将确切学习如何按组构造前N个查询。

前N个查询基础知识

为了更好地理解“前N个查询”,让我们编写一个,从Sakila示例数据库中选择运行时间最长的前5部电影。 如果您不熟悉Sakila数据库,它是一个MySQL数据库,其中包含许多与虚构的视频租赁商店有关的表,视图和查询。 表包括actor、film、customer、rentals等。

top_n (77K)

按类别分组结果

GROUP BY子句将聚合函数应用于一个或多个字段,以便数据与您指定的分组相关。 就分组结果而言,这是向前迈出的一步,但是GROUP BY仍然有两个限制:

  • 它仅提供每个组的第一个结果(即行),而忽略其他结果,
  • 列仅限于分组条件和聚合字段中包含的列。 所有其他列均不可访问。

该查询使用GROUP BY来显示每个评级的最长播放电影:

group_by (47K)

请注意,我们不能包含电影标题,因为它既不是GROUP BY也不是聚合字段的一部分。

窗口函数速成课程

窗口函数(Window Functions)中的术语“窗口”是指函数在其上运行的行集,因为函数使用窗口中行的值来计算返回的值。窗口中的行集合被聚合为一个值。

若要在查询中使用窗口函数,必须使用OVER()子句定义窗口。它做两件事:

  • 通过PARTITION BY子句定义窗口分区以形成行组。
  • 通过ORDER BY子句对分区中的行进行排序。

一个查询可以包括具有相同或不同窗口定义的多个窗口函数。

我们的查询使用ROW_NUMBER()窗口函数。它为查询的内部窗口结果集中的每一行分配一个连续的整数。我们可以使用该值将每个评级的结果限制在前5个。这是通过将电影长度按降序排列来完成的。

row_number (116K)

总结

在今天的博客中,我们学习了如何在Navicat Premium中构造一个查询,以获取每个类别的前5行。 最新的版本15增加了100多个增强功能,其中包括几个新功能,可为您提供比以往更多的构建,管理和维护数据库的方式!

Navicat 文章
频道条目
分享
文章归档