如何统计oracle的每周数据?

Question

我有两个问题。

第一个问题是;我想每周统计数据并查看总数据。总数据不正确。W1显示最近一周的数据，W2显示最近两周的数据，等等。

结果:

<表类>水果w1w2w3w4总tbody><<tr>苹果624125香蕉715932

您可以使用DENSE_RANK来获得最近五周的编号1,2,3,4和5。使用ISO周('IYYY-IW'格式)。

select wk1, wk2, wk3, wk4, wk5, wk1 + wk2 + wk3 + wk4 + wk5 as total
from
(
select
fruit,
dense_rank() over (order by to_char(eventtime, 'iyyy-iw') desc) as wk
from fruits
where eventtime > trunc(sysdate) - interval '35' day
)
pivot
(
count(*)
for wk in (1 as wk1, 2 as wk2, 3 as wk3, 4 as wk4, 5 as wk5)
)
order by fruit;