Использование функции "над" приводит к столбцу "table.id" должно отображаться в предложении GROUP BY или использоваться в агрегатной функции

#postgresql #sqlalchemy

Вопрос:

В настоящее время я пишу приложение, которое показывает рост общего числа событий в моей таблице с течением времени, для этого у меня есть следующий запрос:

 query = session.query(
    count(Event.id).label('count'),
    extract('year', Event.date).label('year'),
    extract('month', Event.date).label('month')
).filter(
    Event.date.isnot(None)
).group_by('year', 'month').all()

Это приводит к следующему результату:

Рассчитывать	Год	Месяц
100	2021	1
50	2021	2
75	2021	3

Хотя это нормально само по себе, я хочу, чтобы оно отображало общее количество с течением времени, а не только количество событий в этом месяце, поэтому желаемый результат должен быть:

Рассчитывать	Год	Месяц
100	2021	1
150	2021	2
225	2021	3

Я читал в разных местах, где я должен использовать функцию окна с помощью функции SQLAlchemy over, однако я, кажется, не могу понять это, и каждый раз, когда я пытаюсь использовать ее, я получаю следующую ошибку:

 sqlalchemy.exc.ProgrammingError: (psycopg2.errors.GroupingError) column "event.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT count(event.id) OVER (PARTITION BY event.date ORDER...
                     ^
[SQL: SELECT count(event.id) OVER (PARTITION BY event.date ORDER BY EXTRACT(year FROM event.date), EXTRACT(month FROM event.date)) AS count, EXTRACT(year FROM event.date) AS year, EXTRACT(month FROM event.date) AS month 
FROM event 
WHERE event.date IS NOT NULL GROUP BY year, month]

Это запрос, который я использовал:

 session.query(
    count(Event.id).over(
        order_by=(
            extract('year', Event.date),
            extract('month', Event.date)
        ),
        partition_by=Event.date
    ).label('count'),
    extract('year', Event.date).label('year'),
    extract('month', Event.date).label('month')
).filter(
    Event.date.isnot(None)
).group_by('year', 'month').all()

Может кто-нибудь показать мне, что я делаю не так? Я искал несколько часов, но не могу понять, как получить желаемый результат при добавлении event.id в группе by мои строки не будут сгруппированы по месяцам и годам

Последний запрос, который я в итоге использовал:

 query = session.query(
    extract('year', Event.date).label('year'),
    extract('month', Event.date).label('month'),
    func.sum(func.count(Event.id)).over(order_by=(
        extract('year', Event.date),
        extract('month', Event.date)
    )).label('count'),
).filter(
    Event.date.isnot(None)
).group_by('year', 'month')

1. Проблема в том, что оконные функции работают с другими столбцами, возвращаемыми запросом… таким образом, вы можете делать все, что хотите, с двумя колонками. Сначала count(event.id) AS event_count , а затем добавьте SUM(event_count) OVER (...)

2. @melcher Я попробовал это, но column "event_count" does not exist по какой-то причине это приводит к ошибке, он не может найти столбец, который я только что создал с помощью AS инструкции

Ответ №1:

Я не уверен на 100%, чего вы хотите, но я предполагаю, что вам нужно количество событий до этого месяца для каждого месяца. Сначала вам нужно будет рассчитать количество событий в месяц, а также суммировать их с помощью функции окна postgresql.

Вы можете сделать это с помощью одной инструкции select:

 SELECT extract(year FROM events.date) AS year
  , extract(month FROM events.date) AS month
  , SUM(COUNT(events.id)) OVER(ORDER BY extract(year FROM events.date), extract(month FROM events.date)) AS total_so_far
FROM events
GROUP BY 1,2

но, возможно, вам будет легче думать об этом, если вы разделите его на две части:

 SELECT year, month, SUM(events_count) OVER(ORDER BY year, month)
FROM (
  SELECT extract(year FROM events.date) AS year
    , extract(month FROM events.date) AS month
    , COUNT(events.id) AS events_count
  FROM events
  GROUP BY 1,2
)

но не уверен, как это сделать в SQLAlchemy