Statistical aggregations
Statistical aggregations can tell you a lot of information about a data set. For this tutorial, we will use the GDELT data set and focus on the avg_tone column. From the GDELT documentation, avg_tone 'is the average “tone” of all documents containing one or more
mentions of this event during the 15 minute update in which it was first seen. The score
ranges from -100 (extremely negative) to +100 (extremely positive). Common values range
between -10 and +10, with 0 indicating neutral.'
First, let's get some general information, by year. Understanding the min, max, and avg is a great place to start:
Calculate min, avg, max, stddev
SELECT toYear(timestamp) as year, round(min(avg_tone),3) AS min, round(avg(avg_tone),3) AS avg, round(max(avg_tone),3) AS max, round(stddevPop(avg_tone),3)
FROM sample.gdelt
WHERE (timestamp BETWEEN '2015-02-01 00:00:00' AND '2020-01-01 00:00:00')
GROUP BY year
ORDER BY year ASC
curl --data-binary @- https://try3.hydrolix.live/query <<EOF
SELECT toYear(timestamp) as year, round(min(avg_tone),3) AS min, round(avg(avg_tone),3) AS avg, round(max(avg_tone),3) AS max, round(stddevPop(avg_tone),3)
FROM sample.gdelt
WHERE (timestamp BETWEEN '2015-02-01 00:00:00' AND '2020-01-01 00:00:00')
GROUP BY year
ORDER BY year ASC
EOF
Looking at this, average tone of news articles are slightly negative, but over time, the range of values of tone has varied greatly. average tone and the standard deviation of the tone is staying fairly steady, meaning that the news, while staying mostly neutral, has more outliers of extemely positive and extremely negative articles.
Over these years, it would be interesting to see the top source domains:
find top occurring values in a set, topK
SELECT toYear(timestamp) as year, topK(3)(domainWithoutWWW(source_url))
FROM sample.gdelt
WHERE (timestamp BETWEEN '2015-01-01 00:00:00' AND '2020-01-01 00:00:00')
GROUP BY year
ORDER BY year ASC
curl --data-binary @- https://try3.hydrolix.live/query <<EOF
SELECT toYear(timestamp) as year, topK(3)(domainWithoutWWW(source_url))
FROM sample.gdelt
WHERE (timestamp BETWEEN '2015-01-01 00:00:00' AND '2020-01-01 00:00:00')
GROUP BY year
ORDER BY year ASC
EOF
Also interesting to investigate would be the domains that had the post positive and most negative article per year:
argMax, argMin - find the URLs with the most +/- articles
SELECT toYear(timestamp) as year, argMax(domainWithoutWWW(source_url), avg_tone), argMin(domainWithoutWWW(source_url), avg_tone)
FROM sample.gdelt
WHERE (timestamp BETWEEN '2015-02-01 00:00:00' AND '2020-01-01 00:00:00')
GROUP BY year
ORDER BY year ASC
curl --data-binary @- https://try3.hydrolix.live/query <<EOF
SELECT toYear(timestamp) as year, argMax(domainWithoutWWW(source_url), avg_tone), argMin(domainWithoutWWW(source_url), avg_tone)
FROM sample.gdelt
WHERE (timestamp BETWEEN '2015-02-01 00:00:00' AND '2020-01-01 00:00:00')
GROUP BY year
ORDER BY year ASC
EOF
The one that stands out is msn.com in 2017. It would be interesting to drill into more of the tone trends for that website.
Statistics for an entity in the set
SELECT toYear(timestamp) as year, round(min(avg_tone),3), round(avg(avg_tone),3), round(max(avg_tone),3), count()
FROM sample.gdelt
WHERE (timestamp BETWEEN '2015-02-01 00:00:00' AND '2020-01-01 00:00:00')
AND equals(domainWithoutWWW(source_url),'msn.com')
GROUP BY year
ORDER BY year ASC
curl --data-binary @- https://try3.hydrolix.live/query <<EOF
SELECT toYear(timestamp) as year, round(min(avg_tone),3), round(avg(avg_tone),3), round(max(avg_tone),3), count()
FROM sample.gdelt
WHERE (timestamp BETWEEN '2015-02-01 00:00:00' AND '2020-01-01 00:00:00')
AND equals(domainWithoutWWW(source_url),'msn.com')
GROUP BY year
ORDER BY year ASC
EOF
The fact that there are fewer articles in 2019 is most likely because the 2019 data set is not complete.
Get the month_year of the most positive and negative events by year
SELECT toYear(timestamp) as year, argMin(toMonth(timestamp), avg_tone), argMax(toMonth(timestamp), avg_tone)
FROM sample.gdelt
WHERE (timestamp BETWEEN '2015-02-01 00:00:00' AND '2020-01-01 00:00:00')
GROUP BY year
ORDER BY year ASC
curl --data-binary @- https://try3.hydrolix.live/query <<EOF
SELECT toYear(timestamp) as year, argMin(toMonth(timestamp), avg_tone), argMax(toMonth(timestamp), avg_tone)
FROM sample.gdelt
WHERE (timestamp BETWEEN '2015-02-01 00:00:00' AND '2020-01-01 00:00:00')
GROUP BY year
ORDER BY year ASC
EOF
The most negative month in 2016 was March, which was likely due to US primary elections.
Updated 12 days ago