Andmekaevandamine Amazoni Raamatud SQL-iga
The SELECT sentence in the given function operates the following way
1) it selects all IDs of people who have purchased book Y
2) it performs inner join of IDs selected in the first step with selection of IDs of people who have
purchased book X
3) it counts the number of distinct IDs in the resulting set, thusly solving the problem
CREATE OR REPLACE FUNCTION joint_buyers_text(integer, integer)
RETURNS text AS
$BODY$
DECLARE
bookX ALIAS FOR $1;
bookY ALIAS FOR $2;
count integer;
BEGIN
SELECT INTO count COUNT(DISTINCT person_id)
FROM
(
SELECT p.person_id FROM person_book AS p
INNER JOIN (SELECT person_id FROM person_book where book_id=bookY)
AS b
ON p.person_id=b.person_id WHERE p.book_id=bookX
) al;
RETURN "Books "||bookX||"and"||bookY||" have been purchased together
by"||count||" people";
END;
$BODY$
annaabi.ee 
