/Math Functions/SIGN

PostgreSQL SIGN() Function

Summary: in this tutorial, you will learn how to use the PostgreSQLSIGN() function to determine the sign of a number.

Introduction to the PostgreSQL SIGN() function

TheSIGN() function allows you to determine the sign of a number.

Here’s the syntax of theSIGN() function:

SIGN(numeric_value)

TheSIGN() function accepts a numeric value (numeric_value) and returns -1 if the value is negative, 0 if the value is zero, and 1 if the value is positive. Additionally, it returnsNULL if the value isNULL.

TheSIGN() function can be handy when you’re working with financial data, performing mathematical calculations, or handling data validation activities.

PostgreSQL SIGN() function examples

Let’s explore some examples of using theSIGN() function.

1) Basic SIGN() function example

The following example uses theSIGN() function to get the sign of various numbers:

SELECT  SIGN(-10),  SIGN(0),  SIGN(10),  SIGN(NULL);

Output:

sign | sign | sign | sign------+------+------+------   -1 |    0 |    1 | null(1 row)

TheSIGN() returns -1 for -10, 0 for 0, 1 for 10, andNULL forNULL.

2) Using the SIGN() function with table data

First,create a table calledsales to store the sales amount by year:

CREATE TABLE sales (    year INTEGER PRIMARY KEY,    sales_amountNUMERIC NOT NULL);

Second,insert rows into thesales table:

INSERT INTO sales (year, sales_amount)VALUES    (2013,10000),    (2014,12000),    (2015,15000),    (2016,15000),    (2017,20000),    (2018,22000),    (2019,22000),    (2020,23000),    (2021,22000),    (2022,24000),    (2023,26000)RETURNING*;

Third, compare the sales of a year with the previous year and use theSIGN() function to output the sales trend:

SELECT    year,    sales_amount,    LAG(sales_amount)OVER (ORDER BY year)AS previous_year_sales,    CASE        WHEN LAG(sales_amount)OVER (ORDER BY year)IS NULL THEN 'N/A'        WHEN SIGN(sales_amount- LAG(sales_amount)OVER (ORDER BY year))= 1 THEN 'up'        WHEN SIGN(sales_amount- LAG(sales_amount)OVER (ORDER BY year))= -1 THEN 'down'        ELSE 'unchanged'    END AS sales_trendFROM    sales;

Output:

year | sales_amount | previous_year_sales | sales_trend------+--------------+---------------------+------------- 2013 |        10000 |                null | N/A 2014 |        12000 |               10000 | up 2015 |        15000 |               12000 | up 2016 |        15000 |               15000 | unchanged 2017 |        20000 |               15000 | up 2018 |        22000 |               20000 | up 2019 |        22000 |               22000 | unchanged 2020 |        23000 |               22000 | up 2021 |        22000 |               23000 | down 2022 |        24000 |               22000 | up 2023 |        26000 |               24000 | up(11 rows)

How it works.

Use theLAG() window function to retrieve thesales_amount from the previous year utilizing theORDERBY year clause to specify the order of rows.
Use theCASE expression to evaluate each row’s sales data and assign a corresponding value to thesales_trend column.

If you want to reuse the result of the LAG() function, you can use acommon table expression:

WITH sales_data AS (    SELECT        year,        sales_amount,        LAG(sales_amount) OVER (ORDER BY year) AS previous_year_sales    FROM        sales)SELECT    year,    sales_amount,    previous_year_sales,    CASE        WHEN previous_year_sales IS NULL THEN 'N/A'        WHEN SIGN(sales_amount - previous_year_sales)= 1 THEN 'up'        WHEN SIGN(sales_amount - previous_year_sales)= -1 THEN 'down'        ELSE 'unchanged'    END AS sales_trendFROM    sales_data;

Summary

Use theSIGN() function to determine the sign of a number.

PreviousPostgreSQL SCALE() Function NextPostgreSQL TRUNC() Function

Last updated onFebruary 17, 2024

Was this page helpful?

Thank you for your feedback!

Movatterモバイル変換