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.