Mysql error 1064 Using SQLVars

Question

After read this thread: MySQL - Subtracting value from previous row, group by

I used the method described in the most upvoted answer and wrote the following code:

SELECT 
      T.id_str, 
      T.created_at, 
      T.retweet_count, 
      T.tweeted_at,
      if (@lastid_str = T.id_str, T.retweet_count - @lastretweet_count, 0000.00) as RetweetChange,
      @lastid_str := T.id_str,
      @lastretweet_count := T.retweet_count
   FROM 
      Tweets T, 
      (SELECT @lastid_str := 0, 
              @lastretweet_count :=0 ) SQLVars
   ORDER BY 
      T.tweeted_at DESC, 
      T.id_str;

Old problem solved, new problem: how to write a trigger.

My table looks like this. Where id_str is a unique identifier for a specific tweet. Since I am inserting 50 tweets from a single user every minute, there would be many same id_str. What I want to look at is the change of retweet_count every minute. tweeted_at is when the user tweeted, created_at is when this data is inserted into my database. I want to generate retweet_change for each new data inserted into the database compared to the same old tweet (into the column retweet_change). How should I write the trigger?

You were missing a comma after your "T.tweeted_at" column. Now see if it works or not as you anticipate. Having a clean formatted readable query help miss simple things like this :) but it is just getting in a habit of formatting as you learn. Also, I think you need to change the order by for the ID_Str first as that appears to be the grouping basis of your IF() comparison — DRapp, Sep 05 '17 at 23:33
it worked! Thank you. I can't imagine it was such a stupid error. — Dylan, Sep 06 '17 at 00:45
Instead of doing a query for this task, is there a way to create a generated column so that I can have the data automatically calculated when I insert them in to MySQL? My database is designed as following: Insert 50 new rows each minute, I have a primary key that is auto-increment. So I guess I can do something as: choose the latest inserted 50 rows, (Max(id) to Maxid - 50) compare them with the 50 rows inserted previously (Max(id)-50, Max(id)-100) if the id_str is the same compute the retweet difference? — Dylan, Sep 06 '17 at 17:37

score 0 · Answer 1 · answered Sep 06 '17 at 18:04

Especially in web-based solutions where counters and aggregates are common, and data is somewhat static (i.e.: old data not changing, being deleted, etc) and new data can be added to, triggers can help.

You could have a few columns added where appropriate that you want these aggregates. Then, when a new record is added into one table, the summary table (or parent ID such as a retweet) could be updated with 1 more, or a sum if other such as financial, order qty, etc. This would simplify your counts as the totals would always be running as an entry is added.

You would just need to prep the counts once the triggers are in place by pre-aggregating and updating the records in question, then the rest just continues to keep in synch.

Sorry, I am kind of new to this. Could you give me some sample code how should I write this trigger? I have updated the information in my table. Thank you so much! — Dylan, Sep 06 '17 at 18:22

Mysql error 1064 Using SQLVars

1 Answers1