In this post we are going to cover the maths functions in Snowflake: POWER, SQRT, EXP, LN, LOG, RANDOM and UNIFORM.
These are the functions that do arithmetic we cannot write with + and *. Powers and roots, the exponential and its logarithm, logarithms to a base we choose, and two different ways of asking Snowflake for a random number.
We will start with powers and roots, because those are the ones people reach for most. Then we will do the logarithms, and use one of them for something real. Along the way we will find out that every function on this page hands back a FLOAT, and what that costs us. We will keep RANDOM and UNIFORM for the end, because nothing they give back is a number we can expect to see twice.
Every query below was run on a real Snowflake account, and every result you see is the output it gave back.
Episode 71 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.
- Let's create some sample data
- Let's start with POWER and SQRT
- Now let's try EXP and LN
- Now let's do LOG, and work out which argument is the base
- Now let's use a logarithm for something real
- Now let's do PI, DEGREES and RADIANS
- Let's see what RANDOM actually gives us back
- Now let's ask UNIFORM for a number in a range
- Can we take a random sample?
Let's create some sample data
Most of this page works on numbers we type in, because a function like LOG is easiest to follow when we already know the answer. Where we need real data we will use the AMOUNT column of ORDERS, the same column Episode 70 rounded.
Copy this into a Snowflake worksheet and run it once. Every post in this series uses the same tables.
-- ORDERS comes from the setup script this course uses throughout.
-- Run it once; every episode reuses the same tables.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;
-- AMOUNT is NUMBER(10,2) - an exact decimal, as Episode 70 relied on.
-- Every function in THIS episode returns a FLOAT instead, and that change of
-- type is the most important thing on the page.
-- The CREATE and the INSERT are both in the course setup script.Let's look at a few of those orders.
SELECT ORDER_ID, CUSTOMER_ID, AMOUNT FROM ORDERS ORDER BY ORDER_ID LIMIT 10;| ORDER_ID | CUSTOMER_ID | AMOUNT |
|---|---|---|
| 1001 | 101 | 4200.00 |
| 1002 | 102 | 890.50 |
| 1003 | 101 | 6150.00 |
| 1004 | 103 | 1500.00 |
| 1005 | 104 | 2310.75 |
| 1006 | 101 | 5020.00 |
| 1007 | 105 | 7890.00 |
| 1008 | 102 | 1120.00 |
| 1009 | 103 | 3340.25 |
| 1010 | 106 | 640.00 |
Nothing surprising there. There is one thing to carry forward though. AMOUNT is an exact decimal, which is why ROUND behaved in Episode 70 the way a person would expect. Every function we are about to use hands back a FLOAT instead, and we will see what that does to us when we get to LOG.
Let's start with POWER and SQRT
POWER takes the base first and the exponent second. The exponent does not have to be a positive whole number, and that is most of what makes the function worth knowing.
SELECT POWER(2, 10) AS TWO_TO_THE_10,
POWER(10, 3) AS TEN_CUBED,
POWER(2, 0.5) AS FRACTIONAL_EXPONENT,
POWER(2, -2) AS NEGATIVE_EXPONENT,
POWER(2, 62) AS A_BIG_ONE;| TWO_TO_THE_10 | TEN_CUBED | FRACTIONAL_EXPONENT | NEGATIVE_EXPONENT | A_BIG_ONE |
|---|---|---|---|---|
| 1024.0 | 1000.0 | 1.4142135623730951 | 0.25 | 4.611686018427388e+18 |
A whole exponent does what we expect. FRACTIONAL_EXPONENT shows that a fraction gives us a root, so POWER(2, 0.5) is the square root of two. NEGATIVE_EXPONENT shows that a negative exponent gives us a reciprocal, so POWER(2, -2) is one quarter.
A_BIG_ONE is the warning shot. POWER(2, 62) comes back in scientific notation, because the answer is a FLOAT and not an integer. The exact value of POWER(2, 62) is a whole number that would fit in a 64-bit integer comfortably. What we got back is a float that is close to it. That is lossy, and the same lossiness turns into a practical problem when we get to LOG.
Can we write it with **?
That is how you would write it in Python, so it is worth trying.
SELECT 2 ** 10 AS SHOULD_NOT_GET_THIS_FAR;001003 (42000): SQL compilation error: syntax error line 1 at position 9 unexpected '**'.
A compilation error, naming the token it did not expect. There is no ** operator in Snowflake. It is not a function that returns NULL or zero either. The statement does not compile at all.
Here are the spellings that do work.
SELECT POWER(2, 10) AS POWER_,
POW(2, 10) AS POW_,
POWER(2, 10) = POW(2, 10) AS SAME,
SQUARE(5) AS SQUARE_,
SQUARE(5) = POWER(5, 2) AS SQUARE_IS_POWER_2;| POWER_ | POW_ | SAME | SQUARE_ | SQUARE_IS_POWER_2 |
|---|---|---|---|---|
| 1024.0 | 1024.0 | TRUE | 25.0 | TRUE |
POW is an alias for POWER, and SAME says so. SQUARE(x) is POWER(x, 2), and SQUARE_IS_POWER_2 says so too. Three spellings, one function, and no operator.
Does SQRT agree with POWER?
SQRT(16) and POWER(16, 0.5) are the same question asked two ways, so let's ask Snowflake whether it gives the same answer to both.
SELECT SQRT(16) AS SQRT_16,
POWER(16, 0.5) AS POWER_16_HALF,
SQRT(16) = POWER(16, 0.5) AS SAME,
SQRT(2) AS SQRT_2,
ROUND(SQRT(2), 6) AS SQRT_2_ROUNDED;| SQRT_16 | POWER_16_HALF | SAME | SQRT_2 | SQRT_2_ROUNDED |
|---|---|---|---|---|
| 4.0 | 4.0 | TRUE | 1.4142135623730951 | 1.414214 |
The SAME column is TRUE, so those are the same value and not two floats that merely look alike in print.
Now compare SQRT_2 with SQRT_2_ROUNDED. The raw square root of two runs to as many digits as a float can hold, which is correct and unreadable. That is why ROUND is usually wanted alongside.
Let's try a few edges in one row.
SELECT POWER(0, 0) AS ZERO_TO_THE_ZERO,
POWER(-8, 2) AS NEGATIVE_BASE_EVEN,
POWER(NULL, 2) AS POWER_NULL,
SQRT(0) AS SQRT_ZERO,
SQRT(NULL) AS SQRT_NULL;| ZERO_TO_THE_ZERO | NEGATIVE_BASE_EVEN | POWER_NULL | SQRT_ZERO | SQRT_NULL |
|---|---|---|---|---|
| 1.0 | 64.0 | NULL | 0.0 | NULL |
POWER(0, 0)is defined here rather than being an error, andZERO_TO_THE_ZEROshows which way Snowflake went.- A negative base with an even exponent is fine, because the answer is real.
NULLanywhere in either function givesNULL, which is the ordinary rule.SQRT(0)is fine.
A negative base under a square root is a different matter, because there is no real answer to hand back. Let's see what Snowflake does with it.
SELECT SQRT(-1) AS SHOULD_NOT_GET_THIS_FAR;100044 (22P01): Invalid floating point operation: sqrt(-1)
SQRT of a negative number is an error, not a NULL. The statement fails and takes everything else in it with it. If the argument is computed rather than typed, say a variance or a difference of two columns, then a single negative row anywhere in the table kills the query. It will do that in production rather than in your worksheet. Guard it with a CASE, an IFF, or ABS if that is what you meant.
Now let's try EXP and LN
EXP(x) raises e to the power of x. LN(x) is the logarithm to base e. Each one undoes the other, and that is the only property of them we need to remember.
SELECT EXP(1) AS E,
LN(EXP(1)) AS LN_OF_E,
EXP(0) AS EXP_ZERO,
LN(1) AS LN_ONE,
ROUND(EXP(LN(7)), 9) AS ROUND_TRIP_ON_7;| E | LN_OF_E | EXP_ZERO | LN_ONE | ROUND_TRIP_ON_7 |
|---|---|---|---|---|
| 2.718281828459045 | 1.0 | 1.0 | 0.0 | 7.0 |
E is e itself, to as many digits as a float holds. LN_OF_E comes back to where it started. So does ROUND_TRIP_ON_7, which is wrapped in a ROUND on purpose, because going out to a float and back is not guaranteed to land exactly where it began.
LN has two edges, and both are errors rather than NULLs. They are worth seeing with their real messages.
SELECT LN(0) AS SHOULD_NOT_GET_THIS_FAR;100043 (2201E): Invalid floating point operation: log(e,0)
SELECT LN(-1) AS SHOULD_NOT_GET_THIS_FAR;100043 (2201E): Invalid floating point operation: log(e,-1)
Have a look at how Snowflake words those two messages. It reports the call as log(e, ...) rather than as LN(...), because that is what LN is. Useful to know before you go looking for the word LN in an error log and fail to find it.
That makes three functions on this page whose bad input is an error and not a NULL: SQRT of a negative, LN of zero and LN of a negative. If any of their arguments comes from a column rather than from your keyboard, the guard is not optional.
Now let's do LOG, and work out which argument is the base
LOG takes two arguments. The order is not something to recall from memory, because getting it backwards produces a plausible wrong number and never an error. So let's run it both ways round and read the answer off the grid.
SELECT LOG(10, 100) AS TEN_FIRST,
LOG(100, 10) AS HUNDRED_FIRST,
LOG(2, 8) AS TWO_FIRST,
LOG(8, 2) AS EIGHT_FIRST;| TEN_FIRST | HUNDRED_FIRST | TWO_FIRST | EIGHT_FIRST |
|---|---|---|---|
| 2.0 | 0.5 | 3.0 | 0.33333333333333337 |
TEN_FIRST and HUNDRED_FIRST are the same two numbers swapped. One of them is a whole number and the other is a fraction, and only one reading of the arguments makes both of them true: LOG(base, value), so the base comes first.
TWO_FIRST and EIGHT_FIRST confirm it on a different base. EIGHT_FIRST is the reciprocal of TWO_FIRST, or as near to it as a float gets, which is the next thing we need to look at.
Now let's check a logarithm we know the answer to
LOG(10, 1000) has an exact answer and it is a small whole number. Let's also work the same logarithm out a second way, through the change-of-base formula, and ask Snowflake whether the two agree.
SELECT LOG(10, 1000) AS LOG_SAYS,
LN(1000) / LN(10) AS CHANGE_OF_BASE,
LOG(10, 1000) = LN(1000) / LN(10) AS SAME;| LOG_SAYS | CHANGE_OF_BASE | SAME |
|---|---|---|
| 2.9999999999999996 | 2.9999999999999996 | TRUE |
LOG_SAYS is not the whole number. It is a float a hair below it. That is not one code path misbehaving. CHANGE_OF_BASE gets there through two completely separate LN calls, and SAME reports that the two agree exactly. Both routes land in the same wrong place, because both of them return a FLOAT.
Episode 70 showed Snowflake's NUMBER type rounding better than most languages do, because it is exact decimal. That was true. This is also true, and the two do not contradict each other: the moment you call LOG, LN, EXP, POWER or SQRT, you have left exact decimal behind.
The practical consequence is short. An equality test against a whole number will be FALSE here. Compare with a tolerance, or ROUND before you compare, and never use a float as a GROUP BY key or a join key.
Now let's use a logarithm for something real
Here is a genuine use for a logarithm in reporting. FLOOR(LOG(10, x)) is the order of magnitude of x, which is how many digits it has, less one. POWER(10, ...) of that turns it back into the bottom of the bucket.
SELECT ORDER_ID, AMOUNT,
FLOOR(LOG(10, AMOUNT)) AS MAGNITUDE,
POWER(10, FLOOR(LOG(10, AMOUNT))) AS BUCKET_FLOOR
FROM ORDERS
ORDER BY AMOUNT
LIMIT 10;| ORDER_ID | AMOUNT | MAGNITUDE | BUCKET_FLOOR |
|---|---|---|---|
| 1010 | 640.00 | 2.0 | 100.0 |
| 1014 | 750.00 | 2.0 | 100.0 |
| 1002 | 890.50 | 2.0 | 100.0 |
| 1021 | 980.00 | 2.0 | 100.0 |
| 1039 | 1080.00 | 3.0 | 1000.0 |
| 1008 | 1120.00 | 3.0 | 1000.0 |
| 1028 | 1240.00 | 3.0 | 1000.0 |
| 1018 | 1430.00 | 3.0 | 1000.0 |
| 1004 | 1500.00 | 3.0 | 1000.0 |
| 1027 | 1690.00 | 3.0 | 1000.0 |
MAGNITUDE says which band each order falls in. BUCKET_FLOOR says where that band starts, in a form a human can read. This is the usual way to put a long-tailed column onto a sensible axis without inventing arbitrary cut points.
Now let's group it, which is the shape a report wants.
SELECT FLOOR(LOG(10, AMOUNT)) AS MAGNITUDE, COUNT(*) AS N,
MIN(AMOUNT) AS SMALLEST, MAX(AMOUNT) AS LARGEST
FROM ORDERS
GROUP BY FLOOR(LOG(10, AMOUNT))
ORDER BY MAGNITUDE;| MAGNITUDE | N | SMALLEST | LARGEST |
|---|---|---|---|
| 2.0 | 4 | 640.00 | 980.00 |
| 3.0 | 32 | 1080.00 | 9640.00 |
| 4.0 | 4 | 10400.00 | 13100.00 |
| NULL | 1 | NULL | NULL |
Four groups came back. Three of them are magnitude bands with 4, 32 and 4 orders in them, and their SMALLEST and LARGEST columns show each band's real range rather than its theoretical one.
The NULL group is the interesting one. It has a single order in it. That means that order's AMOUNT is NULL, because LOG of NULL is NULL and GROUP BY collects NULLs into a group of their own.
That row has been in ORDERS the whole time and nothing on this course had pointed at it. Episode 70 showed the average of this same column and said nothing, because AVG divides by the rows that have a value, not by the rows the table has. That is correct, documented, and completely silent. It took a GROUP BY to make the row visible at all, which is the single most useful habit in this episode: group by the thing you are about to aggregate, once, and look at whether a NULL group comes back.
This idiom has a sharp edge, and LOG is the reason. LOG returns a float, and we watched LOG(10, 1000) come back a hair below the whole number it should be. FLOOR of a value a hair below a whole number is the whole number beneath it. So an amount that is exactly a power of ten is the one input FLOOR(LOG(10, x)) cannot be trusted on. No amount in this data is an exact power of ten, which is why nothing here looks wrong. On your data, check the boundaries first.
Now let's do PI, DEGREES and RADIANS
Three small ones, in one row: π as a function call, a use for it, and the two angle conversions that every trigonometric function needs.
SELECT PI() AS PI_,
ROUND(PI(), 5) AS PI_5DP,
ROUND(PI() * POWER(5, 2), 4) AS AREA_OF_R5_CIRCLE,
ROUND(DEGREES(PI()), 4) AS PI_IN_DEGREES,
ROUND(RADIANS(180), 6) AS DEGREES_180_IN_RADIANS;| PI_ | PI_5DP | AREA_OF_R5_CIRCLE | PI_IN_DEGREES | DEGREES_180_IN_RADIANS |
|---|---|---|---|---|
| 3.141592653589793 | 3.14159 | 78.5398 | 180.0 | 3.141593 |
PI() takes no arguments and the parentheses are not optional. AREA_OF_R5_CIRCLE is the area of a circle, which is π times the radius squared, written with POWER so the formula reads the way it does on paper.
PI_IN_DEGREES and DEGREES_180_IN_RADIANS are the pair worth bookmarking. Snowflake's trigonometric functions take radians, not degrees, so DEGREES and RADIANS are the conversions you will reach for every time. Those two columns are each other's inverse: π radians expressed in degrees, and the same angle in degrees converted back.
Let's see what RANDOM actually gives us back
Almost everybody arrives expecting RANDOM() to return something between zero and one, because that is what the equivalent function does in most languages. It does not. It returns a large signed integer, which can be negative.
Let's give it a seed, so there is something stable in the grid to look at.
SELECT RANDOM(42) AS SEED_42_A,
RANDOM(42) AS SEED_42_B,
RANDOM(42) = RANDOM(42) AS SAME_WITHIN_A_ROW,
RANDOM(7) AS SEED_7;| SEED_42_A | SEED_42_B | SAME_WITHIN_A_ROW | SEED_7 |
|---|---|---|---|
| -4516583221451431210 | -4516583221451431210 | TRUE | -4530791435034240601 |
That is the shape of the thing. A long integer, negative in this case, nowhere near the zero-to-one range anyone expects. The two calls seeded with 42 returned the same value as each other, and SAME_WITHIN_A_ROW says so.
Do not treat the digits in that grid as a constant. They are what this one capture returned, on one account, on the version named at the bottom of this page. A seed is what makes a draw repeatable rather than a promise about which number you personally will see. Run it yourself and read your own output. What we are learning here is the shape and the sign, not the value.
A grid we are going to take apart
Let's ask three questions about RANDOM() in one row. What is its type, is it a big integer, and is it between zero and one?
SELECT TYPEOF(RANDOM()::VARIANT) AS ITS_TYPE,
LENGTH(ABS(RANDOM())::VARCHAR) >= 10 AS IS_IT_A_BIG_INTEGER,
RANDOM() BETWEEN 0 AND 1 AS IS_IT_BETWEEN_0_AND_1;| ITS_TYPE | IS_IT_A_BIG_INTEGER | IS_IT_BETWEEN_0_AND_1 |
|---|---|---|
| INTEGER | TRUE | TRUE |
IS_IT_A_BIG_INTEGER and IS_IT_BETWEEN_0_AND_1 are both TRUE, and they cannot both be true of one number. Nothing is at least ten digits long and also between zero and one.
That tells us something real about the function. Each RANDOM() reference in a statement is its own draw. Three calls in that SELECT are three different numbers wearing one row's clothing, and no two of those columns are describing the same value.
So we learn nothing from that row except ITS_TYPE. That column is safe, because the type of what RANDOM() returns does not depend on which number came out, and it says INTEGER.
The general rule this exposes is worth more than the function. A non-deterministic function called twice in one query is two independent calls. If you need one draw used in several places, compute it once, in a CTE, a subquery or a LATERAL, and reference that column, rather than repeating the call and hoping.
Now let's ask UNIFORM for a number in a range
If what we wanted was a random value between two bounds, the function is UNIFORM. It takes the low bound, the high bound, and a source of randomness. That source is usually RANDOM(), and it is seeded here so the capture has something stable to show.
SELECT UNIFORM(1, 6, RANDOM(1)) AS DICE_1,
UNIFORM(1, 6, RANDOM(2)) AS DICE_2,
UNIFORM(1, 6, RANDOM(3)) AS DICE_3,
UNIFORM(0::FLOAT, 1::FLOAT, RANDOM(4)) AS FRACTION;| DICE_1 | DICE_2 | DICE_3 | FRACTION |
|---|---|---|---|
| 5 | 2 | 1 | 0.6397453769445163 |
DICE_1, DICE_2 and DICE_3 are all inside the range we asked for, which is the whole point of the function.
FRACTION is the answer to the question people were asking of RANDOM in the first place. Give UNIFORM FLOAT bounds and you get a fraction between them. Give it integer bounds and you get an integer. The argument types decide the result type, and that is the detail most worth remembering about this function.
Those are seeded draws, so read the range and the types, not the digits.
Can we claim anything that does not depend on the draw?
Yes, if we ask something that has to be true of every draw rather than of one of them.
SELECT COUNT(*) AS ROWS_TOTAL,
MIN(UNIFORM(1, 6, RANDOM())) AS SMALLEST,
MAX(UNIFORM(1, 6, RANDOM())) AS LARGEST,
COUNT_IF(UNIFORM(1, 6, RANDOM()) BETWEEN 1 AND 6) AS IN_RANGE
FROM TABLE(GENERATOR(ROWCOUNT => 100));| ROWS_TOTAL | SMALLEST | LARGEST | IN_RANGE |
|---|---|---|---|
| 100 | 1 | 6 | 100 |
The column that means something is IN_RANGE. It equals ROWS_TOTAL, so every one of those draws satisfied the bounds. That will be true on your run too, because it is the function's contract.
SMALLEST and LARGEST are a different kind of column. They happen to reach the ends of the range on this run, which over that many draws is likely but is not promised. On your run they may not, and that would not be a bug.
Is it inclusive at both ends?
This is a real question with a real answer, and languages disagree about it. Some ranges include the high bound and some stop just short of it. Snowflake's UNIFORM is documented as inclusive at both ends, so let's draw a lot of times over a narrow range and see whether that is consistent with it.
SELECT COUNT(*) AS DRAWS,
COUNT_IF(UNIFORM(1, 3, RANDOM()) = 1) > 0 AS LOW_END_APPEARS,
COUNT_IF(UNIFORM(1, 3, RANDOM()) = 3) > 0 AS HIGH_END_APPEARS,
COUNT_IF(UNIFORM(1, 3, RANDOM()) NOT BETWEEN 1 AND 3) AS OUT_OF_RANGE
FROM TABLE(GENERATOR(ROWCOUNT => 500));| DRAWS | LOW_END_APPEARS | HIGH_END_APPEARS | OUT_OF_RANGE |
|---|---|---|---|
| 500 | TRUE | TRUE | 0 |
LOW_END_APPEARS and HIGH_END_APPEARS are both TRUE, and OUT_OF_RANGE is zero. Let's be clear about which of those is evidence of what. OUT_OF_RANGE being zero is the contract again, and it will hold. The two TRUEs are this run seeing both ends. That is strong evidence over that many draws, and it is still a sample rather than a proof. The documentation is what settles inclusivity. This grid is what makes it believable.
Each of those three COUNT_IF expressions contains its own UNIFORM(1, 3, RANDOM()), so the three columns are summarising three different samples rather than three views of one. That is the separate-draw rule from RANDOM showing up again, in a query where it does no harm. It is also the reason this grid cannot be read as a single distribution.
Can we take a random sample?
This is the idiom everyone wants from RANDOM: ORDER BY RANDOM() with a LIMIT. Which rows come back differs on every single run, so there is nothing honest to show except the shape of the answer.
SELECT COUNT(*) AS ROWS_RETURNED,
COUNT(DISTINCT ORDER_ID) AS DISTINCT_IDS
FROM (SELECT ORDER_ID FROM ORDERS ORDER BY RANDOM() LIMIT 5);| ROWS_RETURNED | DISTINCT_IDS |
|---|---|
| 5 | 5 |
ROWS_RETURNED matches the LIMIT, and DISTINCT_IDS matches ROWS_RETURNED. So the sample is the size we asked for and it contains no row twice. Those two facts hold every run. The identities of the rows do not, which is exactly why this query counts them instead of listing them.
For a sample of a large table, SAMPLE / TABLESAMPLE from Level 6 is the better tool. ORDER BY RANDOM() has to order the whole table before it can take five rows off the top. This page makes no measurement of that, and nothing in this series is timed. It is a remark about what the two statements ask for, not a benchmark.
Let's put all of it in one query
SELECT ORDER_ID, AMOUNT,
ROUND(SQRT(AMOUNT), 4) AS SQRT_AMOUNT,
ROUND(LN(AMOUNT), 4) AS LN_AMOUNT,
FLOOR(LOG(10, AMOUNT)) AS MAGNITUDE,
ROUND(POWER(AMOUNT, 0.5), 4) AS POWER_HALF_AGREES
FROM ORDERS
ORDER BY ORDER_ID
LIMIT 5;| ORDER_ID | AMOUNT | SQRT_AMOUNT | LN_AMOUNT | MAGNITUDE | POWER_HALF_AGREES |
|---|---|---|---|---|---|
| 1001 | 4200.00 | 64.8074 | 8.3428 | 3.0 | 64.8074 |
| 1002 | 890.50 | 29.8412 | 6.7918 | 2.0 | 29.8412 |
| 1003 | 6150.00 | 78.4219 | 8.7242 | 3.0 | 78.4219 |
| 1004 | 1500.00 | 38.7298 | 7.3132 | 3.0 | 38.7298 |
| 1005 | 2310.75 | 48.0703 | 7.7453 | 3.0 | 48.0703 |
Square root, natural log and order of magnitude on a real column. POWER_HALF_AGREES is POWER(AMOUNT, 0.5), and it is there as a check: it matches SQRT_AMOUNT row for row, which is the first thing we claimed on this page.
Every one of those calculated columns is a FLOAT, which is the other thing we claimed. The ROUND calls are there so the grid is readable, not because the values arrived that way.
Key points
POWER(base, exponent), withPOWas an alias andSQUARE(x)as a shorthand for an exponent of two. There is no**operator in Snowflake — it is a compilation error.- A fractional exponent is a root and a negative exponent is a reciprocal, so
POWERcovers far more than whole powers. - Every function on this page returns a
FLOAT.LOG(10, 1000)comes back a hair below the whole number it should be, and the change-of-base formula returns the identical value, so it is the type and not a code path. Episode 70's exact-decimalNUMBERand this are both true at once. - Because of that, an equality test against a whole number will be
FALSE. Compare with a tolerance orROUNDfirst, and never key aGROUP BYor a join on a float. LOG(base, value)— the base comes first. Backwards gives a plausible wrong number and never an error, which is why the argument order is worth running rather than recalling.SQRTof a negative,LN(0)andLNof a negative are errors, notNULLs, and one bad row fails the whole statement. Guard any of them that takes its argument from a column.FLOOR(LOG(10, x))is a good order-of-magnitude bucket and a bad one exactly at a power of ten, for the float reason above.- A
GROUP BYis what makes aNULLvisible. Grouping these orders by magnitude produced aNULLbucket holding one row, whichAVGhad been skipping silently and correctly for two episodes — becauseAVGdivides by the rows that have a value. RANDOM()returns a large signed integer, not a fraction, and every reference to it is a separate draw — so two columns in one row are two different numbers. Compute a draw once and reference it if you need it twice.UNIFORM(low, high, gen)is the one with a range, it is inclusive at both ends, and the argument types decide whether you get an integer or a fraction.
Episode 72 — dates, part 1. The start of five posts on dates and timestamps, which is not an accident. Between formats, parts, arithmetic and time zones, they earn the room.
Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode, including all four error panes. The ORDERS data is invented demo data created by the course setup script. Every RANDOM and UNIFORM grid on this page is one draw — the values are what that capture returned and are not reproducible figures, including the seeded ones; only the range, type and count claims hold on a re-run. No performance or benchmark claims are made anywhere in this series and nothing here is timed. Function behaviour follows the Snowflake numeric function documentation.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.