Форум русскоязычного сообщества Ubuntu


Считаете, что Ubuntu недостаточно дружелюбна к новичкам?
Помогите создать новое Руководство для новичков!

Автор Тема: MySQL+GROUP BY WEEK()+GROUP_CONCAT()  (Прочитано 1065 раз)

0 Пользователей и 1 Гость просматривают эту тему.

Оффлайн aid

  • Автор темы
  • Любитель
  • *
  • Сообщений: 90
    • Просмотр профиля
MySQL+GROUP BY WEEK()+GROUP_CONCAT()
« : 23 Август 2011, 01:25:04 »
Всем привет

столкнулся с проблемой сортировки выборки в MySQL. Почти весь день просидел, испробовал разные варианты, но нужного результата не добился. Возможно, кто-то подскажет правильное решение или просто еще один вариант "попробовать", потому что мне явно нужен свежий взгляд на проблему.

Итак, имеем таблицу MySQL (пожалуйста, смотрите вложение)

Имеем упрощенный до минимума ЗАПРОС 1

SELECT
  order_id,
  cost,
  rd_id,
  product_order_date
FROM report_data
GROUP BY WEEK(product_order_date)

Возвращает результат (РЕЗУЛЬТАТ 1)
order_idcostrd_idproduct_order_date
88164010000-00-00 00:00:00
143747032011-07-18 11:40:42
14949110.1192011-07-25 04:27:24
151797138662011-08-02 14:45:06
15227194.141092011-08-08 15:49:20
15570294.141662011-08-17 06:00:03

теперь выполним ЗАПРОС 2

SELECT
  order_id,
  cost,
  rd_id,
  product_order_date
FROM report_data

вернет все строки (РЕЗУЛЬТАТ 2)
(Нажмите, чтобы показать/скрыть)

смотрим РЕЗУЛЬТАТ 2. Видим, что действительно в неделю с 8-08 п- 14-08 первая запись была сделана 2011-08-08 15:49:20 и имеет rd_id=109, а в неделю с 15-08 по 21-08 первая запись сделана 2011-08-17 06:00:03 и имеет rd_id=166.

Теперь сравним с РЕЗУЛЬТАТом 1 и увидим, что ЗАПРОС 1 отработал правильно


Теперь в ЗАПРОС 1 добавим одну строку и получим ЗАПРОС 3

SELECT
  GROUP_CONCAT(order_id) AS ids,
  rd_id,
  order_id,
  cost,
  product_order_date
FROM report_data
GROUP BY WEEK(product_order_date)

вернет РЕЗУЛЬТАТ 3
idsrd_idorder_idcostproduct_order_date
88164,8816518816400000-00-00 00:00:00
143829,1438...1214382902011-07-19 10:19:19
150957,1509...5215095733.482011-07-29 12:00:20
152246,1522...9715224642.542011-08-05 17:26:18
152358,1523...14615235812.922011-08-11 12:05:02
155726,1557...17515572663.722011-08-17 12:04:53


Видим, что для предпоследней строки почему-то выбрана запись с rd_id=146 и датой 2011-08-11 12:05:02, а для последней строки запись rd_id=175 и датой 2011-08-17 12:04:53

Таким образом получается, что GROUP_CONCAT() изменяет порядок группы

по ссылке http://lists.mysql.com/mysql/2664 прочел, что WEEK() не гарантирует выборку именно первой записи в группе. Но при этом, без GROUP_CONCAT() группировка правильная.

пробовал следующие запросы
ЗАПРОС 4

SELECT
  rd_id,
  order_id,
  cost,
  product_order_date,
  WEEK(product_order_date) AS _week
FROM report_data
GROUP BY _week

ЗАПРОС 5

SELECT
  GROUP_CONCAT(order_id) AS ids,
  rd_id,
  order_id,
  cost,
  product_order_date,
  WEEK(product_order_date) AS _week
FROM report_data
GROUP BY _week

ЗАПРОС 4 возвращает результат, аналогичный РЕЗУЛЬТАТу 1,  а ЗАПРОС 5 - аналогичный РЕЗУЛЬТАТу 3.

Заметил, что такой (баг?) наблюдается только при использовании функций дат в предложении группировки. Т.е замена WEEK() на  DAY(), DAYOFYEAR(), MONTH() и другие функции даты не спасает - GROUP_CONCAT() изменяет порядок выборки


Пожалуйста, если кто знает решение проблемы или видит мою ошибку, подскажите.

Заранее спасибо

Оффлайн shumtest

  • Активист
  • *
  • Сообщений: 731
  • Это вам просто кажется...
    • Просмотр профиля
    • Блог Шумомера
Re: MySQL+GROUP BY WEEK()+GROUP_CONCAT()
« Ответ #1 : 23 Август 2011, 09:58:30 »
В запросе смешаны групповые и обычные поля, что вообще-то является недопустимым.
Если нужны просто rd_id первые за эту неделю - просто написать MIN(rd_id) т.е., примерно, так:

SELECT
  GROUP_CONCAT(order_id) AS ids,
  MIN(rd_id)
FROM report_data
GROUP BY WEEK(product_order_date)

Результат:

ids MIN(rd_id)
[BLOB - 11Байт] 1
[BLOB - 111Байт] 3
[BLOB - 328Байт] 19
[BLOB - 300Байт] 66
[BLOB - 398Байт] 109
[BLOB - 118Байт] 166

Оффлайн aid

  • Автор темы
  • Любитель
  • *
  • Сообщений: 90
    • Просмотр профиля
Re: MySQL+GROUP BY WEEK()+GROUP_CONCAT()
« Ответ #2 : 23 Август 2011, 12:47:44 »
Спасибо за ответ

MIN(), MAX() на подходят по той причине, что дата может изменяться
Т.е, например:

запись с rd_id=2 имеет дату 2011-08-09 12:12:12, а запись с rd=3 имеет дату 2011-08-12 23:23:23. Это дата, когда запись была добавлена в таблицу или последний раз обновлялась.

rd_id     date
     2        2011-08-09 12:12:12
     3        2011-08-12 23:23:23

Т.е. администратор может отредактировать запись с rd_id=2, например, 13 августа. Тогда таблица будет иметь вид
rd_id     date
     2        2011-08-13 12:12:12
     3        2011-08-12 23:23:23

Значит MIN(rd_id) вернет неправильный результат.

Кстати, пробовал MIN() и MAX() применять к дате, но результата никакого: без GROUP_CONCAT() выборка правильная, с
GROUP_CONCAT() - неправильная

Вопрос актуален

Оффлайн aid

  • Автор темы
  • Любитель
  • *
  • Сообщений: 90
    • Просмотр профиля
Re: MySQL+GROUP BY WEEK()+GROUP_CONCAT()
« Ответ #3 : 23 Август 2011, 19:03:12 »
Спасибо за ответ! Совет с использованием MIN(), MAX() очень выручил. Действительно, свежий взгляд - новое решение.

В итоге все оказалось намного проще по сравнению с теми дебрями, куда я залез.

SELECT
  GROUP_CONCAT(DISTINCT order_id) AS order_ids,
  MIN(order_id) AS per_order_id,
  MIN(product_order_date) AS per_product_order_date,
  FORMAT(SUM(cost),2) AS sum_cost
FROM report_data
GROUP BY WEEK(product_order_date)

 

Страница сгенерирована за 0.098 секунд. Запросов: 25.