Mode Blog (Português)

Timestamps são cruciais para a análise de negócios por uma razão muito simples: eles dizem quando as coisas acontecem. Imagine tentar descobrir tendências em seus dados, como Tráfego mensal na web, ou ganhos trimestrais, ou volume de encomendas diárias sem saber quando os eventos ocorreram. Seria um pesadelo.

PostgreSQL oferece uma variedade de funções de data para a manipulação de datas. Para separar o útil do obscuro, estamos compartilhando how-tos para as funções de data pós-Gres mais usadas e cenários de negócios onde eles vêm a calhar.,

O mais frequentemente utilizado Postgres funções de data e cenários de negócios onde eles vêm a calhar:

• Arredondamento de carimbos de data / hora com DATE_TRUNC
• Encontrar eventos em relação ao tempo presente, com o AGORA() e CURRENT_DATE
• Isolar hora do dia e dia da semana com EXTRATO
• o Cálculo do tempo decorrido, com IDADE

Nós fizemos os dados para cada exemplo disponível no Modo Público Armazém. Tente cada função de data no modo como você trabalha o seu caminho através destes exemplos. Inscreva-se para uma conta no modo Studio e abra um novo relatório para começar.,

arredondando as datas com DATE_TRUNC

o DATE_TRUNC a função rode um valor de hora para um intervalo especificado, o que lhe permite contar eventos. Você pode arredondar um carimbo de data / hora para as seguintes unidades de tempo:

• microssegundo
• milisegundos
• segundo
• minutos
• hora
• dia
• semana
• mês
• bairro
• ano
• década
• xviii
• millenium

DATE_TRUNC sintaxe se parece com isso: DATE_TRUNC('interval',timestamp).,

Por exemplo, SELECT DATE_TRUNC('day','2015-04-12 14:44:18')retornaria um resultado de2015-04-12 00:00:00.

para uma explicação mais detalhada de DATE_TRUNC (e uma referência impressa que pode manter na sua secretária!), confira este post.

como o tráfego da web mudou ao longo do tempo?

tente DATE_TRUNC para si mesmo questionando a tabela modeanalytics.web_events, which contains sample records of website visits, including an occurred_at column. Você pode isolar o mês da visita com DATE_TRUNC.,

Para retornar uma contagem de visitas virtuais a cada mês pelo canal, adicione o channel coluna e uma COUNT SELECT instrução group by month e channel. (Desde o mês e o canal são os dois primeiros valores de SELECT instrução, você pode GROUP BY 1,2), assim:

Finalmente, use ORDER BY 1,2 para organizar os resultados por ordem cronológica (por mês) e em ordem alfabética (por canal).,

no modo, você pode criar um gráfico de linhas para visualizar os resultados da pesquisa.

Analisadores de modo

encontrar eventos relativos ao tempo actual com a função NOW() e CURRENT_ date

oNOW() date devolve a data-hora actual em UTC (se o fuso-horário não for especificado). Você pode subtrair intervalos de NOW() para puxar eventos que aconteceram na última hora, no último dia, última semana, etc.

RunningSELECT NOW() at 9:00 UTC on October 11, 2016 would result in2016-10-11 09:00:00.,

A função CURRENT_DATE só devolve a data actual e não a data-limite completa. Executando SELECT CURRENT_DATEàs 9:00 UTC de 11 de outubro de 2016 retornaria2016-10-11.que encomendas foram feitas nas últimas 12 horas?

a demonstração da tabela.orders contains sample records of all orders, including an occurred_at timestamp column in UTC.,

Para encontrar encomendas feitas nas últimas 12 horas, use WHERE cláusula para retornar somente os pedidos que foram feitos depois, ou exatamente no (>=) a corrente de carimbo de data / hora (NOW()) menos um intervalo de 12 horas.,

SELECT * FROM demo.orders WHERE occurred_at >= NOW() - interval '12 hour'

Modo de Analytics

além de hour, você pode usar qualquer um dos seguintes intervalos:

• microssegundos
• milissegundos
• segundo
• minutos
• hora
• dia
• semana
• mês
• ano
• década
• xviii
• millennium

Você também pode combinar diferentes intervalos na mesma expressão como esta:
interval '4 hours 3 minutes'

o Que as ordens foram colocados ontem?,

pode usar a mesma tabela para encontrar as ordens de ontem combinando as funções DATE_TRUNC e CURRENT_DATE

comece por usar um id

função para contornar o seu id

valores por dia (uma vez que queremos saber se algo aconteceu ontem). Em seguida, useWHEREcláusula para retornar apenas os valores onde ooccurred_atdia é igual à data atual (usando oCURRENT_DATEfunção) menos um intervalo de um dia.,

SELECT * FROM demo.orders WHERE DATE_TRUNC('day',occurred_at) = CURRENT_DATE - interval '1 day'

Analisadores de modo

isolando a hora do dia e o dia da semana com extrato

a EXTRACT date função permite isolar subcampos como o ano ou a hora a partir de datas.

Aqui está a sintaxe: EXTRACT(subfield FROM timestamp). Running EXTRACT(month FROM '2015-02-12')would return a result of2.

