MYSQL CODE
Mon May 06 2024 22:28:51 GMT+0000 (Coordinated Universal Time)
Saved by @madgakantara
CREATE TABLE stats_table ( id INT AUTO_INCREMENT PRIMARY KEY ); SELECT user_mention0_name, user_mention0_id, COUNT(*) AS num_rows FROM tweets_extend WHERE user_mention0_id IN (56377143, 106062176, 18332190, 22536055, 124476322, 26223583, 2182373406, 38676903, 1542862735, 253340062, 218730857, 45621423, 20626359) GROUP BY user_mention0_name, user_mention0_id ORDER BY num_rows DESC; ALTER TABLE stats_table ADD COLUMN num_rows1 INT; INSERT INTO stats_table (user_mention1name, user_mention1id, num_rows1) SELECT COUNT(*) AS num_rows1 FROM tweets_extend WHERE user_mention1_id IN (56377143, 106062176, 18332190, 22536055, 124476322, 26223583, 2182373406, 38676903, 1542862735, 253340062, 218730857, 45621423, 20626359) GROUP BY user_mention1_name, user_mention1_id ORDER BY num_rows1 DESC CREATE TABLE stats_table ( id INT AUTO_INCREMENT PRIMARY KEY, user_mention0_name VARCHAR(255), user_mention0_id INT, user_mention1_name VARCHAR(255), user_mention1_id INT, num_rows0 INT, num_rows1 INT ); INSERT INTO stats_table (user_mention0_name, user_mention0_id, num_rows0) SELECT user_mention0_name, user_mention0_id, COUNT(*) AS num_rows FROM tweets_extend WHERE user_mention0_id IN (56377143, 106062176, 18332190, 22536055, 124476322, 26223583, 2182373406, 38676903, 1542862735, 253340062, 218730857, 45621423, 20626359) GROUP BY user_mention0_name, user_mention0_id ORDER BY num_rows DESC; INSERT INTO stats_table (user_mention1_name, user_mention1_id, num_rows1) SELECT user_mention1_name, user_mention1_id, COUNT(*) AS num_rows1 FROM tweets_extend WHERE user_mention1_id IN (56377143, 106062176, 18332190, 22536055, 124476322, 26223583, 2182373406, 38676903, 1542862735, 253340062, 218730857, 45621423, 20626359) GROUP BY user_mention1_name, user_mention1_id ORDER BY num_rows1 DESC; SELECT num_rows0, num_rows1 FROM stats_table WHERE user_mention0_id = 56377143 OR user_mention1_id = 56377143; -- FOR EXAMPLE TO SEE KLM HOW MANY --TWEEETS IT HAS BEEN MENTIONED ALTER TABLE stats_table ADD COLUMN avg_char0 FLOAT; ALTER TABLE stats_table ADD COLUMN avg_char1 FLOAT; INSERT INTO stats_table (avg_char0, avg_char1) SELECT CASE WHEN user_mention1_id IS NULL THEN user_mention0_id ELSE user_mention1_id END AS user_mention_id, AVG(CHAR_LENGTH(full_text)) AS average_characters FROM tweets_extend GROUP BY user_mention0_id, user_mention1_id;
Comments