Calendário com Moda Estatística em SQL

Olá,
este artigo sendo geral aplica-se , principalmente, à área de vendas a retalho onde o horário de abertura e de fecho das lojas é fundamental para, nomeadamente, o cálculo de objectivos de vendas.

Todos os meses será necessário o cálculo de objectivos de vendas para cada loja, sendo necessário planear o calendário de abertura/fecho para cada loja, principalmente nesta altura com tantas alterações repentinas por causa do COVID.

As ferramentas de suporte ao negócio apenas podem ajudar efectivamente o negócio após terem sido definidos os calendários mensais de cada loja. Esta acção deve ser desenvolvida pelos gerentes ou supervisores de lojas uma vez serem eles os detentores do conhecimento local.

A tarefa pode ser facilitada se lhes for fornecido um calendário pré-preenchido, ficando a seu encargo a validação e alguma alteração esporádica.

Este processo pode ser baseado nos horários do mês anterior, se determinada loja tem um horário “x” este mês será provável que para o próximo mês se mantenha.
Mas aqui a proposta vai ainda mais longe, vamos basear o cálculo dos horários futuros na maior frequência de horário do mês anterior, isto é, na moda, para cada dia da semana.

Para se perceber melhor examinemos o seguinte cenário:
Tomemos por base as sextas-feiras deste mês, para a loja nº1. Existem 4 sextas, com horário de abertura igual (open) e horário de fecho diferente (close).

Percebe-se que a maior frequência (Moda estatística) para a hora de fecho é às 22 horas. Será este valor que deve ficar registado no horário de fecho das sextas-feiras do próximo mês a calcular (Junho). O cálculo para todos os outros dias de semana, para todas as lojas, é semelhante.

Iniciemos por analisar a tabela do calendário para o mês de Maio e, para facilitar, apenas para a loja nº 11:

O weekday 1 decidiu-se ser a segunda-feira.

Vamos começar por calcular a Moda da hora de abertura do mês corrente e, para simplificar, apenas para a loja 11:

A coluna contador faz a contagem do número de vezes que cada hora de abertura está presente. A coluna open_serial_rank, baseada na partição por store/weekday, faz também uma contagem colocando o valor mais baixo para a contagem mais alta, isto é, o número mais frequente (maior valor em contador) é o que tem valor 1 (menor valor em open_serial_rank).

Repare-se por exemplo no weekday=4, tem 3 horários de entrada às 10 horas e 1 horário às 24 (na prática está fechado aqui!) – a Moda será 10. Já no weekday=7, o valor mais frequente para o horário de abertura é 24, isto é, a loja esteve maioritariamente fechada – será este estado a reflectir em Junho.

Apliquemos, agora, o mesmo processo para o cálculo da hora de fecho:

Repare-se por exemplo no weekday = 4 com 3 contagens de hora de fecho às 22 e uma contagem às 24 (fechado) – a Moda será 22, conforme especificado por close_serial_rank = 1.

Os valores da Moda serão os que se devem colocar posteriormente no calendário de Junho.

Agora basta juntar os dois códigos e obteremos a hora de abertura e a hora de fecho com mais frequência, isto é, onde open_serial_rank = 1 e close_serial_rank = 1:

/*
MODA ESTATÍSTICA SQL,
2021 JG
*/
select * from 
(
select  
	store,
	weekday, 
	open_serial,
	count(*) contador, 
	row_number() over (partition by store,weekday order by count(*) desc) open_serial_rank
from [dbo].[CALENDARIO_LOJAS] (NOLOCK)
where 
		date between '2021-05-01' and '2021-05-31'
		and store=11		
group by store, weekday, open_serial 		
) KK
INNER JOIN
(
select  
	store AS STORE_, 
	weekday AS WEEKDAY_, 
	close_serial, 
	count(*) CONTADOR_, 
	row_number() over (partition by store,weekday order by count(*) desc) close_serial_rank
from [dbo].[CALENDARIO_LOJAS] (NOLOCK)
where 
		date between '2021-05-01' and '2021-05-31'
		and store=11		
group by store, weekday, close_serial
) JJ 
on KK.store = JJ.STORE_ and KK.weekday = JJ.WEEKDAY_
where open_serial_rank=1 and close_serial_rank=1

Resultado:

Para cada um dos dias de semana ([1..7]) teremos agora a Moda de abertura (open_serial) e a Moda de fecho (close_serial). No Domingo, correspondente ao weekday 7, a loja estará fechada.

Para finalizar, basta cruzar este código com a tabela calendário para o mês futuro, Junho, assumindo as colunas de hora de abertura e fecho como sendo open_moda e close_moda, respectivamente:

/*
MODA ESTATÍSTICA SQL,
2021 JG
*/

select  HH.store,
	HH.date,
	HH.weekday,
	DD.open_serial AS open_moda, 
	DD.close_serial AS close_moda 
