sum() 中的冗余
表1-> id, time_stamp, value
该表由 10 个 id 组成。每个 id 都会有一天中每个小时的值。 因此 1 天，该表中将有 240 条记录。
表2-> 表 2 的id
由表 1 中存在的 id 的动态变化的子集组成。
在特定实例中，目的是从 table1 中获取 sum(value)，仅考虑 table2 中的 id， 按当天的每个小时进行分组，对汇总值进行排名并每天重复此操作。
查询处于此阶段：
select time_stamp, sum(value),
rank() over (partition by trunc(time_stamp) order by sum(value) desc) rn
from table1
where exists (select t2.id from table2 t2 where id=t2.id)
and
time_stamp >= to_date('05/04/2010 00','dd/mm/yyyy hh24') and
time_stamp <= to_date('25/04/2010 23','dd/mm/yyyy hh24')
group by time_stamp
order by time_stamp asc
如果查询正确，考虑到 table1 实际上将由数千个 id 而不是 10 个组成，是否可以提高效率？
编辑：我在查询中使用 sum(value) 两次，但我无法找到解决方法，使 sum() 仅执行一次。请帮忙解决这个问题
table1 -> id, time_stamp, value
This table consists of 10 id's. Each id would be having a value for each hour in a day.
So for 1 day, there would be 240 records in this table.
table2 -> id
Table2 consists of a dynamically changing subset of id's present in table1.
At a particular instance, the intention is to get sum(value) from table1, considering id's only in table2,
grouping by each hour in that day, giving the summarized values a rank and repeating this each day.
the query is at this stage:
select time_stamp, sum(value),
rank() over (partition by trunc(time_stamp) order by sum(value) desc) rn
from table1
where exists (select t2.id from table2 t2 where id=t2.id)
and
time_stamp >= to_date('05/04/2010 00','dd/mm/yyyy hh24') and
time_stamp <= to_date('25/04/2010 23','dd/mm/yyyy hh24')
group by time_stamp
order by time_stamp asc
If the query is correct, can this be made more efficient, considering that, table1 will actually consist of thousand's of id's instead of 10 ?
EDIT: I am using sum(value) 2 times in the query, which I am not able to get a workaround such that the sum() is done only once. Pls help on this
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
<罢工>
从表1
where contains (select t2.id from table2 t2 where value=t2.value)
table2 没有
Value字段。为什么上面的查询带有t2.Value？您可以在此处使用连接
编辑：我在 Oracle 上工作已经有一段时间了。如果我对 t2.Value 的评论没有意义，请原谅。
from table1
where exists (select t2.id from table2 t2 where value=t2.value)
The table2 doesn't have
Valuefield. Why is the above query witht2.Value?You could use a join here
EDIT: Its been a while that I worked on Oracle. Pardon me, if my comment on
t2.Valuedoesn't make sense.