获取每个id的年平均值

Question

此代码获取一年中每个月显示的ID数。

WITH t1 as (SELECT * FROM daily WHERE EVENT_DATE BETWEEN DATE '2019-01-01' and DATE '2019-12-31'),

SELECT ID, COUNT(ID) AS monthly_count, TO_CHAR(EVENT_DATE, 'YYYY-MM') as mon_yy
FROM t1 GROUP BY ID, TO_CHAR(EVENT_DATE, 'YYYY-MM')

这是我得到的结果：

1 |2019-01 |152
1 |2019-02 |127
1 |2019-03 |186
1 |2019-04 |47
1 |2019-05 |31
1 |2019-06 |46
1 |2019-07 |34
1 |2019-08 |22
1 |2019-09 |17
1 |2019-10 |22
1 |2019-11 |12
1 |2019-12 |16
2 |2019-01 |84930842
2 |2019-02 |69685158
2 |2019-03 |109122032
2 |2019-04 |114767006
2 |2019-05 |116766004
2 |2019-06 |197809839
2 |2019-07 |205065236
2 |2019-08 |383143170
2 |2019-09 |244118916
2 |2019-10 |210451973
2 |2019-11 |142372447
2 |2019-12 |63189538
3 |2019-01 |4361080
3 |2019-02 |5679732
3 |2019-03 |10101952

我想得到每个ID的年平均值，以及总数。结果如下：

ID| YEAR | OVER_ALL_COUNT| AVG
1| 2019 | 712           | 59.33
2| 2019 | 20936849      | 161185684.6
3| 2019 | 14255773      | 2177532.2

Answer 1

您可以添加另一个聚合级别：

select id, to_char(event_month, 'yyyy') event_year, sum(cnt) overall_count, avg(cnt) average_count
from (
select id, trunc(event_date, 'month') event_month, count(*) cnt
from daily 
where event_date >= date '2019-01-01' and event_date < '2020-01-01'
group by id, trunc(event_date, 'month')
) t
group by id, to_char(event_month, 'yyyy')

注：

• 您实际上不需要CTE来预过滤数据：这可以在第一个聚合查询中直接完成

• 我将过滤逻辑更改为使用半开区间；如果你的日期有'00:00:00'以外的时间成分，那么这就避免了过滤掉一个月的最后一天(如果没有，也不会影响