from [dbo].[CALENDARIO_LOJAS] (NOLOCK) HH
LEFT JOIN
(
	select * from 
	(
	select  
		store,
		weekday, 
		open_serial,
		count(*) contador, 
		row_number() over (partition by store,weekday order by count(*) desc) open_serial_rank
	from [dbo].[CALENDARIO_LOJAS] (NOLOCK)
	where 
			date between '2021-05-01' and '2021-05-31'
			and store=11		
	group by store, weekday, open_serial 		
	) KK
	INNER JOIN
	(
	select  
		store AS STORE_, 
		weekday AS WEEKDAY_, 
		close_serial, 
		count(*) CONTADOR_, 
		row_number() over (partition by store,weekday order by count(*) desc) close_serial_rank
	from [dbo].[CALENDARIO_LOJAS] (NOLOCK)
	where 
			date between '2021-05-01' and '2021-05-31'
			and store=11		
	group by store, weekday, close_serial
	) JJ 
	on KK.store = JJ.STORE_ and KK.weekday = JJ.WEEKDAY_
	where open_serial_rank=1 and close_serial_rank=1
) DD
ON HH.STORE = DD.STORE AND HH.WEEKDAY = DD.WEEKDAY
WHERE 
HH.date between '2021-06-01' and '2021-06-30'
and HH.store=11

O resultado final com os “novos” horários para Junho, aqui com amostragem até dia 18, é:

Estes resultados podem ser armazenados numa tabela temporária para posterior alteração na tabela [CALENDARIO_LOJAS].

Para o cálculo de todas as lojas apenas será necessário retirar o filtro da loja 11.

Espero que vos seja útil,

JG

SQL Partitioned Tables

Olá,

o assunto de hoje trata um tema importantíssimo relacionado com a  performance do SQL Server, tema referente às partições de tabelas (Partitioned Tables).

Num modo muito simples, poderemos dizer que Partitioned Tables são uma forma de repartir por localizações diferentes os dados de uma tabela.

Uma DB pode ser composta por diversos FileGroups, sendo cada um constituído por diversos Files físicos, podendo ser localizados em volumes de disco diferentes. Isto permite performance melhorada pois o SQL pode responder paralelamente a pedidos de dados, acedendo a diferentes localizações simultaneamente.

O caso das Partitioned Tables é idêntico mas a separação em diferentes ficheiros é realizada mesmo ao nível da tabela,  tornando o seu acesso de leitura/escrita muito melhorado,  necessitando apenas ser especificado determinado campo para a divisão dos dados – normalmente é uma data, para separação dos dados por anos.

Assim, alguns pontos de interesse nas Partitioned Tables:

• o manuseamento de grandes tabelas é mais eficiente,
• cada partição pode ser guardada num FileGroup específico, sendo cada um destes colocados em qualquer volume de dados, podendo ser em locais diferentes para a mesma tabela,
• os índices ficam alinhados automaticamente, isto é, se for criado um índice numa tabela este fica imediatamente activo nas respectivas partições da tabela.

O processo de crição das Partitioned Tables passa pelas seguintes fases:

1. Criar uma Partition Function, esta define as fronteiras para as partições, por ex. 3;
2. Criar uma Partition Scheme, esta mapeia as partições  previamente criadas para os Filegroups desejados – serão por ex. 4, de acordo com as 3 limitações anteriores – e define o próximo Filegroup a utlizar, ou seja, no ex. passa a 5;
3. Criar uma Tabela, esta é criada na Partition Scheme anteriormente tratada, especificando o campo (coluna) desejado para a separação de dados.
4. Dividir partição (Split Partition) em duas, sendo adicionado uma fronteira;
5. Juntar partições (Merge Partitions), sendo que duas partições passarão a formar apenas uma – remove uma fronteira;
6. Alterar partição (Switch Partitions)

 

Iniciemos por verificar a estrutura física da DB DemoDB, criada para o efeito de demonstração:

use Master
GO

create DATABASE DemoDB
GO

Vejamos os ficheiros constituintes da DemoDB:

e os respectivos Filegroups:

Das imagens anteriores, resume-se que a DemoDB tem apenas um FileGroup (Primary) e dois ficheiros no disco, um para dados e outro para log.

Agora, iremos alterar a DemoDB de forma a contemplar mais FileGroups (FG000, FG2000, FG2001, FG2002 e FG2003) e adicionar ficheiros a cada um destes FileGroups (respectivamente F0000, F2000, F2001, F2002 e F2003), ficando residentes em “C:\Temp”:

use DemoDB;

ALTER DATABASE DemoDB ADD FILEGROUP FG000 GO ALTER DATABASE DemoDB ADD FILE  (NAME=F0000,  FILENAME=’C:\Temp\F0000.ndf’,  SIZE=3MB,  MAXSIZE = 100MB, FILEGROWTH = 3MB  )  TO FILEGROUP FG000;
GO

ALTER DATABASE DemoDB ADD FILEGROUP FG2000 GO ALTER DATABASE DemoDB ADD FILE (NAME=F2000, FILENAME=’C:\Temp\F2000.ndf’, SIZE=3MB, MAXSIZE = 100MB, FILEGROWTH = 3MB  ) TO FILEGROUP FG2000;
GO

