Travel Books Checkouts from 2010 to 2015
MAT 259, 2016
Woohun Joo
Concept
I am going to look closely into dewey class between 914 and 919 since the data that I used last time contains some of non-travel books. This version only shows travel-related books and the top lists in each travel category.
My additional questions are:
where is the most popular spot in each dewey class?
How does checkout frequency change from month to month, year to year?
The checkout period comparison among all travel dewey classes.
I observed some checkout rate changes every month for 6 years, and also checked the popular books at the top of the list in this category. I attached my SQL query combining 5 tables from a new spl DB.
Each dewey class has a certain color within the hue range (0 to 360). The width of the unit varies depends on the checkout frequencies, and the fill colors of all units change according to popularities increasing or decreasing RGB values.
Below is examples of the final screen shots including control P5. Control P5 allows to turn on and off line connecting each units, showing labels, and the movement of all units.
My additional questions are:
where is the most popular spot in each dewey class?
How does checkout frequency change from month to month, year to year?
The checkout period comparison among all travel dewey classes.
I observed some checkout rate changes every month for 6 years, and also checked the popular books at the top of the list in this category. I attached my SQL query combining 5 tables from a new spl DB.
Each dewey class has a certain color within the hue range (0 to 360). The width of the unit varies depends on the checkout frequencies, and the fill colors of all units change according to popularities increasing or decreasing RGB values.
Below is examples of the final screen shots including control P5. Control P5 allows to turn on and off line connecting each units, showing labels, and the movement of all units.
Query
#Below is an example of 3D assignment in Dewey 914
SELECT deweyClass AS Dewey, title AS BookTitle, subject AS Category, DATE(checkOut) AS CheckoutDate, TIME(checkOut) AS CheckoutTime, COUNT(checkOut) AS CheckoutCount, itemtype AS ItemType, TIMESTAMPDIFF(DAY, checkOut, checkInFirst) FROM spl.deweyClass, spl.transactions, spl.title, spl.itemType, spl.itemToBib, spl.subject WHERE deweyClass = 914 -- And (TIMESTAMPDIFF(DAY, checkOut, checkInFirst) != '') #AND deweyClass <= 919 AND spl.deweyClass.bibNumber = spl.transactions.bibNumber AND spl.deweyClass.bibNumber = spl.title.bibNumber AND spl.deweyClass.bibNumber = spl.subject.bibNumber AND spl.deweyClass.bibNumber = spl.itemToBib.bibNumber AND spl.itemToBib.itemNumber = spl.itemType.itemNumber AND (spl.subject.subject LIKE '%travel%' OR spl.subject.subject LIKE '%guide%' OR spl.subject.subject LIKE '%guidebook%') AND (itemType = 'acbk' OR itemType = 'acdvd' OR itemType = 'acvhs' OR itemType = 'jcbk' OR itemType = 'jcdvd' OR itemType = 'jcvhs') AND YEAR(checkOut) >= '2010' GROUP BY title ORDER BY CheckoutCount DESC
SELECT deweyClass AS Dewey, title AS BookTitle, subject AS Category, DATE(checkOut) AS CheckoutDate, TIME(checkOut) AS CheckoutTime, COUNT(checkOut) AS CheckoutCount, itemtype AS ItemType, TIMESTAMPDIFF(DAY, checkOut, checkInFirst) FROM spl.deweyClass, spl.transactions, spl.title, spl.itemType, spl.itemToBib, spl.subject WHERE deweyClass = 914 -- And (TIMESTAMPDIFF(DAY, checkOut, checkInFirst) != '') #AND deweyClass <= 919 AND spl.deweyClass.bibNumber = spl.transactions.bibNumber AND spl.deweyClass.bibNumber = spl.title.bibNumber AND spl.deweyClass.bibNumber = spl.subject.bibNumber AND spl.deweyClass.bibNumber = spl.itemToBib.bibNumber AND spl.itemToBib.itemNumber = spl.itemType.itemNumber AND (spl.subject.subject LIKE '%travel%' OR spl.subject.subject LIKE '%guide%' OR spl.subject.subject LIKE '%guidebook%') AND (itemType = 'acbk' OR itemType = 'acdvd' OR itemType = 'acvhs' OR itemType = 'jcbk' OR itemType = 'jcdvd' OR itemType = 'jcvhs') AND YEAR(checkOut) >= '2010' GROUP BY title ORDER BY CheckoutCount DESC
Final result
X-axis: Date from the first week of 2010 to the last week of 2015 (Week Based)
Y-axis: Checkout Time between 10AM to 20PM
Z-axis: Dewey Class between 914 to 919
Unit Stroke Color: Dewey Classification
Unit Size and Fill Color: Checkout rates.
The check boxes from dewey class 914 to 919 draw lines between all units to check the range of fluctuation.
Label On/Off: It shows what data each axis contains.
Motion On/Off: Based on the value each unit has, it animates all shapes changing the width.
Rotating: The graph is rotating automatically by default.
Time-Year View
Z: Dewey Class
X: Time(Week Based), Y: Time, Z: Category
Zoom In
Zoom In Z Axis View
Regular Z Axis View
Zoom In connecting lines
Y-axis: Checkout Time between 10AM to 20PM
Z-axis: Dewey Class between 914 to 919
Unit Stroke Color: Dewey Classification
Unit Size and Fill Color: Checkout rates.
The check boxes from dewey class 914 to 919 draw lines between all units to check the range of fluctuation.
Label On/Off: It shows what data each axis contains.
Motion On/Off: Based on the value each unit has, it animates all shapes changing the width.
Rotating: The graph is rotating automatically by default.
Time-Year View
Z: Dewey Class
X: Time(Week Based), Y: Time, Z: Category
Zoom In
Zoom In Z Axis View
Regular Z Axis View
Zoom In connecting lines
Code