The Trend in Popularity of World Language Learning
Posted: Thu Jan 14, 2016 10:11 am
M259 Project 1 Xindi (Cindy) Kang
Project Report
------ The Trend in Popularity of World Language Learning
. Question
- What languages are the most popular languages among language learners who borrow books from Seattle Public Library? (Referred to as SPL in this report)
- What does the trend in the popularity of major languages like English, Chinese, Spanish, and Japanese look like in the past decade?
- What about minority languages and Hawaiian, Icelandic and Swedish?
. The Query
Majority languages:
The following is the query for the number of checkouts for Japanese language related books at SPL (in the language deweyClass 490-499) from 2006 to 2016: -----------------------------------------------------------------------------------
SELECT
FLOOR(deweyClass) AS Dewey,
SUM(CASE
WHEN YEAR(checkOut) = 2006 THEN 1
ELSE 0
END) AS '2006',
SUM(CASE
WHEN YEAR(checkOut) = 2007 THEN 1
ELSE 0
END) AS '2007',
SUM(CASE
WHEN YEAR(checkOut) = 2008 THEN 1
ELSE 0
END) AS '2008',
SUM(CASE
WHEN YEAR(checkOut) = 2009 THEN 1
ELSE 0
END) AS '2009',
SUM(CASE
WHEN YEAR(checkOut) = 2010 THEN 1
ELSE 0
END) AS '2010',
SUM(CASE

WHEN YEAR(checkOut) = 2011 THEN 1
ELSE 0
END) AS '2011',
SUM(CASE
WHEN YEAR(checkOut) = 2012 THEN 1
ELSE 0
END) AS '2012',
SUM(CASE
WHEN YEAR(checkOut) = 2013 THEN 1
ELSE 0
END) AS '2013',
SUM(CASE
WHEN YEAR(checkOut) = 2014 THEN 1
ELSE 0
END) AS '2014',
SUM(CASE
WHEN YEAR(checkOut) = 2014 THEN 1
ELSE 0
END) AS '2015',
SUM(CASE
WHEN YEAR(checkOut) = 2014 THEN 1
ELSE 0
END) AS '2016'
FROM
spl3._rawXmlDataCheckOuts
WHERE
deweyClass >= 400 AND deweyClass < 500
AND title LIKE '%japan%'
GROUP BY FLOOR(deweyClass)
ORDER BY SUM(CASE
WHEN
(YEAR(checkOut) >= 2006
OR YEAR(checkOut) <= 2016)
THEN 1
ELSE 0
END) DESC
----------------------------------------------------------------------------------------
(The queries for other languages are the same except for the title keyword)
Results and processing times for Japanese and other major languages:
Japanese: 39.182s
06 07 08 09 10 111213141516 Chinese:
39.612s
06 07 08 09 10 111213141516 English:
39.768s

06 07 08 09 10 111213141516 Spanish:
39.593
06 07 08 09 10 111213141516
Popularity Ranking:
1. English 2. Spanish 3. Chinese 4. Japanese
Minority Languages: Hawaiian:
39.421s

06 Icelandic:
39.693s
07 08 09 10 11 12 13 14 15 16
06 Swedish:
39.761
07 08 09 10 11 12 13 14 15 16
06 07 08 09 10 1112 1314 1516
Popularity Ranking:
1. Swedish 2. Hawaiian 3. Icelandic
Trending:
The popularity of the learning of every majority language researched in this project reached their peak during 2009 and 2010 and then fell back to normal level since then.
The popularity of the learning of the minority languages researched in this project each has different performances over the past decade.
The popularity for Hawaiian learning showed a rather unstable performance whereas Swedish fell back to a descending trend after a peak in 2009.
Icelandic showed a significant increase in 2011 and has remained at that level since then.
Project Report
------ The Trend in Popularity of World Language Learning
. Question
- What languages are the most popular languages among language learners who borrow books from Seattle Public Library? (Referred to as SPL in this report)
- What does the trend in the popularity of major languages like English, Chinese, Spanish, and Japanese look like in the past decade?
- What about minority languages and Hawaiian, Icelandic and Swedish?
. The Query
Majority languages:
The following is the query for the number of checkouts for Japanese language related books at SPL (in the language deweyClass 490-499) from 2006 to 2016: -----------------------------------------------------------------------------------
SELECT
FLOOR(deweyClass) AS Dewey,
SUM(CASE
WHEN YEAR(checkOut) = 2006 THEN 1
ELSE 0
END) AS '2006',
SUM(CASE
WHEN YEAR(checkOut) = 2007 THEN 1
ELSE 0
END) AS '2007',
SUM(CASE
WHEN YEAR(checkOut) = 2008 THEN 1
ELSE 0
END) AS '2008',
SUM(CASE
WHEN YEAR(checkOut) = 2009 THEN 1
ELSE 0
END) AS '2009',
SUM(CASE
WHEN YEAR(checkOut) = 2010 THEN 1
ELSE 0
END) AS '2010',
SUM(CASE

WHEN YEAR(checkOut) = 2011 THEN 1
ELSE 0
END) AS '2011',
SUM(CASE
WHEN YEAR(checkOut) = 2012 THEN 1
ELSE 0
END) AS '2012',
SUM(CASE
WHEN YEAR(checkOut) = 2013 THEN 1
ELSE 0
END) AS '2013',
SUM(CASE
WHEN YEAR(checkOut) = 2014 THEN 1
ELSE 0
END) AS '2014',
SUM(CASE
WHEN YEAR(checkOut) = 2014 THEN 1
ELSE 0
END) AS '2015',
SUM(CASE
WHEN YEAR(checkOut) = 2014 THEN 1
ELSE 0
END) AS '2016'
FROM
spl3._rawXmlDataCheckOuts
WHERE
deweyClass >= 400 AND deweyClass < 500
AND title LIKE '%japan%'
GROUP BY FLOOR(deweyClass)
ORDER BY SUM(CASE
WHEN
(YEAR(checkOut) >= 2006
OR YEAR(checkOut) <= 2016)
THEN 1
ELSE 0
END) DESC
----------------------------------------------------------------------------------------
(The queries for other languages are the same except for the title keyword)
Results and processing times for Japanese and other major languages:
Japanese: 39.182s
06 07 08 09 10 111213141516 Chinese:
39.612s
06 07 08 09 10 111213141516 English:
39.768s

06 07 08 09 10 111213141516 Spanish:
39.593
06 07 08 09 10 111213141516
Popularity Ranking:
1. English 2. Spanish 3. Chinese 4. Japanese
Minority Languages: Hawaiian:
39.421s

06 Icelandic:
39.693s
07 08 09 10 11 12 13 14 15 16
06 Swedish:
39.761
07 08 09 10 11 12 13 14 15 16
06 07 08 09 10 1112 1314 1516
Popularity Ranking:
1. Swedish 2. Hawaiian 3. Icelandic
Trending:
The popularity of the learning of every majority language researched in this project reached their peak during 2009 and 2010 and then fell back to normal level since then.
The popularity of the learning of the minority languages researched in this project each has different performances over the past decade.
The popularity for Hawaiian learning showed a rather unstable performance whereas Swedish fell back to a descending trend after a peak in 2009.
Icelandic showed a significant increase in 2011 and has remained at that level since then.