Блог

Главная - Вопросы по программированию - Сложный запрос, включающий среднее значение столбца за месяц

Сложный запрос, включающий среднее значение столбца за месяц

#sql #sql-server

#sql #sql-сервер

Вопрос:

У меня есть таблица, подобная этой, с именем tv_v2.tv_momentum

  tv_date            instrument_name                        factor

2019-07-22  cbc267f7-6ace-4357-a803-7aaf96a2cc48    50.1228599355797
2019-07-23  cbc267f7-6ace-4357-a803-7aaf96a2cc5a    50.0851750766468
2019-07-24  cbc267f7-6ace-4357-a803-7aaf96a2cc48    50.0474332287848
2019-07-25  cbc267f7-6ace-4357-a803-7aaf96a2cc31    50.0096342626235
2019-07-26  cbc267f7-6ace-4357-a803-7aaf96a2cc48    50.312332423432343
2019-07-27  cbc267f7-6ace-4357-a803-7aaf96a2cc48    23.424234234234
2019-07-28  cbc267f7-6ace-4357-a803-7aaf96a77777    15.33333332332323
2019-07-29  cbc267f7-6ace-4357-a803-7aaf96a2cc48    66.3333333333333
2019-07-30  cbc267f7-6ace-4357-a803-7aaf96a2cc4f    77.322332323223
2019-07-31  cbc267f7-6ace-4357-a803-7aaf96a2cc4s    50

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

 YEAR  MONTH  END_OF_MONTH_DAY   INSTRUMENT_NAME    AVERAGE_FACTOR_OVER_THE_MONTH  END_OF_THE_MONTH_FACTOR

2019    7      31-7     cbc267f7-6ace-4357-a803-7aaf96a2cc48        50.11              50
2019    8      31-8     cbc267f7-6ace-4357-a803-7aaf96a2cc48        33                 56

Комментарии:

1. версия базы данных? как называется ваша таблица?

2. Если вы используете MySQL 8.x, вы можете использовать оконные функции. Если вы используете 5.x, вам нужно написать два запроса, один, чтобы получить последнюю строку месяца, другой, чтобы получить среднее значение за месяц, и объединить их.

3. Извините, имя таблицы — tv_v2.tv_momentum

4. а версия базы данных? select version();

5. SQL Server Management Studio 15.0.18330.0 Объекты управления SQL Server (SMO) 16.100.37971.0 Клиентские инструменты Microsoft Analysis Services 15.0.19040.0 Компоненты доступа к данным Microsoft (MDAC) 10.0.14393.0 Microsoft MSXML 3.0 6.0 Microsoft .NET Framework 4.0.30319.42000 Операционная система 10.0.14393

Ответ №1:

Вы можете использовать условную агрегацию:

 select year(tv_date), month(tv_date), max(tv_date), 
       instrument_name,
       avg(factor),
       max(case when seqnum = 1 then factor end)
from (select t.*,
             row_number() over (partition by year(tv_date), month(tv_date)  order by tv_date desc) as seqnum
      from t
     ) t
group by year(tv_date), month(tv_date), instrument_name;

Комментарии:

1. Я бы просто использовал first_value(factor) over (partition by extract(year_month from tv_date) order by tv_date desc) , а не row_number

2. является ли t.* именем таблицы?

3. ‘extract’ не является распознанным именем встроенной функции.

4. @ysth: вы не можете использовать first_value(factor) over(...) в этом запросе агрегации, если factor это не является частью group by предложения.

5. @user3139149: эта функция не существует в SQL Server, которым в конечном итоге был помечен вопрос (он начинался как MySQL). Просто замените partition by extract(year_month from tv_date) на partition by year(tv_date), month(tv_date)