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

aid · « : 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_id	cost	rd_id	product_order_date
88164	0	1	0000-00-00 00:00:00
143747	0	3	2011-07-18 11:40:42
149491	10.1	19	2011-07-25 04:27:24
151797	138	66	2011-08-02 14:45:06
152271	94.14	109	2011-08-08 15:49:20
155702	94.14	166	2011-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

ids	rd_id	order_id	cost	product_order_date
88164,88165	1	88164	0	0000-00-00 00:00:00
143829,1438...	12	143829	0	2011-07-19 10:19:19
150957,1509...	52	150957	33.48	2011-07-29 12:00:20
152246,1522...	97	152246	42.54	2011-08-05 17:26:18
152358,1523...	146	152358	12.92	2011-08-11 12:05:02
155726,1557...	175	155726	63.72	2011-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 · « **Ответ #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 · « **Ответ #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 · « **Ответ #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)

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

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

aid

MySQL+GROUP BY WEEK()+GROUP_CONCAT()

shumtest

Re: MySQL+GROUP BY WEEK()+GROUP_CONCAT()

aid

Re: MySQL+GROUP BY WEEK()+GROUP_CONCAT()

aid

Re: MySQL+GROUP BY WEEK()+GROUP_CONCAT()