Найти среднее значение oracle

Обновлено: 07.07.2024

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

Итак, у нас есть куча проектов; у каждого проекта есть куча задач, и каждая задача имеет связанный с ней тип документа ID. Проект может принадлежать одной или нескольким рабочим группам.

Мы хотим проанализировать проекты, в которых есть хотя бы одна задача типа doc x, а затем посмотреть, сколько у нее рабочих групп. Я могу сделать это с помощью:

Теперь мы хотим увидеть среднее число рабочих групп в этих проектах. Так что я могу сделать:

Однако мы хотим выполнить этот же запрос для всех типов документов (их около 200). Я не могу найти способ сделать это без копирования и вставки запроса 200 раз. Я пытался:

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

Обновление для Джастина:

Вот пример схемы:

С некоторыми примерами данных:

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

Например, doc type 5 имеет только проект 1, который имеет 2 рабочие группы, поэтому среднее значение равно 2. Doc type 6 имеет 2 проекта (1 и 2) - 1 имеет 2 рабочие группы и 2 имеет одну рабочую группу - так что среднее значение составляет 1.5.

Нам нужно перечислить все типы документов и среднее количество рабочих групп в каждом.

Я ожидал бы, что этот запрос вернется:

1 ответ

Я довольно новичок в SQL/Oracle, и наткнулся на два запроса, которые мне нужно было собрать вместе: 1.) Найдите время в Днях, HH:MM:SS требуется для обработки одной записи AND 2.) Найдите среднее время обработки на основе всех временных меток для выбранных результатов Я решил номер 1 (может быть.

Я работаю в Oracle SQL. У меня есть таблица с IDs и датами, и я пытаюсь найти среднее время между датами по теме. Это выглядело бы примерно так. TABLE SubjectID Date 1 8/01/2013 12:00:00 AM 1 8/31/2013 12:00:00 AM 1 9/10/2013 12:00:00 AM 2 1/01/2010 12:00:00 AM 2 1/21/2010 12:00:00 AM Мне нужно.

Спасибо за образец данных. Это делает все намного яснее.

Я считаю, что это делает то, что вы хотите (я включаю расчеты для количества проектов и количества рабочих групп в выводе, а также просто потому, что это облегчило мое тестирование)

Похожие вопросы:

Мне нужно найти среднее значение, которое связано с датами. У меня есть ниже вещей, и мне нужно найти среднее текущее значение на 2015/05/01 , которое равно 50 , так как же мне его.

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

Я хочу знать, можно ли получить функциональность группы с ее помощью. Мне нужно найти среднее значение без использования group by. в основном я ищу альтернативу приведенному ниже простому запросу.

У меня есть таблица, которая имеет 3 столбца (имя, курс, оценка). Моя колонка 'name' содержит трех студентов, колонка 'course' содержит различные курсы, такие как математика, физика и т. д. Что мне.

Я использую SQL уже около недели на своей первой полной работе и пытаюсь вычислить некоторую статистику по запросу, в котором я объединил столбцы из отдельных таблиц. В частности, я пытаюсь.

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

Мне нужно найти среднее значение двух значений в отдельных строках. Мой файл CSV выглядит следующим образом Name,ID,Marks Mahi,1,90 Mahi,1,100 Andy,2,85 Andy,2,95 Теперь мне нужно сохранить это.

У меня есть запрос, который показывает сумму и среднее значение. Сумма имеет некоторые сложные вычисления. Мой запрос почти такой: SELECT (SELECT a + b - c * d FROM master) AS sum, (SELECT a + b - c.

Рассмотрим на примерах возможности статистического анализа и статистические функции в Oracle 11g.

Статистика имеет дело с наборами измерений некоторой величины. Например,

В первом случае объем выборки равен числу работников компании, во втором равен 20-ти, в третьем - 100. Для дальнейших экспериментов я буду использовать две выборки: цвет глаз и время сетевого отклика, в миллисекундах. Цвет глаз является номинальной дискретной величиной, тогда как время отклика есть числовая непрерывная величина.

Я поместил выборки в таблицы sts_eyec и sts_ping . Вот они:

Прежде всего, исследуем центральные тенденции выборок. Наиболее известные из центральных тенденций

среднее арифметическое,
медиана,
мода.

Поскольку цвет глаз есть величина номинальная, неупорядоченная, то нельзя получить для нее среднее арифметическое или медиану. Хотя можно попытаться:

Из центральных тенденций для номинальной величины можно получить моду - значение, которое встречается в выборке чаще других:

Для числовой выборки в sts_ping найдем все три характеристики:

Проверю, что функция avg действительно возвращает среднее арифметическое выборки:

Проверю, что функция median действительно возвращает середину упорядоченной выборки:

Поскольку количество элементов в выборке четное, то медиана равна среднему арифметическому двух центральных элементов последовательности, а именно элементов с rownum 10 и 11: 124 и 124.

Проверю, что функция stats_mode действительно возвращает наиболее часто встречающееся значение:

И для цвета глаз:

В нашей выборке янтарные глаза встречаются в три с половиной раза чаще, чем голубые.

Получим еще одну меру центральной тенденции - середину диапазона (midrange) - и парную ей характеристику разброса - диапазон выборки (range):

Кроме диапазона, наиболее известные из характеристик разброса выборки следующие:

межквартильный диапазон (interquartile range) - диапазон между 1-м и 3-м квартилями, в который попадает 50% всех значений выборки,
дисперсия (variance) - среднее значение квадратов разностей элементов выборки и среднего арифметического выборки,
среднеквадратичное отклонение (standard deviation) - квадратный корень из дисперсии.

Межквартильный диапазон работает в паре с медианой, дисперсия и среднеквадратичное отклонение - со средним арифметическим.

Перечисленные характеристики применимы только к числовым выборкам. А для исследования распределения значений в номинальной выборке нужно построить таблицу частотности (или столбцовую диаграмму, чтобы представить информацию о частоте встречаемости каждого значения графически). Отложим это на потом, а пока будем работать с характеристиками разброса числовой выборки.

Чтобы подступиться к межквартильному диапазону, вначале получим квартили нашей выборки с помощью функции percentile_cont :

1-й, 2-й, 3-й и 4-й квартили есть 25-й, 50-й, 75-й и 100-й процентили, соответственно. Запрос демонстрирует, что медиана ожидаемо равна 50-му процентилю (или 2-му квартилю), а минимальное и максимальное значения - 0-му и 100-му процентилям, соответственно.

Получим межквартильный диапазон как разность 3-го и 1-го квартилей:

Функция percentile_cont интерполирует результат, исходя из того, что выборка непрерывна, поэтому ее результатом может быть значение, отсутствующее в выборке. В отличие от этого, функция percentile_disc всегда возвращает значение, присутствующее в выборке, не прибегая к линейной регрессии. Сравните результаты:

Теперь получим дисперсию и среднеквадратичное отклонение. В Oracle имеются следующие функции для этой цели:

Используем их все в следующем запросе:

Рассчитаю дисперсию и среднеквадратичное отклонение по известным формулам. Сравните результат с результатом предыдущего запроса:

Проверю, что среднеквадратичное отклонение равно квадратному корню из дисперсии:

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

Несложно получить подобие столбцовой диаграммы (лежащей на боку):

Если считать выборку репрезентативной, то интересно узнать вероятность для каждого цвета глаз в популяции:

Как видим, вероятность встретить янтарные глаза наибольшая, а голубые - наименьшая. Сумма вероятностей ожидаемо равна 1.

Теперь попробуем "нарисовать" гистограмму для числовой выборки из sts_ping (лежащую на боку, как и столбцовая диаграмма раньше). Разобьем диапазон значений в выборке на 5 интервалов. Сначала определим границы ( b1 и b2 ) и середины интервалов ( mid ):

Теперь соединим выборку с рассчитанными интервалами и сгруппируем значения выборки по интервалам:

Приведенные примеры не исчерпывают тему статистических функций в Oracle 11g. В СУБД имеются функции для исследования корреляции, выполнения регрессии, статистических тестов. Расскажу о них позднее.

Будем учиться подводить итоги. Нет, это ещё не итоги изучения SQL, а итоги значений столбцов таблиц базы данных. Агрегатные функции SQL действуют в отношении значений столбца с целью получения единого результирующего значения. Наиболее часто применяются агрегатные функции SQL SUM, MIN, MAX, AVG и COUNT. Следует различать два случая применения агрегатных функций. Первый: агрегатные функции используются сами по себе и возвращают одно результирующее значение. Второй: агрегатные функции используются с оператором SQL GROUP BY, то есть с группировкой по полям (столбцам) для получения результирующих значений в каждой группе. Рассмотрим сначала случаи использования агрегатных функций без группировки.

Функция SQL SUM

Функция SQL SUM возвращает сумму значений столбца таблицы базы данных. Она может применяться только к столбцам, значениями которых являются числа. Запросы SQL для получения результирующей суммы начинаются так:

После этого выражения следует FROM (ИМЯ_ТАБЛИЦЫ), а далее с помощью конструкции WHERE может быть задано условие. Кроме того, перед именем столбца может быть указано DISTINCT, и это означает, что учитываться будут только уникальные значения. По умолчанию же учитываются все значения (для этого можно особо указать не DISTINCT, а ALL, но слово ALL не является обязательным).

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .

Сначала работать будем с базой данных фирмы - Company1. Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными - в файле по этой ссылке .

Пример 1. Есть база данных фирмы с данными о её подразделениях и сотрудниках. Таблица Staff помимо всего имеет столбец с данными о заработной плате сотрудников. Выборка из таблицы имеет следующий вид (для увеличения картинки щёлкнуть по ней левой кнопкой мыши):

Для получения суммы размеров всех заработных плат используем следующий запрос (на MS SQL Server - с предваряющей конструкцией USE company1;):

Этот запрос вернёт значение 287664,63.

А теперь упражнение для самостоятельного решения. В упражнениях уже начинаем усложнять задания, приближая их к тем, что встречаются на практике.

Пример 2. Вывести сумму комиссионных, получаемых всеми сотрудниками с должностью Clerk.

Функция SQL MIN

Функция SQL MIN также действует в отношении столбцов, значениями которых являются числа и возвращает минимальное среди всех значений столбца. Эта функция имеет синтаксис аналогичный синтаксису функции SUM.

Пример 3. База данных и таблица - те же, что и в примере 1.

Требуется узнать минимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос (на MS SQL Server - с предваряющей конструкцией USE company1;):

Запрос вернёт значение 10505,90.

И вновь упражнение для самостоятельного решения. В этом и некоторых других упражнениях потребуется уже не только таблица Staff, но и таблица Org, содержащая данные о подразделениях фирмы:

Пример 4. К таблице Staff добавляется таблица Org, содержащая данные о подразделениях фирмы. Вывести минимальное количество лет, проработанных одним сотрудником в отделе, расположенном в Бостоне.

Функция SQL MAX

Аналогично работает и имеет аналогичный синтаксис функция SQL MAX, которая применяется, когда требуется определить максимальное значение среди всех значений столбца.

Пример 5. База данных и таблица - те же, что и в предыдущих примерах.

Требуется узнать максимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос (на MS SQL Server - с предваряющей конструкцией USE company1;):

Запрос вернёт значение 18352,80

Пришло время упражнения для самостоятельного решения.

Пример 6. Вновь работаем с двумя таблицами - Staff и Org. Вывести название отдела и максимальное значение комиссионных, получаемых одним сотрудником в отделе, относящемуся к группе отделов (Division) Eastern. Использовать JOIN (соединение таблиц).

Функция SQL AVG

Указанное в отношении синтаксиса для предыдущих описанных функций верно и в отношении функции SQL AVG. Эта функция возвращает среднее значение среди всех значений столбца.

Пример 7. База данных и таблица - те же, что и в предыдущих примерах.

Пусть требуется узнать средний трудовой стаж сотрудников отдела с номером 42. Для этого пишем следующий запрос (на MS SQL Server - с предваряющей конструкцией USE company1;):

Результатом будет значение 6,33

В следующем упражнении для самостоятельного решения помимо агрегатной функции требуется использовать также предикат BETWEEN.

Пример 8. Работаем с одной таблицей - Staff. Вывести среднюю зарплату сотрудников со стажем от 4 до 6 лет.

Функция SQL COUNT

Функция SQL COUNT возвращает количество записей таблицы базы данных. Если в запросе указать SELECT COUNT(ИМЯ_СТОЛБЦА) . то результатом будет количество записей без учёта тех записей, в которых значением столбца является NULL (неопределённое). Если использовать в качестве аргумента звёздочку и начать запрос SELECT COUNT(*) . то результатом будет количество всех записей (строк) таблицы.

Пример 9. База данных и таблица - те же, что и в предыдущих примерах.

Требуется узнать число всех сотрудников, которые получают комиссионные. Число сотрудников, у которых значения столбца Comm - не NULL, вернёт следующий запрос (на MS SQL Server - с предваряющей конструкцией USE company1;):

Результатом будет значение 11.

Пример 10. База данных и таблица - те же, что и в предыдущих примерах.

Если требуется узнать общее количество записей в таблице, то применяем запрос со звёздочкой в качестве аргумента функции COUNT (на MS SQL Server - с предваряющей конструкцией USE company1;):

Результатом будет значение 17.

В следующем упражнении для самостоятельного решения потребуется использовать подзапрос.

Пример 11. Работаем с одной таблицей - Staff. Вывести число сотрудников в отделе планирования (Plains).

Агрегатные функции вместе с SQL GROUP BY (группировкой)

Теперь рассмотрим применение агрегатных функций вместе с оператором SQL GROUP BY. Оператор SQL GROUP BY служит для группировки результирующих значений по столбцам таблицы базы данных. На сайте есть урок, посвящённый отдельно этому оператору.

Работать будем с базой данных "Портал объявлений 1". Скрипт для создания этой базы данных, её таблицы и заполения таблицы данных - в файле по этой ссылке .

Пример 12. Итак, есть база данных портала объявлений. В ней есть таблица Ads, содержащая данные об объявлениях, поданных за неделю. Столбец Category содержит данные о больших категориях объявлений (например, Недвижимость), а столбец Parts - о более мелких частях, входящих в категории (например, части Квартиры и Дачи являются частями категории Недвижимость). Столбец Units содержит данные о количестве поданных объявлений, а столбец Money - о денежных суммах, вырученных за подачу объявлений.

Category	Part	Units	Money
Транспорт	Автомашины	110	17600
Недвижимость	Квартиры	89	18690
Недвижимость	Дачи	57	11970
Транспорт	Мотоциклы	131	20960
Стройматериалы	Доски	68	7140
Электротехника	Телевизоры	127	8255
Электротехника	Холодильники	137	8905
Стройматериалы	Регипс	112	11760
Досуг	Книги	96	6240
Недвижимость	Дома	47	9870
Досуг	Музыка	117	7605
Досуг	Игры	41	2665

Используя оператор SQL GROUP BY, найти суммы денег, вырученных за подачу объявлений в каждой категории. Пишем следующий запрос (на MS SQL Server - с предваряющей конструкцией USE adportal1;):

SELECT Category, SUM (Money) AS Money FROM ADS GROUP BY Category

Результатом будет следующая таблица:

Category	Money
Досуг	16510
Недвижимость	40530
Стройматериалы	18900
Транспорт	38560
Электротехника	17160

Пример 13. База данных и таблица - та же, что в предыдущем примере.

Используя оператор SQL GROUP BY, выяснить, в какой части каждой категории было подано наибольшее число объявлений. Пишем следующий запрос (на MS SQL Server - с предваряющей конструкцией USE adportal1;):

SELECT Category, Part, MAX (Units) AS Maximum FROM ADS GROUP BY Category

Результатом будет следующая таблица:

Category	Part	Maximum
Досуг	Музыка	117
Недвижимость	Квартиры	89
Стройматериалы	Регипс	112
Транспорт	Мотоциклы	131
Электротехника	Холодильники	137

Итоговые и индивидуальные значения в одной таблице можно получить объединением результатов запросов с помощью оператора UNION.

ваша группа-это то, что агрегирует ваше среднее значение, и оно группируется по всей таблице (я предполагаю, что вы сделали это, чтобы разрешить выбор для всего), просто переместите свой avg в другой подзапрос, удалите всеобъемлющую группу, и это должно ее решить.

при запуске basic SELECT AVG(cost) заявление это, естественно, группировка по указанному столбцу (стоимость в этом случае), поскольку это то, что вы запрашиваете. Я бы предложил прочитать больше на ГРУППЫ ПО и Сростки чтобы лучше понять концепцию. Это должно помочь вам более простое решение.

обновление:

ответ ниже на самом деле из ответа Дэвида. Он использует аналитические функции. В принципе, происходит то, что при каждом вызове AVG вы говорите движку, что использовать для функции (в этом случае ничего). Достойную запись по аналитическим функциям можно найти здесь и здесь и больше с google по этому вопросу.

однако, если ваш SQL engine допускает переменные, вы можете так же легко сделать следующий ответ. Я действительно предпочитаю это для будущей ремонтопригодности / читаемости. Причина в том, что переменная с хорошим именем может быть очень описательной для будущих читателей кода по сравнению с аналитической функцией, которая требует немного больше работы для чтения (особенно если вы не понимаете функцию over).

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

это переменные в SQL-Server (вам придется адаптировать его для собственного экземпляра SQL)

это решение будет читать намного чище для будущих читателей вашего SQL тоже

поскольку вы используете Oracle, вы должны иметь возможность использовать AVG () в качестве аналитической (оконной) функции:

Не нужны подзапросы или группы.

самое простое изменение-изменить avg(cost) as Average to (select avg(cost) from mobile) as Average . Это также означает, что вам не понадобится GROUP BY пункт больше (так как он не делает то, что вы на самом деле хотели):

если ваш запрос слишком дорог, таким образом, напишите мне рекомендацию, тогда я улучшу ее.

Читайте также: