Início / dba / Perguntas / 318668
Accepted
ivan
Asked: 2022-10-25 19:06:02 +0800 CST

Proibir intervalos sobrepostos em uma programação cíclica (por exemplo, semanal)

  • 772

Estou projetando uma tabela postgres para registrar os intervalos contidos em uma programação semanal. Ele manteria agendas para várias empresas, e um conjunto de dados de exemplo simples pode ser semelhante a:

business_id  interval
-----------  -----------------------------------
1            Sunday   10:00:00 – Sunday 14:00:00
1            Sunday   22:00:00 – Monday 02:00:00
1            Friday   11:00:00 – Friday 16:00:00
1            Saturday 15:00:00 – Sunday 01:00:00

Observe que os intervalos podem cruzar os limites entre os dias.

Uma empresa não deve ter intervalos sobrepostos e gostaria de projetar a tabela de uma forma que me permitisse impor isso.

Eu estava pensando em mapear esses valores de dia da semana + hora do dia para os números correspondentes de segundos desde o início da semana, armazenando intervalos como int4rangee usando uma restrição de exclusão para proibir a sobreposição de intervalos inteiros, mas isso não seria adequado intervalos de endereços que envolvem o final da semana.

Existe uma boa maneira de modelar esse tipo de dados cíclicos e proibir sobreposições?

postgresql

2 respostas

  1. Best Answer

    Mapeie cada valor de dia da semana + hora do dia para o número correspondente de segundos desde o início da semana em que o intervalo começou, armazene os intervalos em uma int4rangecoluna e adicione duas restrições de exclusão.

    A primeira restrição de exclusão proíbe intervalos que se sobrepõem sem envolver o final da semana:

    ALTER TABLE weekly_intervals
ADD CONSTRAINT exclude_overlapping_intervals
EXCLUDE USING GIST (
  business_id WITH =,
  interval WITH &&
)
DEFERRABLE INITIALLY DEFERRED

    A segunda restrição de exclusão proíbe intervalos que se sobrepõem devido a um deles envolver o final da semana:

    -- full week: 7 * 24 * 60 * 60 = 604800

ALTER TABLE weekly_intervals
ADD CONSTRAINT exclude_overlapping_intervals_wraparound
EXCLUDE USING GIST (
  business_id WITH =,
  (
    CASE WHEN upper(interval) > 604800
      THEN int4range(
             lower(interval) - 604800,
             upper(interval) - 604800,
            '[)'
           )
      ELSE interval
    END
  ) WITH &&
)
DEFERRABLE INITIALLY DEFERRED

    As restrições de exclusão são a parte interessante, mas, para completar, a tabela em si seria algo como:

    CREATE TABLE weekly_intervals (
    business_id bigint NOT NULL,
    interval int4range NOT NULL,
    CONSTRAINT interval_duration_max_one_week CHECK (upper(interval) <= lower(interval) + 604800),
    CONSTRAINT interval_left_closed CHECK (lower_inc(interval)),
    CONSTRAINT interval_right_open CHECK (NOT upper_inc(interval)),
    CONSTRAINT interval_start CHECK (lower(interval) <@ int4range(0, 604800, '[)'))
);
    • 1

  2. Este é um caso de uso perfeito para o RANGEtipo (fantástico) do PostgreSQL. Todo o código abaixo está disponível no violino aqui .

    Um excelente artigo sobre eles pode ser encontrado aqui (por Dimitri Fontaine) - o principal a observar é:

    o exemplo principal é o tipo de dados daterange, que armazena como um único valor a lowere um upperlimite do intervalo como um único valor.

    O principal a observar é que um intervalo é ordenado - o primeiro valor é menor que o segundo, portanto, não há medo de compromissos sem sentido que começam após o término (e já vi sistemas que realmente permitem isso!).

    Um post interessante sobre como eles podem ser usados ​​para "transformar 100 linhas de SQL em 3" pode ser encontrado aqui ( tipos multi-range nb ).

    Tabela de amostra simples:

    CREATE TABLE test
(
  business_id INT NOT NULL,
  intval      TSTZRANGE,
  EXCLUDE USING GIST (business_id WITH =, intval WITH &&)
);

    Então, isso nos diz que nenhum intvalpode se sobrepor a outro com o mesmo business_id (pessoa, recurso...).

    Testar:

    INSERT INTO test VALUES
