Как заполнить временной разрыв после группировки записи даты по месяцам в postgres

У меня есть записи таблицы как -

date                n_count
2020-02-19 00:00:00  4
2020-07-14 00:00:00  1
2020-07-17 00:00:00  1
2020-07-30 00:00:00  2
2020-08-03 00:00:00  1
2020-08-04 00:00:00  2
2020-08-25 00:00:00  2
2020-09-23 00:00:00  2
2020-09-30 00:00:00  3
2020-10-01 00:00:00  11
2020-10-05 00:00:00  12
2020-10-19 00:00:00  1
2020-10-20 00:00:00  1
2020-10-22 00:00:00  1
2020-11-02 00:00:00  376
2020-11-04 00:00:00  72
2020-11-11 00:00:00  1

Я хочу сгруппировать все записи по месяцам, чтобы найти общее количество месяцев, которое работает, но отсутствует месяц. как восполнить этот пробел.

time           month_count
"2020-02-01"    4
"2020-07-01"    4
"2020-08-01"    5
"2020-09-01"    5
"2020-10-01"    26
"2020-11-01"    449

Это то, что я пробовал.

SELECT (date_trunc('month', date))::date AS time,
       sum(n_count) as month_count      
FROM table1
group by time
order by time asc

sql postgresql datetime aggregate-functions date-arithmetic

15.12.2020 14:38

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

241

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Вы можете использовать generate_series() для создания всех звезд месяцев между самой ранней и самой поздней датой, доступной в таблице, а затем принести таблицу с left join:

select d.dt, coalesce(sum(t.n_count), 0) as month_count      
from (
    select generate_series(date_trunc('month', min(date)), date_trunc('month', max(date)), '1 month') as dt 
    from table1
) as d(dt)
left join table1 t on t.date >= d.dt and t.date < d.dt + interval '1 month'
group by d.dt
order by d.dt

15.12.2020 14:42

Я бы просто UNION ряд дат, сгенерированный из MIN и MAX даты:

демо:дб<>рабочий пример

WITH cte AS (                                      -- 1
    SELECT
        *,
        date_trunc('month', date)::date AS time
    FROM
        t
)
SELECT 
    time,
    SUM(n_count) as month_count                    --3
FROM (
    SELECT
        time,
        n_count
    FROM cte

    UNION

    SELECT                                        -- 2
        generate_series(
            (SELECT MIN(time) FROM cte),
            (SELECT MAX(time) FROM cte),
            interval '1 month'
        )::date,
        0
) s
GROUP BY time
ORDER BY time

Используйте CTE для вычисления date_trunc только один раз. Может быть опущен, если вы хотите дважды назвать свой стол в UNION ниже
Создавайте ежемесячные серии дат от MIN до MAX даты, содержащие вашу n_count value = 0. Добавьте его в таблицу
Сделайте свой расчет

Спасибо. Это фактически решило мою проблему.

— 16.12.2020 07:40

15.12.2020 14:55