我的表名称是AGENTWISEMONTHLYREPORT,字段是USER_ID MONTH_YEARNO_FO_TICKETS

  USER_ID    MONTH_YEAR  NO_FO_TICKETS
 iciseva00031   201706  8
 iciseva00031   201705  22
 iciseva00031   201704  29
 iciseva00031   201703  30
 iciseva00031   201702  26
 iciseva00031   201701  14
 iciseva00031   201612  14
 iciseva00031   201611  14
 iciseva00031   201610  34
 iciseva00031   201609  25
 iciseva00031   201608  109
 iciseva00031   201607  11


预期的输出如下:

userid         sum(no_of_tickets)
iciseva00031   151
iciseva00031   60


那些总和(no_of_tickets)是每3行的总和。
 谁能帮我。

最佳答案

如果要按季度(3个月)分组,则可以在Oracle SQL中执行以下操作:

select   user_id,
         floor(to_number(month_year)/100) year,
         1 + floor((mod(to_number(month_year), 100) - 1) / 3) quarter,
         sum(no_fo_tickets) sum_tickets
from     mytable
group by user_id,
         floor(to_number(month_year)/100),
         1 + floor((mod(to_number(month_year), 100) - 1) / 3)
order by 1, 2 desc, 3 desc;


输出如下:

   user_id   | year | quarter | sum_tickets
-------------+------+---------+------------
iciseva00031 | 2017 |    2    |    59
iciseva00031 | 2017 |    1    |    70
iciseva00031 | 2016 |    4    |    62
... etc

10-06 05:31