Project Description

Monthly frequencies of checkout between different categories book

Ming-Yu Lin

This 3D model shows the monthly amount of checkout from 2007 to 2016. For the first attempt for this assignment, I queried daily data to compute. However, there would be up to 36500 rows data and the fps became really low due to the large calculation in drawing. Also, the length of the model will be too long to be fully presented. As a result, I changed back to query monthly data. The basic concept was inspired by the TreeMap model from the example. I changed the shape for each layer which showed the amount of the checkout. I just labeled out the year with several circular ring to show the time line. I used colored text to show the relationship between block color and categories' names. The position of the text is randomized.

select t.Year as Year, t.Month as Month, t.dewey as dewey, count(t.dewey) as counts
FROM (
    select year(cout) as Year, date_format(cout, '%m') as Month, case  
    when itemType like '%bk' then 'book'
    else 'non-book' end as item,
    case when deweyClass >= 0 and deweyClass < 100 then '0' 
        when deweyClass >= 100 and deweyClass < 200 then '100' 
        when deweyClass >= 200 and deweyClass < 300 then '200' 
        when deweyClass >= 300 and deweyClass < 400 then '300' 
        when deweyClass >= 400 and deweyClass < 500 then '400' 
        when deweyClass >= 500 and deweyClass < 600 then '500' 
        when deweyClass >= 600 and deweyClass < 700 then '600' 
        when deweyClass >= 700 and deweyClass < 800 then '700' 
        when deweyClass >= 800 and deweyClass < 900 then '800' 
        when deweyClass >= 900 and deweyClass < 1000 then '900' else '' end as dewey 
        from spl_2016.inraw) t
where t.item = 'book' and t.dewey != '' and t.Year > 2006 and t.Year < 2017
group by t.Year,t.Month,t.dewey
ORDER BY t.Year, t.Month
LIMIT 1200;

In inner query, I generalized small categories to ten big categories and classified book and non-book for itemType. In outer query, I group the data to get the total counts for same deweyNumber in same month. The query time was around 100 seconds. The query might be optimized to save more time.

There are still a lot of parts can be improved. Since the amount of checkout for Computer Science(first category) was really high, it was hard to scale the number for each category. The trade off is the bigger difference for the different categories would lead to smaller difference for the same category. Also, there is no interactive function in current code.

All work is developed within Processing
Source Code + Data