Перевести запрос mySQL в Postgres
Я не могу написать запрос Postgres. Я всегда получаю ошибку или получаю неверный результат. Я пытаюсь сравнить подсчеты между сегодняшним и вчерашним днем.
Этот запрос MySQL, который работает нормально:
SELECT
DATE_FORMAT(crh.date, '%d-%m-%Y') AS name,
DATE_FORMAT(crh.date, '%Y-%m-%d') AS nameGroup,
COUNT(crh.id) AS turnover,
crh_.name AS nameChr,
crh_.nameGroup AS nameGroupChr,
crh_.turnover AS turnoverChr
FROM
camera_reports_history AS crh
LEFT JOIN(
SELECT DATE_FORMAT(crh_.date, '%d-%m-%Y') AS name,
DATE_FORMAT(crh_.date, '%Y-%m-%d') AS nameGroup,
COUNT(crh_.id) AS turnover
FROM
camera_reports_history AS crh_
WHERE
crh_.date >= '2018-07-09 00:00:00' AND crh_.date <= '2018-07-20 14:02:22'
GROUP BY
nameGroup
) AS crh_
ON
crh_.nameGroup = DATE_FORMAT(SUBDATE(crh.date, 1),
'%Y-%m-%d')
WHERE
crh.date >= '2018-07-10 00:00:00' AND crh.date <= '2018-07-20 14:02:22'
GROUP BY
nameGroup
Результат:
"10-07-2018","2018-07-10","418","09-07-2018","2018-07-09","581"
"11-07-2018","2018-07-11","389","10-07-2018","2018-07-10","418"
"12-07-2018","2018-07-12","453","11-07-2018","2018-07-11","389"
"13-07-2018","2018-07-13","401","12-07-2018","2018-07-12","453"
...
Мой запрос PostgreSQL выглядит так:
SELECT
to_char(crh."date", 'DD-MM-YYYY') AS name,
to_char(crh."date", 'YYYY-MM-DD') AS nameGroup,
COUNT(crh.id) AS turnover,
crh_.name AS nameChr,
crh_.nameGroup AS nameGroupChr,
crh_.turnover AS turnoverChr
FROM
camera_reports_history AS crh
LEFT JOIN(
SELECT to_char(crh_."date", 'DD-MM-YYYY') AS name,
to_char(crh_."date", 'YYYY-MM-DD') AS nameGroup,
COUNT(crh_.id) AS turnover
FROM
camera_reports_history AS crh_
WHERE
crh_.date >= '2018-07-09 00:00:00' AND crh_.date <= '2018-07-20 14:02:22'
GROUP BY
nameGroup,
name
ORDER BY
nameGroup
) AS crh_
ON
crh_.nameGroup = to_char(
crh."date" - INTERVAL '1 day',
'YYYY-MM-DD'
)
WHERE
crh.date >= '2018-07-10 00:00:00' AND crh.date <= '2018-07-20 14:02:22'
GROUP BY
nameGroup,
name
ORDER BY
nameGroup
ошибки:
ERROR: column `crh.date` must appear in the GROUP BY clause or be used in an aggregate function
если я вставлю необходимые столбцы:
GROUP BY nameGroup, name, date, crh_.nameGroup, crh_.name, crh_.turnover
Я получу бесполезные результаты. Может кто-нибудь мне помочь?
Я получаю все результаты crh."date" и не сгруппированы по nameGroup. каждый запрос SELECT и LEFT JOIN работают нормально, но вместе у меня проблемы
Это должно исчезнуть, если у вас GROUP BY name, namegroup. Более серьезной проблемой являются значения crh_.* в списке выбора - какое из этих значений должно появиться в результате для каждой группы?
где вы видите crh_.* в списке выбора? если я группирую crh.date (для решения ошибки), я получаю все даты разгруппированными в результате.
Итак, GROUP BY name, namegroup. Я говорил о crh_.name и подобных.
Ответы 2
Блоки транзакций хорошо работают в postgresql, поэтому вместо сложного составного оператора, который пытается сортировать, объединять, группировать и фильтровать все сразу, вы можете использовать временные таблицы. Это позволяет вам разбить шаги на несколько простых операторов и хранить промежуточные данные во временных таблицах, которые исчезают в конце блока транзакции.
Я думаю, вам будет намного проще отлаживать этот метод.
Это не совсем ответ на вопрос.
Я нашел решение:
SELECT
DISTINCT ON(nameGroup)
to_char(crh."date", 'DD-MM-YYYY') AS name,
to_char(crh."date", 'YYYY-MM-DD') AS nameGroup,
crh__.turnover AS turnover,
crh_.name AS nameChr,
crh_.nameGroup AS nameGroupChr,
crh_.turnover AS turnoverChr
FROM
camera_reports_history AS crh
LEFT JOIN(
SELECT
to_char(crh__."date", 'YYYY-MM-DD') AS nameGroup__,
COUNT(crh__.id) AS turnover
FROM
camera_reports_history AS crh__
WHERE
crh__.date >= '2018-07-10 00:00:00' AND crh__.date <= '2018-07-20 14:02:22'
GROUP BY
nameGroup__
) AS crh__ ON crh__.nameGroup__ = to_char((crh."date"), 'YYYY-MM-DD')
LEFT JOIN(
SELECT to_char(crh_."date", 'DD-MM-YYYY') AS name,
to_char(crh_."date", 'YYYY-MM-DD') AS nameGroup,
COUNT(crh_.id) AS turnover
FROM
camera_reports_history AS crh_
WHERE
crh_.date >= '2018-07-09 00:00:00' AND crh_.date <= '2018-07-20 14:02:22'
GROUP BY
nameGroup,
name
ORDER BY
nameGroup
) AS crh_ ON crh_.nameGroup = to_char((crh."date" - INTERVAL '1 day'), 'YYYY-MM-DD')
WHERE
crh.date >= '2018-07-10 00:00:00' AND crh.date <= '2018-07-20 14:02:22'
GROUP BY
nameGroup,
name,
crh.date,
crh__.turnover,
crh_.nameGroup,
crh_.name,
crh_.turnover
ORDER BY
nameGroup
Я делаю второй LEFT JOIN для подсчета результатов и использую DISTINCT ON(nameGroup) на основном SELECT.
Я думаю, это не идеальный вопрос. но моя проблема на данный момент решена. Не стесняйтесь оптимизировать этот запрос.
«Бесполезные результаты» - это не очень много информации. Можете ли вы описать разницу на образце?