Skip to content

Statystyki 2013.12.04

Jump to bottom
dmydlarz edited this page Dec 4, 2013 · 21 revisions

W okresie od 23 października do 1 grudnia 2013 pobrano tweety z 11 meczów

# Start_date		Country Home			Country Away				Competitions 
2013-11-23 13:45:00	EN	Everton FC		EN	Liverpool FC			Barclays Premier League
2013-11-23 16:00:00	EN	Arsenal Londyn		EN	Southampton FC			Barclays Premier League
2013-11-26 20:45:00	CH	FC Basel		EN	Chelsea Londyn			UEFA Champions League
2013-11-26 20:45:00	EN	Arsenal Londyn		FR	OLYMPIQUE MARSEILLE		UEFA Champions League
2013-11-27 20:45:00	EN	Manchester City		CZ	Viktoria Plzen			UEFA Champions League
2013-11-27 20:45:00	DE	Bayer Leverkusen	EN	Manchester United		UEFA Champions League
2013-11-30 16:00:00	EN	Cardiff City FC		EN	Arsenal Londyn			Barclays Premier League
2013-12-01 13:00:00	EN	Tottenham Hotspur	EN	Manchester United		Barclays Premier League
2013-12-01 15:05:00	EN	Hull City		EN	Liverpool FC			Barclays Premier League
2013-12-01 17:10:00	EN	Chelsea Londyn		EN	Southampton FC			Barclays Premier League
2013-12-01 17:10:00	EN	Manchester City		EN	Swansea City			Barclays Premier League

SELECT start_date, t1.country, t1.name, t2.country, t2.name, c.name
FROM match_events me
	JOIN teams t1 ON me.home_team = t1.id JOIN teams t2 ON me.away_team = t2.id
	JOIN competitions c ON me.competition = c.id
	WHERE me.id in (40, 176, 404, 445, 486, 526, 547, 567, 588, 589, 609)

Liczba pobranych wpisów

# Home			Away				Tweets	Geotagged	Geotagged %
Everton FC		Liverpool FC			206732	     7464	     3.6105
Arsenal Londyn		Southampton FC			190028	     5231	     2.7528
FC Basel		Chelsea Londyn			121209	     3339	     2.7547
Arsenal Londyn		OLYMPIQUE MARSEILLE		185252	     6255	     3.3765
Manchester City		Viktoria Plzen			 24990	      792	     3.1693
Bayer Leverkusen	Manchester United		199232	     6242	     3.1330
Cardiff City FC		Arsenal Londyn			233151	     6316	     2.7090
Tottenham Hotspur	Manchester United		166394	     4628	     2.7814
Hull City		Liverpool FC			282499	    10248	     3.6276
Chelsea Londyn		Southampton FC			241768	     7536	     3.1170
Manchester City		Swansea City			 29977	      785	     2.6187

SELECT home, away, tweets_num, geotagged, round(CAST(geotagged::float / tweets_num * 100 AS NUMERIC), 4) percentage
FROM (
	SELECT start_date, t1.name home, t2.name away, 
		(SELECT count(*) FROM tweets WHERE match_event = me.id) as tweets_num,
		(SELECT count(*) FROM tweets WHERE match_event = me.id AND latitude IS NOT NULL) as geotagged
	FROM match_events me
		JOIN teams t1 ON me.home_team = t1.id JOIN teams t2 ON me.away_team = t2.id
		JOIN competitions c ON me.competition = c.id
	WHERE me.id in (40, 176, 404, 445, 486, 526, 547, 567, 588, 589, 609)
	) as query
ORDER BY start_date asc

Czas nasłuchiwania

