SIGN en SQL: el signo de un numero y ramificar por direccion

SIGN devuelve el signo de un numero: -1 para los negativos, 0 para el cero y 1 para los positivos. Es una funcion diminuta, pero convierte el "cuanto" en "hacia donde" y reduce montones de logica CASE engorrosa a una sola llamada.

Que devuelve SIGN

La firma es trivial: entra un numero y sale uno de tres valores -1, 0, 1.

SELECT SIGN(-42)   AS neg,   -- -1
       SIGN(0)     AS zero,  --  0
       SIGN(17.5)  AS pos;   --  1

El tipo del resultado refleja el del argumento: con numeric sale numeric, con double precision sale double precision. Si el argumento es NULL, el resultado tambien es NULL: no es un cuarto estado, solo la propagacion habitual del nulo.

Un uso practico es normalizar los importes de los pedidos al sentido del flujo de caja, ignorando la magnitud:

SELECT id,
       amount,
       SIGN(amount) AS direction  -- -1 refund, 0 zero, 1 charge
FROM orders;

Ramificar por la direccion del cambio

Lo mas habitual es recurrir a SIGN para clasificar la diferencia entre dos valores como "sube", "baja" o "igual". Comparemos el importe de un pedido con el pedido anterior del mismo usuario mediante una funcion de ventana:

SELECT id,
       user_id,
       amount,
       SIGN(amount - LAG(amount) OVER (
           PARTITION BY user_id ORDER BY created_at
       )) AS trend  -- -1 down, 0 flat, 1 up
FROM orders;

Los tres valores se despliegan con limpieza en etiquetas mediante CASE. Fijate en que la rama WHEN 0 captura la igualdad exacta, no un "casi igual":

SELECT id,
       CASE SIGN(amount - 100)
           WHEN -1 THEN 'below target'
           WHEN  0 THEN 'on target'
           WHEN  1 THEN 'above target'
       END AS bucket
FROM orders;

SIGN con ABS: la direccion aparte de la magnitud

Cualquier numero se factoriza como x = SIGN(x) * ABS(x). Separar la direccion de la magnitud es la razon principal para tener SIGN a mano. Por ejemplo, sumar lo que salio como reembolsos frente a lo que entro como cargos en una sola pasada:

SELECT SUM(CASE WHEN SIGN(amount) = 1 THEN amount ELSE 0 END) AS charged,
       SUM(CASE WHEN SIGN(amount) = -1 THEN ABS(amount) ELSE 0 END) AS refunded
FROM orders;

La misma combinacion ayuda al ordenar por cercania a un objetivo: ordena por ABS (cuan lejos) y manten el signo aparte para mostrar de que lado queda un salario respecto a una referencia del departamento:

SELECT name,
       dept,
       salary,
       SIGN(salary - 60000) AS side,   -- which way off
       ABS(salary - 60000)  AS gap     -- how far off
FROM employees
ORDER BY gap DESC;

Estabilidad numerica y trampas

SIGN en si mismo es estable: nunca pierde precision ni desborda, porque solo entrega tres valores. El peligro se esconde en el argumento.

• Cero en coma flotante. En un calculo 0.0 puede llegar como un diminuto 1e-18, y entonces SIGN devuelve 1, no 0. Si la rama del cero importa, redondea antes: SIGN(ROUND(x::numeric, 6)).
• NULL se cuela por todas las ramas. En CASE SIGN(x) ni -1, ni 0, ni 1 casan con un NULL: anade un IS NULL explicito o un ELSE.
• Division entera. SIGN(a / b) con enteros divide primero: SIGN(3 / 4) es SIGN(0) = 0. Convierte a un tipo fraccionario antes de dividir.

Las diferencias entre motores son pequenas, pero conviene conocerlas:

• PostgreSQL devuelve el mismo tipo numerico que el argumento.
• MySQL tiene la misma funcion SIGN(), pero siempre devuelve un entero -1/0/1.
• ClickHouse ofrece sign() (en minusculas) con el mismo comportamiento -1/0/1.

-- Pitfall: integer division hides the real sign
SELECT SIGN(3 / 4)               AS wrong,  -- 0
       SIGN(3.0 / 4)             AS right_; -- 1

Recuerda la identidad x = SIGN(x) * ABS(x): resuelve la mitad de las preguntas de "sube o baja" sin un solo CASE anidado.