tenha em mente que, enquanto o exemplo abaixo se concentra na hora do subcampo (hora do dia), você tem muitos outros subcampos à sua disposição variando de millennium a microsecond., Você pode conferir a lista completa de subcampos disponíveis aqui. quantas encomendas são feitas a cada hora do dia?

uma empresa que gere um centro de atendimento pode querer mais funcionários quando a maior parte das encomendas chega. Para descobrir quando as encomendas são feitas durante todo o dia, você pode usar o subcampo

e a funçãohourpara isolar a hora do dia (de 0 a 23) em que uma ordem ocorreu.,

SELECT EXTRACT(hour from occurred_at) AS hour FROM demo.orders

Use the COUNT function to tally orders, and thenGROUP BY hour. (Uma vez que a hora é o primeiro valor na sua declaração SELECT, você pode GROUP BY 1).

SELECT EXTRACT(hour from occurred_at) AS hour, COUNT(*) AS orders FROM demo.orders GROUP BY 1

finalmente, para organizar os seus resultados sequencialmente, useORDER BY 1.

SELECT EXTRACT(hour from occurred_at) AS hour, COUNT(*) AS orders FROM demo.orders GROUP BY 1 ORDER BY 1

e aqui estão os resultados! Parece que pode ajudar a ter alguns trabalhadores extras no relógio no início da manhã e por volta da hora do almoço.,

Analisadores de modo

Qual é o volume médio da ordem dos dias da semana?

para determinar o volume médio de encomendas que ocorreram no dia da semana, use EXTRACT e o dow subcampo para isolar o dia da semana (de 0-6, onde 0 é Domingo) em que ocorreu uma ordem.

SELECT EXTRACT(dow from occurred_at) AS dow FROM demo.orders

Next, round the order timestamps by day withDATE_TRUNC., Tomar um COUNT de pedidos agrupados por dow e day irá retornar o número de encomendas a cada dia, juntamente com o dia correspondente da semana.

SELECT EXTRACT(dow from occurred_at) AS dow, DATE_TRUNC('day',occurred_at) AS day, COUNT(id) AS orders FROM demo.orders GROUP BY 1,2

para encontrar o volume médio de encomendas de dias de semana, use a consulta anterior como uma subquery (aliased as a). Tome a média das encomendas (usando a função AVG()), e depois use uma cláusula WHERE para filtrar os sábados e domingos.

As tabelas de números grandes são ótimas para exibir métricas agregadas., Para manter os olhos no volume de encomendas, reúna métricas como esta num painel.

Analisadores de modo

calculando o tempo decorrido com a idade

a função AGE data calcula há quanto tempo ocorreu um evento. A sintaxe é bastante simples: aplicar AGE() a um único timestamp, e sua consulta irá retornar a quantidade de tempo desde que o evento ocorreu. Running SELECT AGE( '2010-01-01' ) on January 1, 2011 would return a result of 1 years 0 months 0 days.,

AGE() também pode determinar quanto tempo passou entre dois eventos. Em vez de colocar um único timestamp dentro dos parênteses, insira ambos os timestamps (começando com o timestamp mais recente) e separe-os com uma vírgula. A execução SELECT AGE( '2012-12-01',‘2010-01-01’) retornaria 2 years 11 months 0 days.

Note that this application of the AGE function is equivalent to subtracting the timestamps: SELECT '2012-12-01' - '2010-01-01'.

que idade tem uma conta de cliente?,

suponha que sua equipe de vendas quer personalizar saudações com base em quanto tempo o cliente está usando seu produto. Você pode descobrir quanto tempo passou desde a criação da conta usando a função AGE.

A tabela modeanalítica.customer_accounts contains records of sample customer accounts. Selecione a coluna de nomes de conta (name) e aplicar o AGE() função para a coluna de carimbos de data / hora mostra quando cada conta foi criada (created).,

SELECT name, AGE(created) AS account_age FROM modeanalytics.customer_accounts

Analisadores de modo

quanto tempo demora os utilizadores a completar o seu perfil todos os meses, em média?

a tabela modeanalítica.profilecreationevents contains sample data of users who created a profile, including start and end timestamps.

para encontrar o tempo médio para completar um perfil a cada mês, comece por encontrar o tempo que levou cada usuário para completar um perfil, bem como o mês em que o processo de criação do perfil foi iniciado. Primeiro, round the started_at timestamp by month, using the DATE_TRUNC function., Em seguida, encontre o tempo decorrido de started_atpara ended_atpara cada perfil usando a função AGE.

Encontre a média para cada mês aplicando a função AVGfunção ao valor do tempo decorrido (o seu id

) e agrupando por mês.

para devolver os valores numa unidade consistente para cartografar, aplique o EXTRACT função e subcampo da época aos seus valores para devolver os resultados como uma contagem de segundos.

Analisadores de modo

artigos recomendados

• Como dominar as ligações Anti e aplicá-las aos problemas de Negócio
• pensar em SQL vs pensar em Python
• DATE_TRUNC: uma função de hora SQL pode contar com