Как получить наиболее частое значение SQL

sql oracle oracle11g

4295 просмотра

4 ответа

17 Репутация автора

У меня есть таблица Orders (id_trip, id_order), таблица Trip (id_hotel, id_bus, id_type_of_trip) и таблица Hotel (id_hotel, name).

Хотелось бы получить название самой частой гостиницы в таблице заказов.

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;
Автор: M.Naro Источник Размещён: 08.12.2016 03:59

Ответы (4)


0 плюса

879079 Репутация автора

Вот один из методов:

select name
from (select h.name,
             row_number() over (order by count(*) desc) as seqnum  -- use `rank()` if you want duplicates
      from orders o join
           trip t 
           on o.id_trip = t.id_trip join -- this seems like the right join condition
           hotels h
           on t.id_hotel = h.id_hotel
     ) oth
where seqnum = 1;
Автор: Gordon Linoff Размещён: 08.12.2016 04:03

2 плюса

28781 Репутация автора

Решение

«Наиболее часто встречающееся значение» в распределении - это отдельное понятие в статистике с техническим названием. Это называется РЕЖИМ раздачи. И у Oracle есть STATS_MODE()функция для этого. https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions154.htm

Например, используя EMPтаблицу в стандартной SCOTTсхеме, select stats_mode(deptno) from scott.empвернется 30- номер отдела с наибольшим количеством сотрудников. (30 - это название или номер отдела, это НЕ количество сотрудников в этом отделе!)

В твоем случае:

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

Примечание : если два или более отелей привязаны к «наиболее частым», то STATS_MODE()вернется один из них (недетерминированный). Если вам нужны все связанные значения, вам понадобится другое решение - хороший пример приведен в документации (ссылка выше). Это задокументированный недостаток в понимании Oracle и реализации статистической концепции.

Автор: mathguy Размещён: 08.12.2016 04:11

1 плюс

146248 Репутация автора

Используйте FIRSTдля одного результата:

SELECT MAX(hotel.name) KEEP (DENSE_RANK FIRST ORDER BY cnt DESC) 
FROM (
  SELECT hotel.name, COUNT(*) cnt
  FROM orders
  JOIN trip USING (id_trip)
  JOIN hotel USING (id_hotel)
  GROUP BY hotel.name
) t
Автор: Lukas Eder Размещён: 08.12.2016 04:21

0 плюса

8467 Репутация автора

** Получение самого последнего статистического режима из выборки данных **

Я знаю, что прошло больше года, но вот мой ответ. Я столкнулся с этим вопросом в надежде найти более простое решение, чем то, что я знаю, но, увы, нет.

У меня была похожая ситуация, когда мне нужно было получить режим из выборки данных, с требованием получить режим самого последнего введенного значения, если было несколько режимов.

В таком случае не сработали бы STATS_MODEни LASTфункции агрегата, ни агрегатные функции (поскольку они, как правило, возвращали первый найденный режим, а не обязательно режим с самыми последними записями.)

В моем случае было легко использовать ROWNUMпсевдостолбец, потому что рассматриваемые таблицы были таблицами метрик производительности, которые только вставляли (не обновляли)

В этом упрощенном примере я использую ROWNUM- его можно легко заменить на отметку времени или поле последовательности, если оно у вас есть.

 SELECT     VALUE
       FROM
        (SELECT     VALUE        ,
                COUNT( * ) CNT,
                MAX( R ) R
               FROM
                ( SELECT ID, ROWNUM R FROM FOO
                )
           GROUP BY ID
           ORDER BY CNT DESC,
                R DESC
        )
      WHERE
        (
            ROWNUM < 2
        );

То есть, получить общее количество и максимум ROWNUMдля каждого значения (я предполагаю, что значения являются дискретными. Если это не так, это не сработает. )

Затем сортируйте так, чтобы на первом месте стояли те, у кого наибольшее количество, а для тех, у кого такое же количество, - самое большое ROWNUM(что указывает на самую последнюю вставку в моем случае ).

Затем снимите верхний ряд.

Ваша конкретная модель данных должна иметь способ распознавать самые последние (или самые старые или что-то еще) строки, вставленные в вашу таблицу, и, если есть столкновения, то нет другого способа, кроме как использовать ROWNUMили получить случайную выборку размера 1 ,

Если это не работает для вашего конкретного случая, вам придется создать свой собственный агрегатор.

Теперь, если вам все равно, какой режим выберет Oracle (ваш бизнес-случай просто требует режима, и все, тогда все STATS_MODEбудет хорошо.

Автор: luis.espinal Размещён: 18.04.2018 09:59
Вопросы из категории :
32x32