Search code examples
sqlamazon-athenaprestotrino

Presto/Trino/Athena - Wrong subtraction of casted varchar to double

I am using AWS Athena and trying to calculate the weight loss of each user between two samples. My weight column is varchar, so I cast it into double and then subtract them. I am using the following query:

SELECT t1.user_id, t1.sample_id, t1.weight, 
  cast(t1.weight, double) - cast(t2.weight, double) AS weight_loss
FROM my_table t1
JOIN my_table t2 ON t1.user_id = t2.user_id AND t1.sample_id - 1 = t2.sample_id
ORDER BY t1.user_id, t1.sample_id

and I get the folowing result: enter image description here

Why does the calculated weight looks like this with so many floating points?

Solution

  • Decimal Data Type in Presto is the tool which can slove your problem.

    See the following code as example:

    SELECT t1.user_id, t1.sample_id, t1.weight, 
  cast(t1.weight, DECIMAL(10,1)) - cast(t2.weight, DECIMAL(10,1)) AS weight_loss
FROM my_table t1
JOIN my_table t2 ON t1.user_id = t2.user_id AND t1.sample_id - 1 = t2.sample_id
ORDER BY t1.user_id, t1.sample_id