Estou fazendo um serviço que precisa manter os dados agrupados e calculados pela combinação ano-mês. Eu sei como calcular dados e colocá-los em uma nova tabela. Mas estou confuso sobre que tipo de dados devo usar para armazenar o valor do mês - ano. Aqui está o que eu considerei.
- Duas colunas inteiras, uma para o ano e outra para o mês (claras para entender e fortes para manipular (qualquer pessoa que use esta tabela pode facilmente usar intervalos e ordens), mas acho que isso levaria mais espaço do que outra solução.
- Use um campo Data e sempre armazene uma data para o mês inteiro. (Difícil explicar aos usuários como funciona e como criar cláusulas WHERE)
- Uma coluna varchar(7) e coloque as picadas como 2012-02,2013-01. Para mim, isso é fácil de entender, mas difícil de manipular.
Qual devo escolher? ou existe outra solução. Alguém pode sugerir de acordo com o desempenho qual caminho será uma boa solução, porque a maioria das consultas usará o intervalo de dados em que a solução e minha nova tabela terão cerca de 2 a 5 milhões de registros.
Eu usaria um campo de data com o primeiro dia de um mês e uma restrição CHECK para garantir que permaneça no dia 1.
Isso o mantém no formato nativo de data/hora (que é sua observação sobre a opção 3)
A opção 1 exigiria menos armazenamento, mas complicaria as comparações. E 5 milhões de linhas não são muito: você usa menos armazenamento, mas adiciona código e complexidade de consulta
Acho que você deve escolher uma (ou mais) opções, dependendo de como os dados precisam ser recuperados .
Para 5 milhões de registros, o espaço realmente não deve ser sua principal preocupação. Para cada uma das suas opções, aqui estão os prós e contras da recuperação :
(1) Duas colunas inteiras: essa é uma ótima abordagem para usar se você precisar comparar meses de anos diferentes. Extrair por mês e classificar por ano será muito mais rápido se você indexar ano e mês separadamente. A opção 1 é melhor se este for um modo importante ou frequente de usar os dados. Por outro lado, esse modo é péssimo para extrair intervalos que não sejam anos e meses. Por exemplo, não é bom para intervalos que cruzam anos. A cláusula WHERE pode ficar mais complicada do que você gostaria que fosse quando os intervalos de datas ultrapassam os limites do ano civil. (Pense de novembro de 2011 a fevereiro de 2012.)
(2) Campo de data: Você e gbn identificaram pontos positivos sobre esse formato. Também é bom para classificar cronologicamente e para extrair intervalos de meses. Acontece que é a representação mais compacta (apenas 3 bytes). Não é nada bom para comparar meses em vários anos e não é uma ideia para exibição em telas e relatórios.
(3) char(7) YYYY-MM Field: Observe que se você está realmente preocupado com o espaço (e não deveria estar no seu caso), então você pode usar
charem vez de,varcharpois cada item terá um comprimento conhecido. Usar AAAA-MM é bom para classificar e filtrar intervalos. Não é tão bom quanto (2) para espaço, mas é melhor para exibir e simplificar a cláusula WHERE - a menos que você precise extrair meses como anos.Você deve ter notado que há uma diferença entre (1) e (2)/(3) quando se trata da natureza da extração que você precisa fazer. Se você precisa fazer extrações de intervalo de mês/ano a ano e de mês, nenhuma dessas opções é perfeita. Se for esse o caso, sugiro que você considere usar uma combinação de (1) e (2) ou (3) - eu mesmo escolheria (3), pois valorizo a facilidade de exibição/uso em vez do armazenamento espaço. Se você usar uma combinação, torne uma ou outra coluna(s) computada(s) e indexe-a para uma recuperação eficiente.
Considere a modelagem como um período com dois valores de data -- data inicial e data final -- usando a representação aberta-fechada (o 'aberto' indica que a data final não ocorre realmente no período).
Por exemplo, o mês atual (março de 2012) seria modelado usando a linha
Se você também tiver a data completa na tabela e usar a reduzida apenas para agregação, faça uma função que crie um valor normalizado, idealmente distribuído de maneira sensata (por exemplo, "meses desde janeiro de 1970") a partir da data e crie um índice em o resultado dessa função para acelerar o processo de agregação.