SQL - 在一个语句中将查询的结果用作其他两个查询的基础 [英] SQL - Use results of a query as basis for two other queries in one statement

问题描述

我在做一个概率计算。我有一个查询来计算事件发生的总次数。从这些事件，我想获得子事件发生的次数。获得总事件的查询是25行长，我不想只是复制+粘贴它两次。

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.

我想对此查询执行两项操作：计算其中的行数，并计算此查询的查询结果中的行数。现在，我可以想到这样做的唯一方法就是这样（用@ total @替换复杂的查询以获取所有行，@ conditions @使用较不复杂的条件，从@ total @开始必须匹配子事件）：

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):

SELECT (SELECT COUNT(*) FROM (@total@) AS t1 WHERE @conditions@) AS suboccurs, 
       COUNT(*) AS totaloccurs FROM (@total@) as t2

你注意到，@ total @被重复两次。有什么办法吗？是否有更好的方法来做我想做的事？

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?

要重新强调：@ conditions @取决于@ total @返回什么 t1.foo = bar ）。

To re-emphasize: @conditions@ does depend on what @total@ returns (it does stuff like t1.foo = bar).

一些最后的注释：@ total @本身需要〜250ms。这个更复杂的查询需要〜300ms，所以postgres可能会做一些优化，本身。

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.

推荐答案

SELECT COUNT(*) as totaloccurs, COUNT(@conditions@) as suboccurs
FROM (@total@ as t1)

这篇关于SQL - 在一个语句中将查询的结果用作其他两个查询的基础的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！