funciones - Resultados de consultas grupales por mes y año en postgresql

funciones en postgresql

Question

Tengo la siguiente tabla de base de datos en un servidor de Postgres:

id date Product Sales
1245 01/04/2013 Toys 1000
1245 01/04/2013 Toys 2000
1231 01/02/2013 Bicycle 50000
456461 01/01/2014 Bananas 4546

Me gustaría crear una consulta que proporcione la SUM de la columna Sales y agrupe los resultados por mes y año de la siguiente manera:

Apr 2013 3000 Toys
Feb 2013 50000 Bicycle
Jan 2014 4546 Bananas

¿Hay una manera simple de hacer eso?

Answer 1

Hay otra forma de lograr el resultado usando la función date_part () en postgres.

 SELECT date_part(''month'', txn_date) AS txn_month, date_part(''year'', txn_date) AS txn_year, sum(amount) as monthly_sum
FROM yourtable
GROUP BY date_part(''month'', txn_date)

Gracias

Answer 2

No puedo creer que la respuesta aceptada tenga tantos votos positivos: es un método horrible.

Esta es la forma correcta de hacerlo, con date_trunc :

 SELECT date_trunc(''month'', txn_date) AS txn_month, sum(amount) as monthly_sum
FROM yourtable
GROUP BY txn_month

Es una mala práctica pero puede que te perdonen si usas

 GROUP BY 1

en una consulta muy simple.

También puedes usar

 GROUP BY date_trunc(''month'', txn_date)

si no quieres seleccionar la fecha.

Answer 3

Postgress tiene pocos tópicos para las marcas de tiempo en postgreso:

marca de tiempo sin zona horaria - (Preferable para almacenar marcas de tiempo UTC) Lo encuentra en el almacenamiento de base de datos multinacional. El cliente en este caso se ocupará de la compensación de la zona horaria para cada país.

marca de tiempo con zona horaria: la compensación de zona horaria ya está incluida en la marca de tiempo.

En algunos casos, su base de datos no utiliza la zona horaria, pero aún necesita agrupar registros con respecto a la zona horaria local y el horario de verano (por ejemplo, https://www.timeanddate.com/time/zone/romania/bucharest )

Para agregar la zona horaria, puede usar este ejemplo y reemplazar la compensación de la zona horaria con la suya.

"your_date_column" at time zone ''+03''

Para agregar el desfase de horario de verano +1 específico para el horario de verano, debe verificar si su marca de tiempo coincide con un horario de verano. Como esos intervalos varían de 1 a 2 días, usaré una aproximación que no afecte los registros de fin de mes, por lo que en este caso puedo ignorar el intervalo exacto de cada año.

Si se debe generar una consulta más precisa, debe agregar condiciones para crear más casos. Pero aproximadamente, esto funcionará bien en la división de datos por mes con respecto a la zona horaria y SummerTime cuando encuentre la marca de tiempo sin zona horaria en su base de datos:

SELECT
"id", "Product", "Sale",
date_trunc(''month'',
CASE WHEN
Extract(month from t."date") > 03 AND
Extract(day from t."date") > 26 AND
Extract(hour from t."date") > 3 AND
Extract(month from t."date") < 10 AND
Extract(day from t."date") < 29 AND
Extract(hour from t."date") < 4
THEN
t."date" at time zone ''+03'' -- Romania TimeZone offset + DST
ELSE
t."date" at time zone ''+02'' -- Romania TimeZone offset
END) as "date"
FROM
public."Table" AS t
WHERE 1=1
AND t."date" >= ''01/07/2015 00:00:00''::TIMESTAMP WITHOUT TIME ZONE
AND t."date" < ''01/07/2017 00:00:00''::TIMESTAMP WITHOUT TIME ZONE
GROUP BY date_trunc(''month'',
CASE WHEN
Extract(month from t."date") > 03 AND
Extract(day from t."date") > 26 AND
Extract(hour from t."date") > 3 AND
Extract(month from t."date") < 10 AND
Extract(day from t."date") < 29 AND
Extract(hour from t."date") < 4
THEN
t."date" at time zone ''+03'' -- Romania TimeZone offset + DST
ELSE
t."date" at time zone ''+02'' -- Romania TimeZone offset
END)

Answer 4

to_char realidad le permite sacar el año y el mes de una sola vez!

select to_char(date(''2014-05-10''),''Mon-YY'') as year_month; --''May-14''
select to_char(date(''2014-05-10''),''YYYY-MM'') as year_month; --''2014-05''

o en el caso del ejemplo del usuario anterior:

select to_char(date,''YY-Mon'') as year_month
sum("Sales") as "Sales"
from some_table
group by 1;

Answer 5

la respuesta de bma es genial! Lo he usado con ActiveRecords, aquí está si alguien lo necesita en Rails:

Model.find_by_sql(
"SELECT TO_CHAR(created_at, ''Mon'') AS month,
EXTRACT(year from created_at) as year,
SUM(desired_value) as desired_value
FROM desired_table
GROUP BY 1,2
ORDER BY 1,2"
)

Answer 6

select to_char(date,''Mon'') as mon,
extract(year from date) as yyyy,
sum("Sales") as "Sales"
from yourtable
group by 1,2

A pedido de Radu, explicaré esa consulta:

to_char(date,''Mon'') as mon, convierte el atributo "fecha" en el formato definido de la forma abreviada del mes.

extract(year from date) as yyyy : la función "extraer" de Postgresql se usa para extraer el año YYYY del atributo "fecha".

sum("Sales") as "Sales" : la función SUM () suma todos los valores de "Ventas" y proporciona un alias sensible a mayúsculas y minúsculas, con la mayúscula de minúscula mantenida mediante comillas dobles.

group by 1,2 : la función GROUP BY debe contener todas las columnas de la lista SELECT que no formen parte del agregado (es decir, todas las columnas que no estén dentro de las funciones SUM / AVG / MIN / MAX etc.). Esto le dice a la consulta que SUM () debe aplicarse para cada combinación única de columnas, que en este caso son las columnas de mes y año. La parte "1,2" es una abreviatura en lugar de utilizar los alias de columna, aunque probablemente sea mejor usar las expresiones completas "to_char (...)" y "extract (...)" para facilitar la lectura.