![]() ![]() And also, if you're wondering why some systems use numerical identifiers, hopefully this discussion on case-sensitivity makes clear why such decisions are made. * lower case all kinds of screen_names */Īll of the above data-cleaning work is routine in the real world. SET in_reply_to_screen_name = NULL WHERE in_reply_to_status_id IS NULL SET in_reply_to_status_id = NULL WHERE in_reply_to_status_id = ' ' OR in_reply_to_status_id = 0 SET retweeted_status_screen_name = NULL, retweeted_status_user_id = NULL WHERE retweeted_status_id IS NULL SET retweeted_status_id = NULL WHERE retweeted_status_id = ' ' OR retweeted_status_id = 0 ![]() In_reply_to_screen_name = LOWER(in_reply_to_screen_name) Retweeted_status_screen_name = LOWER(retweeted_status_screen_name), * normalize twitter_profiles */ UPDATE twitter_profiles SET youtube_id = NULL WHERE youtube_id = ' ' SET facebook_id = NULL WHERE facebook_id = ' ' SET twitter_screen_name = NULL WHERE twitter_screen_name = ' ' SET twitter_screen_name = LOWER(twitter_screen_name) * normalize social_accounts */ UPDATE social_accounts To be absolutely sure, you can use either the LOWER or UPPER functions to transform text strings before they are compared: Sometimes using LIKE instead of = will be case-insensitive, but again, it depends on your database configuration. However, this query will not be the same for SQLite (and MySQL, if it's configured to be case-sensitive). With my colleague and good friend Dave Joyce in Cleveland, OH and candidate Alex Mooney who's running… With Evan Jenkins in West Virginia, David Rouzer and Mark Walker in North Carolina - 3 great soon to… Post Gov Debate in Peoria with Illinois' next Governor #BringBackIL #stoptheinsanity Sure hope #Rauner is elected so I can pack my bags! #H… Notice how social_accounts.twitter_screen_name is AaronSchock and tweets.screen_name is aaronschock: twitter_screen_nameĬelebrating the Illinois College Homecoming Day Parade #GoBlueboys ON social_accounts.twitter_screen_name = tweets.screen_name SELECT social_accounts.twitter_screen_name, tweets.screen_name, text FROM tweets If your database config happens to be also case-insensitive, the following query will bring up results as expected: The upshot for us is that in the provided database files, the twitter.screen_name in social_accounts may be of a different capitalization than the ones in twitter_profiles.screen_name and tweets.screen_name and tweets. That is, the following URLs will go to the same page: This is a particular problem in this lesson's context, because Twitter screen names are case-insensitive. About case-sensitivityĭepending on your database setup, you may run into issues of case-sensitivity. You might also want to consult the quick tutorial on using UPDATE statements. If you're getting unexpected results, please read the following notes. The next two sub-sections deal with inconsistencies that may exist in the two database files I've provided. SQLite dump of Congress and Twitter data. ![]() MySQL dump of Congress and Twitter data.tweets - The most recent 3,200 tweets of each Twitter profile (about 800,000+ tweets altogether).twitter_profiles - Twitter profile data for the accounts in social_accounts.social_accounts - social account names for current U.S.terms - The terms served by the current U.S.The database we will use consists of five tables: The queries and concepts should be the same as they are with SQLite. Note: For this SQL lesson, I will be using MySQL and the Sequel Pro GUI. This tutorial assumes you've completed all of the basic SQL tutorials The syntax of JOIN is no different from before, so this will be more of a refresher on aggregate queries. We refer to this as a many-to-one relationship. For example, a Twitter account, and by extension, a Congressmember, has many tweets. Not everything has a one-to-one relationship. a Congressmember to his/her Twitter account. In the previous tutorial, we learned the basics of the JOIN syntax and how to join one record from a table to one record from another table, e.g. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |