如何获得最频繁的值SQL [英] How to get the most frequent value SQL

问题描述

我有一个表Orders(id_trip，id_order)，表Trip(id_hotel，id_bus，id_type_of_trip)和表Hotel(id_hotel，名称).

I have a table Orders(id_trip, id_order), table Trip(id_hotel, id_bus, id_type_of_trip) and table Hotel(id_hotel, name).

我想获得表Orders中最常入住的酒店的名称.

I would like to get name of the most frequent hotel in table Orders.

SELECT hotel.name from Orders
 JOIN Trip
 on Orders.id_trip = Trip.id_hotel
 JOIN hotel
 on trip.id_hotel = hotel.id_hotel
  FROM (SELECT hotel.name, rank() over (order by cnt desc) rnk
          FROM (SELECT hotel.name, count(*) cnt
                  FROM Orders
                 GROUP BY hotel.name))
 WHERE rnk = 1;

推荐答案

分布中的最频繁出现的值"是统计学中的一个不同概念，带有一个技术名称.它称为发行版的MODE. Oracle具有STATS_MODE()函数. https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions154.htm

The "most frequently occurring value" in a distribution is a distinct concept in statistics, with a technical name. It's called the MODE of the distribution. And Oracle has the STATS_MODE() function for it. https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions154.htm

例如，使用标准SCOTT架构中的EMP表，select stats_mode(deptno) from scott.emp将返回30-员工人数最多的部门编号. (30是部门的名称"或编号，不是该部门的雇员人数！)

For example, using the EMP table in the standard SCOTT schema, select stats_mode(deptno) from scott.emp will return 30 - the number of the department with the most employees. (30 is the department "name" or number, it is NOT the number of employees in that department!)

在您的情况下:

select stats_mode(h.name) from (the rest of your query)

注意:如果两个或两个以上酒店并列为最常入住"酒店，则STATS_MODE()将返回其中之一(不确定).如果需要所有绑定值，则将需要其他解决方案-文档中有一个很好的例子(上面链接).这是Oracle理解和实施统计概念的有据可查的缺陷.

Note: if two or more hotels are tied for "most frequent", then STATS_MODE() will return one of them (non-deterministic). If you need all the tied values, you will need a different solution - a good example is in the documentation (linked above). This is a documented flaw in Oracle's understanding and implementation of the statistical concept.

这篇关于如何获得最频繁的值SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！