К сожалению, мои навыки SQL очень просты. Я надеюсь, что кто-то может мне помочь. Я хотел бы рассчитать среднее логарифмическое по нескольким значениям дБмкВ. К сожалению, я не могу использовать стандартную среднюю функцию, так как результат неверен.
Сегодня у меня есть данные в Excel, и мой расчет работает нормально. К сожалению, производительность Excel достигла своего предела, поэтому я импортировал данные в MS Access (я использую MS Access Ver Office 365).
У меня есть таблица с идентификатором, частотой и значениями дБмкВ. Я хотел бы получить среднее логарифмическое всех выборок дБмкВ в определенном диапазоне частот для определенного идентификатора. Частота начинается со 122 и может доходить до 1802. Не каждый ID имеет значение для всех частот.
Диапазоны частот могут быть следующими и никогда не меняются (означает, что начало и конец диапазона частот постоянны):
В Excel я использовал следующую формулу для диапазона частот F122_170 (B1:H1 содержит значения дБмкВ):
{=10*LOG(SUM(10^(B1:H1/10))/COUNTA(B1:H1))}
Теперь я полностью потерял оператор SQL, чтобы получить желаемый результат.
Ниже приведен пример моей таблицы «tblDev».
Результат, который я хотел бы получить, выглядит следующим образом:
Было бы здорово, если бы кто-нибудь помог мне с оператором SQL.
С уважением Патрик
Однажды я попытался перевести формулу Excel в Access. Пришлось использовать VBA для построения процедуры умножения матриц. Я сравнил два метода в VBA с вычислением рабочего листа Excel и получил 3 разных результата. Один метод ссылался на матричные функции Excel в VBA, а другой — нет (это код, найденный в Интернете). Никогда не получалось договориться.
Единственное значение, с которым я могу согласиться, это 31,97. Не предоставил достаточно данных для получения желаемого результата.
@GordonLinoff Я знаю гораздо более профессиональные базы данных, чем доступ. Но все, что у меня есть на моем корпоративном ноутбуке, который может обрабатывать больше данных, чем Excel, — это доступ. Поскольку нам не разрешено устанавливать какие-либо другие инструменты, кроме предоставленных, мне приходится иметь дело с тем, что у меня есть.
Хм . . . Я думаю, вы можете использовать:
select id,
10 * log(avg(iif (freq between 122 and 170, 10^dBuV), null)) / log(10)
10 * log(avg(iif (freq between 178 and 256, 10^dBuV), null)) / log(10)
from t
group by id;
Примечание. Это заменяет SUM()
/COUNT()
на AVG()
. . . что я думаю, это одно и то же.
Пробовал и получил синтаксическую ошибку. Доступ имеет функцию журнала, но не Log10. Полномочия выполняются с помощью символа ^. Отсутствует /10 в расчете. Не должно быть запятой перед ОТ. Все еще получаю синтаксическую ошибку.
@June7 . . . Я изменил ответ. Я взял эти функции из того, что выглядело как страница документации MS Access.
Они не работают в Access 2010, поэтому, возможно, они были UDF или добавлены в более поздние версии Access.
@June7 . . . Как я уже сказал в комментарии, есть более мощные базы данных, которые вы могли бы выбрать.
Не мне выбирать. Я оставлю это ОП.
Учитывать:
Query1: Log() возвращает натуральный логарифм (по основанию e), поэтому, чтобы получить логарифм по основанию 10, разделите Log(x) на Log(10)
SELECT tblDev.ID, 10*(Log(Avg(10^([dBuV]/10)))/Log(10)) AS dB,
Switch([freq]<=170,"F122_170",[freq]<=226,"F178_226",[freq]<=530,"F234_530",True,"F770_up") AS Grp
FROM tblDev
GROUP BY tblDev.ID, Switch([freq]<=170,"F122_170",[freq]<=226,"F178_226",[freq]<=530,"F234_530",True,"F770_up");
Query2: для поворота результатов query1
TRANSFORM First(dB)
SELECT Query1.ID
FROM Query1
GROUP BY Query1.ID
PIVOT Query1.Grp IN (F122_170,F178_226,F234_530,F770_up);
@Jun7, спасибо за вашу помощь. Я тоже попробовал ваше решение, и оно тоже сработало отлично. Все примеры, которые я проверил, были такими же, как в Excel.
Рад, что у вас есть рабочий ответ, и вы его приняли. Также можете проголосовать столько, сколько хотите.
Сначала вам нужна функция Log10:
Public Function Log10( _
ByVal Value As Double) _
As Double
' Returns Log 10 of Value.
' 2015-08-17. Gustav Brock, Cactus Data ApS, CPH.
Const Base10 As Double = 10
' No error handling as this should be handled
' outside this function.
'
' Example:
'
' If MyValue > 0 then
' LogMyValue = Log10(MyValue)
' Else
' ' Do something else ...
' End If
Log10 = Log(Value) / Log(Base10)
End Function
Затем вы можете запустить этот запрос:
Select
ID,
Sum(AF122_170) As F122_170,
Sum(AF178_226) As F178_226,
Sum(AF234_530) As F234_530,
Sum(AF770_up) As F770_up
From
(Select
tblDev.ID,
IIf(freq Between 122 And 170, Log10(Avg(10 ^ (tblDev.dBuV / 10))) * 10, 0) As AF122_170,
IIf(freq Between 178 And 226, Log10(Avg(10 ^ (tblDev.dBuV / 10))) * 10, 0) As AF178_226,
IIf(freq Between 234 And 530, Log10(Avg(10 ^ (tblDev.dBuV / 10))) * 10, 0) As AF234_530,
IIf(freq >= 770, Log10(Avg(10 ^ (tblDev.dBuV / 10))) * 10, 0) As AF770_up From
tblDev
Group By
ID,
freq Between 122 And 170,
freq Between 178 And 226,
freq Between 234 And 530,
freq >= 770) As T
Group By ID
Упс, у меня была обратная математика. Исправленный. Теперь для первой группы возвращается 31,97.
Спасибо за вашу помощь. Это работает просто идеально. Я проверил несколько примеров, и до сих пор все они имеют точно такое же среднее логарифмическое значение, как и с формулой в Excel.
Это не сайт рекомендаций по программному обеспечению, но есть гораздо более мощные базы данных, чем MS Access, если вы выбираете новую базу данных — даже бесплатные версии SQL Server.