# Start_date		# Home			# Away				# Start		# End		# Interval
2013-11-23 13:45:00	Everton FC		Liverpool FC			12:28:33	15:26:33	02:58:00
2013-11-23 16:00:00	Arsenal Londyn		Southampton FC			15:35:24	18:23:02	02:47:38
2013-11-26 20:45:00	FC Basel		Chelsea Londyn			19:52:16	22:59:32	03:07:16
2013-11-26 20:45:00	Arsenal Londyn		OLYMPIQUE MARSEILLE		19:52:16	22:59:27	03:07:11
2013-11-27 20:45:00	Manchester City		Viktoria Plzen			19:54:37	22:56:13	03:01:36
2013-11-27 20:45:00	Bayer Leverkusen	Manchester United		19:54:35	22:56:25	03:01:50
2013-11-30 16:00:00	Cardiff City FC		Arsenal Londyn			15:00:21	18:19:18	03:18:57
2013-12-01 13:00:00	Tottenham Hotspur	Manchester United		13:35:50	16:31:48	02:55:58
2013-12-01 15:05:00	Hull City		Liverpool FC			14:22:09	22:37:28	08:15:19
2013-12-01 17:10:00	Chelsea Londyn		Southampton FC			16:31:50	22:37:09	06:05:19
2013-12-01 17:10:00	Manchester City		Swansea City			16:31:52	22:36:04	06:04:12

SELECT start_date, home, away, time_min, time_max, age(time_max, time_min) age
FROM (
	SELECT start_date, t1.name home, t2.name away, 
		(SELECT min(created_at) FROM tweets WHERE match_event = me.id) as time_min,
		(SELECT max(created_at) FROM tweets WHERE match_event = me.id AND latitude IS NOT NULL) as time_max
	FROM match_events me
		JOIN teams t1 ON me.home_team = t1.id JOIN teams t2 ON me.away_team = t2.id
		JOIN competitions c ON me.competition = c.id
	WHERE me.id in (40, 176, 404, 445, 486, 526, 547, 567, 588, 589, 609)
) as query
ORDER BY start_date asc

Wykresy aktywności

SELECT geo.time, query.count tweets, geo.count geotagged FROM (
	SELECT count(*), to_char(created_at, 'YYYY-MM-DD HH24:MI') AS time
	FROM mgr.tweets
	WHERE match_event = 40 
	GROUP BY time
) AS query
JOIN (
	SELECT count(*), to_char(created_at, 'YYYY-MM-DD HH24:MI') AS time
	FROM mgr.tweets
	WHERE match_event = 40 AND latitude IS NOT NULL
	GROUP BY time
) AS geo
ON query.time = geo.time
ORDER BY geo.time DESC

2013-11-23 13:45:00 - Everton FC - Liverpool FC

zerwane połączenie internetowe

  • 0:1 - 5'
  • 1:1 - 8'
  • 1:2 - 19'
  • 2:2 - 72'
  • 3:2 - 82'
  • 3:3 - 89'

text

2013-11-23 16:00:00 - Arsenal Londyn - Southampton FC

  • 1:0 - 22' (duży błąd A. Boruca - bramkarza Southampton)
  • 2:0 - 86'

text

2013-11-26 20:45:00 - FC Basel - Chelsea Londyn

  • 1:0 - 87'

text

2013-11-26 20:45:00 - Arsenal Londyn - OLYMPIQUE MARSEILLE

  • 1:0 - 1'
  • 38' - niewykorzystany rzut kartny (Arsenal)
  • 2:0 - 65'

text

2013-11-26 20:45:00 - Manchester City - Viktoria Plzen

  • 1:0 - 33'
  • 1:1 - 43'
  • 2:1 - 65'
  • 2:2 - 69'
  • 3:2 - 78'
  • 4:2 - 89'

text

2013-11-26 20:45:00 - Bayer Leverkusen - Manchester United

  • 0:1 - 22'
  • 0:2 - 30'
  • 0:3 - 66'
  • 0:4 - 77'
  • 0:5 - 88'

text

2013-11-30 16:00:00 - Cardiff City AFC - Arsenal Londyn

  • 0:1 - 29'
  • 0:2 - 86'
  • 0:3 - 90 + 3'

text

2013-12-01 13:00:00 - Tottenham Hotspur - Manchester United

  • 1:0 - 18'
  • 1:1 - 32'
  • 2:1 - 54'
  • 2:2 - 57'

text

2013-12-01 17:10:00 - Chelsea - Southampton

  • 0:1 - 1'
  • 1:1 - 55'
  • 2:1 - 62'
  • 3:1 - 90'

text

2013-12-01 17:10:00 - Manchester City- Swansea City

  • 1:0 - 8'
  • 2:0 - 58'
  • 3:0 - 77'

text

Clone this wiki locally