From ostrochovsky@mobiletech.sk Thu Feb 27 14:20:24 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJ1p2-0005a6-8c for pgsql-sql@arkaria.postgresql.org; Thu, 27 Feb 2014 14:20:24 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WJ1p1-0003lt-MF for pgsql-sql@arkaria.postgresql.org; Thu, 27 Feb 2014 14:20:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJ1oz-0003k1-Uy for pgsql-sql@postgresql.org; Thu, 27 Feb 2014 14:20:22 +0000 Received: from zimbra.isdd.sk ([91.233.248.204]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJ1ox-0006vy-S5 for pgsql-sql@postgresql.org; Thu, 27 Feb 2014 14:20:21 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by zimbra.isdd.sk (Postfix) with ESMTP id 9F8AEAC8767 for ; Thu, 27 Feb 2014 15:20:18 +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 shEwPyNazhwd for ; Thu, 27 Feb 2014 15:20:18 +0100 (CET) Received: from zimbra.isdd.sk (zimbra.isdd.sk [91.233.248.204]) by zimbra.isdd.sk (Postfix) with ESMTP id 5ECA2AC8756 for ; Thu, 27 Feb 2014 15:20:18 +0100 (CET) Date: Thu, 27 Feb 2014 15:20:18 +0100 (CET) From: Jan Ostrochovsky To: pgsql-sql@postgresql.org Message-ID: <1001132708.567696.1393510818245.JavaMail.root@mobiletech.sk> In-Reply-To: <883409020.566998.1393509966607.JavaMail.root@mobiletech.sk> Subject: how to effectively SELECT new "customers" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_567695_231456775.1393510818244" 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.5 (/) 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_567695_231456775.1393510818244 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit Hello, I am solving following task and it seems hard to me to find effective solution. Maybe somebody knows how to help me: We have table "purchases" and each one record is identified by "customer_id". We want to know not only how many different customers did at least one purchase per time period, grouped by time periods (easy task: "COUNT(DISTINCT customer_id)" with "GROUP BY period"), but also to know how many NEW customers there were. We define new customer as customer_id, which had first record in table "purchases" after 12 month of inactivity (no record in table "purchases" previous 12 months). I have found one solution, but it is very slow and ugly. I tried several other concepts, but without success. Any hint could be helpful for me. Thanks in advance! Jano ------=_Part_567695_231456775.1393510818244 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'>
Hello,
I am solving following task and=
 it seems hard to me to find effective solution. Maybe somebody knows how t=
o help me:
We have table "purchases" and each one record is ident=
ified by "customer_id".
We want to know not only how many differe=
nt customers did at least one purchase per time period, grouped by time per=
iods
(easy task: "COUNT(DISTINCT customer_id)" with "GROUP BY per=
iod"), but also to know how many NEW customers there were.
We def=
ine new customer as customer_id, which had first record in table "purchases=
" after 12 month of inactivity (no record
in table "purchases" pr=
evious 12 months).
I have found one solution, but it is very slow=
 and ugly. I tried several other concepts, but without success.
A=
ny hint could be helpful for me. Thanks in advance!
Jano
------=_Part_567695_231456775.1393510818244-- From polobo@yahoo.com Thu Feb 27 15:09:40 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJ2ai-00079i-Ff for pgsql-sql@arkaria.postgresql.org; Thu, 27 Feb 2014 15:09:40 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WJ2ah-0001cn-Pi for pgsql-sql@arkaria.postgresql.org; Thu, 27 Feb 2014 15:09:39 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJ2ah-0001ch-1D for pgsql-sql@postgresql.org; Thu, 27 Feb 2014 15:09:39 +0000 Received: from sam.nabble.com ([216.139.236.26]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJ2ae-0006xb-1F for pgsql-sql@postgresql.org; Thu, 27 Feb 2014 15:09:38 +0000 Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1WJ2ac-0005wb-CV for pgsql-sql@postgresql.org; Thu, 27 Feb 2014 07:09:34 -0800 Date: Thu, 27 Feb 2014 07:09:34 -0800 (PST) From: David Johnston To: pgsql-sql@postgresql.org Message-ID: <1393513774376-5793882.post@n5.nabble.com> In-Reply-To: <1001132708.567696.1393510818245.JavaMail.root@mobiletech.sk> References: <1001132708.567696.1393510818245.JavaMail.root@mobiletech.sk> Subject: Re: how to effectively SELECT new "customers" MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: 4.5 (++++) 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 Jan Ostrochovsky wrote > Hello, I am solving following task and it seems hard to me to find > effective solution. Maybe somebody knows how to help me: We have table > "purchases" and each one record is identified by "customer_id". We want to > know not only how many different customers did at least one purchase per > time period, grouped by time periods (easy task: "COUNT(DISTINCT > customer_id)" with "GROUP BY period"), but also to know how many NEW > customers there were. We define new customer as customer_id, which had > first record in table "purchases" after 12 month of inactivity (no record > in table "purchases" previous 12 months). I have found one solution, but > it is very slow and ugly. I tried several other concepts, but without > success. Any hint could be helpful for me. Thanks in advance! Jano 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) --- Depending on the frequency that you need to run this query it may be worthwhile to create a materialized view that captures the necessary data and then whenever a new sale is generated you simply update that view by changing the attributes of that single customer. At any point you can quickly determine, using the view, which customers were active at the time of last purchases and which ones were dormant or non-existent. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-effectively-SELECT-new-customers-tp5793867p5793882.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql From ostrochovsky@mobiletech.sk Fri Feb 28 11:44:24 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJLrc-0000Cg-7R for pgsql-sql@arkaria.postgresql.org; Fri, 28 Feb 2014 11:44:24 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WJLrb-0007Xj-FS for pgsql-sql@arkaria.postgresql.org; Fri, 28 Feb 2014 11:44:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJLra-0007Xc-EJ for pgsql-sql@postgresql.org; Fri, 28 Feb 2014 11:44:22 +0000 Received: from zimbra.isdd.sk ([91.233.248.204]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJLrS-0004N2-HF for pgsql-sql@postgresql.org; Fri, 28 Feb 2014 11:44:21 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by zimbra.isdd.sk (Postfix) with ESMTP id 583A5AC86A7; Fri, 28 Feb 2014 12:44:13 +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 5aPDS4bHeUPg; Fri, 28 Feb 2014 12:44:12 +0100 (CET) Received: from zimbra.isdd.sk (zimbra.isdd.sk [91.233.248.204]) by zimbra.isdd.sk (Postfix) with ESMTP id 84577AC865E; Fri, 28 Feb 2014 12:44:12 +0100 (CET) Date: Fri, 28 Feb 2014 12:44:12 +0100 (CET) From: Jan Ostrochovsky To: David Johnston Cc: pgsql-sql@postgresql.org Message-ID: <966514640.595308.1393587852428.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_595307_1818397471.1393587852427" 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: -1.9 (-) 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_595307_1818397471.1393587852427 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit > From: "David Johnston" > To: pgsql-sql@postgresql.org > Sent: Thursday, February 27, 2014 4:09:34 PM > Subject: Re: [SQL] how to effectively SELECT new "customers" > Jan Ostrochovsky wrote > > Hello, I am solving following task and it seems hard to me to find > > effective solution. Maybe somebody knows how to help me: We have > > table > > "purchases" and each one record is identified by "customer_id". We > > want to > > know not only how many different customers did at least one > > purchase per > > time period, grouped by time periods (easy task: "COUNT(DISTINCT > > customer_id)" with "GROUP BY period"), but also to know how many > > NEW > > customers there were. We define new customer as customer_id, which > > had > > first record in table "purchases" after 12 month of inactivity (no > > record > > in table "purchases" previous 12 months). I have found one > > solution, but > > it is very slow and ugly. I tried several other concepts, but > > without > > success. Any hint could be helpful for me. Thanks in advance! Jano > 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) > --- > Depending on the frequency that you need to run this query it may be > worthwhile to create a materialized view that captures the necessary > data > and then whenever a new sale is generated you simply update that view > by > changing the attributes of that single customer. At any point you can > quickly determine, using the view, which customers were active at the > time > of last purchases and which ones were dormant or non-existent. > David J. ------=_Part_595307_1818397471.1393587852427 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'>
From: "David Johnston" <polobo@yahoo.com= >
To: pgsql-sql@postgresql.org
Sent: Thursday, Febru= ary 27, 2014 4:09:34 PM
Subject: Re: [SQL] how to effectively SEL= ECT new "customers"

Jan Ostrochovsky wrote
> Hello, I am solvi= ng following task and it seems hard to me to find
> effective solutio= n. Maybe somebody knows how to help me: We have table
> "purchases" a= nd each one record is identified by "customer_id". We want to
> know = not only how many different customers did at least one purchase per
>= time period, grouped by time periods (easy task: "COUNT(DISTINCT
> c= ustomer_id)" with "GROUP BY period"), but also to know how many NEW
>= customers there were. We define new customer as customer_id, which had
= > first record in table "purchases" after 12 month of inactivity (no rec= ord
> in table "purchases" previous 12 months). I have found one solu= tion, but
> it is very slow and ugly. I tried several other concepts,= but without
> success. Any hint could be helpful for me. Thanks in a= dvance! Jano

Without incorporating additional meta-data about the pu= rchases 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 W= HERE date <=3D (now() - '12
months'::interval)

---

Anot= her solution:
WHERE ... >12 AND NOT EXISTS (SELECT ... WHERE <=3D = 12)

---

Depending on the frequency that you need to run this = query it may be
worthwhile to create a materialized view that captures t= he necessary data
and then whenever a new sale is generated you simply u= pdate that view by
changing the attributes of that single customer. &nbs= p;At any point you can
quickly determine, using the view, which customer= s were active at the time
of last purchases and which ones were dormant = or non-existent.

David J.

------=_Part_595307_1818397471.1393587852427-- From ostrochovsky@mobiletech.sk Fri Feb 28 11:45:47 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJLsx-0000IC-CO for pgsql-sql@arkaria.postgresql.org; Fri, 28 Feb 2014 11:45:47 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WJLsw-0000G7-Sq for pgsql-sql@arkaria.postgresql.org; Fri, 28 Feb 2014 11:45:46 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJLsw-0000G0-2x for pgsql-sql@postgresql.org; Fri, 28 Feb 2014 11:45:46 +0000 Received: from zimbra.isdd.sk ([91.233.248.204]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJLsf-0003YG-4V for pgsql-sql@postgresql.org; Fri, 28 Feb 2014 11:45:45 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by zimbra.isdd.sk (Postfix) with ESMTP id 4BE7EAC86A7; Fri, 28 Feb 2014 12:45:27 +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 1EDpvU8MoJwY; Fri, 28 Feb 2014 12:45:26 +0100 (CET) Received: from zimbra.isdd.sk (zimbra.isdd.sk [91.233.248.204]) by zimbra.isdd.sk (Postfix) with ESMTP id D85D9AC865E; Fri, 28 Feb 2014 12:45:26 +0100 (CET) Date: Fri, 28 Feb 2014 12:45:26 +0100 (CET) From: Jan Ostrochovsky To: David Johnston Cc: pgsql-sql@postgresql.org Message-ID: <976203811.595346.1393587926835.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_595345_143802898.1393587926834" 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: -1.9 (-) 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_595345_143802898.1393587926834 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit ----- Original Message ----- > From: "David Johnston" > To: pgsql-sql@postgresql.org > Sent: Thursday, February 27, 2014 4:09:34 PM > Subject: Re: [SQL] how to effectively SELECT new "customers" > Jan Ostrochovsky wrote > > Hello, I am solving following task and it seems hard to me to find > > effective solution. Maybe somebody knows how to help me: We have > > table > > "purchases" and each one record is identified by "customer_id". We > > want to > > know not only how many different customers did at least one > > purchase per > > time period, grouped by time periods (easy task: "COUNT(DISTINCT > > customer_id)" with "GROUP BY period"), but also to know how many > > NEW > > customers there were. We define new customer as customer_id, which > > had > > first record in table "purchases" after 12 month of inactivity (no > > record > > in table "purchases" previous 12 months). I have found one > > solution, but > > it is very slow and ugly. I tried several other concepts, but > > without > > success. Any hint could be helpful for me. Thanks in advance! Jano > 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) > --- > Depending on the frequency that you need to run this query it may be > worthwhile to create a materialized view that captures the necessary > data > and then whenever a new sale is generated you simply update that view > by > changing the attributes of that single customer. At any point you can > quickly determine, using the view, which customers were active at the > time > of last purchases and which ones were dormant or non-existent. > David J. Thank you David, your alternatives seem as smart alternatives of my solution, what is based on the difference between COUNT(DISTINCT customer_id) in e.g. 13 months (in case when reporting period is 1 month over previous 12 months) and COUNT(DISTINCT customer_id) in 12 months prior to reporting period (but without the reporting period). But I would also like to incorporate other data in the output, which should seem e.g.: town, period, payment_channel_id, purchases_count, purchases_total_revenue, distinct_customer_count, distinct_NEW_customer_count BA, 201401, A, 495, 847, 103, 25 BA, 201401, B, 345, 456, 99, 21 BA, 201312, A, 554, 1021, 105, 30 ZA, 201401, A, 323, 987, 95, 23 ... there is 12-month-long time-window for new customer determination, sliding according to particular period, back to the past how to achieve this? e.g. by running your or my concept repeatedly (manually or in the higher level loop), but this is too slow... or to find more sophisticated SQL query to accomplish this in one step... any other ideas for such SQL query running once, not repeatedly (in loop) for each reporting period? (I was considering window functions, connectby, grouping sets, ..., but no success yet) materialized view could be part of that solution, when we will have the right query and uprgade from 9.0 to 9.3 (as I see it: http://www.postgresql.org/docs/9.3/static/rules-materializedviews.html)... but it should be scalable to several to tens purchases every minute thanks again for your contribution, David Jano ------=_Part_595345_143802898.1393587926834 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'>
From: "David Johnston" <polobo@yahoo.com&g= t;
To: pgsql-sql@postgresql.org
Sent: Thursday, Februar= y 27, 2014 4:09:34 PM
Subject: Re: [SQL] how to effectively SELEC= T new "customers"

Jan Ostrochovsky wrote
> Hello, I am solving= following task and it seems hard to me to find
> effective solution.= Maybe somebody knows how to help me: We have table
> "purchases" and= each one record is identified by "customer_id". We want to
> know no= t only how many different customers did at least one purchase per
> t= ime period, grouped by time periods (easy task: "COUNT(DISTINCT
> cus= tomer_id)" with "GROUP BY period"), but also to know how many NEW
> c= ustomers there were. We define new customer as customer_id, which had
&g= t; first record in table "purchases" after 12 month of inactivity (no recor= d
> in table "purchases" previous 12 months). I have found one soluti= on, but
> it is very slow and ugly. I tried several other concepts, b= ut without
> success. Any hint could be helpful for me. Thanks in adv= ance! Jano

Without incorporating additional meta-data about the purc= hases onto the
customer table the most basic solution would be:

S= ELECT DISTINCT customer_id FROM products WHERE date > (now() - '12
mo= nths'::interval)
EXCEPT
SELECT DISTINCT customer_id FROM products WHE= RE date <=3D (now() - '12
months'::interval)

---

Anothe= r solution:
WHERE ... >12 AND NOT EXISTS (SELECT ... WHERE <=3D 12= )

---

Depending on the frequency that you need to run this qu= ery it may be
worthwhile to create a materialized view that captures the= necessary data
and then whenever a new sale is generated you simply upd= ate that view by
changing the attributes of that single customer.  = At any point you can
quickly determine, using the view, which customers = were active at the time
of last purchases and which ones were dormant or= non-existent.

David J.
Thank you David, your altern= atives seem as smart alternatives of my solution, what is based on the diff= erence between COUNT(DISTINCT customer_id) in e.g. 13 months (in case when = reporting period is 1 month over previous 12 months) and COUNT(DISTINCT cus= tomer_id) in 12 months prior to reporting period (but without the reporting= period). But I would also like to incorporate other data in the output, wh= ich should seem e.g.:

town, period, payment_channel_id, = purchases_count, purchases_total_revenue, distinct_customer_count, distinct= _NEW_customer_count
BA, 201401, A, 495, 847, 103, 25
BA= , 201401, B, 345, 456, 99, 21
BA, 201312, A, 554, 1021, 105, 30
ZA, 201401, A, 323, 987, 95, 23
...

there is 12-m= onth-long time-window for new customer determination, sliding according to = particular period, back to the past

how to achieve= this? e.g. by running your or my concept repeatedly (manually or in the hi= gher level loop), but this is too slow... or to find more sophisticated SQL= query to accomplish this in one step... any other ideas for such SQL query= running once, not repeatedly (in loop) for each reporting period? (I was c= onsidering window functions, connectby, grouping sets, ..., but no success = yet)

materialized view could be part of that solut= ion, when we will have the right query and uprgade from 9.0 to 9.3 (as I se= e it: http://www.postgresql.org/docs/9.3/static/rules-materializedview= s.html)... but it should be scalable to several to tens purchases every min= ute

thanks again for your contribution, David

Jano

------=_Part_595345_143802898.1393587926834-- From polobo@yahoo.com Fri Feb 28 15:39:45 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJPXM-0007h7-Sr for pgsql-sql@arkaria.postgresql.org; Fri, 28 Feb 2014 15:39:45 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WJPXM-0001Iw-CD for pgsql-sql@arkaria.postgresql.org; Fri, 28 Feb 2014 15:39:44 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJPXK-0001HD-N5 for pgsql-sql@postgresql.org; Fri, 28 Feb 2014 15:39:42 +0000 Received: from sam.nabble.com ([216.139.236.26]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJPXI-0007br-ED for pgsql-sql@postgresql.org; Fri, 28 Feb 2014 15:39:42 +0000 Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1WJPXG-0001bi-W7 for pgsql-sql@postgresql.org; Fri, 28 Feb 2014 07:39:39 -0800 Date: Fri, 28 Feb 2014 07:39:38 -0800 (PST) From: David Johnston To: pgsql-sql@postgresql.org Message-ID: <1393601978985-5794056.post@n5.nabble.com> In-Reply-To: <976203811.595346.1393587926835.JavaMail.root@mobiletech.sk> References: <1001132708.567696.1393510818245.JavaMail.root@mobiletech.sk> <1393513774376-5793882.post@n5.nabble.com> <976203811.595346.1393587926835.JavaMail.root@mobiletech.sk> Subject: Re: how to effectively SELECT new "customers" MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: 4.5 (++++) 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 Jan Ostrochovsky wrote > materialized view could be part of that solution, when we will have the > right query and uprgade from 9.0 to 9.3 (as I see it: > http://www.postgresql.org/docs/9.3/static/rules-materializedviews.html)... 9.3 adds materialized views syntax and functionality directly into PostgreSQL but you can "roll your own" in any version and that is what I would suggest. I would probably focus on getting a single reporting period to execute performantly and just use a loop to build up the materialized view period-by-period. I don't know how you want to go about dealing with your payment channel since it depends on whether a customer can make use of more than one and whether their "new-ness" is impacted by such. Incorporating other data is as simple as building the different pieces and joining them together into a final output; usually through a series of CTEs/WITH sub-queries. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-effectively-SELECT-new-customers-tp5793867p5794056.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql From ostrochovsky@mobiletech.sk Fri Feb 28 16:12:30 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJQ34-0000RM-66 for pgsql-sql@arkaria.postgresql.org; Fri, 28 Feb 2014 16:12:30 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WJQ33-0005QC-Lc for pgsql-sql@arkaria.postgresql.org; Fri, 28 Feb 2014 16:12:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJQ32-0005Q5-KC for pgsql-sql@postgresql.org; Fri, 28 Feb 2014 16:12:28 +0000 Received: from zimbra.isdd.sk ([91.233.248.204]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJQ2v-0000gb-PC for pgsql-sql@postgresql.org; Fri, 28 Feb 2014 16:12:28 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by zimbra.isdd.sk (Postfix) with ESMTP id 5D80FAC86A7; Fri, 28 Feb 2014 17:12:21 +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 zFezchMGIVeF; Fri, 28 Feb 2014 17:12:20 +0100 (CET) Received: from zimbra.isdd.sk (zimbra.isdd.sk [91.233.248.204]) by zimbra.isdd.sk (Postfix) with ESMTP id 35E77AC860F; Fri, 28 Feb 2014 17:12:20 +0100 (CET) Date: Fri, 28 Feb 2014 17:12:20 +0100 (CET) From: Jan Ostrochovsky To: David Johnston Cc: pgsql-sql@postgresql.org Message-ID: <1083597622.602688.1393603940121.JavaMail.root@mobiletech.sk> In-Reply-To: <1393601978985-5794056.post@n5.nabble.com> Subject: Re: how to effectively SELECT new "customers" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_602687_1418346646.1393603940120" 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.8 (/) 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_602687_1418346646.1393603940120 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit ----- Original Message ----- > From: "David Johnston" > 9.3 adds materialized views syntax and functionality directly into > PostgreSQL but you can "roll your own" in any version and that is > what I > would suggest. > I would probably focus on getting a single reporting period to > execute > performantly and just use a loop to build up the materialized view > period-by-period. > I don't know how you want to go about dealing with your payment > channel > since it depends on whether a customer can make use of more than one > and > whether their "new-ness" is impacted by such. > Incorporating other data is as simple as building the different > pieces and > joining them together into a final output; usually through a series > of > CTEs/WITH sub-queries. > David J. > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/how-to-effectively-SELECT-new-customers-tp5793867p5794056.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql customer may have various payment channels during the time... new-ness is not impacted by the channel, it does not matter from which channel, all customer_id occurences count (in determined filtration criteria, e.g. town, service, subservice) and there are also other filtration and grouping criteria (town, service, subservice) and user of reporting tool should have possibility to select from those... there are dozens of services and subservices, cca 4 payment channels, dozens of towns... therefore preprocessing through materialized view (if I understand your suggestion correctly), would contain a lot of combinations, it seems quite complex for me in these circumstances I also considered WITH (CTEs) previously, I will rethink it yet, after these your recommendations thanks ------=_Part_602687_1418346646.1393603940120 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'>
From: "David Johnston" <polo= bo@yahoo.com>

9.3 adds materialized views syntax and functionalit= y directly into
PostgreSQL but you can "roll your own" in any version an= d that is what I
would suggest.

I would probably focus on getting= a single reporting period to execute
performantly and just use a loop t= o build up the materialized view
period-by-period.

I don't know h= ow you want to go about dealing with your payment channel
since it depen= ds on whether a customer can make use of more than one and
whether their= "new-ness" is impacted by such.

Incorporating other data is as simp= le as building the different pieces and
joining them together into a fin= al output; usually through a series of
CTEs/WITH sub-queries.

Dav= id J.





--
View this message in context: http://pos= tgresql.1045698.n5.nabble.com/how-to-effectively-SELECT-new-customers-tp579= 3867p5794056.html
Sent from the PostgreSQL - sql mailing list archive at= Nabble.com.


--
Sent via pgsql-sql mailing list (pgsql-sql@p= ostgresql.org)
To make changes to your subscription:
http://www.postg= resql.org/mailpref/pgsql-sql
customer may have various paym= ent channels during the time... new-ness is not impacted by the channel, it= does not matter from which channel, all customer_id occurences count (in d= etermined filtration criteria, e.g. town, service, subservice)

and there are also other filtration and grouping criteria (town, ser= vice, subservice) and user of reporting tool should have possibility to sel= ect from those... there are dozens of services and subservices, cca 4 payme= nt channels, dozens of towns... therefore preprocessing through materialize= d view (if I understand your suggestion correctly), would contain a lot of = combinations, it seems quite complex for me in these circumstances

I also considered WITH (CTEs) previously, I will rethink i= t yet, after these your recommendations

thanks
------=_Part_602687_1418346646.1393603940120-- From polobo@yahoo.com Fri Feb 28 16:33:15 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJQN9-00017l-Fk for pgsql-sql@arkaria.postgresql.org; Fri, 28 Feb 2014 16:33:15 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WJQN9-000391-04 for pgsql-sql@arkaria.postgresql.org; Fri, 28 Feb 2014 16:33:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJQN8-00038u-Bg for pgsql-sql@postgresql.org; Fri, 28 Feb 2014 16:33:14 +0000 Received: from sam.nabble.com ([216.139.236.26]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJQN5-00012d-DI for pgsql-sql@postgresql.org; Fri, 28 Feb 2014 16:33:14 +0000 Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1WJQN3-0003PK-6R for pgsql-sql@postgresql.org; Fri, 28 Feb 2014 08:33:09 -0800 Date: Fri, 28 Feb 2014 08:33:09 -0800 (PST) From: David Johnston To: pgsql-sql@postgresql.org Message-ID: <1393605189189-5794066.post@n5.nabble.com> In-Reply-To: <1083597622.602688.1393603940121.JavaMail.root@mobiletech.sk> References: <1001132708.567696.1393510818245.JavaMail.root@mobiletech.sk> <1393513774376-5793882.post@n5.nabble.com> <976203811.595346.1393587926835.JavaMail.root@mobiletech.sk> <1393601978985-5794056.post@n5.nabble.com> <1083597622.602688.1393603940121.JavaMail.root@mobiletech.sk> Subject: Re: how to effectively SELECT new "customers" MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: 1.8 (+) 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 Jan Ostrochovsky wrote > customer may have various payment channels during the time... new-ness is > not impacted by the channel, it does not matter from which channel, all > customer_id occurences count (in determined filtration criteria, e.g. > town, service, subservice) > > and there are also other filtration and grouping criteria (town, service, > subservice) and user of reporting tool should have possibility to select > from those... there are dozens of services and subservices, cca 4 payment > channels, dozens of towns... therefore preprocessing through materialized > view (if I understand your suggestion correctly), would contain a lot of > combinations, it seems quite complex for me in these circumstances > > I also considered WITH (CTEs) previously, I will rethink it yet, after > these your recommendations I'm not sure what you are going for since you keep adding additional criteria/constraints to your problem. At this point you are faced with a series of trade-offs between caching, speed, and flexibilty, complexity. I would suggest you break up your requirements into smaller pieces and not go looking for some kind of magic bullet that will solve all your problems in a single query. It likely does not exist. I would also suggest that you look into resources on data warehousing and the star schema; doing what you are trying directly within the OLTP is probably not the best solution - especially not on front-end servers. My experience in this area is thin but maybe someone else can make some suggestions and/or provide some useful resource links. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-effectively-SELECT-new-customers-tp5793867p5794066.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql From ostrochovsky@mobiletech.sk Sat Mar 1 14:24:56 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJkqW-0006YK-Cs for pgsql-sql@arkaria.postgresql.org; Sat, 01 Mar 2014 14:24:56 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WJkqV-0006yP-Pc for pgsql-sql@arkaria.postgresql.org; Sat, 01 Mar 2014 14:24:55 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJkqU-0006yI-MC for pgsql-sql@postgresql.org; Sat, 01 Mar 2014 14:24:54 +0000 Received: from zimbra.isdd.sk ([91.233.248.204]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJkqR-0006hz-8i for pgsql-sql@postgresql.org; Sat, 01 Mar 2014 14:24:54 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by zimbra.isdd.sk (Postfix) with ESMTP id B74B8AC86A7; Sat, 1 Mar 2014 15:24:48 +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 UjB6TfFZjT9C; Sat, 1 Mar 2014 15:24:48 +0100 (CET) Received: from zimbra.isdd.sk (zimbra.isdd.sk [91.233.248.204]) by zimbra.isdd.sk (Postfix) with ESMTP id 24DACAC85F8; Sat, 1 Mar 2014 15:24:48 +0100 (CET) Date: Sat, 1 Mar 2014 15:24:47 +0100 (CET) From: Jan Ostrochovsky To: David Johnston Cc: pgsql-sql@postgresql.org Message-ID: <893390760.607836.1393683887897.JavaMail.root@mobiletech.sk> In-Reply-To: <1393605189189-5794066.post@n5.nabble.com> Subject: Re: how to effectively SELECT new "customers" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_607835_1014827662.1393683887896" 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_607835_1014827662.1393683887896 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit > I'm not sure what you are going for since you keep adding additional > criteria/constraints to your problem. At this point you are faced > with a > series of trade-offs between caching, speed, and flexibilty, > complexity. I > would suggest you break up your requirements into smaller pieces and > not go > looking for some kind of magic bullet that will solve all your > problems in a > single query. It likely does not exist. > I would also suggest that you look into resources on data warehousing > and > the star schema; doing what you are trying directly within the OLTP > is > probably not the best solution - especially not on front-end servers. > My > experience in this area is thin but maybe someone else can make some > suggestions and/or provide some useful resource links. > David J. Probably you are right. It seems, that there is no such single-query solution, just only with PostgreSQL. I will break it up into smaller pieces. There is medium-tern plan for data-warehousing, just this one task I will solve yet without it. Maybe this is the way to go then: http://www.jaspersoft.com/tour. Thanks. Jano ------=_Part_607835_1014827662.1393683887896 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'>
I'm not sure what you are going for since you keep adding additional<= br>criteria/constraints to your problem.  At this point you are faced = with a
series of trade-offs between caching, speed, and flexibilty, comp= lexity.  I
would suggest you break up your requirements into smalle= r pieces and not go
looking for some kind of magic bullet that will solv= e all your problems in a
single query.  It likely does not exist.
I would also suggest that you look into resources on data warehousing= and
the star schema; doing what you are trying directly within the OLTP= is
probably not the best solution - especially not on front-end servers= .  My
experience in this area is thin but maybe someone else can ma= ke some
suggestions and/or provide some useful resource links.

Da= vid J.
Probably you are right. It seems, that there is no s= uch single-query solution, just only with PostgreSQL. I will break it up in= to smaller pieces. There is medium-tern plan for data-warehousing, just thi= s one task I will solve yet without it. Maybe this is the way to go then:&n= bsp;http://www.jaspersoft.com/tour.

Thanks.
Jano
------=_Part_607835_1014827662.1393683887896-- From ostrochovsky@mobiletech.sk Sat Mar 1 16:09:14 2014 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-- From polobo@yahoo.com Sun Mar 2 04:08:45 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJxhl-0006UY-6C for pgsql-sql@arkaria.postgresql.org; Sun, 02 Mar 2014 04:08:45 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WJxhk-0004WX-IO for pgsql-sql@arkaria.postgresql.org; Sun, 02 Mar 2014 04:08:44 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJxhj-0004WR-SE for pgsql-sql@postgresql.org; Sun, 02 Mar 2014 04:08:44 +0000 Received: from sam.nabble.com ([216.139.236.26]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJxhg-00049W-Dr for pgsql-sql@postgresql.org; Sun, 02 Mar 2014 04:08:43 +0000 Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1WJxhf-0007DP-Gj for pgsql-sql@postgresql.org; Sat, 01 Mar 2014 20:08:39 -0800 Date: Sat, 1 Mar 2014 20:08:39 -0800 (PST) From: David Johnston To: pgsql-sql@postgresql.org Message-ID: <1393733319509-5794267.post@n5.nabble.com> In-Reply-To: <2042881838.607913.1393690147570.JavaMail.root@mobiletech.sk> References: <1001132708.567696.1393510818245.JavaMail.root@mobiletech.sk> <1393513774376-5793882.post@n5.nabble.com> <2042881838.607913.1393690147570.JavaMail.root@mobiletech.sk> Subject: Re: how to effectively SELECT new "customers" MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: 4.5 (++++) 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 Jan Ostrochovsky wrote >> 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 I don't know; it somewhat depends on how smart the planner is which is out of my league. I would expect that NOT EXISTS is typically a better first option since EXCEPT needs to do sorting and de-duplicating (maybe?) of large amounts of data while the NOT EXISTS method seems to require some level of nested looping to process but only needs to find a single matching record to return false so less memory constraints. Someone more familiar with the internals may be able to give a more detailed answer from the top of their head. But in a critical (or under-performing) piece of code you should probably test both to see in your reality which one perform better as I would guess hardware is going to have an impact. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-effectively-SELECT-new-customers-tp5793867p5794267.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql