unir tablas tabla misma funciones funciona consultas como comandos anidadas sql ruby-on-rails postgresql constraints range-types

tablas - Realice esta consulta de horas de operación en PostgreSQL



unir dos consultas sql misma tabla (1)

Disposición de la mesa

Rediseñe la tabla y almacene las horas de apertura (horas de operación) como un conjunto de tsrange rango (rango de marca de tiempo sin zona horaria) . Requiere Postgres 9.2 o posterior .

Elija una semana al azar para organizar sus horarios de apertura. Me gusta la semana
1996-01-01 (lunes) a 1996-01-07 (domingo)
Ese es el año bisiesto más reciente en el que el 1 de enero resulta ser un lunes. Pero puede ser cualquier semana al azar para este caso. Solo sé consistente.

Instale el módulo adicional btree_gist primero. ¿Por qué?

CREATE EXTENSION btree_gist;

Crea la tabla así:

CREATE TABLE hoo ( hoo_id serial PRIMARY KEY , shop_id int NOT NULL REFERENCES shop(shop_id) -- reference to shop , hours tsrange NOT NULL , CONSTRAINT hoo_no_overlap EXCLUDE USING gist (shop_id with =, hours WITH &&) , CONSTRAINT hoo_bounds_inclusive CHECK (lower_inc(hours) AND upper_inc(hours)) , CONSTRAINT hoo_standard_week CHECK (hours <@ tsrange ''[1996-01-01 0:0, 1996-01-08 0:0]'') );

Las hours una columna reemplazan a todas sus columnas:

opens_on, closes_on, opens_at, closes_at

Por ejemplo, las horas de operación desde el miércoles, de 18:30 a jueves, a las 05:00 UTC se ingresan como:

''[1996-01-03 18:30, 1996-01-04 05:00]''

La restricción de exclusión hoo_no_overlap evita la superposición de entradas por tienda. Se implementa con un índice GiST , que también es compatible con su consulta. Considere el capítulo "Índice y rendimiento" a continuación, sobre la estrategia de indexación.

La restricción de verificación hoo_bounds_inclusive impone límites inclusivos para sus rangos, con dos consecuencias notables:

  • Un punto en el tiempo que cae exactamente en el límite inferior o superior está siempre incluido.
  • Las entradas adyacentes para la misma tienda no están permitidas. Con fronteras inclusivas, esas "superposiciones" y la restricción de exclusión generarían una excepción. Las entradas adyacentes deben fusionarse en una sola fila. Excepto cuando se envuelven alrededor de la medianoche del domingo , en cuyo caso deben dividirse en dos filas. Ver la herramienta 2 a continuación.

La restricción de verificación hoo_standard_week impone los límites exteriores de la semana de puesta en escena con el operador "range is contained by" <@ .

Con límites inclusivos , debe observar un caso especial / esquina donde el tiempo se cierra alrededor de la medianoche del domingo:

''1996-01-01 00:00+0'' = ''1996-01-08 00:00+0'' Mon 00:00 = Sun 24:00 (= next Mon 00:00)

Tienes que buscar ambas marcas de tiempo a la vez. Aquí hay un caso relacionado con un límite superior exclusivo que no presentaría esta deficiencia:

Función f_hoo_time(timestamptz)

Para "normalizar" cualquier timestamp with time zone determinada timestamp with time zone :

CREATE OR REPLACE FUNCTION f_hoo_time(timestamptz) RETURNS timestamp AS $func$ SELECT date ''1996-01-01'' + ($1 AT TIME ZONE ''UTC'' - date_trunc(''week'', $1 AT TIME ZONE ''UTC'')) $func$ LANGUAGE sql IMMUTABLE;

