Обсуждение: Rewrite without correlated subqueries
Hi All,<br /><br />I'm having some trouble wrapping my head around the syntax to rewrite a query using correlated subqueries,to using outer joins etc.<br /><br />The query:<br /><br />SELECT ps.userid,<br /> SUM( ps.hits) as numhits<br /> FROM primarystats AS ps<br /> INNER JOIN camp ON <a href="http://camp.id" target="_blank">camp.id</a>= ps.idcamp<br /> INNER JOIN sites ON <a href="http://sites.id" target="_blank">sites.id</a>= ps.idsite<br />WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory WHEREsitescategory.idsites = ps.idsites )<br /> AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory WHEREsitescategory.idsites = ps.idsites )<br />GROUP BY ps.userid;<br /><br />Because I am rewriting this query to use Greenplum,I cannot use correlated subqueries (they are not currently supported).<br /><br />Can anyone suggest a versionthat will garner the same results? I tried with OUTER JOINS and some IS NULLs, but I couldn't get it right.<br /><br/>Thanks!<br /><br />bricklen<br /><br /><br />
Try putting your subqueries into temporary tables, first, inside a BEGIN ... COMMIT block. But your subqueries would producethe negative, i.e., everything except where sitescategory.idsites = ps.idsites. Then reference these temp tablesin your query with inner or outer joins as appropriate. Your new query would not include the ... IN ( <list>) syntax...<br /><br /> Mark<br /><br /> bricklen wrote: <blockquote cite="mid:33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com"type="cite">Hi All,<br /><br /> I'm having some troublewrapping my head around the syntax to rewrite a query using correlated subqueries, to using outer joins etc.<br /><br/> The query:<br /><br /> SELECT ps.userid,<br /> SUM( ps.hits ) as numhits<br /> FROM primarystatsAS ps<br /> INNER JOIN camp ON <a href="http://camp.id" moz-do-not-send="true" target="_blank">camp.id</a>= ps.idcamp<br /> INNER JOIN sites ON <a href="http://sites.id" moz-do-not-send="true" target="_blank">sites.id</a>= ps.idsite<br /> WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory WHEREsitescategory.idsites = ps.idsites )<br /> AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory WHEREsitescategory.idsites = ps.idsites )<br /> GROUP BY ps.userid;<br /><br /> Because I am rewriting this query to useGreenplum, I cannot use correlated subqueries (they are not currently supported).<br /><br /> Can anyone suggest a versionthat will garner the same results? I tried with OUTER JOINS and some IS NULLs, but I couldn't get it right.<br /><br/> Thanks!<br /><br /> bricklen<br /><br /><br /></blockquote>
Interesting idea. Preferably this operation could be done in straight SQL in a single transaction, to fit in with the way our application works, but if that's not possible I may need to go the temporary table route.
On Thu, Aug 20, 2009 at 1:40 PM, Mark Fenbers <Mark.Fenbers@noaa.gov> wrote:
Try putting your subqueries into temporary tables, first, inside a BEGIN ... COMMIT block. But your subqueries would produce the negative, i.e., everything except where sitescategory.idsites = ps.idsites. Then reference these temp tables in your query with inner or outer joins as appropriate. Your new query would not include the ... IN ( <list> ) syntax...
Mark
bricklen wrote:Hi All,
I'm having some trouble wrapping my head around the syntax to rewrite a query using correlated subqueries, to using outer joins etc.
The query:
SELECT ps.userid,
SUM( ps.hits ) as numhits
FROM primarystats AS ps
INNER JOIN camp ON camp.id = ps.idcamp
INNER JOIN sites ON sites.id = ps.idsite
WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory WHERE sitescategory.idsites = ps.idsites )
AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory WHERE sitescategory.idsites = ps.idsites )
GROUP BY ps.userid;
Because I am rewriting this query to use Greenplum, I cannot use correlated subqueries (they are not currently supported).
Can anyone suggest a version that will garner the same results? I tried with OUTER JOINS and some IS NULLs, but I couldn't get it right.
Thanks!
bricklen
On Thu, Aug 20, 2009 at 3:16 PM, bricklen<bricklen@gmail.com> wrote: > Interesting idea. Preferably this operation could be done in straight SQL in > a single transaction, to fit in with the way our application works, but if > that's not possible I may need to go the temporary table route. Temp tables can be included in a transaction, and they're not visible to other connections.
On Thu, Aug 20, 2009 at 2:59 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Thu, Aug 20, 2009 at 3:16 PM, bricklen<bricklen@gmail.com> wrote:Temp tables can be included in a transaction, and they're not visible
> Interesting idea. Preferably this operation could be done in straight SQL in
> a single transaction, to fit in with the way our application works, but if
> that's not possible I may need to go the temporary table route.
to other connections.
Yeah I know, but I was thinking more of replacing this query with vanilla SQL. Maybe that's just not be feasible.