camtech's questions

Problema

Ao trabalhar com dados SCD tipo 2, é fácil ver o estado de uma tabela "a partir de" um determinado momento usando suas colunas de data, por exemplo: valid_frome valid_to. Por exemplo:

select * from table
where '2023-11-01' between valid_from and valid_to

Você pode então agrupar ou usar funções de janela para agregar.

Mas e se eu quiser fazer isso repetidamente, para cada data de um intervalo (por exemplo: diariamente)? Não preciso agregar essas datas, apenas dentro de cada uma.

Exemplo

Digamos que eu tenha uma tabela que rastreia o quantitynúmero de pessoas de cada um reservation_id. Tanto a quantitypersonalidade das pessoas quanto a reservation_statuspodem mudar com o tempo, conforme monitorado pela tabela. Cada reserva está anexada a um arquivo event_id. event_dateestá incluído para ajudar a limitar o escopo, se necessário (veja as suposições abaixo).

PostgreSQL já que o BigQuery é mais difícil de testar – db-fiddle /SQL:

CREATE TABLE Reservations (
  "reservation_id" INTEGER,
  "reservation_status" VARCHAR(9),
  "quantity" INTEGER,
  "event_id" INTEGER,
  "event_date" DATE,
  "valid_from" DATE,
  "valid_to" DATE
);

INSERT INTO Reservations
  ("reservation_id", "reservation_status", "quantity", "event_id", "event_date", "valid_from", "valid_to")
VALUES
  ('1', 'active', '4', '100', '2023-05-25', '2023-01-01', '2023-01-02'),
  ('2', 'active', '2', '200', '2024-01-07', '2023-01-01', '2023-01-03'),
  ('3', 'active', '7', '100', '2023-05-25', '2023-01-02', '9999-12-31'),
  ('4', 'active', '1', '200', '2024-01-07', '2023-01-02', '9999-12-31'),
  ('1', 'active', '5', '100', '2023-05-25', '2023-01-03', '9999-12-31'),
  ('5', 'active', '8', '100', '2023-05-25', '2023-01-03', '9999-12-31'),
  ('2', 'cancelled', '2', '200', '2024-01-07', '2023-01-04', '9999-12-31'),
  ('6', 'active', '3', '100', '2023-05-25', '2023-01-06', '9999-12-31');

Embora isso seja, em última análise, para o BigQuery, a resposta será aceita em qualquer dialeto, desde que seja um tanto genérica.

Premissas

• As datas "a partir de" podem ser uma lista ou intervalo baseado em valid_frommínimo/máximo
• valid_tode 9999-12-31são os dados mais recentes
• Todas as reservas para um determinado evento serão entre event_date - INTERVAL '2 years'e event_date. Isso não muda nada neste exemplo, mas talvez seja útil para dimensionar (?)

Saída desejada

Gostaria de saber a soma dos quantityagrupados por event_ide reservation_status a partir de cada intervalo (dia).

^{Estimativa aproximada dos valores das linhas. Será diferente se usar o intervalo completo de datas.}

Eu essencialmente quero fazer o seguinte:

/* Invalid SQL, just for conceptual purposes */

-- Given a list of dates, for each "date":
select
  event_id,
  reservation_status,
  sum(quantity)
from table
where {{date}} between valid_from and valid_to
group by
  event_id,
  reservation_status

Acredito que isso pode ser feito usando linguagem processual, como um loop for, mas sinto que estou pensando demais nisso e tendo problemas para combinar conceitos mais simples.

Eu tenho várias tabelas que normalmente possuem colunas de data e hora "CreatedDate" e/ou "ModifiedDate" anuláveis. Eu gostaria de uma consulta que pudesse selecionar qualquer tabela única onde existisse uma, ambas ou nenhuma coluna de data e hora e cada coluna recebesse BETWEENintervalos (separados).

Aqui está um exemplo aproximado (não funcional). Isso tenta usar COL_LENGTHpara determinar se existe uma coluna. Porém, se uma coluna não estiver presente, será gerado um erro, acredito porque o todo WHEREainda é avaliado.

SELECT * FROM tableName
WHERE 1=1 AND
(COL_LENGTH(tableName, 'CreatedDate') IS NULL OR CreatedDate BETWEEN @CreatedFrom AND @CreatedTo) AND
(COL_LENGTH(tableName, 'ModifiedDate') IS NULL OR ModifiedDate BETWEEN @ModifiedFrom AND @ModifiedTo)

Isso pode ser resolvido com SQL dinâmico. No entanto, neste caso estou restrito a permissões focadas na leitura. Portanto, não EXEC, CREATE ou INSERT.

Isso é possível com SQL estático?

Tabelas de exemplo

Com ambas as colunas:

DECLARE @Table1 table
(
    Id int NOT NULL,
    CreatedDate datetime NULL,
    ModifiedDate datetime NULL
);

INSERT @Table1
    (Id, CreatedDate, ModifiedDate)
VALUES
    (1, NULL, NULL),
    (2, NULL, '20230101 01:23:45.678'),
    (3, '20230101 02:34:56.789', NULL),
    (4, '20230101 03:45:00.891', '20230101 04:56:00.789');

DECLARE 
    @CreatedFrom datetime = {TS '2023-01-01 01:30:00.000'},
    @CreatedTo datetime = {TS '2023-12-31 23:30:00.000'},
    @ModifiedFrom datetime = {TS '2023-01-01 06:00:00.000'},
    @ModifiedTo datetime = {TS '2023-12-31 18:00:00.000'};

Com uma coluna:

DECLARE @Table2 table
(
    Id int NOT NULL,
    CreatedDate datetime NULL
);

INSERT @Table2
    (Id, CreatedDate)
VALUES
    (1, NULL),
    (2, '20230101 02:34:56.789');

DECLARE 
    @CreatedFrom datetime = {TS '2023-01-01 01:30:00.000'},
    @CreatedTo datetime = {TS '2023-12-31 23:30:00.000'};

^{Obrigado @PaulWhite pelo exemplo inicial .}