如何使用oracle sql聚合每月数据库和以前的数据

h9a6wy2h  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(363)

假设我们有这样一个简单的表“示例”:

  1. id time
  2. 1 2-2-20
  3. 2 3-1-20
  4. 3 15-2-20
  5. 4 20-3-20

我想按每个月对包含上月数据的行进行计数,并获得每个月的平均数据(除以指定月份的行):

  1. month rows_num avg_per_day
  2. 1-20 1 1/31
  3. 2-20 2+1 2/29
  4. 3-20 2+1+1 1/31

我在考虑是否可以像这样使用sql,但不会包含上个月的数据,也不知道如何处理平均值:
从示例group by trunc(截止日期(时间)),'month'中选择count(*)
有人能帮我吗?

6vl6ewon

6vl6ewon1#

尽管查询的输出看起来相当简单,但编写一个内联视图来生成可在外部select中使用的值(例如使用示例表)可能是一个优势
表和数据

  1. create table example ( id, day_ )
  2. as
  3. select 1, date '2020-02-02' from dual union all -- 2-2-20
  4. select 2, date '2020-01-03' from dual union all -- 3-1-20
  5. select 3, date '2020-02-15' from dual union all -- 15-2-20
  6. select 4, date '2020-03-20' from dual -- 20-3-20
  7. ;

第一步(将其用于“内联视图”)

  1. select
  2. to_char( day_, 'MM-YY') month_
  3. , to_char( extract ( day from last_day( day_ ) ) ) lastday_
  4. , count(*) over ( order by to_char( day_, 'MM-YY') ) runningtotal_
  5. , row_number() over ( partition by to_char( day_, 'MM-YY') order by day_ ) rn_
  6. from example ;
  7. -- result
  8. +------+--------+-------------+---+
  9. |MONTH_|LASTDAY_|RUNNINGTOTAL_|RN_|
  10. +------+--------+-------------+---+
  11. |01-20 |31 |1 |1 |
  12. |02-20 |29 |3 |1 |
  13. |02-20 |29 |3 |2 |
  14. |03-20 |31 |4 |1 |
  15. +------+--------+-------------+---+

最终查询

  1. select
  2. month_
  3. , runningtotal_ rows_num
  4. , round( max( rn_ ) / lastday_, 5 ) avg_per_day
  5. , to_char( max( rn_ ) ) || '/' || to_char( lastday_ ) avg_per_day
  6. from (
  7. select
  8. to_char( day_, 'MM-YY') month_
  9. , to_char( extract ( day from last_day( day_ ) ) ) lastday_
  10. , count(*) over ( order by to_char( day_, 'MM-YY') ) runningtotal_
  11. , row_number() over ( partition by to_char( day_, 'MM-YY') order by day_ ) rn_
  12. from example
  13. )
  14. group by month_, runningtotal_, lastday_
  15. order by month_
  16. ;
  17. -- result
  18. +------+--------+-----------+-----------+
  19. |MONTH_|ROWS_NUM|AVG_PER_DAY|AVG_PER_DAY|
  20. +------+--------+-----------+-----------+
  21. |01-20 |1 |0.03226 |1/31 |
  22. |02-20 |3 |0.06897 |2/29 |
  23. |03-20 |4 |0.03226 |1/31 |
  24. +------+--------+-----------+-----------+

在这儿摆弄。
注意:我不太清楚你需要哪种形式的“平均每天”。只需从外部选择中删除不需要的行。

展开查看全部
polhcujo

polhcujo2#

您可以使用:

  1. select to_char(time, 'yyyy-mm') as month,
  2. sum(count(*)) over (order by min(time)) as rows_num,
  3. count(*) / extract(day from last_day(time))
  4. from t
  5. group by to_char(time, 'yyyy-mm'), extract(day from last_day(time))
  6. order by min(time);

我格式化了一个月有点不同,但你可以格式化任何你喜欢的。

相关问题