Proj2 - Visualize an SQL Query in Processing

saeedm
Posts: 6
Joined: Tue Jan 15, 2013 1:21 pm

Re: Proj2 - Visualize an SQL Query in Processing

Post by saeedm » Tue Jan 29, 2013 12:04 pm

Attachments
pj2.zip
Saeed Mahani Project 2
(4.24 MiB) Downloaded 235 times

saeedm
Posts: 6
Joined: Tue Jan 15, 2013 1:21 pm

Re: Proj2 - Visualize an SQL Query in Processing

Post by saeedm » Tue Feb 05, 2013 10:16 am

My second attempt at project 2.

Intro

I was curious to see how the SPL reflected the shifts in programming language preferences in the past few years. I knew mobile programming was on the rise, as well as web, but didn't quite know how core languages like Java and C++ were doing. I picked the two most popular languages from each category to observe.

Query

select
year(activity.o) as 'Year',
month(activity.o) as 'Month',
sum(case when (keyword.keyword = 'c') then 1 else 0 end) as 'C',
sum(case when (keyword.keyword = 'java') then 1 else 0 end) as 'Java',
sum(case when (keyword.keyword = 'javascript') then 1 else 0 end) as 'JavaScript',
sum(case when (keyword.keyword = 'html') then 1 else 0 end) as 'HTML',
sum(case when (keyword.keyword = 'android') then 1 else 0 end) as 'Android',
sum(case when (keyword.keyword = 'ios') then 1 else 0 end) as 'iOS'
from
keyword, title, collection, item, subject, activity
where
keyword.bib = title.bib and
item.bib = title.bib and
item.item = collection.item and
item.bib = subject.bib and
activity.bib = item.bib and
(subject.subject like '%programming%' or subject.subject like '%computer%' or subject.subject like '%software%' or subject.subject like '%development%') and
(keyword.keyword = 'java' or keyword.keyword = 'javascript' or keyword.keyword = 'python' or keyword.keyword = 'android' or keyword.keyword = 'ios' or keyword.keyword = 'c' or keyword.keyword = 'html') and
year(activity.o) >= 2006 and year(activity.o) <= 2012
group by year(activity.o), month(activity.o);

Query Explanation

The query searches for checkout events that contain programming language names in the subject or title and are categorized as a programming/software item. I only look in the years for which we have complete SPL data, and group the results by month.

Though this query uses expensive SQL operators ('like', 'sum'), it completes in just a few seconds.

Result and Analysis

I wanted to visualize the individual rise/fall in popularity of these popular programming languages while still showing the general trend in programming related checkouts at the library. A segmented bar graph was ideal for this. The number of languages I was examining was small enough to assign a unique color to each. It was then simply a matter of adjusting the saturation and brightness (and keeping it consistent for each color) to find colors that delimitated the languages enough but were also easy on the eyes. The results make clear a few interesting points in the data. For one, the mobile platform begins to appear in checkouts in early 2010 with the first Android books. Second, core languages seem to remain at a fairly constant level despite the increase in the other categories. Lastly, though web languages have been around for a couple decades, they are recently experiencing an upward surge, possibly due to the radical changes in website architecture visible in complex websites like Facebook.
Attachments
Screen Shot 2013-02-05 at 10.15.30 AM.png
Project 2 Screenshot
saeed_pj2a.zip
Project 2 Code
(1.89 KiB) Downloaded 235 times

Post Reply