Mode Blog (Español)

Las Marcas de tiempo son cruciales para el análisis de negocios por una razón muy simple: te dicen cuándo suceden las cosas. Imagine intentar detectar tendencias en sus datos, como el tráfico web mensual, las ganancias trimestrales o el volumen de pedidos diario sin saber cuándo ocurrieron los eventos. Sería una pesadilla.

PostgreSQL ofrece una variedad de funciones de fecha para manipular marcas de tiempo. Para separar lo útil de lo oscuro, estamos compartiendo instrucciones para las funciones de fecha Postgres más utilizadas y los escenarios de negocios donde son útiles.,

Las funciones de fecha de Postgres más utilizadas y los escenarios de negocio donde son útiles:

• redondear las marcas de tiempo con DATE_TRUNC
• encontrar eventos relativos al tiempo presente con NOW() y CURRENT_DATE
• aislar la hora del día y el día de la semana con EXTRACT
• calcular el tiempo transcurrido con AGE

hemos hecho disponibles los datos para cada ejemplo en el modo almacén público. Pruebe cada función de fecha en modo mientras trabaja a través de estos ejemplos. Regístrese para obtener una cuenta en Mode Studio y abra un nuevo informe para comenzar.,

redondeando las marcas de tiempo con DATE_TRUNC

la funciónDATE_TRUNC Redondea un valor de marca de tiempo a un intervalo especificado, lo que le permite contar eventos. Usted puede redondear una marca de tiempo para las siguientes unidades de tiempo:

• microsegundo
• milisegundo
• segundo
• minutos
• hora
• día
• semana
• mes
• cuarto
• año
• decenio
• siglo
• millenium

El DATE_TRUNC sintaxis se parece a esto: DATE_TRUNC('interval',timestamp).,

Por ejemplo SELECT DATE_TRUNC('day','2015-04-12 14:44:18') devuelve un resultado de 2015-04-12 00:00:00.

para una explicación más detallada de DATE_TRUNC (y una referencia imprimible que puede mantener en su escritorio!), echa un vistazo a este post.

¿cómo ha cambiado el tráfico web con el tiempo?

pruebe DATE_TRUNC consultando la tabla modeanalytics.web_events, que contiene registros de muestra de visitas al sitio web, incluida una columna occurred_at. Puede aislar el mes de la visita con DATE_TRUNC.,

para devolver un recuento de visitas web cada mes por canal, agregue la columna channel y una instrucción COUNT a la instrucción SELECT, luego agrupe por month y channel. (Dado que mes y canal son los dos primeros valores en su instrucción SELECT, puede GROUP BY 1,2), así:

finalmente, use ORDER BY 1,2 para organizar sus resultados cronológicamente (por mes) y alfabéticamente (por canal).,

en modo, puede crear un gráfico de líneas para visualizar los resultados de la consulta.

MODE Analytics

buscar eventos relativos al tiempo presente con NOW () y CURRENT_DATE

la función NOW() date devuelve la marca de tiempo actual en UTC (si la zona horaria no está especificada). Puede restar intervalos de NOW() para extraer eventos que ocurrieron en la última hora, El último día,la última semana, etc.

EjecutarSELECT NOW() a las 9: 00am UTC del 11 de octubre de 2016 resultaría en 2016-10-11 09:00:00.,

la función CURRENT_DATE solo devuelve la fecha actual, no toda la marca de tiempo. Ejecutar SELECT CURRENT_DATE a las 9: 00am UTC del 11 de octubre de 2016 devolvería 2016-10-11.

¿qué pedidos se han realizado en las últimas 12 horas?

la demostración de la tabla.orders contiene registros de muestra de todos los pedidos, incluida una columna de marca de tiempo occurred_at en UTC.,

para encontrar pedidos realizados en las últimas 12 horas, use una cláusula WHEREpara devolver solo los pedidos que se colocaron después o exactamente en (>=) la marca de tiempo actual (NOW()) menos un intervalo de 12 horas.,

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

Modo de Analytics

además hour, se puede utilizar cualquiera de los siguientes intervalos:

• microsegundos
• milisegundos
• segundo
• minutos
• hora
• día
• semana
• mes
• año
• decenio
• siglo
• milenio

también Se pueden combinar diferentes intervalos de la misma expresión como esta:
interval '4 hours 3 minutes'

¿Qué órdenes se coloca ayer?,

puede usar la misma tabla para encontrar los pedidos de ayer combinando las funciones DATE_TRUNC y CURRENT_DATE.

comience usando una función DATE_TRUNC para redondear sus valores occurred_at por día(ya que queremos saber si algo sucedió ayer). Luego use una cláusula WHERE para devolver solo valores donde el día occurred_at es igual a la fecha actual (usando la función CURRENT_DATE) menos un intervalo de un día.,

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

Mode Analytics

aislar la hora del día y el día de la semana con EXTRACT

la función EXTRACT date le permite aislar subcampos como year u hour de marcas de tiempo.

