SQL

The SIGN function

Travis Tang

20 Dec 2022 — 1 min read

Use SIGN to see if a number is positive, negative, or zero.

It's as simple as it sounds.

Apply SIGN() to an integer or float column to get:
- SIGN(0) gives 0
- SIGN(POSITIVE_NUM) gives 1
- SIGN(NEGATIVE_NUM) gives -1

Caution! Be careful of near-zero floats that are not actually zero.

For example, SIGN(0.0000001) will return 1 since it is a positive number.

This can happen when you have round-off errors when storing FLOATS in a database. For example, 3.0 might be stored as 2.9999999...

👇 The SIGN() function can be implemented with a CASE WHEN. See example 2 below.

Use ARRAY_AGG to flatten columns to lists.

You need to convert a long table of values into a list? This is the most convenient function. 📌 Syntax: 𝚂𝙴𝙻𝙴𝙲𝚃 𝙰𝚁𝚁𝙰𝚈_𝙰𝙶𝙶(𝚌𝚘𝚕𝚞𝚖𝚗) 𝙵𝚁𝙾𝙼 𝚝𝚊𝚋𝚕𝚎 There you go, the column is flattened into a list separated by comma. ❌ To do so, I used to copy the entire column into a spreadsheet tool, transpose it, and use

Lazypredict: Run All Sklearn Algorithms With a Line Of Code

How to (and why you shouldn’t) use it

Generate Histogram Distribution

For a quick exploration of the distribution

Convert Jupyter Notebooks into Functions

Papermill is an open-sourced tool for parameterizing, executing, and analyzing Jupyter notebooks. Just pass in parameters to the notebook, and the Jupyter notebook runs automatically.

Read more

Use ARRAY_AGG to flatten columns to lists.

Lazypredict: Run All Sklearn Algorithms With a Line Of Code

Generate Histogram Distribution

Convert Jupyter Notebooks into Functions