Numbers: INT64, FLOAT64 and NUMERIC
Prices, quantities and rates are numbers, but BigQuery keeps them in three types, and a wrong pick gives an answer that is a unit or a cent off without raising an error. This lesson uses two tables of learn.shop, the online shop from the time zones lesson: products has a list price for each product, and order_items has one row per line of an order, with the quantity bought and the unit_price paid.
How it works
INT64 holds whole numbers. NUMERIC holds decimals exactly, with up to 9 digits after the point, which suits money. FLOAT64 holds a decimal as the nearest binary fraction, so it covers a huge range but is approximate. In learn.shop, quantity is an INT64, and price and unit_price are NUMERIC. The type of a result follows the types that go in, and some functions change it, so check the type of a number before you trust a total.
- An operation on an
INT64and aNUMERICgives aNUMERIC, and any operation with aFLOAT64gives aFLOAT64. A decimal written in the query, such as0.075, is aFLOAT64unless the other side of its own operation is aNUMERIC, and*runs left to right. Sounit_price * 0.075is aNUMERIC, butquantity * 0.075 * unit_priceis aFLOAT64, because the decimal meetsquantityfirst.(1 + 0.075) * unit_priceis aFLOAT64too, because the decimal meets the1. AFLOAT64also comes from aFLOAT64column, from a cast such asCAST(unit_price AS FLOAT64), or from/on two integers. /on twoINT64values returns aFLOAT64, even though both inputs are integers.7 / 2is 3.5, and6 / 3is 2.0, with a fraction part even though it is zero. OnNUMERICvalues/returns aNUMERICrounded to 9 digits after the point, soNUMERIC '1' / 3 * 3is 0.999999999.DIV(a, b)divides whole numbers and drops the fraction.DIV(7, 2)is 3, anINT64, andDIV(-7, 2)is -3.MOD(a, b)is the remainder, soMOD(7, 2)is 1.- A zero divisor stops the query with a
division by zeroerror, for/and forDIV.SAFE_DIVIDE(a, b), from the COUNTIF lesson, returnsNULLinstead. ROUND(x, n)rounds tondigits after the point. ANUMERICstays aNUMERICand aFLOAT64stays aFLOAT64. A half goes to the next number away from zero, soROUND(2.5)is 3.0, aFLOAT64, because2.5is one.TRUNC(x, n)cuts the digits afternplaces without rounding, so it moves toward zero, andTRUNC(2.5)is 2.0.CAST(x AS INT64)returns anINT64and rounds the wayROUNDdoes. It does not cut the fraction off. PutTRUNCinside the cast when you want the whole part.- A
FLOAT64stores0.075and24.27as the nearest binary fractions, which sit a little above or below the decimal. AROUNDon them can land on the wrong side of a half cent, and aSUMof them can end in stray digits. The same calculation onNUMERICis exact.
Common mistake
CAST(19.99 AS INT64) is 20, because the cast rounds. A point count or a box count built this way is one too high whenever the fraction is a half or more. The query runs and the totals look close. Cut with TRUNC or DIV first, then cast when the column must be an INT64.
Worked example
SELECT order_id, product_id, quantity, unit_price,
unit_price * quantity * 0.075 AS tax_unrounded,
ROUND(unit_price * quantity * 0.075, 2) AS tax_numeric,
ROUND(CAST(unit_price AS FLOAT64) * quantity * 0.075, 2) AS tax_float
FROM `learn.shop.order_items`
WHERE order_id IN (1, 72)
ORDER BY order_idA sales tax of 7.5% is worked out on each line and rounded to the cent. Both lines are 4 units. Before rounding the tax is 7.281 on the first and 5.985 on the second, and tax_unrounded is a NUMERIC.
The first line gives 7.28 in both types. The second is exactly half a cent, so NUMERIC rounds it up to 5.99. The FLOAT64 copy of 19.95 is a little under 19.95, so its tax falls a hair below the half cent and rounds down to 5.98. The query raises no error.