Regrouper les résultats d'une requête par mois et par année dans postgresql

Question

J'ai la table de base de données suivante sur un serveur 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

J'aimerais créer une requête qui donne les résultats suivants SUM de la Sales et regroupe les résultats par mois et par année comme suit :

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

Y a-t-il un moyen simple de le faire ?

Answer 1

Pourquoi ne pas simplement utiliser date_part fonction. https://www.postgresql.org/docs/8.0/functions-datetime.html

SELECT date_part('year', txn_date) AS txn_year,
       date_part('month', txn_date) AS txn_month,
       sum(amount) as monthly_sum
FROM payment
GROUP BY txn_year, txn_month
order by txn_year;

Answer 2

bma La réponse est géniale ! Je l'ai utilisée avec ActiveRecords, la voici si quelqu'un en a besoin dans 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 3

Postgres dispose de plusieurs types d'horodatage :

timestamp sans fuseau horaire - (Il est préférable de stocker les horodatages UTC) On le trouve dans le stockage des bases de données multinationales. Dans ce cas, le client se chargera du décalage du fuseau horaire pour chaque pays.

timestamp avec le fuseau horaire - Le décalage du fuseau horaire est déjà inclus dans l'horodatage.

Dans certains cas, votre base de données n'utilise pas le fuseau horaire mais vous devez quand même regrouper les enregistrements en fonction du fuseau horaire local et de l'heure d'été (par ex. https://www.timeanddate.com/time/zone/romania/bucharest )

Pour ajouter le fuseau horaire, vous pouvez utiliser cet exemple et remplacer le décalage du fuseau horaire par le vôtre.

"your_date_column" at time zone '+03'

Pour ajouter le décalage de l'heure d'été +1 spécifique à l'heure d'été, vous devez vérifier si votre horodatage tombe dans une heure d'été. Comme ces intervalles varient de 1 ou 2 jours, je vais utiliser une approximation qui n'affecte pas les enregistrements de fin de mois, donc dans ce cas je peux ignorer l'intervalle exact de chaque année.

Si une requête plus précise doit être construite, vous devez ajouter des conditions pour créer plus de cas. Mais en gros, cela fonctionnera bien dans répartition des données par mois en fonction du fuseau horaire et de l'heure d'été. lorsque vous trouvez un timestamp sans fuseau horaire dans votre base de données :

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)