Curso
Manipulação de dados em SQL
4 h
335K
Algumas das funções mais úteis em implementações de SQL como o Postgres (por exemplo, Amazon Redshift) são DATE_DIFF e DATE_TRUNC:
DATE_DIFFinforma quanto tempo se passou entre duas datas. Por exemplo, o código a seguir retorna o número de dias entredate1edate2:
DATE_DIFF('day', date1, date2)
DATE_DIFF é ótimo para calcular o número de dias do cadastro ao cancelamento ou o número de horas do login ao logout.
DATE_TRUNCcorta uma data para o dia, semana, mês ou ano mais próximo. Por exemplo, o código a seguir retorna a segunda-feira mais próxima para o timestampmy_timestamp:
DATE_TRUNC('week', my_timestamp)
DATE_TRUNC é excelente para agregar dados. Por exemplo, dá para usá-la para encontrar o número de Monthly Active Users (MAU) truncando para o primeiro dia do mês.
Mas nem toda implementação de SQL traz essas ótimas funções. No nosso código do curso Learn SQL from Scratch, usamos SQLite, uma implementação leve de SQL que pode rodar em uma única instância Docker. O SQLite é ótimo para back-ends de sites e projetos pequenos, mas não tem minhas duas funções favoritas. Felizmente, há jeitos de contornar isso.
Para emular DATE_DIFF, podemos usar uma função pouco conhecida chamada juliandate. Segundo a Wikipedia, "o Julian Day Number (JDN) é o inteiro atribuído a um dia solar completo na contagem juliana iniciada ao meio-dia do tempo universal, com o número 0 atribuído ao dia que começou ao meio-dia de segunda-feira, 1º de janeiro de 4713 a.C.". Convertendo uma data em um número de ponto flutuante, podemos subtrair para achar a diferença entre dois timestamps.
Também dá para converter o resultado em horas multiplicando por 24 ou em minutos multiplicando por 24 * 60.
Conseguimos emular parte da funcionalidade de DATE_TRUNC usando strftime. Essa função converte um timestamp em uma string no formato desejado.
%d dia do mês: 00 %f segundos fracionários: SS.SSS %H hora: 00-24 %j dia do ano: 001-366 %J número do dia juliano %m mês: 01-12 %M minuto: 00-59 %s segundos desde 1970-01-01 %S segundos: 00-59 %w dia da semana 0-6 com domingo==0 %W semana do ano: 00-53 %Y ano: 0000-9999
Normalmente, usaríamos isso para converter entre formatos de timestamp, como de YYYY-MM-DD para MM-DD-YYYY:
strftime('%M-%D-%Y', mydate)
Mas podemos escolher o formato de forma esperta para truncar no ponto certo. Por exemplo, se quisermos truncar para o mês, podemos fazer:
strftime('%M/%Y', mydate)
Ou truncar para a semana usando:
strftime('%Y-%w', mydate)
Com esses dois truques simples, você consegue usar o SQLite para análises tão boas quanto as do Amazon Redshift!
Se quiser aprender mais sobre os fundamentos de SQL, faça o curso Intro to SQL for Data Science da DataCamp e confira nosso SQL Tutorial for Beginners.
Tópicos
