Is there a way to sum two columns into another column in Hive HQL?

Question

I'm looking to get a running daily, weekly, and monthly sum of the number of messages I sent out. There are about 500 different message types.

I have the following tables:

Table name: messages

int message_type
BIGINT num_sent
string date

Table name: stats

int message_type
BIGINT num_sent_today
BIGINT num_sent_week
BIGINT num_sent_month

Table messages is updated daily with new rows for today's date. Is there a single hive query I can run daily to update the stats table? Note I can't get the running counts by querying the messages table directly using WHERE date >= 30 days ago because the table is too big. I have to add/subtract daily values from table stats instead. Something like this:

// pseudocode
// Get this table (call it table b) from table messages

   int message_type
   BIGINT num_sent_today
   BIGINT num_sent_seven_days_ago
   BIGINT num_sent_thirty_days_ago

// join b with table stats so that I can

// Set stats.num_sent_today = b.num_sent_today
// Set stats.num_sent_week = stats.num_sent_week + b.num_sent_today - b.num_sent_seven_days_ago
// Set stats.num_sent_month = stats.num_sent_month + b.num_sent_today - b.num_sent_thirty_days_ago

Answer 1

looks like I can just directly add the columns with +