I'm doing a probability calculation. I have a query to calculate the total number of times an event occurs. From these events, I want to get the number of times a sub-event occurs. The query to get the total events is 25 lines long and I don't want to just copy + paste it twice.
我正在进行概率计算。我有一个查询来计算事件发生的总次数。从这些事件中,我想得到子事件发生的次数。获取总事件的查询长度为25行,我不想只复制+粘贴两次。
I want to do two things to this query: calculate the number of rows in it, and calculate the number of rows in the result of a query on this query. Right now, the only way I can think of doing that is this (replace @total@ with the complicated query to get all rows, and @conditions@ with the less-complicated conditions that rows, from @total@, must have to match the sub-event):
我想对此查询执行两项操作:计算其中的行数,并计算此查询的查询结果中的行数。现在,我能想到的唯一方法就是这样做(用复杂的查询替换@ total @来获取所有行,并且@ conditions @的条件不太复杂,来自@ total @的行必须匹配子事件):
SELECT (SELECT COUNT(*) FROM (@total@) AS t1 WHERE @conditions@) AS suboccurs,
COUNT(*) AS totaloccurs FROM (@total@) as t2
As you notice, @total@ is repeated twice. Is there any way around this? Is there a better way to do what I'm trying to do?
如你所知,@ total @重复两次。有没有办法解决?有没有更好的方法来做我想做的事情?
To re-emphasize: @conditions@ does depend on what @total@ returns (it does stuff like t1.foo = bar).
重新强调:@condition @取决于@otal @返回的内容(它的确如t1.foo = bar)。
Some final notes: @total@ by itself takes ~250ms. This more complicated query takes ~300ms, so postgres is likely doing some optimization, itself. Still, the query looks terribly ugly with @total@ literally pasted in twice.
最后的一些注意事项:@ total @本身需要大约250ms。这个更复杂的查询需要大约300毫秒,因此postgres可能会进行一些优化。尽管如此,查询看起来非常难看,@ total @字面上粘贴了两次。
4 个解决方案
#1
SELECT COUNT(*) as totaloccurs, COUNT(@conditions@) as suboccurs
FROM (@total@ as t1)
#2
If your sql supports subquery factoring, then rewriting it using the WITH statement is an option. It allows subqueries to be used more than once. With will create them as either an inline-view or a temporary table in Oracle.
如果你的sql支持子查询因子分解,那么使用WITH语句重写它是一个选项。它允许子查询多次使用。使用它将它们创建为Oracle中的内联视图或临时表。
Here is a contrived example.
这是一个人为的例子。
WITH
x AS
(
SELECT this
FROM THERE
WHERE something is true
),
y AS
(
SELECT this-other-thing
FROM somewhereelse
WHERE something else is true
),
z AS
(
select count(*) k
FROM X
)
SELECT z.k, y.*, x.*
FROM x,y, z
WHERE X.abc = Y.abc
#3
Put the reused sub-query into a temp table, then select what you need from the temp table.
将重用的子查询放入临时表中,然后从临时表中选择所需的内容。
#4
@EvilTeach:
I've not seen the "with" (probably not implemented in Sybase :-(). I like it: does what you need in one chunk then goes away, with even less cruft than temp tables. Cool.
我没有看过“with”(可能没有在Sybase中实现:-()。我喜欢它:你需要在一个块中做什么然后消失,甚至比临时表更少残酷。酷。
#1
SELECT COUNT(*) as totaloccurs, COUNT(@conditions@) as suboccurs
FROM (@total@ as t1)
#2
If your sql supports subquery factoring, then rewriting it using the WITH statement is an option. It allows subqueries to be used more than once. With will create them as either an inline-view or a temporary table in Oracle.
如果你的sql支持子查询因子分解,那么使用WITH语句重写它是一个选项。它允许子查询多次使用。使用它将它们创建为Oracle中的内联视图或临时表。
Here is a contrived example.
这是一个人为的例子。
WITH
x AS
(
SELECT this
FROM THERE
WHERE something is true
),
y AS
(
SELECT this-other-thing
FROM somewhereelse
WHERE something else is true
),
z AS
(
select count(*) k
FROM X
)
SELECT z.k, y.*, x.*
FROM x,y, z
WHERE X.abc = Y.abc
#3
Put the reused sub-query into a temp table, then select what you need from the temp table.
将重用的子查询放入临时表中,然后从临时表中选择所需的内容。
#4
@EvilTeach:
I've not seen the "with" (probably not implemented in Sybase :-(). I like it: does what you need in one chunk then goes away, with even less cruft than temp tables. Cool.
我没有看过“with”(可能没有在Sybase中实现:-()。我喜欢它:你需要在一个块中做什么然后消失,甚至比临时表更少残酷。酷。