Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a system to assign score for each sentence stored in a column:

TABLE 1: word_scores

word       score
this       3
is         4
a          5
test       1
another    0
sentence   8

TABLE 2: sentences (score column is calculated from TABLE 1)

sentence           score
this is a test     13
another sentence   8
this is            7

Now I need to 1. fetch each sentence into PHP, 2. then split the string to words, 3. then catch the score for each word, 4. then calculate the sum of word_scores

Is it possible to calculate the sentence score within mysql?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

Bascially yes, there's a way to do that.

Here's a select statement that gets you each sentence with it's score

SELECT
PARSED_SENTENCES.sentence, SUM(COALESCE(WordScores.score, 0)) score
FROM (
     SELECT sentence,
     SUBSTRING(
          sentence
          FROM CASE
               WHEN INDEX_TABLE.POS = 1 THEN 1
               ELSE INDEX_TABLE.POS + 1
               END
          FOR CASE LOCATE(' ', sentence, INDEX_TABLE.POS + 1)
              WHEN 0 THEN CHARACTER_LENGTH(sentence) + 1
              ELSE LOCATE(' ', sentence, INDEX_TABLE.POS + 1)
              END
              - CASE
                WHEN INDEX_TABLE.POS = 1 THEN 1
                ELSE INDEX_TABLE.POS + 1
                END
     ) AS word
     FROM SentenceScores
     INNER JOIN (
          SELECT @rownum:=@rownum+1 POS
          FROM (
             SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
             UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
             UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          ) a, (
             SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
             UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
             UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          ) b, (
             SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
             UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
             UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          ) c, (SELECT @rownum:=0) r
     ) INDEX_TABLE
     ON INDEX_TABLE.POS <= CHAR_LENGTH(SentenceScores.sentence)
     AND (
          INDEX_TABLE.POS = 1
          OR SUBSTRING(SentenceScores.sentence FROM INDEX_TABLE.POS FOR 1) = ' '
     )
) AS PARSED_SENTENCES
LEFT OUTER JOIN WordScores
ON PARSED_SENTENCES.word = WordScores.word
GROUP BY PARSED_SENTENCES.sentence;

You should be able to convert that to an update statement so that you could calculate the scores and apply them to the table at the same time.

Per the comment discussion below, if you wanted to replace the numbers-table subquery with a static numbers table you could replace this part of the query:

 INNER JOIN (
      SELECT @rownum:=@rownum+1 POS
      FROM (
         SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
         UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
         UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
      ) a, (
         SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
         UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
         UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
      ) b, (
         SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
         UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
         UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
      ) c, (SELECT @rownum:=0) r
 ) INDEX_TABLE

with just:

INNER JOIN INDEX_TABLE

where your numbers table and number column are assumed to have the same names as in the subquery, i.e. INDEX_TABLE and POS

share|improve this answer
    
Isn't there a better way to set up numer table? A CTE if nothing else helps, for exapmle? –  dezso Dec 12 '12 at 9:23
    
"better" depends on your personal preferences or your specific requirements. You mention CTE (common table expression) which MySQL doesn't seem to support.Sure, there's a better way. But that's kind of subjective. If you don't like this way, or it doesn't work for your specific requirements you could for example create a static table, or a temp table –  JM Hicks Dec 12 '12 at 9:29
    
Oh, my bad, somehow I thought this is for SQL Server. My problem (not very serious though :) is that those UNIONs are ugly and repeated three times. –  dezso Dec 12 '12 at 9:32
    
I accidentally submitted my comment while I was still editing, and couldn't change it :-( I just wanted to add that a static table for example, would probably be better performance, but I wouldn't normally think of refactoring the query as such because I think someone asking this kind of question probably doesn't already have a numbers table in their database, and adding a new table is sometimes a political challenge if you're working in a team. But if adding a new table isn't such a challenge then static numbers table would be a nice way to go. –  JM Hicks Dec 12 '12 at 9:55

Under some reasonable assumptions:

select sentence, SUM(score) score
    from sentences join word_scores
    on sentence like concat('% ',word,' %')  
    or sentence like concat(word,' %')  
    or sentence like concat('% ',word)
    group by sentence;
share|improve this answer
1  
This answer looks really nice because it's small and neat. But I see concerns, lesser and greater: 1) the user would turn the 'join' into a left join to get 0-value resuts 2) multiple same 'word' occurences would only count once per sentence 3) one word sentences wouldn't be matched 4) 2 out of 3 like expressions would not work with any index on column word 5) complexity will not be less than than, [word_scores] X [sentences] (O(M X N)), instead of [sentences] * word_scores-btree-lookup (O(N log M)), if we suppose a large enough word_scores table would have an efficient index on word column. –  JM Hicks Dec 12 '12 at 20:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.