Aquí está la sintaxis: EXTRACT(subfield FROM timestamp). Ejecuta EXTRACT(month FROM '2015-02-12') devuelve un resultado de 2.

tenga en cuenta que mientras que el ejemplo a continuación se centra en la hora del subcampo (hora del día), tiene muchos otros subcampos a su disposición que van desde millenniumhasta microsecond., Puede consultar la lista completa de subcampos disponibles aquí.

¿cuántos pedidos se realizan cada hora del día?

una empresa que administra un centro de distribución puede querer contratar más empleados cuando llega la mayor parte de los pedidos. Para averiguar cuándo se realizan pedidos a lo largo del día, puede usar la función EXTRACT y el subcampo hour para aislar la hora del día (de 0 a 23) en la que se produjo un pedido.,

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

Use la funciónCOUNTpara contar órdenes, y luegoGROUP BY hora. (Dado que hour es el primer valor en su instrucción SELECT, puede GROUP BY 1).

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

finalmente, para organizar sus resultados secuencialmente, use ORDER BY 1.

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

Y aquí están los resultados! Parece que podría ayudar tener algunos trabajadores adicionales en el reloj temprano en la mañana y alrededor de la hora del almuerzo.,

MODE Analytics

¿Cuál es el volumen promedio de pedidos entre semana?

para determinar el volumen promedio de órdenes que ocurrieron por día de la semana, use EXTRACT y el subcampo dow para aislar el día de la semana (de 0 a 6, donde 0 es domingo) en el que se produjo una orden.

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

a continuación, redondee las marcas de tiempo de la orden por día con DATE_TRUNC., Tomando un COUNT de pedidos agrupados por dow y day devuelve el número de pedidos realizados cada día, junto con el correspondiente día de la 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 el volumen de orden promedio del día de la semana, use la consulta anterior como subconsulta (alias a). Tome el promedio de pedidos (usando la función AVG()), y luego use una cláusula WHERE para filtrar los sábados y domingos.

Los gráficos de números grandes son excelentes para mostrar métricas agregadas., Para vigilar el volumen de pedidos, reúne métricas como esta en un panel.

MODE Analytics

calculando el tiempo transcurrido con la edad

la funciónAGE date calcula cuánto tiempo hace que ocurrió un evento. La sintaxis es bastante sencilla: aplique AGE() a una sola marca de tiempo, y su consulta devolverá la cantidad de tiempo desde que ese evento tuvo lugar. Ejecutar SELECT AGE( '2010-01-01' ) el 1 de enero de 2011 devolvería un resultado de 1 years 0 months 0 days.,

AGE() también puede determinar cuánto tiempo pasó entre los dos eventos. En lugar de poner una sola marca de tiempo dentro de los paréntesis, inserte ambas marcas de tiempo (comenzando con la marca de tiempo más reciente) y sepárelas con una coma. Ejecuta SELECT AGE( '2012-12-01',‘2010-01-01’) volvería 2 years 11 months 0 days.

tenga en cuenta que esta aplicación de la función AGEes equivalente a restar las marcas de tiempo: SELECT '2012-12-01' - '2010-01-01'.

¿qué edad tiene una cuenta de cliente?,

supongamos que su equipo de ventas quiere personalizar los saludos en función del tiempo que el cliente ha estado utilizando su producto. Puede encontrar cuánto tiempo ha transcurrido desde la creación de la cuenta utilizando la función AGE.

La tabla modeanalytics.customer_accounts contiene registros de cuentas de clientes de muestra. Seleccione la columna de nombres de cuenta (name) y aplique la función AGE() a la columna de marcas de tiempo que muestran cuándo se creó cada cuenta (created).,

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

Mode Analytics

¿cuánto tiempo tardan los usuarios en completar su perfil cada mes, en promedio?

la tabla modeanalytics.profilecreationevents contiene datos de muestra de los usuarios que crearon un perfil, incluidas las marcas de tiempo de inicio y finalización.

para encontrar el tiempo promedio para completar un perfil cada mes, comience buscando el tiempo que le tomó a cada usuario completar un perfil, así como el mes en el que se inició el proceso de creación del perfil. Primero, redondee la marca de tiempo started_at por mes, utilizando la función DATE_TRUNC., A continuación, encuentre el tiempo transcurrido desde started_at hasta ended_at para cada perfil utilizando la función AGE.

encuentre el promedio de cada mes aplicando la función AVG al valor de tiempo transcurrido (su instrucción AGE) y agrupando por mes.

para devolver valores en una unidad consistente para gráficos, aplique la función EXTRACT y el subcampo epoch a sus valores para devolver resultados como una cuenta de segundos.

análisis de modo

artículos recomendados

• Cómo dominar las uniones Anti y aplicarlas a problemas de Negocio
• pensar en SQL vs pensar en Python
• DATE_TRUNC: una función de marca de tiempo SQL con la que puedes contar