Hive query language has a PMOD function which can be used to calculate the remainder. This function works perfectly fine with integers and double. For example, calculate the remainder when 4.7 is divided by 2.
SELECT PMOD(4.7,2);
The answer to this query in hive query language is 0.7
Google big query has MOD function to calculate the remainder. But this function only works with integers.
SELECT MOD(4.7,2); does not work in google big query.
It is possible to cast 4.7 as INT64 in big query. Then the MOD function will work but the answer will be 1 and not 0.7
Any help to calculate the remainder with floating data points in google big query will be very useful.
Calculating remainder of a floating point value is an interesting concept, personally I have never heard about it so far.
According to your description it is basically all about finding remainder of an integer part and adding up a fractional part to the result?
If so, it could probably be imitated by using the following expression (in your example): MOD(FLOOR(4.7),2) + 4.7 - FLOOR(4.7) considering that the integer part is FLOOR(4.7) and the fractional part is thus 4.7 - FLOOR(4.7)
UPD. This one assumed the non-negative floating point values, in case of negative values the fractional part is obviously value - CEIL(value). Using value - TRUNC(value) would generalize finding fractional part for both cases, but I am not pretty sure about the outcome of MOD() on negative values so I would leave it to you