 |
 |
The righteous crimson king solved all the problems Your evil red king over taxes us. -----Original Message----- From: Code for Libraries [mailto:[log in to unmask]] On Behalf Of Eric Lease Morgan Sent: Saturday, July 22, 2017 10:36 AM To: [log in to unmask] Subject: Re: [CODE4LIB] good kings & bad kings: an sql query [resolved] On Jul 22, 2017, at 8:47 AM, Andreas Orphanides <[log in to unmask]> wrote: >> How might I write an SQL query to find all the descriptions of good >> kings and bad kings in my database? >> >> I have a table that looks something like this: >> >> key word part_of_speech position >> --- --------- -------------- -------- >> w1 The article 1 >> w2 righteous adjective 2 >> w3 king noun 3 >> w4 solved verb 4 >> w5 all adjective 5 >> w6 the article 6 >> w7 problems noun 7 >> w8 Your noun 8 >> w9 evil adjective 9 >> w10 king noun 10 >> w11 over adverb 11 >> w12 taxes verb 12 >> w13 us pronoun 13 > > In a perfect world, your parser would include the referents for the > adjectives, which would solve the problem lickety split, but I assume > that's not an option here. Are you ok with only discovering > adjective-noun pairs?... > > Add a sequentially numbered autogen column that's pure integer. > (Unless you have string manipulation -- in which case you could just work on the "key" > column). > > Then something like: > > SELECT * FROM table WHERE autogen IN (SELECT (autogen-1) FROM table > WHERE word = "king") AND part_of_speech=“adjective" Resolved, and the secret sauce was the SQL qualifier IN. To find all positions of the word “king”, I can do this: SELECT position FROM words WHERE word=“king”; To find all the positions prior to the word “king”, I can do this: SELECT position-1 FROM words WHERE word=“king”; I can then use the IN qualifier to use the results of the previous query as input for a subsequent query. Thus I can find all the words prior to the word “king”; SELECT word FROM words WHERE position IN (SELECT position-1 FROM words WHERE word=“king”); I can then further limit my query to only list adjectives: SELECT word FROM words WHERE part_of_speach=“adjective” AND position IN (SELECT position-1 FROM words WHERE word=“king”); I can then count and group the results as an ordered list: SELECT COUNT(word) AS c, word FROM words WHERE part_of_speach=“adjective” AND position IN (SELECT position-1 FROM words WHERE word=“king”) GROUP BY word ORDER BY c DESC; Since my database includes the words from my entire corpus (60,000,000 words), I need to further limit my query to document identifiers. Moreover, my parts-of-speech are not as simple as “noun”, “verb”, and “adjective”. Without going into too much detail, my cool query ultimately looks like this: SELECT COUNT(word) AS c, word FROM words WHERE did='A05406' AND pos LIKE 'j%' AND (position IN (SELECT position-1 FROM words WHERE (word='king' AND did='A05406'))) GROUP by word ORDER by c DESC; In this particular case, kings are described as both earthly and good. I can then search my index for documents of interest, and then I can feed my SQL query an identifier as well as a few words. For example, a humorous document on the topic of love describes men and women: [1] SELECT COUNT(word) AS c, word FROM words WHERE did='A13520' AND pos LIKE 'j%' AND (position IN (SELECT position-1 FROM words WHERE((LOWER(word)='man' OR LOWER(word)='men' OR LOWER(word)='woman' OR LOWER(word)='women') AND did='A13520'))) GROUP BY word ORDER BY c DESC; c word = ========= 5 old 5 young 4 poore 3 good 2 Old 1 Good 1 Great 1 Rich 1 Seruing 1 best 1 great 1 labouring 1 little 1 painfull 1 proper 1 singing 1 wise Apparently, when it comes to love, age doesn’t matter, men & women are poor, and at the same time, they are not very wise. ;-) Fun with SQL. Thank you. Code4lib++ [1] humorous document - http://cds.crc.nd.edu/freebo/A13/A13520/A13520.htm — Eric Morgan