sql - 涉及一个月内列平均值的复杂查询

Question

我有一张像这样的桌子，名字是 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

标签： sqlsql-server

Answer 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;