Estou construindo um banco de dados com Postgres onde haverá um monte de agrupamento de coisas por monthe year, mas nunca pelo date.
monthe yearcolunas e usá-los.month_yearcoluna e sempre definir daycomo 1.O primeiro parece um pouco mais simples e claro se alguém estiver analisando os dados, mas o último é bom porque usa um tipo adequado.
Vamos supor que eu queira armazenar números de telefone em um banco de dados. Posso aceitar números de telefone de fora dos Estados Unidos. Como eu faria para armazenar esses números de telefone?
Dentro de um aplicativo da Web em que estou trabalhando, todas as operações de banco de dados são abstraídas usando alguns repositórios genéricos definidos sobre o Entity Framework ORM.
No entanto, para ter um design simples para os repositórios genéricos, todas as tabelas envolvidas devem definir um inteiro único ( Int32em C#, intem SQL). Até agora, esse sempre foi o PK da mesa e também o IDENTITY.
As chaves estrangeiras são muito usadas e fazem referência a essas colunas inteiras. Eles são necessários para a consistência e para a geração de propriedades de navegação pelo ORM.
A camada de aplicação normalmente faz as seguintes operações:
SELECT * FROM tableUPDATE table SET Col1 = Val1 WHERE Id = IdValDELETE FROM table WHERE Id = IdValINSERT INTO table (cols) VALUES (...)Operações menos frequentes:
BULK INSERT ... into tableseguida (*) por todo o carregamento de dados (para recuperar identificadores gerados)DELETE FROM table where OtherThanIdCol = SomeValueUPDATE table SET SomeCol = SomeVal WHERE OtherThanIdCol = OtherValue*todas as tabelas pequenas são armazenadas em cache no nível do aplicativo e quase todas SELECTsnão alcançarão o banco de dados. Um padrão típico é a carga inicial e muitos INSERTs, UPDATEs e DELETEs.
Com base no uso atual do aplicativo, há uma chance muito pequena de atingir 100 milhões de registros em qualquer uma das tabelas.
Pergunta: Do ponto de vista de um DBA, existem problemas significativos que posso encontrar por ter essa limitação de design de tabela?
[EDITAR]
Depois de ler as respostas (obrigado pelo ótimo feedback) e os artigos referenciados, sinto que tenho que adicionar mais detalhes:
Especificidades do aplicativo atual - não mencionei sobre o aplicativo da web atual, porque quero entender se o modelo pode ser reutilizado para outros aplicativos também. No entanto, meu caso particular é um aplicativo que extrai muitos metadados de um DWH. Os dados de origem são bastante confusos (desnormalizados de maneira estranha, com algumas inconsistências, sem identificador natural em muitos casos etc.) e meu aplicativo está gerando entidades separadas claras. Além disso, muitos dos identificadores gerados ( IDENTITY) são exibidos, para que o usuário possa utilizá-los como chaves de negócios. Isso, além de uma refatoração de código massiva, exclui o uso de GUIDs .
"eles não devem ser a única maneira de identificar exclusivamente uma linha" (Aaron Bertrand♦) - esse é um conselho muito bom. Todas as minhas tabelas também definem uma UNIQUE CONSTRAINT para garantir que duplicatas de negócios não sejam permitidas.
Design orientado a aplicativos de front-end versus design orientado a banco de dados - a escolha do design é causada por esses fatores
Limitações do Entity Framework - PKs de várias colunas são permitidos, mas seus valores não podem ser atualizados
Limitações personalizadas - ter uma única chave inteira simplifica muito as estruturas de dados e o código não SQL. Ex.: todas as listas de valores possuem uma chave inteira e valores exibidos. Mais importante, garante que qualquer tabela marcada para armazenamento em cache poderá ser colocada em um Unique int key -> valuemapa.
Consultas de seleção complexas - isso quase nunca acontecerá porque todos os dados de tabelas pequenas (< 20-30 K registros) são armazenados em cache no nível do aplicativo. Isso torna a vida um pouco mais difícil ao escrever o código do aplicativo (mais difícil de escrever LINQ), mas o banco de dados é atingido muito melhor:
Visualizações de lista - não gerarão SELECTconsultas no carregamento (tudo é armazenado em cache) ou consultas que se parecem com isso:
SELECT allcolumns FROM BigTable WHERE filter1 IN (val1, val2) AND filter2 IN (val11, val12)
Todos os outros valores necessários são buscados por meio de pesquisas de cache (O(1)), portanto, nenhuma consulta complexa será gerada.
Editar visualizações - gerará SELECTinstruções como esta:
SELECT allcolumns FROM BigTable WHERE PKId = value1
(todos os filtros e valores são ints)
De acordo com a documentação datetime2 (Transact-SQL) :
Tamanho de armazenamento
6 bytes para precisões menores que 3.
7 bytes para precisões 3 e 4.
Todas as outras precisões requerem 8 bytes.
O tamanho de datetime2(0), datetime2(1), datetime2(2)usa a mesma quantidade de armazenamento (6 bytes).
Estaria correto ao dizer que poderia muito bem seguir datetime2(2)e obter o benefício da precisão sem nenhum custo adicional de tamanho?
Observe:
A CPU seria datetime2(0)mais eficiente quando usada em uma cláusula where ou ao buscar por meio de um índice?
Esta é uma tabela enorme, então a menor otimização fará uma grande diferença.
Observe que esta pergunta é independente de fornecedor/versão
Parece-me, como um falante (datilógrafo, escritor) de inglês, razoável esperar que as palavras sejam adequadamente maiúsculas, mas não necessariamente tenham os acentos corretos indo na direção certa:
como refleti em um tête-à-tête com Chloe, a maitre d'hôtel, no restaurante Champs-Elysées, enquanto esperava que o garçon trouxesse meu patê de jalapeno salteado...
Você começa a idéia com isso.
Então, hoje eu pensei que queria uma condição de pesquisa para usar um agrupamento que diferencia maiúsculas de minúsculas, mas não diferencia acentos, mas não consegui encontrar um. Existe uma boa razão para isso ou o meu é apenas um caso de uso raro?
Aqui está um exemplo de alguma documentação que eu estava olhando (embora pensando em fornecedor/versão agnóstica):
Estou descobrindo TYPEno PostgreSQL. Tenho um TABLE TYPEque alguma tabela deve respeitar (interface). Por exemplo:
CREATE TYPE dataset AS(
ChannelId INTEGER
,GranulityIdIn INTEGER
,GranulityId INTEGER
,TimeValue TIMESTAMP
,FloatValue FLOAT
,Status BIGINT
,QualityCodeId INTEGER
,DataArray FLOAT[]
,DataCount BIGINT
,Performance FLOAT
,StepCount INTEGER
,TableRegClass regclass
,Tags TEXT[]
,WeightedMean FLOAT
,MeanData FLOAT
,StdData FLOAT
,MinData FLOAT
,MaxData FLOAT
,MedianData FLOAT
,Percentiles FLOAT[]
);
Eu posso criar uma tabela usando este modelo com:
CREATE TABLE test OF dataset;
Já vi muitas opções na API , mas estou um pouco perdido. Gostaria de saber se é possível atribuir esse tipo aos INPUT/OUTPUTparâmetros da função.
Digamos que eu tenha um FUNCTIONchamado processque receba uma amostra de registros de um dataset TABLE source, os processe e depois retorne um TABLE sinkcom o mesmo TYPE.
Ou seja, gostaria de saber se é possível criar um TYPEque se comporte assim:
CREATE FUNCTION process(
input dataset
) RETURNS dataset
AS ...
E isso pode ser chamado assim:
SELECT
*
FROM
source, process(input := source) AS sink;
Eu me pergunto se isso é possível com o PostgreSQL e pergunto como fazer isso. Alguém de vocês sabe?
Aqui está um MWE do que estou tentando fazer:
DROP TABLE IF EXISTS source;
DROP FUNCTION IF EXISTS process(dataset);
DROP TYPE dataset;
CREATE TYPE dataset AS (
id INTEGER
,t TIMESTAMP
,x FLOAT
);
CREATE TABLE source OF dataset;
ALTER TABLE source ADD PRIMARY KEY(Id);
INSERT INTO source VALUES
(1, '2016-01-01 00:00:00', 10.0)
,(2, '2016-01-01 00:30:00', 11.0)
,(3, '2016-01-01 01:00:00', 12.0)
,(4, '2016-01-01 01:30:00', 9.0)
;
CREATE OR REPLACE FUNCTION process(
_source dataset
)
RETURNS SETOF dataset
AS
$BODY$
SELECT * FROM source;
$BODY$
LANGUAGE SQL;
SELECT * FROM process(source);
Mas não tem sucesso, é como se a fonte fosse percebida como uma coluna em vez de uma SETOF RECORDScom o tipo de conjunto de dados.
Digamos que eu tenha os seguintes valores inteiros em uma tabela
32
11
15
123
55
54
23
43
44
44
56
23
OK, a lista pode continuar; Não importa. Agora eu quero consultar esta tabela e quero retornar um certo número de arquivos closest records. Digamos que eu queira retornar 10 correspondências de registro mais próximas ao número 32. Posso fazer isso com eficiência?
Está no SQL Server 2014.
Comparando algumas das respostas na pergunta do Palindrome (mais de 10 mil usuários apenas, desde que excluí a resposta), estou obtendo resultados confusos.
Propus um TVF com várias instruções e vinculado ao esquema, que pensei que seria mais rápido do que executar uma função padrão, o que é. Também tive a impressão de que o TVF com várias instruções seria "embutido", embora eu esteja errado nessa contagem, como você verá abaixo. Esta pergunta é sobre a diferença de desempenho desses dois estilos de TVF. Primeiro, você precisa ver o código.
Aqui está o TVF de várias declarações:
IF OBJECT_ID('dbo.IsPalindrome') IS NOT NULL
DROP FUNCTION dbo.IsPalindrome;
GO
CREATE FUNCTION dbo.IsPalindrome
(
@Word NVARCHAR(500)
)
RETURNS @t TABLE
(
IsPalindrome BIT NOT NULL
)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @IsPalindrome BIT;
DECLARE @LeftChunk NVARCHAR(250);
DECLARE @RightChunk NVARCHAR(250);
DECLARE @StrLen INT;
DECLARE @Pos INT;
SET @RightChunk = '';
SET @IsPalindrome = 0;
SET @StrLen = LEN(@Word) / 2;
IF @StrLen % 2 = 1 SET @StrLen = @StrLen - 1;
SET @Pos = LEN(@Word);
SET @LeftChunk = LEFT(@Word, @StrLen);
WHILE @Pos > (LEN(@Word) - @StrLen)
BEGIN
SET @RightChunk = @RightChunk + SUBSTRING(@Word, @Pos, 1)
SET @Pos = @Pos - 1;
END
IF @LeftChunk = @RightChunk SET @IsPalindrome = 1;
INSERT INTO @t VALUES (@IsPalindrome);
RETURN
END
GO
O TVF em linha:
IF OBJECT_ID('dbo.InlineIsPalindrome') IS NOT NULL
DROP FUNCTION dbo.InlineIsPalindrome;
GO
CREATE FUNCTION dbo.InlineIsPalindrome
(
@Word NVARCHAR(500)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
WITH Nums AS
(
SELECT
N = number
FROM
dbo.Numbers
)
SELECT
IsPalindrome =
CASE
WHEN EXISTS
(
SELECT N
FROM Nums
WHERE N <= L / 2
AND SUBSTRING(S, N, 1) <> SUBSTRING(S, 1 + L - N, 1)
)
THEN 0
ELSE 1
END
FROM
(SELECT LTRIM(RTRIM(@Word)), LEN(@Word)) AS v (S, L)
);
GO
A Numberstabela na função acima é definida como:
CREATE TABLE dbo.Numbers
(
Number INT NOT NULL
);
Observação: a tabela de números não possui nenhum índice nem chave primária e contém 1.000.000 linhas.
Uma mesa temporária de teste:
IF OBJECT_ID('tempdb.dbo.#Words') IS NOT NULL
DROP TABLE #Words;
GO
CREATE TABLE #Words
(
Word VARCHAR(500) NOT NULL
);
INSERT INTO #Words(Word)
SELECT o.name + REVERSE(w.name)
FROM sys.objects o
CROSS APPLY (
SELECT o.name
FROM sys.objects o
) w;
No meu sistema de teste, os INSERTresultados acima em 16.900 linhas sendo inseridas na #Wordstabela.
Para testar as duas variações, eu SET STATISTICS IO, TIME ON;e uso o seguinte:
SELECT w.Word
, p.IsPalindrome
FROM #Words w
CROSS APPLY dbo.IsPalindrome(w.Word) p
ORDER BY w.Word;
SELECT w.Word
, p.IsPalindrome
FROM #Words w
CROSS APPLY dbo.InlineIsPalindrome(w.Word) p
ORDER BY w.Word;
Eu esperava que a InlineIsPalindromeversão fosse significativamente mais rápida, porém os resultados a seguir não suportam essa suposição.
TVF multi-estado:
Tabela '#A1CE04C3'. Contagem de varredura 16896, leituras lógicas 16900, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Table 'Worktable'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Tabela '#Words'. Contagem de varredura 1, leituras lógicas 88, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.Tempos de execução do SQL Server:
tempo de CPU = 1700 ms, tempo decorrido = 2022 ms.
Tempo de análise e compilação do SQL Server: tempo
de CPU = 0 ms, tempo decorrido = 0 ms.
TVF em linha:
Tabela 'Números'. Contagem de varredura 1, leituras lógicas 1272030, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Tabela 'Worktable'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Tabela '#Words'. Contagem de varredura 1, leituras lógicas 88, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.Tempos de execução do SQL Server:
tempo de CPU = 137874 ms, tempo decorrido = 139415 ms.
Tempo de análise e compilação do SQL Server: tempo
de CPU = 0 ms, tempo decorrido = 0 ms.
Os planos de execução se parecem com:
Por que a variante inline é muito mais lenta do que a variante de várias instruções, neste caso?
Em resposta a um comentário de @AaronBertrand, modifiquei a dbo.InlineIsPalindromefunção para limitar as linhas retornadas pelo CTE para corresponder ao comprimento da palavra de entrada:
CREATE FUNCTION dbo.InlineIsPalindrome
(
@Word NVARCHAR(500)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
WITH Nums AS
(
SELECT
N = number
FROM
dbo.Numbers
WHERE
number <= LEN(@Word)
)
SELECT
IsPalindrome =
CASE
WHEN EXISTS
(
SELECT N
FROM Nums
WHERE N <= L / 2
AND SUBSTRING(S, N, 1) <> SUBSTRING(S, 1 + L - N, 1)
)
THEN 0
ELSE 1
END
FROM
(SELECT LTRIM(RTRIM(@Word)), LEN(@Word)) AS v (S, L)
);
Como sugeriu @MartinSmith, adicionei uma chave primária e um índice clusterizado à dbo.Numberstabela, o que certamente ajuda e estaria mais próximo do que se esperaria ver em um ambiente de produção.
Executar novamente os testes acima agora resulta nas seguintes estatísticas:
CROSS APPLY dbo.IsPalindrome(w.Word) p:
(17424 linhas afetadas)
Tabela '#B1104853'. Contagem de varredura 17420, leituras lógicas 17424, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Table 'Worktable'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Tabela '#Words'. Contagem de varredura 1, leituras lógicas 90, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.Tempos de execução do SQL Server:
tempo de CPU = 1763 ms, tempo decorrido = 2192 ms.
dbo.FunctionIsPalindrome(w.Word):
(17424 linhas afetadas)
Tabela 'Worktable'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Tabela '#Words'. Contagem de varredura 1, leituras lógicas 90, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.Tempos de execução do SQL Server:
tempo de CPU = 328 ms, tempo decorrido = 424 ms.
CROSS APPLY dbo.InlineIsPalindrome(w.Word) p:
(17424 linhas afetadas)
Tabela 'Números'. Contagem de varredura 1, leituras lógicas 237100, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Tabela 'Worktable'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Tabela '#Words'. Contagem de varredura 1, leituras lógicas 90, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.Tempos de execução do SQL Server:
tempo de CPU = 17737 ms, tempo decorrido = 17946 ms.
Estou testando isso no SQL Server 2012 SP3, v11.0.6020, Developer Edition.
Aqui está a definição da minha tabela de números, com a chave primária e o índice clusterizado:
CREATE TABLE dbo.Numbers
(
Number INT NOT NULL
CONSTRAINT PK_Numbers
PRIMARY KEY CLUSTERED
);
;WITH n AS
(
SELECT v.n
FROM (
VALUES (1)
,(2)
,(3)
,(4)
,(5)
,(6)
,(7)
,(8)
,(9)
,(10)
) v(n)
)
INSERT INTO dbo.Numbers(Number)
SELECT ROW_NUMBER() OVER (ORDER BY n1.n)
FROM n n1
, n n2
, n n3
, n n4
, n n5
, n n6;
Eu preciso converter dados entre dois sistemas.
O primeiro sistema armazena horários como uma lista simples de datas. Cada data incluída na agenda é uma linha. Podem existir várias lacunas na sequência de datas (fins de semana, feriados e pausas mais longas, alguns dias da semana podem ser excluídos da programação). Não pode haver nenhuma lacuna, mesmo fins de semana podem ser incluídos. O cronograma pode durar até 2 anos. Geralmente dura algumas semanas.
Aqui está um exemplo simples de uma programação que abrange duas semanas excluindo fins de semana (existem exemplos mais complicados no script abaixo):
+----+------------+------------+---------+--------+
| ID | ContractID | dt | dowChar | dowInt |
+----+------------+------------+---------+--------+
| 10 | 1 | 2016-05-02 | Mon | 2 |
| 11 | 1 | 2016-05-03 | Tue | 3 |
| 12 | 1 | 2016-05-04 | Wed | 4 |
| 13 | 1 | 2016-05-05 | Thu | 5 |
| 14 | 1 | 2016-05-06 | Fri | 6 |
| 15 | 1 | 2016-05-09 | Mon | 2 |
| 16 | 1 | 2016-05-10 | Tue | 3 |
| 17 | 1 | 2016-05-11 | Wed | 4 |
| 18 | 1 | 2016-05-12 | Thu | 5 |
| 19 | 1 | 2016-05-13 | Fri | 6 |
+----+------------+------------+---------+--------+
IDé único, mas não é necessariamente sequencial (é a chave primária). As datas são únicas dentro de cada Contrato (há um índice único em (ContractID, dt)).
O segundo sistema armazena as programações como intervalos com a lista de dias da semana que fazem parte da programação. Cada intervalo é definido por suas datas de início e término (inclusive) e uma lista de dias da semana incluídos na programação. Nesse formato, você pode definir com eficiência padrões semanais repetitivos, como de segunda a quarta, mas torna-se um problema quando um padrão é interrompido, por exemplo, em um feriado.
Veja como ficará o exemplo simples acima:
+------------+------------+------------+----------+----------------------+
| ContractID | StartDT | EndDT | DayCount | WeekDays |
+------------+------------+------------+----------+----------------------+
| 1 | 2016-05-02 | 2016-05-13 | 10 | Mon,Tue,Wed,Thu,Fri, |
+------------+------------+------------+----------+----------------------+
[StartDT;EndDT]intervalos que pertençam ao mesmo Contrato não devem se sobrepor.
Preciso converter dados do primeiro sistema para o formato usado pelo segundo sistema. No momento, estou resolvendo isso no lado do cliente em C # para o único contrato fornecido, mas gostaria de fazer isso em T-SQL no lado do servidor para processamento em massa e exportação/importação entre servidores. Provavelmente, isso poderia ser feito usando CLR UDF, mas neste estágio não posso usar SQLCLR.
O desafio aqui é tornar a lista de intervalos o mais curta e amigável possível.
Por exemplo, este cronograma:
+-----+------------+------------+---------+--------+
| ID | ContractID | dt | dowChar | dowInt |
+-----+------------+------------+---------+--------+
| 223 | 2 | 2016-05-05 | Thu | 5 |
| 224 | 2 | 2016-05-06 | Fri | 6 |
| 225 | 2 | 2016-05-09 | Mon | 2 |
| 226 | 2 | 2016-05-10 | Tue | 3 |
| 227 | 2 | 2016-05-11 | Wed | 4 |
| 228 | 2 | 2016-05-12 | Thu | 5 |
| 229 | 2 | 2016-05-13 | Fri | 6 |
| 230 | 2 | 2016-05-16 | Mon | 2 |
| 231 | 2 | 2016-05-17 | Tue | 3 |
+-----+------------+------------+---------+--------+
deve se tornar isso:
+------------+------------+------------+----------+----------------------+
| ContractID | StartDT | EndDT | DayCount | WeekDays |
+------------+------------+------------+----------+----------------------+
| 2 | 2016-05-05 | 2016-05-17 | 9 | Mon,Tue,Wed,Thu,Fri, |
+------------+------------+------------+----------+----------------------+
,isso não:
+------------+------------+------------+----------+----------------------+
| ContractID | StartDT | EndDT | DayCount | WeekDays |
+------------+------------+------------+----------+----------------------+
| 2 | 2016-05-05 | 2016-05-06 | 2 | Thu,Fri, |
| 2 | 2016-05-09 | 2016-05-13 | 5 | Mon,Tue,Wed,Thu,Fri, |
| 2 | 2016-05-16 | 2016-05-17 | 2 | Mon,Tue, |
+------------+------------+------------+----------+----------------------+
Eu tentei aplicar uma gaps-and-islandsabordagem para este problema. Eu tentei fazer isso em duas passagens. Na primeira passagem encontro ilhas de dias consecutivos simples, ou seja, o fim da ilha é qualquer intervalo na sequência de dias, seja final de semana, feriado ou qualquer outra coisa. Para cada ilha encontrada, construo uma lista separada por vírgulas de arquivos WeekDays. Na segunda passagem, o grupo I encontrou ilhas mais adiante, observando a lacuna na sequência dos números da semana ou uma mudança no WeekDays.
Com essa abordagem, cada semana parcial acaba sendo um intervalo extra, conforme mostrado acima, porque, embora os números das semanas sejam consecutivos, a WeekDaysmudança. Além disso, pode haver intervalos regulares dentro de uma semana (veja ContractID=3nos dados de amostra, que contém dados apenas para Mon,Wed,Fri,) e essa abordagem geraria intervalos separados para cada dia nessa programação. Pelo lado bom, gera um intervalo se o cronograma não tiver nenhuma lacuna (veja ContractID=7nos dados de amostra que incluem finais de semana) e, nesse caso, não importa se a semana de início ou fim é parcial.
Por favor, veja outros exemplos no script abaixo para ter uma ideia melhor do que estou procurando. Você pode ver que muitas vezes os fins de semana são excluídos, mas quaisquer outros dias da semana também podem ser excluídos. No exemplo 3 apenas Mon, Wede Frifazem parte do cronograma. Além disso, finais de semana podem ser incluídos, como no exemplo 7. A solução deve tratar todos os dias da semana igualmente. Qualquer dia da semana pode ser incluído ou excluído da programação.
Para verificar se a lista de intervalos gerada descreve o cronograma fornecido corretamente, você pode usar o seguinte pseudocódigo:
WeekDays. Se sim, então esta data está incluída no cronograma.Esperançosamente, isso esclarece em quais casos um novo intervalo deve ser criado. Nos exemplos 4 e 5 uma segunda-feira ( 2016-05-09) é retirada do meio do horário e tal horário não pode ser representado por um único intervalo. No exemplo 6, há um longo intervalo no cronograma, portanto, são necessários dois intervalos.
Os intervalos representam padrões semanais na programação e quando um padrão é interrompido/alterado, um novo intervalo deve ser adicionado. No exemplo 11, as três primeiras semanas têm um padrão Tue, então esse padrão muda para Thu. Como resultado, precisamos de dois intervalos para descrever tal cronograma.
Estou usando o SQL Server 2008 no momento, então a solução deve funcionar nesta versão. Se uma solução para SQL Server 2008 puder ser simplificada/melhorada usando recursos de versões posteriores, isso é um bônus, mostre-o também.
Eu tenho uma Calendartabela (lista de datas) e uma Numberstabela (lista de números inteiros começando em 1), então não há problema em usá-los, se necessário. Também não há problema em criar tabelas temporárias e ter várias consultas que processam dados em vários estágios. O número de estágios em um algoritmo deve ser corrigido, porém, cursores e WHILEloops explícitos não são aceitáveis.
Script para dados de amostra e resultados esperados
-- @Src is sample data
-- @Dst is expected result
DECLARE @Src TABLE (ID int PRIMARY KEY, ContractID int, dt date, dowChar char(3), dowInt int);
INSERT INTO @Src (ID, ContractID, dt, dowChar, dowInt) VALUES
-- simple two weeks (without weekend)
(110, 1, '2016-05-02', 'Mon', 2),
(111, 1, '2016-05-03', 'Tue', 3),
(112, 1, '2016-05-04', 'Wed', 4),
(113, 1, '2016-05-05', 'Thu', 5),
(114, 1, '2016-05-06', 'Fri', 6),
(115, 1, '2016-05-09', 'Mon', 2),
(116, 1, '2016-05-10', 'Tue', 3),
(117, 1, '2016-05-11', 'Wed', 4),
(118, 1, '2016-05-12', 'Thu', 5),
(119, 1, '2016-05-13', 'Fri', 6),
-- a partial end of the week, the whole week, partial start of the week (without weekends)
(223, 2, '2016-05-05', 'Thu', 5),
(224, 2, '2016-05-06', 'Fri', 6),
(225, 2, '2016-05-09', 'Mon', 2),
(226, 2, '2016-05-10', 'Tue', 3),
(227, 2, '2016-05-11', 'Wed', 4),
(228, 2, '2016-05-12', 'Thu', 5),
(229, 2, '2016-05-13', 'Fri', 6),
(230, 2, '2016-05-16', 'Mon', 2),
(231, 2, '2016-05-17', 'Tue', 3),
-- only Mon, Wed, Fri are included across two weeks plus partial third week
(310, 3, '2016-05-02', 'Mon', 2),
(311, 3, '2016-05-04', 'Wed', 4),
(314, 3, '2016-05-06', 'Fri', 6),
(315, 3, '2016-05-09', 'Mon', 2),
(317, 3, '2016-05-11', 'Wed', 4),
(319, 3, '2016-05-13', 'Fri', 6),
(330, 3, '2016-05-16', 'Mon', 2),
-- a whole week (without weekend), in the second week Mon is not included
(410, 4, '2016-05-02', 'Mon', 2),
(411, 4, '2016-05-03', 'Tue', 3),
(412, 4, '2016-05-04', 'Wed', 4),
(413, 4, '2016-05-05', 'Thu', 5),
(414, 4, '2016-05-06', 'Fri', 6),
(416, 4, '2016-05-10', 'Tue', 3),
(417, 4, '2016-05-11', 'Wed', 4),
(418, 4, '2016-05-12', 'Thu', 5),
(419, 4, '2016-05-13', 'Fri', 6),
-- three weeks, but without Mon in the second week (no weekends)
(510, 5, '2016-05-02', 'Mon', 2),
(511, 5, '2016-05-03', 'Tue', 3),
(512, 5, '2016-05-04', 'Wed', 4),
(513, 5, '2016-05-05', 'Thu', 5),
(514, 5, '2016-05-06', 'Fri', 6),
(516, 5, '2016-05-10', 'Tue', 3),
(517, 5, '2016-05-11', 'Wed', 4),
(518, 5, '2016-05-12', 'Thu', 5),
(519, 5, '2016-05-13', 'Fri', 6),
(520, 5, '2016-05-16', 'Mon', 2),
(521, 5, '2016-05-17', 'Tue', 3),
(522, 5, '2016-05-18', 'Wed', 4),
(523, 5, '2016-05-19', 'Thu', 5),
(524, 5, '2016-05-20', 'Fri', 6),
-- long gap between two intervals
(623, 6, '2016-05-05', 'Thu', 5),
(624, 6, '2016-05-06', 'Fri', 6),
(625, 6, '2016-05-09', 'Mon', 2),
(626, 6, '2016-05-10', 'Tue', 3),
(627, 6, '2016-05-11', 'Wed', 4),
(628, 6, '2016-05-12', 'Thu', 5),
(629, 6, '2016-05-13', 'Fri', 6),
(630, 6, '2016-05-16', 'Mon', 2),
(631, 6, '2016-05-17', 'Tue', 3),
(645, 6, '2016-06-06', 'Mon', 2),
(646, 6, '2016-06-07', 'Tue', 3),
(647, 6, '2016-06-08', 'Wed', 4),
(648, 6, '2016-06-09', 'Thu', 5),
(649, 6, '2016-06-10', 'Fri', 6),
(655, 6, '2016-06-13', 'Mon', 2),
(656, 6, '2016-06-14', 'Tue', 3),
(657, 6, '2016-06-15', 'Wed', 4),
(658, 6, '2016-06-16', 'Thu', 5),
(659, 6, '2016-06-17', 'Fri', 6),
-- two weeks, no gaps between days at all, even weekends are included
(710, 7, '2016-05-02', 'Mon', 2),
(711, 7, '2016-05-03', 'Tue', 3),
(712, 7, '2016-05-04', 'Wed', 4),
(713, 7, '2016-05-05', 'Thu', 5),
(714, 7, '2016-05-06', 'Fri', 6),
(715, 7, '2016-05-07', 'Sat', 7),
(716, 7, '2016-05-08', 'Sun', 1),
(725, 7, '2016-05-09', 'Mon', 2),
(726, 7, '2016-05-10', 'Tue', 3),
(727, 7, '2016-05-11', 'Wed', 4),
(728, 7, '2016-05-12', 'Thu', 5),
(729, 7, '2016-05-13', 'Fri', 6),
-- no gaps between days at all, even weekends are included, with partial weeks
(805, 8, '2016-04-30', 'Sat', 7),
(806, 8, '2016-05-01', 'Sun', 1),
(810, 8, '2016-05-02', 'Mon', 2),
(811, 8, '2016-05-03', 'Tue', 3),
(812, 8, '2016-05-04', 'Wed', 4),
(813, 8, '2016-05-05', 'Thu', 5),
(814, 8, '2016-05-06', 'Fri', 6),
(815, 8, '2016-05-07', 'Sat', 7),
(816, 8, '2016-05-08', 'Sun', 1),
(825, 8, '2016-05-09', 'Mon', 2),
(826, 8, '2016-05-10', 'Tue', 3),
(827, 8, '2016-05-11', 'Wed', 4),
(828, 8, '2016-05-12', 'Thu', 5),
(829, 8, '2016-05-13', 'Fri', 6),
(830, 8, '2016-05-14', 'Sat', 7),
-- only Mon-Wed included, two weeks plus partial third week
(910, 9, '2016-05-02', 'Mon', 2),
(911, 9, '2016-05-03', 'Tue', 3),
(912, 9, '2016-05-04', 'Wed', 4),
(915, 9, '2016-05-09', 'Mon', 2),
(916, 9, '2016-05-10', 'Tue', 3),
(917, 9, '2016-05-11', 'Wed', 4),
(930, 9, '2016-05-16', 'Mon', 2),
(931, 9, '2016-05-17', 'Tue', 3),
-- only Thu-Sun included, three weeks
(1013,10,'2016-05-05', 'Thu', 5),
(1014,10,'2016-05-06', 'Fri', 6),
(1015,10,'2016-05-07', 'Sat', 7),
(1016,10,'2016-05-08', 'Sun', 1),
(1018,10,'2016-05-12', 'Thu', 5),
(1019,10,'2016-05-13', 'Fri', 6),
(1020,10,'2016-05-14', 'Sat', 7),
(1021,10,'2016-05-15', 'Sun', 1),
(1023,10,'2016-05-19', 'Thu', 5),
(1024,10,'2016-05-20', 'Fri', 6),
(1025,10,'2016-05-21', 'Sat', 7),
(1026,10,'2016-05-22', 'Sun', 1),
-- only Tue for first three weeks, then only Thu for the next three weeks
(1111,11,'2016-05-03', 'Tue', 3),
(1116,11,'2016-05-10', 'Tue', 3),
(1131,11,'2016-05-17', 'Tue', 3),
(1123,11,'2016-05-19', 'Thu', 5),
(1124,11,'2016-05-26', 'Thu', 5),
(1125,11,'2016-06-02', 'Thu', 5),
-- one week, then one week gap, then one week
(1210,12,'2016-05-02', 'Mon', 2),
(1211,12,'2016-05-03', 'Tue', 3),
(1212,12,'2016-05-04', 'Wed', 4),
(1213,12,'2016-05-05', 'Thu', 5),
(1214,12,'2016-05-06', 'Fri', 6),
(1215,12,'2016-05-16', 'Mon', 2),
(1216,12,'2016-05-17', 'Tue', 3),
(1217,12,'2016-05-18', 'Wed', 4),
(1218,12,'2016-05-19', 'Thu', 5),
(1219,12,'2016-05-20', 'Fri', 6);
SELECT ID, ContractID, dt, dowChar, dowInt
FROM @Src
ORDER BY ContractID, dt;
DECLARE @Dst TABLE (ContractID int, StartDT date, EndDT date, DayCount int, WeekDays varchar(255));
INSERT INTO @Dst (ContractID, StartDT, EndDT, DayCount, WeekDays) VALUES
(1, '2016-05-02', '2016-05-13', 10, 'Mon,Tue,Wed,Thu,Fri,'),
(2, '2016-05-05', '2016-05-17', 9, 'Mon,Tue,Wed,Thu,Fri,'),
(3, '2016-05-02', '2016-05-16', 7, 'Mon,Wed,Fri,'),
(4, '2016-05-02', '2016-05-06', 5, 'Mon,Tue,Wed,Thu,Fri,'),
(4, '2016-05-10', '2016-05-13', 4, 'Tue,Wed,Thu,Fri,'),
(5, '2016-05-02', '2016-05-06', 5, 'Mon,Tue,Wed,Thu,Fri,'),
(5, '2016-05-10', '2016-05-20', 9, 'Mon,Tue,Wed,Thu,Fri,'),
(6, '2016-05-05', '2016-05-17', 9, 'Mon,Tue,Wed,Thu,Fri,'),
(6, '2016-06-06', '2016-06-17', 10, 'Mon,Tue,Wed,Thu,Fri,'),
(7, '2016-05-02', '2016-05-13', 12, 'Sun,Mon,Tue,Wed,Thu,Fri,Sat,'),
(8, '2016-04-30', '2016-05-14', 15, 'Sun,Mon,Tue,Wed,Thu,Fri,Sat,'),
(9, '2016-05-02', '2016-05-17', 8, 'Mon,Tue,Wed,'),
(10,'2016-05-05', '2016-05-22', 12, 'Sun,Thu,Fri,Sat,'),
(11,'2016-05-03', '2016-05-17', 3, 'Tue,'),
(11,'2016-05-19', '2016-06-02', 3, 'Thu,'),
(12,'2016-05-02', '2016-05-06', 5, 'Mon,Tue,Wed,Thu,Fri,'),
(12,'2016-05-16', '2016-05-20', 5, 'Mon,Tue,Wed,Thu,Fri,');
SELECT ContractID, StartDT, EndDT, DayCount, WeekDays
FROM @Dst
ORDER BY ContractID, StartDT;
A tabela real @Srcpossui 403,555linhas 15,857com ContractIDs. Todas as respostas produzem resultados corretos (pelo menos para meus dados) e todas elas são razoavelmente rápidas, mas diferem em otimização. Quanto menos intervalos gerados, melhor. Incluí tempos de execução apenas por curiosidade. O foco principal é o resultado correto e ideal, não a velocidade (a menos que demore muito - interrompi a consulta não recursiva de Ziggy Crueltyfree Zeitgeister após 10 minutos).
+--------------------------------------------------------+-----------+---------+
| Answer | Intervals | Seconds |
+--------------------------------------------------------+-----------+---------+
| Ziggy Crueltyfree Zeitgeister | 25751 | 7.88 |
| While loop | | |
| | | |
| Ziggy Crueltyfree Zeitgeister | 25751 | 8.27 |
| Recursive | | |
| | | |
| Michael Green | 25751 | 22.63 |
| Recursive | | |
| | | |
| Geoff Patterson | 26670 | 4.79 |
| Weekly gaps-and-islands with merging of partial weeks | | |
| | | |
| Vladimir Baranov | 34560 | 4.03 |
| Daily, then weekly gaps-and-islands | | |
| | | |
| Mikael Eriksson | 35840 | 0.65 |
| Weekly gaps-and-islands | | |
+--------------------------------------------------------+-----------+---------+
| Vladimir Baranov | 25751 | 121.51 |
| Cursor | | |
+--------------------------------------------------------+-----------+---------+
Eu tenho lido MSDN sobre TRY...CATCHe XACT_STATE.
Tem o seguinte exemplo que usa XACT_STATEno CATCHbloco de uma TRY…CATCHconstrução para determinar se deve confirmar ou reverter uma transação:
USE AdventureWorks2012;
GO
-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- A FOREIGN KEY constraint exists on this table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
-- If the delete operation succeeds, commit the transaction. The CATCH
-- block will not execute.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Test XACT_STATE for 0, 1, or -1.
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should
-- be rolled back.
-- XACT_STATE = 0 means there is no transaction and
-- a commit or rollback operation would generate an error.
-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
PRINT 'The transaction is in an uncommittable state.' +
' Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT 'The transaction is committable.' +
' Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
GO
O que não entendo é: por que devo me importar e verificar o que XACT_STATEretorna?
Observe que o sinalizador XACT_ABORTestá definido como ONno exemplo.
Se houver um erro grave o suficiente dentro do TRYbloco, o controle passará para CATCH. Então, se estou dentro do CATCH, sei que aquela transação teve um problema e realmente a única coisa sensata a fazer nesse caso é revertê-la, não é?
Mas, este exemplo do MSDN implica que pode haver casos em que o controle é passado CATCHe ainda faz sentido confirmar a transação. Alguém poderia dar algum exemplo prático de quando isso pode acontecer, quando faz sentido?
Não vejo em que casos o controle pode ser passado para dentro CATCHcom uma transação que pode ser confirmada quando XACT_ABORTestá definida comoON .
O artigo do MSDN sobre SET XACT_ABORTtem um exemplo quando algumas instruções dentro de uma transação são executadas com êxito e algumas falham quando XACT_ABORTé definido como OFF, eu entendo isso. Mas, SET XACT_ABORT ONcomo pode acontecer que XACT_STATE()retorne 1 dentro do CATCHbloco?
Inicialmente, eu teria escrito este código assim:
USE AdventureWorks2012;
GO
-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- A FOREIGN KEY constraint exists on this table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
-- If the delete operation succeeds, commit the transaction. The CATCH
-- block will not execute.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Some severe problem with the transaction
PRINT 'Rolling back transaction.';
ROLLBACK TRANSACTION;
END CATCH;
GO
Levando em consideração uma resposta de Max Vernon, eu escreveria o código assim. Ele mostrou que faz sentido verificar se há uma transação ativa antes de tentar ROLLBACK. Ainda assim, com SET XACT_ABORT ONo CATCHbloco pode haver uma transação condenada ou nenhuma transação. Portanto, em qualquer caso, não há nada a fazer COMMIT. Estou errado?
USE AdventureWorks2012;
GO
-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- A FOREIGN KEY constraint exists on this table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
-- If the delete operation succeeds, commit the transaction. The CATCH
-- block will not execute.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Some severe problem with the transaction
IF (XACT_STATE()) <> 0
BEGIN
-- There is still an active transaction that should be rolled back
PRINT 'Rolling back transaction.';
ROLLBACK TRANSACTION;
END;
END CATCH;
GO