Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJmTR-0000z4-Vm for pgsql-sql@arkaria.postgresql.org; Sat, 01 Mar 2014 16:09:14 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WJmTR-000061-EL for pgsql-sql@arkaria.postgresql.org; Sat, 01 Mar 2014 16:09:13 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJmTP-0008Ve-EA for pgsql-sql@postgresql.org; Sat, 01 Mar 2014 16:09:12 +0000 Received: from zimbra.isdd.sk ([91.233.248.204]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJmTM-00005a-Ra for pgsql-sql@postgresql.org; Sat, 01 Mar 2014 16:09:10 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by zimbra.isdd.sk (Postfix) with ESMTP id DE65AAC86A7; Sat, 1 Mar 2014 17:09:07 +0100 (CET) X-Virus-Scanned: amavisd-new at zimbra.isdd.sk Received: from zimbra.isdd.sk ([127.0.0.1]) by localhost (zimbra.isdd.sk [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id hHzQ64+e7rlf; Sat, 1 Mar 2014 17:09:07 +0100 (CET) Received: from zimbra.isdd.sk (zimbra.isdd.sk [91.233.248.204]) by zimbra.isdd.sk (Postfix) with ESMTP id A376CAC8617; Sat, 1 Mar 2014 17:09:07 +0100 (CET) Date: Sat, 1 Mar 2014 17:09:07 +0100 (CET) From: Jan Ostrochovsky To: David Johnston Cc: pgsql-sql@postgresql.org Message-ID: <2042881838.607913.1393690147570.JavaMail.root@mobiletech.sk> In-Reply-To: <1393513774376-5793882.post@n5.nabble.com> Subject: Re: how to effectively SELECT new "customers" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_607912_1348384026.1393690147569" X-Originating-IP: [10.8.253.141] X-Mailer: Zimbra 7.2.6_GA_2926 (ZimbraWebClient - GC33 (Win)/7.2.6_GA_2926) X-Pg-Spam-Score: 0.0 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ------=_Part_607912_1348384026.1393690147569 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit > Without incorporating additional meta-data about the purchases onto > the > customer table the most basic solution would be: > SELECT DISTINCT customer_id FROM products WHERE date > (now() - '12 > months'::interval) > EXCEPT > SELECT DISTINCT customer_id FROM products WHERE date <= (now() - '12 > months'::interval) > --- > Another solution: > WHERE ... >12 AND NOT EXISTS (SELECT ... WHERE <= 12) > David J. subsidiary matter: in what circumstances is better to use EXCEPT and in what NOT EXISTS? are those equivalents? tried to google their comparison, but no relevant results found for PostgreSQL ------=_Part_607912_1348384026.1393690147569 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable <= div style=3D'font-family: times new roman,new york,times,serif; font-size: = 12pt; color: #000000'>
Without incorporating additional meta-data about the purchases o= nto the
customer table the most basic solution would be:

SELECT D= ISTINCT customer_id FROM products WHERE date > (now() - '12
months'::= interval)
EXCEPT
SELECT DISTINCT customer_id FROM products WHERE date= <=3D (now() - '12
months'::interval)

---

Another solut= ion:
WHERE ... >12 AND NOT EXISTS (SELECT ... WHERE <=3D 12)
David J.
subsidiary matter: in what circumstances is bett= er to use EXCEPT and in what NOT EXISTS?

are those equiv= alents? tried to google their comparison, but no relevant results found for= PostgreSQL
------=_Part_607912_1348384026.1393690147569--