Andmekaevandamine Amazoni Raamatud SQL-iga (0)

TALLINN  UNIVERSITY  OF  TECHNOLOGY
Institute of Informatics
Department of Informatics
Chair of Foundations of Informatics
Similarity
Nth Assignment for Data Mining   Course
IDN0100
Student :
Matriculation ID:
E-mail:
Supervisor:  Innar  Liiv
TALLINN
2013
Preparation :
This  task  report describes completion of the assignment of  finding  customer  behavior  similarities via
creation  of database entries and execution of SQL queries.  First  off a PostgreSQL 9.2.2 has been
installed and a table has been created to accommodate the data from data.txt file.
-- DROP TABLE person_book;
CREATE TABLE person_book
id  serial  NOT NULL,
person_id  integer  NOT NULL,
book_id integer NOT NULL,
CONSTRAINT person_book_pkey  PRIMARY  KEY (id)
CREATE INDEX idx_book_id
ON person_book
USING btree
(book_id);
Then, the data was imported into the table. The  following  command  works  when database actually
runs on the  machine  the data file is  located  on (or  something  like this…  Sure  as  hell  the  processing
engine  couldn’t get to the file when database with the table was located on a ttu  server  and the
data.txt file on my PC).
COPY person_book(person_id, book_id) FROM 'C:\data.txt' DELIMITERS ','
CSV HEADER;
Task 1:
In  order  to determine the number of people who have  purchased   both  book X and book Y a  function
has been written in PLPGSQL. 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$
LANGUAGE plpgsql VOLATILE  SECURITY  DEFINER
Example function  call  to test the function:
SELECT joint_buyers_text(0, 9) as  answer
Result  as presented in pgAdmin III:
Task 2:
In order to simplify the SQL query  required  for completion of the task a view has been created to  store
the list of IDs of all distinct books mentioned in data.txt
CREATE OR REPLACE VIEW unique_books AS
SELECT distinct book_id from person_book
Also, a  slightly  altered  version  of the function presented  above  has been created as it is does the
counting  required for the final  output of the task. In the function presented  below  the return type has
been altered to integer and text portion of the return  statement  eliminated.
CREATE OR REPLACE FUNCTION joint_buyers(integer,integer)
RETURNS integer
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 count;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
For the sake of pro- activity  the  results  of this task’s resolution are saved in a table created specifically
for the  purpose  of simplifying and speeding-up the  attainment  of results for the third task of this
assignment.
CREATE TABLE joint_purchase_stat
stats_id serial NOT NULL,
bookx integer NOT NULL,
booky integer NOT NULL,
joint_buys integer NOT NULL,
CONSTRAINT joint_purchase_stat_pkey PRIMARY KEY (stats_id)
Then the above table  gets  populated with the result of a  complex  and a  demanding  select query (it has
taken over 10(!)  hours  to run on my PC as the  amount  of data to  process  was huge and it resulted in
creation of over 3,500,000 rows).
1)  Insert   clause  is commented out  since  it’s optional.
2) Selection of all  unique combinations of books (t1.book_id>t2.book_id clause  takes  care of
uniqueness of the combinations) that
3) Have both been purchased by at  least  one  person  by
3.1) selection of people who have purchased book X and
3.2)  testing  their IDs against the records of people who have bought book Y – if  there ’s at least one
matching ID  between  those selections, it  means  that  there indeed is a  person  who has purchased both
books and the combination can be regarded further and
4) the  overall  number of  joint  purchases  established .
--INSERT INTO joint_purchase_stat (bookX, bookY,joint_buys)
SELECT t1.book_id, t2.book_id, joint_buyers(t1.book_id, t2.book_id) AS
bought_together FROM unique_books AS t1  CROSS  JOIN unique_books AS t2
WHERE t1.book_id!=t2.book_id AND t1.book_id>t2.book_id and EXISTS
(SELECT t4.person_id, t4.book_id FROM person_book AS t4 INNER JOIN
(SELECT t3.person_id, t3.book_id FROM person_book WHERE
t3.book_id=t2.book_id)t3
on t3.person_id=t4.person_id WHERE t4.book_id=t1.book_id)
The data was extracted from the table into a .txt file, but  unfortunately , due to its  size  (44 MB) it’s
unreasonable to try to  upload  it for inspection. The command used to  export  the data:
COPY joint_purchase_stat(bookx, booky, joint_buys) to
'C:\ windows \temp\joint.txt'  DELIMITER ',' CSV HEADER;
Task 3:
The  solution  of the third task was  quite  straightforward compared to the  previous  two. The table
joint_purchase_stat we created in the course of completion of task 2 was used as a  basis  for
calculations of the following query, in which
1) a number of people who have purchased book X is counted and  cast  to decimal ( cause  we don’t
want to see 0% as result – it tells us  nothing , so we make sure that the data  types  participating in
division have that decimal  part ) and
2) two books are listed, alongside the nicely readable ( thanks  to  round  function, which allows to
display the designated number of decimal digits) percentage of people who have also purchased book
Y out of those who have purchased book X
3) and as we want to see the reverse  number for the combination of book X and book Y too, we add
another , almost  identical SELECT query
3.1) tweaking the name of the book overall buys are  found  for
3.2) and merge results by UNION clause.
SELECT bookx, booky,
ROUND(CAST(100*joint_buys AS
DECIMAL(10,2))/(SELECT CAST(COUNT (distinct person_id)AS decimal
(10,2)) FROM person_book WHERE book_id=bookx),2)||'%' AS percentage
FROM joint_purchase_stat
UNION
SELECT jps2.booky, jps2.bookx,
ROUND(CAST(100*joint_buys AS
decimal(10,2))/(SELECT CAST(COUNT (distinct person_id)AS decimal
(10,2)) FROM person_book WHERE book_id=jps2.booky),2)||'%' AS
percentage
FROM joint_purchase_stat AS jps2
In order to export the data to a text file a table has been created and populated by the data returned by
the above query. The file has over 7,000,000 lines in it and takes over 110 MB of  disk   space .