(1, '[2022-01-01 11:30, 2022-01-01 15:00)');

    e depois:

    --
-- Non-overlapping
-- 

INSERT INTO test VALUES
(1, '[2022-01-01 16:30, 2022-01-01 18:00)');

    Sem problemas!

    Mas!

    --
-- Overlapping!
--

INSERT INTO test VALUES
(1, '[2022-01-01 10:30, 2022-01-01 13:00)');

    e obtemos (corretamente):

    ERRO: o valor da chave conflitante viola a restrição de exclusão "test_business_id_intval_excl" DETALHE: Chave (business_id, intval)=(1, ["2022-01-01 10:30:00+00","2022-01-01 13:00:00 +00")) está em conflito com a chave existente (business_id, intval)=(1, ["2022-01-01 11:30:00+00","2022-01-01 15:00:00+00")) .

    e agora com um diferente business_id, mas se sobrepõe aos horários anteriores:

    --
-- Interval overlaps, but business_id (person, other resource
-- doesn't - no problem!
--

INSERT INTO test VALUES
(2, '[2022-01-01 11:30, 2022-01-01 15:00)');

    Resultado (como esperado):

    INSERT 0 1

    Não há necessidade de se preocupar com domingos, segundas-feiras ou intervalos que ultrapassam os limites do dia, semana, mês ou ano. Esses tipos de intervalo são incrivelmente poderosos e valem o esforço de conhecê-los. Veja aqui uma lista de funções e operadores.

    Seguindo os comentários do OP, aqui está um código para configurar algum tipo de agendamento de turno. Provavelmente poderia fazer algum tratamento com PL/pgSQL para arrumar, para agendamentos recorrentes, como linha de base, pode ajudar.

    Seu comentário diz (presumivelmente como um exemplo) "Mondays 9AM to 5PM, Tuesdays 10AM to 6PM, .., então eu atendi a esse exemplo - obviamente você pode adicionar seu próprio código para cenários mais complexos. Você também pode usar o tipo de dados range conforme descrito acima.

    SELECT
  d.i,
  h.i,
 '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL AS "Slot",
  EXTRACT(DOW FROM  '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) AS "Day num",
  TO_CHAR( '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL, 'DAY') AS "Day name",
  CASE
    WHEN 
      EXTRACT(DOW  FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) =   1 AND 
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) >=  9 AND
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) <= 17 THEN TRUE
    WHEN 
      EXTRACT(DOW  FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) =   2 AND 
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) >= 10 AND
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) <= 18 THEN TRUE
    ELSE FALSE
  END AS "Shift"
FROM 
  GENERATE_SERIES(0,  6) AS d(i),  --  <<-- generates hourly slots for weeks/months... on end
  GENERATE_SERIES(0, 23) AS h(i)

LIMIT 150;                        -- don't want to overload db<>fiddle - this shows the example shifts.

    Resultado (recortado por brevidade):

    i   i   Slot    Day num     Day name    Shift
0   0   2022-10-31 00:00:00     1   MONDAY  f
0   1   2022-10-31 01:00:00     1   MONDAY  f
0   2   2022-10-31 02:00:00     1   MONDAY  f
0   3   2022-10-31 03:00:00     1   MONDAY  f
0   4   2022-10-31 04:00:00     1   MONDAY  f
0   5   2022-10-31 05:00:00     1   MONDAY  f
0   6   2022-10-31 06:00:00     1   MONDAY  f
0   7   2022-10-31 07:00:00     1   MONDAY  f
0   8   2022-10-31 08:00:00     1   MONDAY  f
0   9   2022-10-31 09:00:00     1   MONDAY  t
0   10  2022-10-31 10:00:00     1   MONDAY  t
0   11  2022-10-31 11:00:00     1   MONDAY  t
0   12  2022-10-31 12:00:00     1   MONDAY  t

    Então, você pode ver aqui (e se você examinar o violino) que as segundas-feiras das 09:00 às 17:00 são horas de turno e as terças das 10:00 às 18:00 são horas de turno.

    • 0

relate perguntas