X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 0718D9FB28B for ; Wed, 12 Jul 2006 16:38:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 56868-06 for ; Wed, 12 Jul 2006 16:37:52 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.188]) by postgresql.org (Postfix) with ESMTP id 009559FA4A8 for ; Wed, 12 Jul 2006 16:37:51 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id p46so276177nfa for ; Wed, 12 Jul 2006 12:37:50 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=XKmOxN1iFzVKm97UgIssY3mIvQ1zyVdg6bR3PUyJcMlJ+UfZGrsZKT1LfEqxjt6Zbk/24l4e+bxlRfHyE9VyQou+n1RbZHajvi/MuXnfXX70fKByCuHvhuUEPbqW4Sqkxy+Y0MDb+2laRCYQpJhf+tN8LIncroi9vmsVdyXKoLs= Received: by 10.78.151.15 with SMTP id y15mr34640hud; Wed, 12 Jul 2006 12:37:50 -0700 (PDT) Received: by 10.78.29.15 with HTTP; Wed, 12 Jul 2006 12:37:50 -0700 (PDT) Message-ID: Date: Wed, 12 Jul 2006 14:37:50 -0500 From: "Aaron Bono" To: "Vinnie Ma" , pgsql-sql@postgresql.org Subject: Re: SQL (Venn diagram type of logic) In-Reply-To: <20060711183321.93253.qmail@web52005.mail.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_8203_8366842.1152733070404" References: <20060711183321.93253.qmail@web52005.mail.yahoo.com> X-Google-Sender-Auth: 53dbd235d9a62ef2 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/195 X-Sequence-Number: 25769 ------=_Part_8203_8366842.1152733070404 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/11/06, Vinnie Ma wrote: > > Hello Aaron, > > Thank you for the help. My apologies for the direct > email but i couldn't figure out how to reply to the > thread on the postresql site. Just doing a reply to all should send it to the mailing list but the from address must match the email address you registered on the list. Once i get the purchases seperated by categories, I > plan to track the month on month status of each > account. > > For example, in may 2006, based on her previous > purchaes, customer1 was in category A but in june > 2006, she bought something in category B. This would > make her in bucket 2. > > bucket 1: A > bucket 2: A&B > bucket 3: A&C > bucket 4: B > bucket 5: B&C > bucket 6: C > bucket 7: A&B&C > > (these are the combinations i was thinking about when > i had the venn diagram in my head.) > > To follow with the example above: > > In May, she would be counted as part of bucket 1 and > her total spending (life to may) will be part of > running total for bucket 1 > > In June, she would be counted as part of bucket 2 and > her total spending (life to june) will be part of > running total for bucket 2 > > In the end, we will have count(customers), > sum(all_purchases) for each of the 7 buckets for each > month. From a business perpective, we hope this > exercise will show the general migration patterns of > customers from one bucket to another over time. > > I figured out a way to do it but it is no where near > efficient. > > i added three boolean fields to the account table to > indicate catA, catB, and catC. i update the table 3 > times for each of the categories, turning on the > boolean fields where applicable. > > then i select the count, and sum fields from an inner > join on account and purchase tables for each of the 7 > buckets. it works, but i would manually do it for > each month. > > any thoughts would be most appreciated. thanks and > please have a great day. What I have done for situations like this is to first create a report schema. Then I have a daily process run that updates reporting tables every night. These reporting tables keep full history and we only update the latest information. The main reason for doing it this way is to: 1. Remove the need to worry about performance of the query to build the report - it is done once a day during low activity on the server 2. Segment permissions so people have rights to run reports but not dig through the main database (the tech savy analysts tend to write bad queries and run them on production) 3. Keep history without having to run the report on everything (my refreshes update this month and last month - this provides enough overlap that I don't have to worry about changing months, leap year, etc.) Hope this helps! ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com ================================================================== ------=_Part_8203_8366842.1152733070404 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/11/06, Vinnie Ma <nyubambam@yahoo.com> wrote:
Hello Aaron,

Thank you for the help.  My apologies for the direct
email but i couldn't figure out how to reply to the
thread on the postresql site.

 Just doing a reply to all should send it to the mailing list but the from address must match the email address you registered on the list.

Once i get the purchases seperated by categories, I
plan to track the month on month status of each
account.

For example, in may 2006, based on her previous
purchaes, customer1 was in category A but in june
2006, she bought something in category B.  This would
make her in bucket 2.

bucket 1: A
bucket 2: A&B
bucket 3: A&C
bucket 4: B
bucket 5: B&C
bucket 6: C
bucket 7: A&B&C

(these are the combinations i was thinking about when
i had the venn diagram in my head.)

To follow with the example above:

In May, she would be counted as part of bucket 1 and
her total spending (life to may) will be part of
running total for bucket 1

In June,  she would be counted as part of bucket 2 and
her total spending (life to june) will be part of
running total for bucket 2

In the end, we will have count(customers),
sum(all_purchases) for each of the 7 buckets for each
month.  From a business perpective, we hope this
exercise will show the general migration patterns of
customers from one bucket to another over time.

I figured out a way to do it but it is no where near
efficient.

i added three boolean fields to the account table to
indicate catA, catB, and catC.  i update the table 3
times for each of the categories, turning on the
boolean fields where applicable.

then i select the count, and sum fields from an inner
join on account and purchase tables for each of the 7
buckets.  it works, but i would manually do it for
each month.

any thoughts would be most appreciated.  thanks and
please have a great day.

What I have done for situations like this is to first create a report schema.  Then I have a daily process run that updates reporting tables every night.  These reporting tables keep full history and we only update the latest information.

The main reason for doing it this way is to:
  1. Remove the need to worry about performance of the query to build the report - it is done once a day during low activity on the server
  2. Segment permissions so people have rights to run reports but not dig through the main database (the tech savy analysts tend to write bad queries and run them on production)
  3. Keep history without having to run the report on everything (my refreshes update this month and last month - this provides enough overlap that I don't have to worry about changing months, leap year, etc.)

Hope this helps!

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
================================================================== ------=_Part_8203_8366842.1152733070404--