Representação Matemática de Intersecção de Intervalos de Valores
A versão 9.2 apresenta o conceito de "Range Types", que engloba um tipo de dados específico para intervalos, além de recursos para cálculos e manipulações relacionadas a estes tipos peculiares de dados. Considero este um grande avanço, que pode reduzir o esforço de implementação e aumentar o desempenho em várias situações. Pode-se por exemplo, indexar um campo intervalar.
Neste post, embora não se busque esgotar o tema, vamos ilustrar as principais possibilidades desta nova feature com exemplos.
* Tipos de Intervalos
O postgresql apresenta seis tipos de intervalos padrão, sendo alguns discretos e outros contínuos, mas você pode criar outros utilizando CREATE TYPE:
- int4range — Intervalo de inteiros de 4 bytes.
- int8range — Intervalo de "bigint" (inteiros de 8 bytes)
- numrange — Intervalo de números reais
- tsrange — Intervalo de timestamps sem time zone
- tstzrange — Intervalo de timestamps com time zone
- daterange — Intervalo de datas
* Operações Básicas para Definir Dados Intervalares
Um intervalo consiste em um conjunto de valores delimitados por um valor inicial e um final. O postgres oferece opção de se trabalhar com intervalos total e parcialmente limitados e não limitados. Os delimitadores compreendem colchetes "[]" para os intervalos fechados e parênteses "()" para os intervalos abertos.
Intervalos abertos e fechados
Abaixo segue uma sequência de exemplos ilustrando as principais operações feitas com intervalos e os resultados obtidos.
Exemplo 1: Intervalo fechado contendo os números 1 a 5.
postgres=# SELECT '[1,5]'::numrange;
numrange
----------
[1,5]
(1 registro)
Exemplo 2: Intervalo fechado em campo inteiro, contendo os números de 1 a 5.
postgres=# SELECT '[1,5]'::int4range;
int4range
-----------
[1,6)
(1 registro)
Exemplo 3: Intervalo de 1 a 5, aberto no 5, isto é, não contendo o valor 5.
postgres=# SELECT '[1,5)'::numrange;
numrange
----------
[1,5)
(1 registro)
Exemplo 4: Intervalo sem limite máximo.
postgres=# SELECT '[1,]'::numrange;
numrange
----------
[1,)
(1 registro)
Exemplo 5: Intervalo sem limite mínimo.
postgres=# SELECT '(,1]'::numrange;
numrange
----------
(,1]
(1 registro)
Exemplo 6: Duas formas de expressar um intervalo sem quaisquer limites.
postgres=# SELECT '(,)'::numrange, numrange(null, null);
numrange | numrange
----------+----------
(,) | (,)
(1 registro)
Exemplo 7: Intervalos de 1 a 5, abertos e fechados, criados a partir do construtor do tipo numrange.postgres=# SELECT numrange(1,5,'[)'), numrange(1,5,'(]'), numrange(1,5,'[]'), numrange(1,5,'()');
numrange | numrange | numrange | numrange
----------+----------+----------+----------
[1,5) | (1,5] | [1,5] | (1,5)
(1 registro)
Exemplo 8: Duas formas de expressar um intervalo sem elementos.postgres=# SELECT numrange(1,1,'[)'), '[1,1)'::numrange;
numrange | numrange
----------+----------
empty | empty
(1 registro)
Exemplo 9: Intervalo em campo data.
postgres=# SELECT daterange('12/01/2012',current_date,'[)');
daterange
-------------------------
[2012-01-12,2012-10-17)
(1 registro)
Exemplo 10: Intervalo em campo data, exemplo 2.
postgres=# SELECT daterange(current_date -10,current_date,'[)');
daterange
-------------------------
[2012-10-07,2012-10-17)
(1 registro)
Exemplo 11: Teste de pertencimento de elemento a um intervalo.
postgres=# SELECT int4range(10, 20, '[]') @> 9, int4range(10, 20,'[]') @> 15, int4range(10, 20,'[]') @> 21;
?column? | ?column? | ?column?
----------+----------+----------
f | t | f
Exemplo 12: Recuperando Limites Superior e Inferior de um Intervalo
postgres=# SELECT lower(numrange(10, 100, '[]')), upper(numrange(10, 100, '[]'));
lower | upper
-------+-------
10 | 100
(1 registro)
Exemplo 13: Verificação de sobreposição de intervalos
É feita com o operador &&.
postgres=# SELECT numrange(10, 20) && numrange(25, 30), daterange('01/01/2010', '31/12/2011') && daterange('01/01/2011', '31/12/2012');
?column? | ?column?
----------+----------
f | t
(1 registro)
Exemplo 14: Intersecção de Intervalos
O teste é feito com o operador *, retornando 'empty' caso a intersecção não apresente elementos.
postgres=# SELECT int4range(10, 20,'[]') * int4range(15, 25,'[]'),daterange('2010-01-01','2012-06-30','[]') * daterange('2011-01-01','2012-12-30','[]');
?column? | ?column?
----------+-------------------------
[15,21) | [2011-01-01,2012-07-01)
(1 registro)
postgres=# SELECT int4range(10, 20) * int4range(25, 35);
?column?
----------
empty
(1 registro)
Exemplo 15: Teste se intervalo é vazio (empty)
postgres=# SELECT isempty(numrange(10, 20)), isempty(numrange(10, 10));
isempty | isempty
---------+---------
f | t
(1 registro)
Exemplo 16: Criação de Tabelas e Visões e Índices com Intervalos
postgres=# CREATE TABLE teste_range (rang_data daterange, rang_int4 int4range, rang_int8 int8range, rang_num numrange, rang_timestamp tsrange);
CREATE TABLE
postgres=# CREATE TABLE teste_range_2 (rang_data daterange PRIMARY KEY, rang_int4 int4range UNIQUE, rang_int8 int8range, rang_num numrange, rang_timestamp tsrange);
NOTA: CREATE TABLE / PRIMARY KEY criará índice implícito "teste_range_2_pkey" na tabela "teste_range_2"
NOTA: CREATE TABLE / UNIQUE criará índice implícito "teste_range_2_rang_int4_key" na tabela "teste_range_2"
CREATE TABLE
postgres=# CREATE OR REPLACE VIEW view_teste_range AS SELECT * FROM teste_range ORDER BY rang_data;
CREATE VIEW
postgres=# CREATE INDEX ind_teste_range ON teste_range (rang_data, rang_int4);
CREATE INDEX
postgres=# INSERT INTO teste_range (rang_data, rang_int4) VALUES (daterange('2010-01-01','2012-06-30','[]'), int4range(10, 20,'[]'));
INSERT 0 1
postgres=# select * from TESTE_RANGE;
rang_data | rang_int4 | rang_int8 | rang_num | rang_timestamp
-------------------------+-----------+-----------+----------+----------------
[2010-01-01,2012-07-01) | [10,21) | | |
(1 registro)
* Pontos Fortes
- Programas, funções e consultas que utilizem intervalos ficam menores.
- Opções para manipulação de intervalos são confiáveis e não demandam pluigins ou instalação de novos componentes.
* Pontos Fracos
- Perda de compatibilidade com outros SGBDs em todas as funcionalidades que utilizem intervalos.
Nenhum comentário:
Postar um comentário