MOD e o operador % no SQL: o resto da divisao na pratica

MOD(a, b) retorna o resto da divisao de a por b, o mesmo valor que o operador %. E a ferramenta basica para testar paridade, escolher cada N-esima linha, distribuir linhas em shards de forma uniforme por um identificador e qualquer logica ciclica.

Comportamento basico

MOD(a, b) e a % b produzem o mesmo resultado: o resto que sobra apos a divisao inteira. O sinal do resultado importa, e voltaremos a ele mais abaixo.

SELECT
  MOD(10, 3)  AS a,   -- 1
  10 % 3      AS b,   -- 1
  MOD(9, 3)   AS c,   -- 0
  MOD(7, 10)  AS d;   -- 7

Pontos principais:

• MOD(a, b) e a % b sao intercambiaveis no PostgreSQL e no MySQL; use o que ficar mais legivel.
• Se a divide exatamente por b, o resto e 0. Esse e o seu teste de divisibilidade.
• Um divisor 0 e um erro: MOD(5, 0) lanca division by zero, igual a divisao comum.

Par/impar e cada N-esima linha

O resto por 2 separa na hora as linhas em pares e impares. Marque os pedidos pela paridade do id:

SELECT
  id,
  amount,
  CASE WHEN MOD(id, 2) = 0 THEN 'even' ELSE 'odd' END AS parity
FROM orders
ORDER BY id;

O mesmo truque escala para "cada N-esima linha". Para pegar aproximadamente um a cada dez usuarios como amostra de verificacao, filtre pelo resto:

SELECT id, email, country
FROM users
WHERE MOD(id, 10) = 0
ORDER BY id;

Pegadinha. MOD(id, 10) = 0 so funciona como amostragem se os valores de id forem densos e espacados de forma uniforme. Sobre um id sequencial e confiavel, mas sobre UUIDs ou sequencias com grandes lacunas a amostra fica enviesada. Para uma amostragem justa sobre texto, pegue o resto de um hash, nao do id cru.

Sharding por id % bucket

O resto e a forma natural de espalhar linhas por um numero fixo de buckets. Distribua os usuarios em 4 shards conforme o id:

SELECT
  MOD(id, 4) AS shard,
  COUNT(*)   AS users
FROM users
GROUP BY MOD(id, 4)
ORDER BY shard;

Cada usuario cai sempre no mesmo shard 0..3, o que e util para processamento em lote ou para liberar recursos por coorte:

SELECT id, email
FROM users
WHERE MOD(id, 4) = 2   -- process only shard 2 in this batch
ORDER BY id;

Isso funciona porque, para id nao negativos, o resultado de MOD(id, 4) sempre cai em 0..3. Valores negativos se comportam de forma diferente, e o que vemos a seguir.

A regra do sinal e os negativos

A principal armadilha do MOD: o sinal do resultado segue o sinal do dividendo (o primeiro argumento), nao do divisor. E o comportamento do padrao SQL no PostgreSQL e no MySQL.

SELECT
  MOD(-10, 3)  AS a,   -- -1
  MOD(10, -3)  AS b,   -- 1
  MOD(-10, -3) AS c;   -- -1

Por isso, MOD(value, n) = 0 para paridade e divisibilidade e seguro, mas MOD(value, n) como indice de bucket nao e: para um value negativo ele da um bucket negativo. Para sempre obter um resto nao negativo, some o divisor e tire o resto mais uma vez:

-- always lands in 0..(n-1), even for negative input
SELECT MOD(MOD(-10, 3) + 3, 3) AS bucket;  -- 2

Lembre-se tambem do NULL: se qualquer um dos argumentos for NULL, o resultado e NULL, nao 0. Leve isso em conta no WHERE e no GROUP BY.

Diferencas entre bancos de dados

• PostgreSQL e MySQL: ambos suportam MOD(a, b) e a % b com a mesma regra de sinal (pelo dividendo).
• PostgreSQL: % funciona com inteiros e numeric. Para tipos fracionarios existe MOD(numeric, numeric), por exemplo MOD(5.5, 2) e 1.5.
• ClickHouse: use modulo(a, b) ou o operador %; tambem existe moduloOrZero(a, b), que retorna 0 em vez de falhar com divisor zero, e positiveModulo(a, b) para um resultado sempre nao negativo.

-- ClickHouse: safe modulo and always-positive modulo
SELECT
  moduloOrZero(10, 0)     AS a,   -- 0, no error
  positiveModulo(-10, 3)  AS b;   -- 2