MS Access SQL для расчета среднего логарифмического значения дБмкВ

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

Сегодня у меня есть данные в Excel, и мой расчет работает нормально. К сожалению, производительность Excel достигла своего предела, поэтому я импортировал данные в MS Access (я использую MS Access Ver Office 365).

У меня есть таблица с идентификатором, частотой и значениями дБмкВ. Я хотел бы получить среднее логарифмическое всех выборок дБмкВ в определенном диапазоне частот для определенного идентификатора. Частота начинается со 122 и может доходить до 1802. Не каждый ID имеет значение для всех частот.

Диапазоны частот могут быть следующими и никогда не меняются (означает, что начало и конец диапазона частот постоянны):

  • от 122 до 170
  • от 178 до 226
  • от 234 до 530
  • и так далее

В Excel я использовал следующую формулу для диапазона частот F122_170 (B1:H1 содержит значения дБмкВ):

{=10*LOG(SUM(10^(B1:H1/10))/COUNTA(B1:H1))}

Теперь я полностью потерял оператор SQL, чтобы получить желаемый результат.

Ниже приведен пример моей таблицы «tblDev».

ИДЕНТИФИКАТОР частота дБмкВ 3977739907 122 32,44 3977739907 130 32.24 3977739907 138 31,93 3977739907 146 30,79 3977739907 154 31.31 3977739907 162 32.39 3977739907 170 32.43 3977739907 178 32,81 3977739907 186 32.21 3977739907 194 31.23 … … …

Результат, который я хотел бы получить, выглядит следующим образом:

ИДЕНТИФИКАТОР F122_170 Ф178_226 F234_530 F770_up 3977739907 31,97 31.23 37.03 0,00 3845056609 32.24 23.40 41.09 38.18 3882949203 23.10 16,98 31.03 32,26 ... ... ... ... ...

Было бы здорово, если бы кто-нибудь помог мне с оператором SQL.

С уважением Патрик

Это не сайт рекомендаций по программному обеспечению, но есть гораздо более мощные базы данных, чем MS Access, если вы выбираете новую базу данных — даже бесплатные версии SQL Server.

Gordon Linoff 22.12.2020 13:36

Однажды я попытался перевести формулу Excel в Access. Пришлось использовать VBA для построения процедуры умножения матриц. Я сравнил два метода в VBA с вычислением рабочего листа Excel и получил 3 разных результата. Один метод ссылался на матричные функции Excel в VBA, а другой — нет (это код, найденный в Интернете). Никогда не получалось договориться.

June7 22.12.2020 14:28

Единственное значение, с которым я могу согласиться, это 31,97. Не предоставил достаточно данных для получения желаемого результата.

June7 22.12.2020 15:19

@GordonLinoff Я знаю гораздо более профессиональные базы данных, чем доступ. Но все, что у меня есть на моем корпоративном ноутбуке, который может обрабатывать больше данных, чем Excel, — это доступ. Поскольку нам не разрешено устанавливать какие-либо другие инструменты, кроме предоставленных, мне приходится иметь дело с тем, что у меня есть.

maraguma 22.12.2020 21:27
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
2
4
114
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Хм . . . Я думаю, вы можете использовать:

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 22.12.2020 13:58

@June7 . . . Я изменил ответ. Я взял эти функции из того, что выглядело как страница документации MS Access.

Gordon Linoff 22.12.2020 15:14

Они не работают в Access 2010, поэтому, возможно, они были UDF или добавлены в более поздние версии Access.

June7 22.12.2020 15:16

@June7 . . . Как я уже сказал в комментарии, есть более мощные базы данных, которые вы могли бы выбрать.

Gordon Linoff 22.12.2020 15:49

Не мне выбирать. Я оставлю это ОП.

June7 22.12.2020 15:50

Учитывать:

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.

maraguma 22.12.2020 22:07

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

June7 22.12.2020 22:16
Ответ принят как подходящий

Сначала вам нужна функция 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.

Gustav 22.12.2020 16:47

Спасибо за вашу помощь. Это работает просто идеально. Я проверил несколько примеров, и до сих пор все они имеют точно такое же среднее логарифмическое значение, как и с формулой в Excel.

maraguma 22.12.2020 21:29

Другие вопросы по теме