查找一个月中ID的平均值 [英] Find the average of ids in a month

问题描述

我可以计算一个月中ID的数量，然后将其汇总12个月。

I can calculate the number of ids in a month and then sum it up over 12 months. I also get the average using this code.

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 < '2019-01-31'
    group by id, trunc(event_date, 'month')
) t
group by id, to_char(event_month, 'yyyy')

结果看起来像这样：

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

但是，我想修改它以获取一个月的所有ID总数以及ID的平均值每月计数。期望的结果是：

However, I want to modify this to get the over all id counts for a month instead and the average of the id counts per month. Desired result is:

ID| MONTH | OVER_ALL_COUNT| AVG
 1| Jan   | 152            | 10.3
 2| Jan   | 15000          | 1611
 3| Jan   | 14255          | 2177
 1| Feb   | 4300           | 113
 2| Feb   | 9700           | 782
 3| Feb   | 1900           | 97

其中，一月id = 1时，总共有152个id计数，每天的平均id计数是10.3。对于id = 2，一月计数为15000，而jan的平均id = 2计数为1611。

where January has 152 id counts over all for id=1, and the average id count per day is 10.3. For id=2, the january count is 15000 and the average id=2 count for jan is 1611.

推荐答案

您只需要将子查询的截断更改为按天而不是按月截断，然后按月而不是按年截断外部查询。

You just need to change the truncating on your subquery to truncate by day instead of by month, then truncate the outer query by month instead of year.

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

这篇关于查找一个月中ID的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！