Re: need help in building a query - Mailing list pgsql-sql

From Devil™ Dhuvader
Subject Re: need help in building a query
Date
Msg-id aaa67ada0811080912j3b956570xd584750d729e2a55@mail.gmail.com
Whole thread Raw
In response to Resp.: need help in building a query  ("Osvaldo Kussama" <osvaldo.kussama@gmail.com>)
List pgsql-sql
<div class="gmail_quote"><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt
0pt0pt 0.8ex; padding-left: 1ex;"><div class="Ih2E3d"><br /></div>Try:<br /><br /> bdteste=# SELECT o1.user_id,
o1.order_id,'>= 500' AS cond FROM Orders o1<br /> bdteste-#  WHERE (SELECT sum(o2.amount_paid) FROM Orders o2
WHERE<br/> o2.user_id = o1.user_id AND o2.order_id > o1.order_id) < 500 AND<br /> bdteste-#        (SELECT
sum(o2.amount_paid)FROM Orders o2 WHERE<br /> o2.user_id = o1.user_id AND o2.order_id >= o1.order_id) >=500<br />
bdteste-#UNION<br /> bdteste-# SELECT user_id, min(order_id) AS "min order id", '< 500' AS<br /> cond FROM Orders<br
/>bdteste-#  WHERE user_id IN (SELECT user_id FROM Orders GROUP BY<br /> user_id HAVING sum(amount_paid) < 500)<br
/>bdteste-#  GROUP BY user_id;<br />  user_id | order_id |  cond<br /> ---------+----------+--------<br />       1 |  
    2 | >= 500<br />       2 |        3 | < 500<br /><font color="#888888"><br /> Osvaldo<br
/></font></blockquote></div><br/>this sounds too heavy. as the order table has too many entries (4105258)<br /> 

pgsql-sql by date:

Previous
From: "Devil™ Dhuvader"
Date:
Subject: Re: need help in building a query
Next
From: "Sebastian Ritter"
Date:
Subject: Query optimizing