ALTER DATABASE DemoDB ADD FILEGROUP FG2001 GO ALTER DATABASE DemoDB ADD FILE (NAME=F2001, FILENAME=’C:\Temp\F2001.ndf’, SIZE=3MB, MAXSIZE = 100MB, FILEGROWTH = 3MB  ) TO FILEGROUP FG2001;
GO

ALTER DATABASE DemoDB ADD FILEGROUP FG2002 GO ALTER DATABASE DemoDB ADD FILE (NAME=F2002, FILENAME=’C:\Temp\F2002.ndf’, SIZE=3MB, MAXSIZE = 100MB, FILEGROWTH = 3MB  ) TO FILEGROUP FG2002;
GO

ALTER DATABASE DemoDB ADD FILEGROUP FG2003 GO ALTER DATABASE DemoDB ADD FILE (NAME=F2003, FILENAME=’C:\Temp\F2003.ndf’, SIZE=3MB, MAXSIZE = 100MB, FILEGROWTH = 3MB  ) TO FILEGROUP FG2003;
GO

Vejamos agora a estrutura de ficheiros da DemoDB:

e seus Filegroups:

Importante aqui salientar que cada um dos ficheiros criados para a DemoDB poderia ficar residente em diferentes volumes de disco.

 

1 – Criar uma Partition Function

Vamos criar agora a Partition Function com o nome de PF, definindo 3 fronteiras, isto é, dividindo os dados por ano contemplando os anteriores a 2000, depois os dados entre 2000 e 2001, entre 2001 e 2002 e maiores que 2002:

CREATE PARTITION FUNCTION PF (int) AS RANGE RIGHT FOR VALUES (20000101, 20010101, 20020101);
GO

 

2 – Criar uma Partition Scheme

Vamos agora criar a Partition Scheme PS dentro da Partition Function PF para os Filegroups criados anteriormente.

O FG000 ficará com os dados anteriores a 2000, o FG2000 ficará com os dados do ano 2000, o FG2001 com os do ano 2001, o FG2002 com os dados do ano 2002 e o FG2003 com os dados superiores a 2002 (este é o próximo Filegroup a utilizar):

CREATE PARTITION SCHEME PS AS PARTITION PF TO (FG000, FG2000, FG2001, FG2002, FG2003);
GO

 

 

3 – Criar uma Tabela

O próximo passo é criar uma tabela, OrderTable, especificando que a criação deve ser feita na Partition Scheme PS e o campo de divisão de dados é o campo data (datekey).

A tabela é constituída por apenas dois campos – datekey e amount, ambos numéricos.

Criar tabela OrderTable:

CREATE TABLE dbo.OrderTable
 (datekey int, amount int)
ON PS (datekey);
GO

e inserir dados (datekey e amount), alterando sucessivamente as datas:

INSERT dbo.OrderTable VALUES (20000101, 100);
INSERT dbo.OrderTable VALUES (20001231, 100);
INSERT dbo.OrderTable VALUES (20010101, 100);
INSERT dbo.OrderTable VALUES (20010403, 100);
GO

Os registos inseridos contemplaram os anos de 2000 e 2001.

Poderemos utilizar o seguinte código para verificar o resumo do que acabamos de criar:

• uma Partition Scheme,
• uma Partition Function com 4 partes, cada uma identificada numa partição do ficheiro do disco
• ficheiros da DemoDB onde se inscreveram dados

SELECT ps.name as PartitionScheme, pf.name as PartitionFunction, 
p.partition_number as PartitionNumber, fg.name, prv_left.value inicio, 
prv_right.value fim, p.row_count registos
FROM sys.dm_db_partition_stats p 
INNER JOIN sys.indexes i ON i.OBJECT_ID = p.OBJECT_ID and i.index_id = p.index_id
INNER JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.destination_data_spaces dds 
ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.filegroups fg ON fg.data_space_id = dds.data_space_id
LEFT OUTER JOIN sys.partition_range_values prv_right 
ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number
LEFT OUTER JOIN sys.partition_range_values prv_left 
ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1
WHERE OBJECT_NAME (p.object_id) = 'OrderTable'
AND i.index_id = 0
ORDER BY partition_number
GO

 

Percebe-se que os registos foram inseridos na tabela OrderTable,  internamente no Filegroup FG2000 e FG2001, cujos mapeamentos físicos no disco são c:\Temp\F2000.ndf e c:\Temp\F2001.ndf.

É um método que permite uma performance melhorada na medida em que passará a ler em localizações físicas diferentes consoante os dados que se desejem. A escrita também fica melhorada uma vez que pode ser efetuado paralelismo de escrita em ficheiros diferentes.

Na prática, efectuamos uma divisão de uma tabela utilizando como fronteira a coluna do ano.

Os pontos 4, 5 e 6:

 

• Dividir partição (Split Partition) em duas, sendo adicionado uma fronteira;
• Juntar partições (Merge Partitions), sendo que duas partições passarão a formar apenas uma – remove uma fronteira;
• Alterar partição (Switch Partitions)

serão publicados no próximo artigo.

 

Espero que vos seja útil,

JG