La función toma timestamptz y devuelve timestamp . Agrega el intervalo transcurrido de la semana respectiva ($1 - date_trunc(''week'', $1) en hora UTC (!) Hasta el punto de inicio de nuestra semana de preparación. ( date + interval produce timestamp ).

Función f_hoo_hours(timestamptz, timestamptz)

Para normalizar los rangos y dividir los que cruzan Mon 00:00. Esta función toma cualquier intervalo (como dos timestamptz ) y produce uno o dos valores de tsrange normalizados. Cubre cualquier entrada legal y no permite el resto:

CREATE OR REPLACE FUNCTION f_hoo_hours(_from timestamptz, _to timestamptz) RETURNS TABLE (hoo_hours tsrange) AS $func$ DECLARE ts_from timestamp := f_hoo_time(_from); ts_to timestamp := f_hoo_time(_to); BEGIN -- test input for sanity (optional) IF _to <= _from THEN RAISE EXCEPTION ''%'', ''_to must be later than _from!''; ELSIF _to > _from + interval ''1 week'' THEN RAISE EXCEPTION ''%'', ''Interval cannot span more than a week!''; END IF; IF ts_from > ts_to THEN -- split range at Mon 00:00 RETURN QUERY VALUES (tsrange(''1996-01-01 0:0'', ts_to , ''[]'')) , (tsrange(ts_from, ''1996-01-08 0:0'', ''[]'')); ELSE -- simple case: range in standard week hoo_hours := tsrange(ts_from, ts_to, ''[]''); RETURN NEXT; END IF; RETURN; END $func$ LANGUAGE plpgsql IMMUTABLE COST 1000 ROWS 1;

Para INSERT una sola fila de entrada:

INSERT INTO hoo(shop_id, hours) SELECT 123, f_hoo_hours(''2016-01-11 00:00+04'', ''2016-01-11 08:00+04'');

Esto resulta en dos filas si el rango necesita división a las 00:00.

Para INSERT múltiples filas de entrada:

INSERT INTO hoo(shop_id, hours) SELECT id, hours FROM ( VALUES (7, timestamp ''2016-01-11 00:00'', timestamp ''2016-01-11 08:00'') , (8, ''2016-01-11 00:00'', ''2016-01-11 08:00'') ) t(id, f, t), f_hoo_hours(f, t) hours; -- LATERAL join

Acerca de la unión LATERAL implícita:

Consulta

Con el diseño ajustado, toda su consulta grande, compleja y costosa puede reemplazarse por ... esto:

SELECT *
FROM hoo
WHERE hours @> f_hoo_time(now());

Para un poco de suspenso, puse una placa de alerón sobre la solución. Mueve el mouse sobre él.

La consulta está respaldada por dicho índice GiST y es rápida, incluso para tablas grandes.

SQL Fiddle (con más ejemplos).

Si desea calcular el horario total de apertura (por tienda), aquí hay una receta:

Índice y rendimiento

El operador de contención para tipos de rango puede ser compatible con un índice GiST o SP-GiST . Cualquiera de los dos puede usarse para implementar una restricción de exclusión, pero solo GiST admite índices de columnas múltiples :

Actualmente, solo los tipos de índice B-tree, GiST, GIN y BRIN admiten índices de columnas múltiples.

Y el orden de las columnas de índice importa :

Se puede usar un índice GiST multicolumna con condiciones de consulta que involucran cualquier subconjunto de las columnas del índice. Las condiciones en columnas adicionales restringen las entradas devueltas por el índice, pero la condición en la primera columna es la más importante para determinar qué parte del índice necesita escanearse. Un índice GiST será relativamente ineficaz si su primera columna tiene solo unos pocos valores distintos, incluso si hay muchos valores distintos en columnas adicionales.

Entonces tenemos intereses en conflicto aquí. Para tablas grandes, habrá muchos más valores distintos para shop_id que para hours .

  • Un índice GiST con shop_id líder es más rápido de escribir y aplicar la restricción de exclusión.
  • Pero estamos buscando la columna de hours en nuestra consulta. Tener esa columna primero sería mejor.
  • Si necesitamos buscar shop_id en otras consultas, un índice simple de btree es mucho más rápido para eso.
  • Para colmo, encontré un índice SP-GiST en pocas hours para ser el más rápido para la consulta.

Punto de referencia

Mi script para generar datos ficticios:

INSERT INTO hoo(shop_id, hours) SELECT id, hours FROM generate_series(1, 30000) id, generate_series(0, 6) d , f_hoo_hours(((date ''1996-01-01'' + d) + interval ''4h'' + interval ''15 min'' * trunc(32 * random())) AT TIME ZONE ''UTC'' , ((date ''1996-01-01'' + d) + interval ''12h'' + interval ''15 min'' * trunc(64 * random() * random())) AT TIME ZONE ''UTC'') AS hours WHERE random() > .33;

Resultados en 141k filas generadas aleatoriamente, 30k distinct shop_id , 12k hours distintas. (Por lo general, la diferencia será mayor). Tamaño de la tabla 8 MB.

Eliminé y recreé la restricción de exclusión:

ALTER TABLE hoo ADD CONSTRAINT hoo_no_overlap EXCLUDE USING gist (shop_id WITH =, hours WITH &&); -- 4.4 sec !! ALTER TABLE hoo ADD CONSTRAINT hoo_no_overlap EXCLUDE USING gist (hours WITH &&, shop_id WITH =); -- 16.4 sec

shop_id primero es ~ 4x más rápido.

Además, probé dos más para el rendimiento de lectura:

CREATE INDEX hoo_hours_gist_idx on hoo USING gist (hours); CREATE INDEX hoo_hours_spgist_idx on hoo USING spgist (hours); -- !!

Después de VACUUM FULL ANALYZE hoo; , Ejecuté dos consultas:

  • Q1 : tarde en la noche, encontrando solo 53 filas
  • Q2 : por la tarde, encontrando 2423 filas .

Resultados

Obtuve un escaneo index-only para cada uno (excepto para "sin índice", por supuesto):

index idx size Q1 Q2 ------------------------------------------------ no index 41.24 ms 41.2 ms gist (shop_id, hours) 8MB 14.71 ms 33.3 ms gist (hours, shop_id) 12MB 0.37 ms 8.2 ms gist (hours) 11MB 0.34 ms 5.1 ms spgist (hours) 9MB 0.29 ms 2.0 ms -- !!

  • SP-GiST y GiST están a la par para las consultas que encuentran pocos resultados (GiST es aún más rápido para muy pocos).
  • SP-GiST escala mejor con un número creciente de resultados y también es más pequeño.

Si lee mucho más de lo que escribe (caso de uso típico), mantenga la restricción de exclusión tal como se sugirió desde el principio y cree un índice SP-GiST adicional para optimizar el rendimiento de lectura.

Estoy en la pila RoR y tuve que escribir algunos SQL reales para completar esta consulta para todos los registros que están "abiertos", lo que significa que la hora actual está dentro de las horas especificadas de operación. En la tabla hours_of_operations , dos columnas integer opens_on y closes_on store un weekday, y dos campos de time opens_at y closes_at almacenan la hora del día respectiva.

Hice una consulta que compara la fecha y la hora actual con los valores almacenados pero me pregunto si hay alguna forma de convertirlo a algún tipo de fecha y hacer que PostgreSQL haga el resto.

La carne de la consulta es:

WHERE ( ( /* Opens in Future */ (opens_on > 5 OR (opens_on = 5 AND opens_at::time > ''2014-03-01 00:27:25.851655'')) AND ( (closes_on < opens_on AND closes_on > 5) OR ((closes_on = opens_on) AND (closes_at::time < opens_at::time AND closes_at::time > ''2014-03-01 00:27:25.851655'')) OR ((closes_on = 5) AND (closes_at::time > ''2014-03-01 00:27:25.851655'' AND closes_at::time < opens_at::time))) OR /* Opens in Past */ (opens_on < 5 OR (opens_on = 5 AND opens_at::time < ''2014-03-01 00:27:25.851655'')) AND (closes_on > 5) OR ((closes_on = 5) AND (closes_at::time > ''2014-03-01 00:27:25.851655'')) OR (closes_on < opens_on) OR ((closes_on = opens_on) AND (closes_at::time < opens_at::time)) ) )

La razón de tal complejidad densa es que una hora de operación puede envolver al final de la semana, por ejemplo, comenzando al mediodía del domingo y hasta las 6 AM del lunes. Como almaceno valores en UTC, hay muchos casos en que la hora local del usuario podría envolver de una manera muy extraña. La consulta anterior asegura que puede ingresar CUALQUIER dos veces de la semana y compensamos el ajuste.