Oracle SQL 查询显示每日事件计数和过去 7 天的事件总数
有下表：event_table，其中包含object_name、event_number、event_supplementary_info、event_time的信息。我想要一个 sql 查询，它显示过去 7 天每天的事件数和总数。
我需要这样的东西
select Object_name, event_number
max(decode(trim(dow),'MONDAY',totalquantity,0)) Mon,
max(decode(trim(dow),'TUESDAY',totalquantity,0)) Tue,
max(decode(trim(dow),'WEDNESDAY',totalquantity,0)) Wed,
max(decode(trim(dow),'THURSDAY',totalquantity,0)) Thu,
max(decode(trim(dow),'FRIDAY',totalquantity,0)) Fri,
max(decode(trim(dow),'SATURDAY',totalquantity,0)) Sat,
max(decode(trim(dow),'SUNDAY',totalquantity,0)) Sun,
(
max(decode(trim(dow),'MONDAY',totalquantity,0)) +
max(decode(trim(dow),'TUESDAY',totalquantity,0)) +
max(decode(trim(dow),'WEDNESDAY',totalquantity,0)) +
max(decode(trim(dow),'THURSDAY',totalquantity,0)) +
max(decode(trim(dow),'FRIDAY',totalquantity,0)) +
max(decode(trim(dow),'SATURDAY',totalquantity,0)) +
max(decode(trim(dow),'SUNDAY',totalquantity,0))
) TOTAL
from
(
select event_name,
to_char(event_time, 'DAY') as dow,
sum(event_time) as totalquantity
from event_table a
where a.event_time >= trunc(sysdate-7,'D')
and a.tradedate <= trunc(sysdate-7,'D') + 7
group by a.event_name, alarm_time
)
group by Object_name, event_number;
There is the following table: event_table, which contains information about object_name, event_number, event_supplementary_info, event_time. I would like to have a sql query, which shows number of events per last seven day daily and total number.
I need something like this
select Object_name, event_number
max(decode(trim(dow),'MONDAY',totalquantity,0)) Mon,
max(decode(trim(dow),'TUESDAY',totalquantity,0)) Tue,
max(decode(trim(dow),'WEDNESDAY',totalquantity,0)) Wed,
max(decode(trim(dow),'THURSDAY',totalquantity,0)) Thu,
max(decode(trim(dow),'FRIDAY',totalquantity,0)) Fri,
max(decode(trim(dow),'SATURDAY',totalquantity,0)) Sat,
max(decode(trim(dow),'SUNDAY',totalquantity,0)) Sun,
(
max(decode(trim(dow),'MONDAY',totalquantity,0)) +
max(decode(trim(dow),'TUESDAY',totalquantity,0)) +
max(decode(trim(dow),'WEDNESDAY',totalquantity,0)) +
max(decode(trim(dow),'THURSDAY',totalquantity,0)) +
max(decode(trim(dow),'FRIDAY',totalquantity,0)) +
max(decode(trim(dow),'SATURDAY',totalquantity,0)) +
max(decode(trim(dow),'SUNDAY',totalquantity,0))
) TOTAL
from
(
select event_name,
to_char(event_time, 'DAY') as dow,
sum(event_time) as totalquantity
from event_table a
where a.event_time >= trunc(sysdate-7,'D')
and a.tradedate <= trunc(sysdate-7,'D') + 7
group by a.event_name, alarm_time
)
group by Object_name, event_number;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这是使用此示例数据的尝试。下次请您自行提供一些样本数据。
您的查询不起作用：有多余的右括号。如果我删除它们，它仍然不起作用，因为您正在将日期与 varchar2 进行比较：
所以我的基本查询是我将日期与日期进行比较的查询：
我解释了您的问题，即您想要一条包含每个事件总数的记录对象名称。对于此示例数据，您需要 4 个额外记录，分别用于对象 A、B、C 和 D。为了实现这一点，我仅在 object_name 上添加了另一个分组集。为了清楚起见，我将 trunc(event_time) 添加到现有分组集中。
编写此类查询的简短方法是将分组集转换为汇总：
问候，
抢。
Here is a try using this sample data. For next time please provide some sample data yourself.
Your query doesn't work: there are superfluous right brackets. If I remove them, it still doesn't work because you are comparing dates with varchar2's:
So my base query is this one where I compare the dates with dates:
I interpreted your question that you want a record with the total number of events per object_name. With this sample data, you need 4 extra records, for objects A, B, C and D. To achieve that, I added another grouping set on just object_name. And I included trunc(event_time) to the existing grouping set for clarity.
The short way to write such a query, is to convert the grouping sets to a rollup:
Regards,
Rob.
或者也许这就是您正在寻找的： http://psoug.org/reference/rollup.html< /a>？
Or maybe this is what you are looking for: http://psoug.org/reference/rollup.html ?