TL; WR: Comment interroger la moyenne de la somme mensuelle, lorsque certains mois n'ont pas d'enregistrement (donc devrait être 0)?
Mes enfants rapportent quotidiennement combien de temps ils ont fait les tâches ménagères (dans une base de données PostgreSQL). Mon ensemble de données ressemble alors à ceci:
CREATE TABLE public.chores_record
(
date date NOT NULL,
"user" text NOT NULL,
duration integer NOT NULL,
PRIMARY KEY (date, "user")
);
INSERT INTO
public.chores_record(date, "user", duration)
VALUES
('2020-01-01','Alice',120),
('2020-01-02','Bob',30),
('2020-01-03','Charlie',10),
('2020-01-23','Charlie',10),
('2020-02-03','Charlie',10),
('2020-02-23','Charlie',10),
('2020-03-02','Bob',30),
('2020-03-03','Charlie',10),
('2020-03-23','Charlie',10)
;
Je veux savoir combien font-ils en moyenne par mois. Concrètement, le résultat que je souhaite est:
=(120+0+0)÷3=(30+0+30)÷3=([10+10]+[10+10]+[10+10])÷3Certains mois, je n'ai pas de dossier pour certains utilisateurs (par exemple, Alice en février et mars). Ainsi, l'exécution de la requête imbriquée suivante ne renvoie pas le résultat souhaité; en effet, cela ne prend pas en compte le fait que, parce qu'il n'y a pas d'enregistrement pour ces mois, la contribution d'Alice en février et mars devrait être de 0 (ici la moyenne est incorrectement calculée comme 120).
SELECT
unique_user,
round(avg(COALESCE(monthly_duration, 0))) -- COALESCE transforms 'null' into 0
FROM (
-- monthly duration with 'null' if no record for that user×month
SELECT
month_user_combinations.month,
month_user_combinations.unique_user,
monthly_duration.monthly_duration
FROM
(
(
-- all months×users combinations
SELECT
month,
unique_user
FROM (
(
-- list of unique months
SELECT DISTINCT
date_trunc('month', date) as month
FROM
public.chores_record
) AS unique_months
CROSS JOIN
(
-- list of unique users
SELECT DISTINCT
"user" as "unique_user"
FROM
public.chores_record
) AS unique_users
)
) AS month_user_combinations
LEFT OUTER JOIN
(
-- monthly duration for existing month×user combination only
SELECT
date_trunc('month', date) as month,
"user",
sum(duration) as monthly_duration
FROM
public.chores_record
GROUP BY
date_trunc('month', date),
"user"
) AS monthly_duration
ON (
month_user_combinations.month = monthly_duration.month
AND
month_user_combinations.unique_user = monthly_duration.user
)
)
) AS monthly_duration_for_all_combinations
GROUP BY
unique_user
;
Ainsi, j'ai construit une requête assez lourde comme suit:
-- this does not work
SELECT
"user",
round(avg(monthly_duration)) as avg_monthly_sum
FROM (
SELECT
date_trunc('month', date),
"user",
sum(duration) as monthly_duration
FROM
public.chores_record
GROUP BY
date_trunc('month', date),
"user"
) AS monthly_sum
GROUP BY
"user"
;
-- Doesn't return what I want:
--
-- "unique_user","avg_monthly_sum"
-- "Alice",120
-- "Bob",30
-- "Charlie",20
Cette requête fonctionne, mais est assez volumineuse.
Comment interroger la moyenne de la somme mensuelle plus élégamment que ci-dessus, en prenant en compte «aucun enregistrement» somme mensuelle = 0 »?
Remarque: il est prudent de supposer que je veux calculer la moyenne sur les mois qui ont au moins un enregistrement seulement (c'est-à-dire qu'il est normal de ne pas considérer décembre ou avril ici.)
date,user,duration 2020-01-01,Alice,120 2020-01-02,Bob,30 2020-01-03,Charlie,10 2020-01-23,Charlie,10 2020-02-03,Charlie,10 2020-02-23,Charlie,10 2020-03-02,Bob,30 2020-03-03,Charlie,10 2020-03-23,Charlie,10
4 Réponses :
Vous pouvez utiliser un CTE pour construire un calendrier-table:
-- EXPLAIN
WITH cal AS ( -- The unique months
SELECT DISTINCT date_trunc('mon', zdate) AS tick
FROM chores_record
)
, cnt AS ( -- the number of months (a scalar)
SELECT COUNT(*) AS nmonth
FROM cal
)
SELECT
x.zuser
, SUM(x.duration) AS tot_duration
, SUM(x.duration) / SUM(c.nmonth) AS Averarage_month -- this is ugly ...
FROM cal t
JOIN cnt c ON true -- This is ugly
LEFT JOIN chores_record x ON date_trunc('mon', x.zdate) = t.tick
GROUP BY x.zuser
;
Vous avez besoin de deux jeux de données supplémentaires pour cela: la liste des enfants et la liste des mois:
with
...
select
k."user",
avg(coalesce(d.duration, 0)) as duration
from
k cross join m left join d on (k."user" = d."user" and m."month" = d."month")
group by k."user"
order by k."user";
┌─────────┬─────────────────────â”
│ user │ duration │
├─────────┼─────────────────────┤
│ Alice │ 40.0000000000000000 │
│ Bob │ 20.0000000000000000 │
│ Charlie │ 20.0000000000000000 │
└─────────┴─────────────────────┘
Et la dernière étape est le calcul des moyennes:
with
k as (/* list of kids */
select distinct "user" from chores_record),
m as (/* list of months in format "yyyy-mm-01" */
select distinct date_trunc('month', "date") as "month" from chores_record),
d as (/* sums by moths and kids */
select
date_trunc('month', "date") as "month",
"user",
sum(duration) as duration
from chores_record
group by 1, 2)
select
m."month",
k."user",
coalesce(d.duration, 0) as duration
from
k cross join m left join d on (k."user" = d."user" and m."month" = d."month")
order by "month", "user";
┌────────────────────────┬─────────┬──────────â”
│ month │ user │ duration │
├────────────────────────┼─────────┼──────────┤
│ 2020-01-01 00:00:00+02 │ Alice │ 120 │
│ 2020-01-01 00:00:00+02 │ Bob │ 30 │
│ 2020-01-01 00:00:00+02 │ Charlie │ 20 │
│ 2020-02-01 00:00:00+02 │ Alice │ 0 │
│ 2020-02-01 00:00:00+02 │ Bob │ 0 │
│ 2020-02-01 00:00:00+02 │ Charlie │ 20 │
│ 2020-03-01 00:00:00+02 │ Alice │ 0 │
│ 2020-03-01 00:00:00+02 │ Bob │ 30 │
│ 2020-03-01 00:00:00+02 │ Charlie │ 20 │
└────────────────────────┴─────────┴──────────┘
Comme le cas d'utilisation était plus petit (pas des millions de lignes), une approche simple consiste à rechercher séparément
rejoignez les 2 pour avoir la réponse
Alice,40 Bob,20 Charlie,20
select "user", totalHours/monthCount from (select "user", sum(duration) totalHours from chores_record group by "user") as a, (select count(distinct(to_char(date, 'YYYYMM'))) monthCount from chores_record) as b ;
Bien que ce code puisse répondre à la question, fournir un contexte supplémentaire sur la manière et / ou pourquoi il résout le problème améliorerait la valeur à long terme de la réponse.
Dans Postgres, je recommanderais generate_series() pour construire la table de calendrier, puis l'agrégation. L'avantage est que cela fonctionne même s'il y a des mois où aucun utilisateur n'était actif.
select u."user", avg(coalesce(c.duration, 0)) avg_duration
from (
select generate_series(date_trunc('month', min(date)), date_trunc('month', max(date)), '1 month') as dt
from chores_record
) d
cross join (select distinct "user" from chores_record) u
left join (
select "user", date_trunc('month', date) as dt, sum(duration) as duration
from chores_record c
group by "user", date_trunc('month', date)
) c on c."user" = u."user" and c.dt = d.dt
group by u."user"
generate_series() génère tous les débuts de mois entre la date la plus ancienne et la plus récente du tableau. Ensuite, nous cross join cela avec la liste des utilisateurs distincts (dans une situation réelle, vous auriez probablement une table référentielle pour stocker les utilisateurs, que vous utiliseriez à la place). Ensuite, nous agrégons la table d'origine par utilisateur et par mois, puis nous la left join avec les combinaisons utilisateur / mois. La dernière étape est l'agrégation externe.
generate_series() est à la fois intelligent et puissant, merci: c'est exactement le genre de «moyens plus intelligents» que je recherchais! (Petite faute de frappe: l'avant-dernière ligne doit lire […] and c.dt = d.dt )
@ebosi: en effet. J'ai corrigé la faute de frappe.
Envisagez de gérer les problèmes d'affichage des données (par exemple, des données manquantes) dans le code de l'application
@Strawberry semble intéressant, mais je ne suis pas sûr de bien comprendre. Pourriez-vous détailler ou donner un exemple de ce que vous voulez dire?