Skip to content

Instantly share code, notes, and snippets.

@alegomes

alegomes/Principais retweets.sql

Last active June 30, 2016 22:42
Show Gist options
  • Save alegomes/fbde8c13f4d73aa7270749afb0c7d980 to your computer and use it in GitHub Desktop.
Save alegomes/fbde8c13f4d73aa7270749afb0c7d980 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Principais retweets.sql
#####################################################################################################
# Quais os textos mais retweetados?
#
mysql> select text,count(text) from retweets group by text order by count(text) desc limit 10;
+---------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| text | count(text) |
+---------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | 1844 |
| RT @camila_vallejo: No sólo ganó Chávez, ganó el proyecto bolivariano x una América Latina Libre y Soberana. Viva Venezuela!! su pueblo ... | 1397 |
| RT @camila_vallejo: Sr @matiasdelrio, Bachelet es candidata del PS PPD y el MAS, no del PC y tpco mía. Favor aclarar en #T0, gracias | 1267 |
| RT @camila_vallejo: Yo no he congelado, estoy haciendo la tesis!. Aunq x cultura gral: todos los pdtes FECH congelan para dedicarse 100% ... | 1072 |
| RT @GiorgioJackson: Parece que la mayoría de Chile somos #MugrientosDeMierda por estar preocupados de la educación en Chile o no? http:/ ... | 1014 |
| RT @NoamTitelman: @vidalvocero Gracias por las felicitaciones, pero después de 20 años de dejar la educación botada, por favor no se and ... | 1013 |
| RT @camila_vallejo: Una mujer no puede decir q somos simples vasijas donde Dios o un Hombre deposita la esperma de la reproducción. Abor ... | 974 |
| RT @camila_vallejo: Cn dolor escucho la amarga noticia d la muerte dl Pdte Chávez. Fuerza al pueblo bolivariano para q cn amor sigan con ... | 974 |
| RT @camila_vallejo: Lo que hoy pasa en nuestro pais, desnuda a la derecha en su verdadera forma de gobernar, nosotros no tenemos miedo, ... | 961 |
| RT @camila_vallejo: Denunciamos que la casa FECH está siendo atacada por carabineros con bombas lacrimogenas, esto es un ataque directo ... | 928 |
+---------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
10 rows in set (36.57 sec)
#
# Vamos conferir se, o tweet "Hoy a las 21 (…) tem, de fato, esse tanto de ocorrências.
#
mysql> select count(*) from retweets where text = "RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR!";
+----------+
| count(*) |
+----------+
| 1844 |
+----------+
1 row in set (1.71 sec)
# De fato.
#
# Vamos agora listar 10 retweets do texto mais retweetado.
#
mysql> select * from retweets where text = "RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR!" limit 10;
+-------+----------+------------+-------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
| id | actor_id | mention_id | text | user_screen_name | rt_date | tweet_id |
+-------+----------+------------+-------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
| 5804 | 1 | 123546 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | isioc | 2011-08-08 18:29:41 | NULL |
| 11193 | 1 | 138262 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | pmorrisk | 2011-08-09 20:51:38 | NULL |
| 11405 | 1 | 138575 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | DavidHHenriquez | 2011-08-09 20:54:48 | NULL |
| 11516 | 1 | 138832 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | valenzuelalevi | 2011-08-09 20:58:34 | NULL |
| 11522 | 1 | 138846 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | polylla | 2011-08-09 20:58:50 | NULL |
| 11532 | 1 | 138869 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | pabloastudillo | 2011-08-09 20:59:21 | NULL |
| 11534 | 1 | 138878 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | nvaizquierda | 2011-08-09 20:59:30 | NULL |
| 11540 | 1 | 138889 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | andres_ibanez | 2011-08-09 20:59:52 | NULL |
| 11551 | 1 | 138910 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | paulioz | 2011-08-09 21:00:21 | NULL |
| 11570 | 1 | 138952 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | ceciliatoro | 2011-08-09 21:01:15 | NULL |
+-------+----------+------------+-------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
10 rows in set (0.02 sec)
# Reparem que:
# 1. Todos, aparentemente, têm o mesmo actor_id.
# 2. Todos, aparentemente, fazem referência a um mention_id distinto.
# 3. user_screen_name parece ser o usuário que retweetou, e não o tweetador original.
# 4. rt_date parece ser a data do retweet, e não do tweet original.
#
# Vamos validar algumas dessas hipóteses?
######################################################################################################
#
# 1. Todos, aparentemente, têm o mesmo actor_id. Vejamos:
mysql> select distinct actor_id from retweets where text = "RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR!";
+----------+
| actor_id |
+----------+
| 1 |
+----------+
1 row in set (6.00 sec)
# Confirmado. Todos os retweets do texto "Hoy a las 21...." referem-se ao actor_id = 1, que deve ser o da @camila_vallejo
mysql> select * from actors where id = 1;
+----+----------------+-----------------+--------+-----------+-----------------+-----------+-----------+
| id | name | screen_name | nature | year_2011 | real_username | year_2012 | year_2013 |
+----+----------------+-----------------+--------+-----------+-----------------+-----------+-----------+
| 1 | Camila Vallejo | @camila_vallejo | 0 | 1 | @camila_vallejo | 1 | 0 |
+----+----------------+-----------------+--------+-----------+-----------------+-----------+-----------+
1 row in set (0.02 sec)
#
######################################################################################################
# 2. Todos, aparentemente, fazem referência a um mention_id distinto. Será?
#
mysql> select count(*) from retweets;
+----------+
| count(*) |
+----------+
| 921900 |
+----------+
1 row in set (0.77 sec)
mysql> select count(distinct mention_id) from retweets;
+----------------------------+
| count(distinct mention_id) |
+----------------------------+
| 921900 |
+----------------------------+
1 row in set (1.68 sec)
# Confirmado. Cada retweet tem um mention_id próprio.
#
######################################################################################################
# 3. user_screen_name parece ser o usuário que retweetou, e não o tweetador original. Será?
#
mysql> select count(distinct user_screen_name) from retweets;
+----------------------------------+
| count(distinct user_screen_name) |
+----------------------------------+
| 146917 |
+----------------------------------+
1 row in set (1.91 sec)
# Dada a quantidade de valores distintos, não tem como ser o tweetador original, concordam?
#
######################################################################################################
# 4. rt_date parece ser a data do retweet, e não do tweet original. Será?
#
mysql> select count(distinct rt_date) from retweets;
+-------------------------+
| count(distinct rt_date) |
+-------------------------+
| 860445 |
+-------------------------+
1 row in set (1.53 sec)
#
# Idem. Se rt_date referenciasse o tweet original, teríamos apenas 1 valor.
#
######################################################################################################
# Aí, a Marisa pediu que incluíssemos a data na contagem dos tweets mais retweetados.
# Imagino que não seja a hora do retweet (rt_date) mas a data e hora da postagem original, né?
# Ora, mas qual coluna tem o potencial de nos dar essa informação?
mysql> select * from retweets where text = "RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR!" limit 10;
+-------+----------+------------+-------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
| id | actor_id | mention_id | text | user_screen_name | rt_date | tweet_id |
+-------+----------+------------+-------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
| 5804 | 1 | 123546 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | isioc | 2011-08-08 18:29:41 | NULL |
| 11193 | 1 | 138262 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | pmorrisk | 2011-08-09 20:51:38 | NULL |
| 11405 | 1 | 138575 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | DavidHHenriquez | 2011-08-09 20:54:48 | NULL |
| 11516 | 1 | 138832 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | valenzuelalevi | 2011-08-09 20:58:34 | NULL |
| 11522 | 1 | 138846 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | polylla | 2011-08-09 20:58:50 | NULL |
| 11532 | 1 | 138869 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | pabloastudillo | 2011-08-09 20:59:21 | NULL |
| 11534 | 1 | 138878 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | nvaizquierda | 2011-08-09 20:59:30 | NULL |
| 11540 | 1 | 138889 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | andres_ibanez | 2011-08-09 20:59:52 | NULL |
| 11551 | 1 | 138910 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | paulioz | 2011-08-09 21:00:21 | NULL |
| 11570 | 1 | 138952 | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | ceciliatoro | 2011-08-09 21:01:15 | NULL |
+-------+----------+------------+-------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
10 rows in set (0.07 sec)
#
# Talvez, mention_id?
#
# Peguemos um mention da primeira linha acima..
#
mysql> select * from mentions where id = 123546;
+--------+--------------------------------+-----------+-------------------------+----------------------------------------------------+------+--------+-------------------------------------------------------------------------------------------------------------------------------+--------------------------+-----------------------+--------------------------------+----------------------+--------------+------------------+----------------+----------+
| id | mention_date | favorited | in_reply_to_screen_name | permanent_link | lang | source | text | user_profile_description | user_profile_location | user_date | user_followers_count | user_name | user_screen_name | user_time_zone | actor_id |
+--------+--------------------------------+-----------+-------------------------+----------------------------------------------------+------+--------+-------------------------------------------------------------------------------------------------------------------------------+--------------------------+-----------------------+--------------------------------+----------------------+--------------+------------------+----------------+----------+
| 123546 | Mon Aug 08 18:29:41 +0000 2011 | 0 | NULL | http://twitter.com/isioc/status/100634802226597889 | NULL | web | RT @camila_vallejo: Hoy a las 21 horas cacerolazo en todo Chile en repudio a la represión contra los estudiantes...DIFUNDIR! | NULL | chile | Thu Apr 14 22:11:38 +0000 2011 | 6 | Isabel Ortiz | isioc | NULL | 1 |
+--------+--------------------------------+-----------+-------------------------+----------------------------------------------------+------+--------+-------------------------------------------------------------------------------------------------------------------------------+--------------------------+-----------------------+--------------------------------+----------------------+--------------+------------------+----------------+----------+
1 row in set (0.02 sec)
"
# Reparem que o mention_date da tabela mentions coincide com o rt_date da tabela retweets.
# Reparem que na tabela mentions não tem nada que faça referência ao tweet original,
# a não ser o permanent_link, mas aí teríamos que fazer outro processo de coleta.
# Ou seja, pela estrutura do banco, não temos como descobrir qual a data e hora de postagem
# do tweet original. Isso é um problema de modelagem do banco.
# Ainda que o tweet original esteja na tabela tweets, não existe nenhuma referência explícita que nos
# leve de um registro na tabela retweets a um registro na tabela tweets....
# ...hummmm... nenhum referência explícita... Mas, e implícita? E se usássemos o texto do
# retweet pra tentar localizar o tweet original? Não é confiável, mas pode ser uma.
# Olha a regex de volta! Vejam o que fiz:
mysql> select * from retweets where text regexp('RT @.*:') limit 10;
+----+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
| id | actor_id | mention_id | text | user_screen_name | rt_date | tweet_id |
+----+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
| 1 | 1 | 105019 | RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | cristiancastrof | 2011-01-05 15:46:37 | NULL |
| 2 | 1 | 105020 | RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, // y q si al final se la pagariamos igual todos quien le paga el sueldo a ese | carlosampuero | 2011-01-05 15:47:04 | NULL |
| 3 | 1 | 105021 | RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | carlosampuero | 2011-01-05 15:47:10 | NULL |
| 4 | 1 | 105022 | RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | waldocarrasco | 2011-01-05 15:47:50 | NULL |
| 5 | 1 | 105024 | RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | marisademartini | 2011-01-05 15:48:06 | NULL |
| 6 | 1 | 105025 | RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | barrocoaustral | 2011-01-05 15:48:13 | NULL |
| 7 | 1 | 105028 | RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | chripalma | 2011-01-05 15:50:19 | NULL |
| 10 | 1 | 105037 | RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | NICOMARTINEZ666 | 2011-01-05 16:05:11 | NULL |
| 11 | 1 | 105038 | RT @camila_vallejo: La U de Chile en el 3er lugar del ranking de utilidades? Y aún sostienen el 2% adicional en el alza de aranceles de ... | NICOMARTINEZ666 | 2011-01-05 16:05:13 | NULL |
| 12 | 1 | 105039 | RT @camila_vallejo: Jaja, que me pille la tecnologia del twiter no significa que me hagan bulliyng | NICOMARTINEZ666 | 2011-01-05 16:05:40 | NULL |
+----+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
10 rows in set (0.00 sec)
# e agora...
mysql> select * from retweets where text not regexp('RT @.*:') limit 10;
+-----+----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
| id | actor_id | mention_id | text | user_screen_name | rt_date | tweet_id |
+-----+----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
| 8 | 1 | 105035 | "@camila_vallejo: Que caradura @JoaquinLavin, aceptando la beca de ped que obtuvo su hija, como si la necesitara."/se la gano con sus ptjes | Yalilesaid | 2011-01-05 16:02:13 | NULL |
| 9 | 1 | 105036 | RT“@camila_vallejo: Que caradura ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija// la obtuvo en buena lid!! | carlosmilenko | 2011-01-05 16:03:11 | NULL |
| 53 | 1 | 107244 | @camila_vallejo: inteligente, paciente, clara, y guapa!!! las tiene todas para sacar adelante la lucha de los estudiantes. | pablodiazdelrio | 2011-06-06 03:46:29 | NULL |
| 55 | 1 | 107322 | "@camila_vallejo: La Sra. Mirta, alcaldeza de la UDI de Iquique, acaba...//ult tweet 29 abril, ha escrito 37 y "alcaldeza" con Z! No pues! | paulinasalman | 2011-06-06 03:49:39 | NULL |
| 83 | 1 | 107811 | "@camila_vallejo: he regresado al twitter!!!!!" // es lo que muchos esperábamos! | NicolasAndres1 | 2011-06-06 12:10:40 | NULL |
| 84 | 1 | 107815 | "@camila_vallejo: he regresado al twitter!!!!!"// welcome to the jungle! | conejomalon | 2011-06-06 12:36:19 | NULL |
| 95 | 1 | 108114 | “@camila_vallejo: he regresado al twitter!!!!!” Siguiendote! | udoscl | 2011-06-08 00:21:33 | NULL |
| 99 | 1 | 108272 | “@camila_vallejo: he regresado al twitter!!!!!” // Oh no! | roltramarib | 2011-06-10 22:38:55 | NULL |
| 102 | 1 | 108275 | @roltramarib “@camila_vallejo: he regresado al twitter!!!!!” // Oh no! // NOOOOO.! | J_Pizarro | 2011-06-10 22:51:52 | NULL |
| 104 | 1 | 108277 | @J_Pizarro @roltramarib @camila_vallejo: he regresado al twitter!!!!!” // Oh no! // NOOOOO.! // NOOOO POR FAVOOOOORRR!!!!.... | MarioChannels | 2011-06-10 23:13:51 | NULL |
+-----+----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
10 rows in set (0.00 sec)
# Reparem que nem todos os retweets seguem o padrão "RT @usuario: blablablablab". Isso é um problema pra nós.
# Mas, vamos fazer vistas grossas pra isso por enquanto, já que a quantidade de registros no segundo caso
# corresponde apenas a 3% dos retweets totais. Vejam:
mysql> select count(*) from retweets where text regexp('RT @.*:') ;
+----------+
| count(*) |
+----------+
| 889426 |
+----------+
1 row in set (4.50 sec)
mysql> select count(*) from retweets where text not regexp('RT @.*:') ;
+----------+
| count(*) |
+----------+
| 32474 |
+----------+
1 row in set (4.35 sec)
# Xô aprensentar agora a função LENGTH() do SQL pra vocês.
mysql> select length('alexandre');
+---------------------+
| length('alexandre') |
+---------------------+
| 9 |
+---------------------+
1 row in set (0.00 sec)
mysql> select text, length(text) from retweets where text regexp('RT @.*:') limit 10;
+-----------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| text | length(text) |
+-----------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 134 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, // y q si al final se la pagariamos igual todos quien le paga el sueldo a ese | 137 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 134 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 134 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 134 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 134 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 134 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 134 |
| RT @camila_vallejo: La U de Chile en el 3er lugar del ranking de utilidades? Y aún sostienen el 2% adicional en el alza de aranceles de ... | 141 |
| RT @camila_vallejo: Jaja, que me pille la tecnologia del twiter no significa que me hagan bulliyng | 98 |
+-----------------------------------------------------------------------------------------------------------------------------------------------+--------------+
10 rows in set (0.01 sec)
# Vamos entender agora a função POSITION()
mysql> select text, position(':' in text) from retweets where text regexp('RT @.*:') limit 10;
+-----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+
| text | position(':' in text) |
+-----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 19 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, // y q si al final se la pagariamos igual todos quien le paga el sueldo a ese | 19 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 19 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 19 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 19 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 19 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 19 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 19 |
| RT @camila_vallejo: La U de Chile en el 3er lugar del ranking de utilidades? Y aún sostienen el 2% adicional en el alza de aranceles de ... | 19 |
| RT @camila_vallejo: Jaja, que me pille la tecnologia del twiter no significa que me hagan bulliyng | 19 |
+-----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+
10 rows in set (0.00 sec)
# Agora, a função SUBSTR()...
mysql> select text, substr(text, 20, 10) from retweets where text regexp('RT @.*:') limit 10;
+-----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+
| text | substr(text, 20, 10) |
+-----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | Que carad |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, // y q si al final se la pagariamos igual todos quien le paga el sueldo a ese | Que carad |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | Que carad |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | Que carad |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | Que carad |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | Que carad |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | Que carad |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | Que carad |
| RT @camila_vallejo: La U de Chile en el 3er lugar del ranking de utilidades? Y aún sostienen el 2% adicional en el alza de aranceles de ... | La U de C |
| RT @camila_vallejo: Jaja, que me pille la tecnologia del twiter no significa que me hagan bulliyng | Jaja, que |
+-----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+
Juntando tudo....
mysql> select text, position(':' in text), length(text), (length(text)-position(':' in text)) from retweets where text regexp('RT @.*:') limit 10;
+-----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+--------------+--------------------------------------+
| text | position(':' in text) | length(text) | (length(text)-position(':' in text)) |
+-----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+--------------+--------------------------------------+
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 19 | 134 | 115 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, // y q si al final se la pagariamos igual todos quien le paga el sueldo a ese | 19 | 137 | 118 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 19 | 134 | 115 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 19 | 134 | 115 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 19 | 134 | 115 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 19 | 134 | 115 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 19 | 134 | 115 |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | 19 | 134 | 115 |
| RT @camila_vallejo: La U de Chile en el 3er lugar del ranking de utilidades? Y aún sostienen el 2% adicional en el alza de aranceles de ... | 19 | 141 | 122 |
| RT @camila_vallejo: Jaja, que me pille la tecnologia del twiter no significa que me hagan bulliyng | 19 | 98 | 79 |
+-----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+--------------+--------------------------------------+
10 rows in set (0.00 sec)
# E, finalmente.
mysql> select text, substr(text,position(':' in text)+1) from retweets where text regexp('RT @.*:') limit 10;
+-----------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------+
| text | substr(text,position(':' in text)+1) |
+-----------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------+
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, // y q si al final se la pagariamos igual todos quien le paga el sueldo a ese | Que caradura el ministro @JoaquinLavin, // y q si al final se la pagariamos igual todos quien le paga el sueldo a ese |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. |
| RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. | Que caradura el ministro @JoaquinLavin, aceptando la beca de pedagogía que obtuvo su hija, como si la necesitara. |
| RT @camila_vallejo: La U de Chile en el 3er lugar del ranking de utilidades? Y aún sostienen el 2% adicional en el alza de aranceles de ... | La U de Chile en el 3er lugar del ranking de utilidades? Y aún sostienen el 2% adicional en el alza de aranceles de ... |
| RT @camila_vallejo: Jaja, que me pille la tecnologia del twiter no significa que me hagan bulliyng | Jaja, que me pille la tecnologia del twiter no significa que me hagan bulliyng |
+-----------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)
# Aí vocês se perguntam, e pra quê isso, né?
# Isso é pra eu tentar chegar ao texto do tweet original. Agora vamos usa-lo pra tentar identificar
# seu respectivo registro na tabela tweets.
# Antes disso, só mais dois detalhes do SQL: LIKE e CONCAT()
mysql> select * from retweets where text like '%ese' limit 10;
+--------+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
| id | actor_id | mention_id | text | user_screen_name | rt_date | tweet_id |
+--------+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
| 2 | 1 | 105020 | RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, // y q si al final se la pagariamos igual todos quien le paga el sueldo a ese | carlosampuero | 2011-01-05 15:47:04 | NULL |
| 429187 | 31 | 1133381 | RT @feliperasa: Ripley es claramente una empresa vergonzosa, no gaste lo poco que tiene -o su margen de endeudamiento- en un lugar como ese | FVillanelo | 2013-08-20 14:59:30 | NULL |
| 429584 | 33 | 1024067 | RT @EliasLonconado: Utilidad pública: La hora es 00:58, si usted ve que tiene otra hora... preocupese | FEUTEM | 2012-03-11 04:05:26 | NULL |
| 559989 | 79 | 1141295 | RT @felipevargasr: La música de la @RitoqueFM apaña caleta en estas horas de trabajo en la @feutfsm :D cc: @simonconese | simonconese | 2013-08-26 22:15:55 | NULL |
+--------+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
4 rows in set (4.81 sec)
mysql> select * from retweets where text like '%y q si al final%' limit 10;
+----+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
| id | actor_id | mention_id | text | user_screen_name | rt_date | tweet_id |
+----+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
| 2 | 1 | 105020 | RT @camila_vallejo: Que caradura el ministro @JoaquinLavin, // y q si al final se la pagariamos igual todos quien le paga el sueldo a ese | carlosampuero | 2011-01-05 15:47:04 | NULL |
+----+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
1 row in set (3.55 sec)
mysql> select concat('ale','r','gomes');
+---------------------------+
| concat('ale','r','gomes') |
+---------------------------+
| alergomes |
+---------------------------+
1 row in set (0.00 sec)
# E então...
mysql> select t.id, t.tweet_date, t.user_screen_name, t.text, r.id from tweets as t, retweets as r where t.text like concat('%',r.text,'%') limit 10;
^[[C+------+--------------------------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+--------+
| id | tweet_date | user_screen_name | text | id |
+------+--------------------------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+--------+
| 1142 | Thu Dec 29 18:07:06 +0000 2011 | FEUCEN2012 | RT @panchofigueroa: Luis Mayol, aparte de ex pdte de la SNA, no es hermano de Manfredo Mayol, artífice de los nobles montajes comunicaci ... | 182039 |
| 1142 | Thu Dec 29 18:07:06 +0000 2011 | FEUCEN2012 | RT @panchofigueroa: Luis Mayol, aparte de ex pdte de la SNA, no es hermano de Manfredo Mayol, artífice de los nobles montajes comunicaci ... | 182040 |
| 1142 | Thu Dec 29 18:07:06 +0000 2011 | FEUCEN2012 | RT @panchofigueroa: Luis Mayol, aparte de ex pdte de la SNA, no es hermano de Manfredo Mayol, artífice de los nobles montajes comunicaci ... | 182041 |
| 1142 | Thu Dec 29 18:07:06 +0000 2011 | FEUCEN2012 | RT @panchofigueroa: Luis Mayol, aparte de ex pdte de la SNA, no es hermano de Manfredo Mayol, artífice de los nobles montajes comunicaci ... | 182042 |
| 1142 | Thu Dec 29 18:07:06 +0000 2011 | FEUCEN2012 | RT @panchofigueroa: Luis Mayol, aparte de ex pdte de la SNA, no es hermano de Manfredo Mayol, artífice de los nobles montajes comunicaci ... | 182043 |
| 1142 | Thu Dec 29 18:07:06 +0000 2011 | FEUCEN2012 | RT @panchofigueroa: Luis Mayol, aparte de ex pdte de la SNA, no es hermano de Manfredo Mayol, artífice de los nobles montajes comunicaci ... | 182044 |
| 1142 | Thu Dec 29 18:07:06 +0000 2011 | FEUCEN2012 | RT @panchofigueroa: Luis Mayol, aparte de ex pdte de la SNA, no es hermano de Manfredo Mayol, artífice de los nobles montajes comunicaci ... | 182045 |
| 1142 | Thu Dec 29 18:07:06 +0000 2011 | FEUCEN2012 | RT @panchofigueroa: Luis Mayol, aparte de ex pdte de la SNA, no es hermano de Manfredo Mayol, artífice de los nobles montajes comunicaci ... | 182046 |
| 1142 | Thu Dec 29 18:07:06 +0000 2011 | FEUCEN2012 | RT @panchofigueroa: Luis Mayol, aparte de ex pdte de la SNA, no es hermano de Manfredo Mayol, artífice de los nobles montajes comunicaci ... | 182047 |
| 1142 | Thu Dec 29 18:07:06 +0000 2011 | FEUCEN2012 | RT @panchofigueroa: Luis Mayol, aparte de ex pdte de la SNA, no es hermano de Manfredo Mayol, artífice de los nobles montajes comunicaci ... | 182048 |
+------+--------------------------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+--------+
10 rows in set (10 min 4.75 sec)
# Reparem que:
# 1. Cada linha refere-se a um retweet. Vejam seus ids na última coluna.
# 2. Todos os retweets referem-se ao mesmo tweet (1142)
mysql> select * from tweets where id = 1142;
+------+--------------------------------+-----------+-------------------------+---------------------------------------------------------+------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+-----------------------+--------------------------------+----------------------+------------+------------------+----------------+----------+
| id | tweet_date | favorited | in_reply_to_screen_name | permanent_link | lang | source | text | user_profile_description | user_profile_location | user_date | user_followers_count | user_name | user_screen_name | user_time_zone | actor_id |
+------+--------------------------------+-----------+-------------------------+---------------------------------------------------------+------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+-----------------------+--------------------------------+----------------------+------------+------------------+----------------+----------+
| 1142 | Thu Dec 29 18:07:06 +0000 2011 | 0 | NULL | http://twitter.com/FEUCEN2012/status/152450586820882433 | NULL | web | RT @panchofigueroa: Luis Mayol, aparte de ex pdte de la SNA, no es hermano de Manfredo Mayol, artífice de los nobles montajes comunicaci ... | NULL | NULL | Thu Nov 03 22:27:05 +0000 2011 | 27 | FEUCEN2012 | FEUCEN2012 | Santiago | 110 |
+------+--------------------------------+-----------+-------------------------+---------------------------------------------------------+------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+-----------------------+--------------------------------+----------------------+------------+------------------+----------------+----------+
1 row in set (0.00 sec)
# 3. O texto do tweet original é um "RT @...". Estranho isso, hein? Quer dizer um retweet foi retweetado?
# Vamos analisar melhor um desses retweets (182039)
mysql> select * from retweets where id = 182039;
+--------+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
| id | actor_id | mention_id | text | user_screen_name | rt_date | tweet_id |
+--------+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
| 182039 | 2 | 2500605 | RT @panchofigueroa: Luis Mayol, aparte de ex pdte de la SNA, no es hermano de Manfredo Mayol, artífice de los nobles montajes comunicaci ... | IgnacioIriarte | 2011-12-29 18:05:09 | NULL |
+--------+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+----------+
1 row in set (0.01 sec)
# Vejamos o mention 2500605 a que o retweet 182039 se refere:
mysql> select * from mentions where id = 2500605;
+---------+--------------------------------+-----------+-------------------------+-------------------------------------------------------------+------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------+-----------------------+--------------------------------+----------------------+-----------------+------------------+----------------+----------+
| id | mention_date | favorited | in_reply_to_screen_name | permanent_link | lang | source | text | user_profile_description | user_profile_location | user_date | user_followers_count | user_name | user_screen_name | user_time_zone | actor_id |
+---------+--------------------------------+-----------+-------------------------+-------------------------------------------------------------+------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------+-----------------------+--------------------------------+----------------------+-----------------+------------------+----------------+----------+
| 2500605 | Thu Dec 29 18:05:09 +0000 2011 | 0 | NULL | http://twitter.com/IgnacioIriarte/status/152450092715094016 | NULL | web | RT @panchofigueroa: Luis Mayol, aparte de ex pdte de la SNA, no es hermano de Manfredo Mayol, artífice de los nobles montajes comunicaci ... | Periodista porfiado, interesado en asociatividad, política y afectos | Chile | Fri Jun 18 23:17:33 +0000 2010 | 3148 | Ignacio Iriarte | IgnacioIriarte | Santiago | 2 |
+---------+--------------------------------+-----------+-------------------------+-------------------------------------------------------------+------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------+-----------------------+--------------------------------+----------------------+-----------------+------------------+----------------+----------+
1 row in set (0.01 sec)
# Aparentemente, então, o @IgnacioIriarte retweetou o @FEUCEN2012 que retweetou @panchofigueroa.
# Voltando à vaca fria então, a Marisa queria a data do tweet na listagem da quantificação dos retweets.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment