[Oracle] SIGN()函数

SIGN() 是 Oracle 中用于判断数值符号的函数，它返回一个数值的符号指示值

SIGN()函数的主要功能是判断输入数值的符号，返回以下三种可能值：
1：当输入为正数时
0：当输入为零时
-1：当输入为负数时

语法格式

SIGN(n)

参数说明
n：要检查符号的数值表达式(可以是列、计算值或函数结果)

示例

-- 零测试
SELECT SIGN(0) FROM dual;  -- 0-- 正数测试
SELECT SIGN(12) FROM dual;  -- 1
SELECT SIGN(3.14159) FROM dual;  -- 1-- 负数测试
SELECT SIGN(-12) FROM dual;  -- -1
SELECT SIGN(-0.0001) FROM dual;  -- -1

SELECT SIGN(NULL) FROM dual;  -- NULL-- SIGN()函数结合NVL()函数处理NULL
SELECT account_id,SIGN(NVL(balance, 0)) AS balance_sign
FROM accounts;

提示Tips：SIGN()函数对NULL的处理(如果输入为NULL，结果返回NULL)

-- 将账户余额分类
-- 结果示例:
-- ACCOUNT_ID  BALANCE  BALANCE_STATUS
-- 1001        500.00   盈余
-- 1002        0.00     平衡
-- 1003        -200.00  透支
SELECT account_id,balance,CASE SIGN(balance)WHEN 1 THEN '盈余'WHEN 0 THEN '平衡'WHEN -1 THEN '透支'END AS balance_status
FROM accounts;-- 比较实际销售额与目标销售额
-- 结果示例:
-- PRODUCT_ID  ACTUAL_SALES  TARGET_SALES  DIFFERENCE  PERFORMANCE
-- P001        1200         1000          200         1
-- P002        800          1000          -200        -1
-- P003        1000         1000          0           0
SELECT product_id,actual_sales,target_sales,actual_sales - target_sales AS difference,SIGN(actual_sales - target_sales) AS performance
FROM sales_data;

SIGN()函数在WHERE子句中使用

-- 查找所有透支账户
SELECT account_id, balance
FROM accounts
WHERE SIGN(balance) = -1;-- 查找所有非负库存
SELECT product_id, stock_quantity
FROM inventory
WHERE SIGN(stock_quantity) >= 0;

SIGN()函数与CASE表达式结合使用

-- 统计销售业绩情况
SELECT COUNT(*) AS total_products,SUM(CASE WHEN SIGN(actual_sales - target_sales) = 1 THEN 1 ELSE 0 END)  AS above_target,SUM(CASE WHEN SIGN(actual_sales - target_sales) = -1 THEN 1 ELSE 0 END) AS below_target,SUM(CASE WHEN SIGN(actual_sales - target_sales) = 0 THEN 1 ELSE 0 END)  AS on_target
FROM sales_data;-- 实现分段函数
SELECT x,CASE WHEN SIGN(x) = 1 THEN x*xWHEN SIGN(x) = -1 THEN -xELSE 0END AS function_value
FROM math_values;

SIGN()函数与其他函数结合使用

-- 计算绝对值的符号表示
SELECT number_value,SIGN(number_value) AS sign,ABS(number_value) AS absolute_value,SIGN(number_value) * ABS(number_value) AS original_value
FROM numbers_table;