From: Dan Langille <dan@langille.org>
To: pgsql-sql@postgresql.org
Cc: dan@langille.org
Subject: people who buy A, also buy C, D, E
Date: Mon, 25 Apr 2005 22:21:35 -0400
Message-ID: <426D6D6F.7549.B12D9FC@localhost> (raw)
The goal of my query is: given a book, what did other people who
bought this book also buy? I plan the list the 5 most popular such
books. In reality, this isn't about books, but that makes it easier
to understand I think.
We have a table of customer_id (watch_list_id) and book_id
(element_id).
freshports.org=# \d watch_list_element
Table "public.watch_list_element"
Column | Type | Modifiers
---------------+---------+-----------
watch_list_id | integer | not null
element_id | integer | not null
Indexes:
"watch_list_element_pkey" primary key, btree (watch_list_id,
element_id)
"watch_list_element_element_id" btree (element_id)
Foreign-key constraints:
"$2" FOREIGN KEY (watch_list_id) REFERENCES watch_list(id) ON
UPDATE CASCADE ON DELETE CASCADE
"$1" FOREIGN KEY (element_id) REFERENCES element(id) ON UPDATE
CASCADE ON DELETE CASCADE
freshports.org=#
I have a query which returns the needed results:
SELECT W.element_id
FROM watch_list_element W
WHERE w.watch_list_id in (select watch_list_id from
watch_list_element where element_id = 54968)
GROUP BY W.element_id
ORDER BY count(W.watch_list_id) DESC
LIMIT 5;
But performance is an issue here. So I'm planning to calculate all
the possible values and cache them. That is, given each element_id in
a watch_list, what are the top 5 element_id values on all the lists
on which the original element_id appears?
I'm having trouble constructing the query. I'm not even sure I can
do this in one select, but that would be nice. Examples and clues
are appreciated.
Any ideas?
Thank you.
--
Dan Langille : http://www.langille.org/
BSDCan - The Technical BSD Conference - http://www.bsdcan.org/
NEW brochure available at http://www.bsdcan.org/2005/advocacy/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dan@langille.org
Subject: Re: people who buy A, also buy C, D, E
In-Reply-To: <426D6D6F.7549.B12D9FC@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox