Блог

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

В этом запросе MySQL указывается, когда каждый курс был в последний раз посещен и кем. Я конвертирую этот запрос в PostgreSQL 9.3.22. У меня не было большого опыта работы с Postgres, что оказалось очень сложным. Мне также нужно преобразовать дату эпохи в другой часовой пояс, поскольку местоположение базы данных PostgreSQL находится не в моем часовом поясе. Редактировать: timecreated в обеих базах данных хранится как эпоха (например 1612399773 )

 select
    userid 'lastaccesseduserid',
    courseid,
    contextid,
    from_unixtime(max(timecreated), '%D %M %Y') 'lastaccesseddate'
from mdl_logstore_standard_log
where timecreated >= unix_timestamp(date_sub(now(), interval 2 year))
group by courseid
 

Здесь выводится вывод как таковой:

 | lastaccesseduserid | courseid | contextid | lastaccesseddate  |
|--------------------|----------|-----------|-------------------|
| 45                 | 6581     | 68435     | 22nd January 2021 |
| 256676             | 32       | 4664      | 19th August 2019  |
etc.
 

Мои усилия по преобразованию в PostgreSQL:

 select
    distinct ON (courseid) courseid,
    contextid,
    to_timestamp(max(timecreated))::timestamptz::date at time zone 'utc' at time zone 'Australia/Sydney' "last accessed date",
    userid
from mdl_logstore_standard_log
where timecreated >= extract(epoch from now()- interval '2 year')
group by courseid
-- error: column userid, contextid must appear in the GROUP BY clause or be used in an aggregate function
 

Ни один из этих столбцов не является первичным ключом ( id как указано здесь). Группировка по id плоха, так как вместо этого будет отображаться каждая запись в таблице журнала. Любая помощь приветствуется!

Postgres верен, этот запрос не является допустимым SQL.

SQL-92 и более ранние версии не разрешают запросы, для которых список выбора, условие наличия или ПОРЯДОК ПО списку относятся к неагрегированным столбцам, которые не названы в предложении GROUP BY .

Вы не можете group by courseid и select courseid, contextid, userid потому, что у каждого courseid может быть много строк с разными идентификаторами контекста и идентификаторами пользователя. Вам либо нужно group by courseid, contextid, userid , либо вам нужно сообщить базе данных, как вы хотите, чтобы эти столбцы были объединены, например, с sum помощью или string_agg .

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

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

MySQL допускал некоторые неразумные «расширения» SQL, которые в более поздних версиях были отключены по умолчанию. Этот конкретный контролируется ONLY_FULL_GROUP_BY, который MySQL 5.7 и выше разумно включается по умолчанию. Ваша база данных либо отключила ее, либо настолько старая, что она не была установлена по умолчанию.

Подробнее см. В разделе Обработка MySQL GROUP BY.

Я бы предложил сначала включить ONLY_FULL_GROUP_BY и исправить запросы в MySQL. Затем перенесите на Postgres.

MySQL имеет много таких нестандартных функций. PostgreSQL намного более соответствует стандартам. Будет сложно конвертировать как в стандартный SQL, так и в PostgreSQL. Я бы предложил выполнять их по одному. Сначала выполните преобразование в стандартный SQL, включив ANSI и ТРАДИЦИОННЫЕ режимы SQL, и устраните возникающие проблемы в MySQL. Затем попробуйте преобразовать теперь более стандартный SQL в PostgreSQL. Эти режимы SQL представляют собой наборы конфигураций сервера MySQL, например ONLY_FULL_GROUP_BY , и могут быть включены и исправлены по одному за раз.

Обратите внимание, что срок службы PostgreSQL 9.3.22 истек два года назад. Было бы глупо выполнять всю эту работу по изменению баз данных только для использования устаревшей версии. Подумайте об обновлении.

Хранить времена как эпохи Unix неудобно и не нужно. Если это вообще возможно, рассмотрите возможность преобразования в timestamp при переносе ваших данных. Если вы собираетесь также сохранить часовой пояс, используйте timestamp with zone .

Вы не указали, каковы ваши намерения, но, похоже, вы хотите получить последнюю timecreated версию для каждого идентификатора курса.

Для этого не требуется GROUP BY в Postgres, только distinct on () . Дополнительным преимуществом которого является то, что вы можете включить любой столбец, который хотите, не ограничиваясь GROUP BY правилами. Однако это работает, только если вам нужна одна строка на courseid (и это должно быть «самое раннее» или «самое последнее»). Для других требований (например, «три последних») оконные функции лучше подходят.

to_timestamp() уже возвращает timestamptz , поэтому приведение не требуется. Если вы хотите удалить временную часть (что ::date и будет делать приведение) Я думаю, это следует сделать после настройки часового пояса. Но тогда настройка часового пояса кажется довольно бесполезной, если вас не волнует время.

 select distinct ON (courseid) 
        courseid,
        contextid,
        to_timestamp(timecreated) at time zone 'utc' at time zone 'Australia/Sydney' "last accessed date",
    userid
from mdl_logstore_standard_log
where to_timestamp(timecreated) >= current_timestamp - interval '2 year'
group by courseid, 3 DESC
 

Вы также должны использовать реальное timestamp значение в предложении WHERE, потому что продолжительность «2 года» может отличаться в зависимости от фактического года. Сравнение эпох не будет учитывать это.

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

Вместо ссылки на индекс столбца в ( 3 ) в порядке, вы также можете повторить все выражение: order by courseid, to_timestamp(timecreated) at time zone 'utc' at time zone 'Australia/Sydney' DESC

И вам действительно не следует использовать Postgres 9.3 — особенно для новой установки. Нет причин не использовать последнюю версию (которая составляет 13 по состоянию на 2021-02-04). Если это существующая (старая) установка, обновите ее как можно скорее. Обновление с 9.3.22 до 13.1 дает вам исправления на 2,7 года (из них 2278)