Форум MySQL

Есть желающие пошевелить мозгами? =)

Есть таблица расходов:

CREATE TABLE `charge` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID расхода',   `user` varchar(50) NOT NULL COMMENT 'ФИО растратчика',   `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'дата/время расхода',   `summ` mediumint(8) unsigned NOT NULL COMMENT 'сумма расхода',   `type` enum('nal','beznal') NOT NULL COMMENT 'способ оплаты',   `descr` varchar(255) NOT NULL COMMENT 'описание',   PRIMARY KEY (`id`) ) ENGINE=MyISAM  DEFAULT CHARSET=utf8

И дамп таблицы:

INSERT INTO `charge` (`id`, `user`, `date`, `summ`, `type`, `descr`) VALUES (1, 'Иванов И.И.', '2013-01-12 20:55:48', 45000, 'nal', 'Инкасация'), (2, 'Иванов И.И.', '2013-01-13 16:13:27', 100, 'nal', 'Связь'), (3, 'Петров П.П.', '2013-01-13 16:14:55', 5000, 'nal', 'Дог.№25'), (4, 'Сидоров С.С.', '2013-01-13 16:15:52', 3000, 'nal', 'Аренда'), (5, 'Петров П.П.', '2013-01-13 16:14:22', 200, 'nal', 'Связь'), (6, 'Иванов И.И.', '2013-01-13 17:10:42', 10000, 'nal', 'Инкасация'), (7, 'Петров П.П.', '2013-01-13 16:15:01', 4000, 'beznal', 'Дог.№25'), (8, 'Петров П.П.', '2013-01-05 20:00:00', 3000, 'nal', 'другое'), (9, 'Иванов И.И.', '2013-01-13 20:00:00', 2000, 'nal', 'другое'), (10, 'Петров П.П.', '2013-01-08 20:00:00', 10000, 'beznal', 'Дог.№24'), (11, 'Петров П.П.', '2013-01-06 20:00:00', 100, 'nal', 'Связь')

Необходимо вывести отчет по расходам за текущую неделю (сегодня 13 января 2013 г.) в виде:

date   |     user     |   descr   |  nal  | beznal ---------------------------------------------------- 07.01.13 | Петров П.П.  | Связь     | 100   | 09.01.13 | Петров П.П.  | Дог.№24   |       | 10000 13.01.13 | Иванов И.И.  | Инкасация | 45000 |   13.01.13 | Иванов И.И.  | Связь     | 100   | 13.01.13 | Петров П.П.  | Дог.№25   | 5000  | 4000 13.01.13 | Сидоров С.С. | Аренда    | 3000  |  13.01.13 | Петров П.П.  | Связь     | 200   | 13.01.13 | Иванов И.И.  | Инкасация | 10000 |

Комментарий: расходы группируются в одну строку только если это расходы по оплате одного и того же договора.

Требования:
- решить на уровне сервера, т.е. без PHP;
- решить одним запросом, без процедур и т.п.;
- ключевое слово SELECT встречается в запросе только 1 раз;
- нулевые суммы не выводить.
- запрос должен работать не с 07.01.2013 по 13.01.2013, а ДЛЯ ТЕКУЩЕЙ НЕДЕЛИ с понедельника по пятницу. Т.е. если на сервере выставить дату на 13.01.2013, то результат совпадет с представленным.

=)

Комментарий: расходы группируются в одну строку только если это расходы по оплате одного и того же договора
и где это отображено в таблице?
Дог.№25 группируется, а Инкасация или Связь нет?

Да, именно. И, если по одному договору есть несколько платежей одного типа (хотя тут нет таких данных), то они суммируются. Забыл сказать.

пардон, а почему в результате нет 9 и 11 строки?

Хороший вопрос..... Пока не понял.... Пойму - отпишусь. Должны быть!

пардон, а почему в результате нет 9 и 11 строки?rnrnладно, про 11 я загнул, обшибся. но девятая то??
глючит чтото

Понял.
Потому что я Вам не правильный дамп дал.
При экспорте в PMA поставил галочку "Сохранять поля с типом TIMESTAMP в UTC", а управляющую строку

SET time_zone = "+00:00";

из кода не скопировал.

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

INSERT INTO `charge` (`id`, `user`, `date`, `summ`, `type`, `descr`) VALUES (1, 'Иванов И.И.', '2013-01-13 00:55:48', 45000, 'nal', 'Инкасация'), (2, 'Иванов И.И.', '2013-01-13 20:13:27', 100, 'nal', 'Связь'), (3, 'Петров П.П.', '2013-01-13 20:14:55', 5000, 'nal', 'Дог.№25'), (4, 'Сидоров С.С.', '2013-01-13 20:15:52', 3000, 'nal', 'Аренда'), (5, 'Петров П.П.', '2013-01-13 20:14:22', 200, 'nal', 'Связь'), (6, 'Иванов И.И.', '2013-01-13 21:10:42', 10000, 'nal', 'Инкасация'), (7, 'Петров П.П.', '2013-01-13 20:15:01', 4000, 'beznal', 'Дог.№25'), (8, 'Петров П.П.', '2013-01-06 00:00:00', 3000, 'nal', 'другое'), (9, 'Иванов И.И.', '2013-01-14 00:00:00', 2000, 'nal', 'другое'), (10, 'Петров П.П.', '2013-01-09 00:00:00', 10000, 'beznal', 'Дог.№24'), (11, 'Петров П.П.', '2013-01-07 00:00:00', 100, 'nal', 'Связь');

Пардон!

Кстати, раз нашлись заинтересовавшиеся,

!!! Давайте несколько дней НЕ БУДЕМ ТУТ ВЫКЛАДЫВАТЬ ОТВЕТЫ, чтобы не лишать других возможности решить самостоятельно !!!

Присылайте мне ответы по ссылке "Письмо автору", а я тут буду отписываться у кого верно, у кого - нет.

а приз будет?

Ага.... Медальку нарисую =)

Если б мне нужно было решить, то может и приз был бы.
Но все было иначе.
Я по работе столкнулся с этой задачей (правда там по 3ем таблицам выборка была, но я оставил только суть), решил и запрос получился довольно интересный.
Подумал что и другим может быть интересно поупражняться.
Так что не хотите - не решайте =)

:(

Коллеги, как дела с задачей обстоят ?

Сегодня уже 16 число, а по условиям задачи - в результате выборки должна быть одна запись.

Я не буду писать личные письма Sfinks - потому что есть личные обиды, но да Бог с ним,я не гордый, да и сообщений от него нет - а значит нет ответов, да и времени прошло.

SELECT DATE_FORMAT(date, '%d.%m.%Y') AS date_o,        user AS user_o,        descr AS desrc_o,        IFNULL(SUM(IF(type = "nal", summ, NULL)), "") AS nal_o,        IFNULL(SUM(IF(type = "beznal", summ, NULL)), "") AS beznal_o   FROM charge GROUP BY date_o, descr, user HAVING STR_TO_DATE(date_o, '%d.%m.%Y') > DATE_SUB(CURDATE(), INTERVAL (DAYOFWEEK(CURDATE()) -1) DAY)   AND STR_TO_DATE(date_o, '%d.%m.%Y') < DATE_ADD(CURDATE(), INTERVAL (9 - DAYOFWEEK(CURDATE())) DAY)   ORDER BY STR_TO_DATE(date_o, '%d.%m.%Y'),user_o;

Коллеги, как дела с задачей обстоят ?
пока руки не доходят, так что голова отдыхает
а по условиям задачи - в результате выборки должна быть одна запись.
нет там такого условия
есть таблица и есть результат выборки, ваш результат совпадает с ТЗ?
Но даже если совпадает, честно скажу меня смущает HAVING после GROUP BY.
Мне кажется после того как GROUP BY сделает свое дело, уже поздно отсеивать значения по датам.

Я так напишу, Valick вы читаете задание внимательно, а потом отписываетесь, угу ?

По-поводу вашего смущения вообще не боком, где же ему быть ? Неужели хотите WHERE применить до GROUP BY - читаете матчать, как сказал бы Sfinks.

Сегодня 16.01.2013, а выборка у нас происходит за неделю, так сколько записей должно быть ?
Данные исходные смотрели вообще ? Или у вас неделя начинает не с понедельника ?


Так проверьте и посмотрите совпадает или нет, право гадать не нужно.


Итог один: вы не понимаете как выполняются запросы, что очень плохо, одна ваша фраза только о том что вас смущает - меня навела на эту мысль, не обижаетесь.

Сегодня 16.01.2013
нет, сегодня 13.01.2013

Так смысл этого запроса тогда в чем ? Чтобы выводить данные за неделю от 13.01.2013 ?
Вот те раз.

это сразу описано в задании, и данные даны с расчетом на ту неделю
HAVING используется для обработки результата запроса, и в данном случае GROUP BY нагруппирует вам все что есть в таблице хоть за 10 лет

Скажем так, я вас не правильно понял, потому как упражнялся с HAVING и не поменял я его на WHERE - здесь да, ошибка - критика уместна.

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

Окончательный вариант запроса:

SELECT DATE_FORMAT(date, '%d.%m.%Y') AS date_o,         user AS user_o,         descr AS desrc_o,         IFNULL(SUM(IF(type = "nal", summ, NULL)), "") AS nal_o,         IFNULL(SUM(IF(type = "beznal", summ, NULL)), "") AS beznal_o    FROM charge  WHERE  date > DATE_SUB(CURDATE(), INTERVAL (DAYOFWEEK(CURDATE()) -1) DAY)   AND date < DATE_ADD(CURDATE(), INTERVAL (9 - DAYOFWEEK(CURDATE())) DAY) GROUP BY date_o, descr, user   ORDER BY STR_TO_DATE(date_o, '%d.%m.%Y'),user_o;

А почему с ошибками ?

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

Да и в выборке никакая ни одна запись, там ведь тип TIMESTAMP - Sfinks закрутил круто конечно.

А почему с ошибками ?
ваш запрос учитывает комментарий?

Комментарий: расходы группируются в одну строку только если это расходы по оплате одного и того же договора и где это отображено в таблице? Дог.№25 группируется, а Инкасация или Связь нет?

>А почему с ошибками ?
>ваш запрос учитывает комментарий?
>

Комментарий: расходы группируются в одну строку только если это расходы по оплате одного и того же договора >и где это отображено в таблице? >Дог.№25 группируется, а Инкасация или Связь нет?

Я этот пункт понял так, что группируются расходы по одинаковым статьям (по Связи, Договору) и т.д. Если бы был автор пояснил. Если так как вы говорите - я тратить время на составление этого запроса более не буду, потому что нормализацией здесь не пахнет в принципе.

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

Но в любом случаи для тренировки такие запросы нужны
да кто ж спорит, как будет мне совсем нечем заняться, попробую решить поставленную задачу.

Попробуете - о результатах сообщите.

> Я этот пункт понял так, что группируются расходы по одинаковым статьям (по Связи, Договору) и т.д.
Вы не правильно поняли. Правильно понял Valick.
Группируются ТОЛЬКО договора и ТОЛЬКО с одинаковым номером.

кстати договора с одинаковым номером группируются с учетом даты или все до кучи за неделю?

С учетом пользователя. Дату в пределах недели можно не учитывать.

один и тот же договор может принадлежать разным пользователям?

Такого ограничения нет, поэтому - да, вполне.

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

я имел ввиду надо ли группировать разных пользователей по одному договору
ответ - нет
___
в принципе задача ясна, буду думать

Valick, выкладывать решение, или Вы еще думаете?

Я еще даже не приступал :) и неизвестно когда приступлю. Да и не факт что додумаюсь, иначе уже бы додумался.
Можете выкладывать.

Sfinks если к концу недели у меня также не наступит вдохновение - будет интересным посмотреть ваш запрос, мой запрос написан на коленках и в попыхах, это я признаю и каюсь.

Все-таки извините, за грубость, цель конечно не с вами поспорить.

Могу немного подсказать. В задаче 3 интересных момента:
1. (Вы разгадали) - это MySQL-аналог стандартной операции Oracle и MS SQL - PIVOT.
2. Хитрая группировка
3. (появилась в ходе дискуссии) - универсальное определение даты начала (понедельника) и конца (воскресенья) недели не зависящее от настроек сервера.

P.S.
Честно признаюсь - Вы меня удивили последним абзацем. Я уж думал что все это троллинг.
Извинения принимаются.
Я понятия не имею, чем и когда я обидел Вас, но если это действительно так - примите и мои извинения.
И давайте уже закончим эти детские, никому не интересные перепалки.
Я никогда не утверждаю, что мое мнение - есть истина в последней инстанции и является единственно правильным. Тем более в программировании в целом и в SQL в частности всегда бывает несколько ПРАВИЛЬНЫХ решений. Следовательно всем будет интереснее, прочитать фразу "А еще можно сделать так ...", чем "А с чего это вы решили ....".

Ругаться не будем, конечно.

Хорошо, я буду думать также.

Подсказка по пивот, понял - буду кумекать во вдохновении,а с having я протупил конечно здорово, да еще и валика оскорбил - не хорошо получилось.

Конечно вариантов масса может быть, более я с вами спорить не буду.

Это преднамеренная денормализация. В таблице договоров - их тысячи. А в таблице расходов дублируются только возвраты по расторгнутым договорам.

> читаете матчать, как сказал бы Sfinks.
Будьте добры, говорите за себя. Особенно, когда говорите ересь - не нужно прикрываться моим именем.

Или у вас неделя начинает не с понедельника ?
У кого как, а у MySQL неделя начинается с воскресенья. И Ваши оба запроса этого не учитывают.

У кого как, а у MySQL неделя начинается с воскресенья
это еще почему?
у MySQL неделя начинается когда я ей скажу...

Возможно.... По крайней мере во всех норсальных СУБД так и есть..... Но как???

Я не нашел.

Больше того, в мане, в описании функций нет ни одного отсыла к настройкам локали и т.п. Все значения прописаны строго:

DAYOFWEEK(date)  Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday).   DATE_FORMAT(date,format) с форматом: %w  Day of the week (0=Sunday..6=Saturday) WEEKDAY(date)  Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

Плохо искал?

WEEKDAY(date) - это уже понедельник
а при содействии царицы наук, я могу указать любое смещение

SELECT *      FROM charge     WHERE `date`          BETWEEN  DATE_SUB("2013-01-13 00:00:00", INTERVAL WEEKDAY("2013-01-13 00:00:00") DAY)             AND              DATE_ADD("2013-01-13 23:59:59", INTERVAL (6 - WEEKDAY("2013-01-13 23:59:59")) DAY)

Это да.
Я имею ввиду, что я не нашел в MySQL как определить какой день недели является первым на уровне настроек сервера. Например в MS SQL это точно можно задать настройками сервера. Подчеркну: Я не говорю, что это не возможно, я говорю, что не нашел. Особо не упирался. Скажете как - буду признателен. Я свой запрос составлял исходя из настроек по умолчанию.

Так вот тут получается, что первый день недели зависит от того, какую функцию выберешь.

> Сегодня уже 16 число, а по условиям задачи - в результате выборки должна быть одна запись.
Сегодня и всегда 13.01.2013 =)
А то Вы через неделю скажете, что верен запрос:

SELECT 1 FROM charge WHERE 0

, т.к. он тоже не возвращает ни одной строки.
Я ж не буду писать данные для всего календаря.
А для недели с 07 по 13 я составил данные, которые учитывают некоторые неочевидные подвохи.
Т.е. перед проверкой запроса нужно выставить дату сервера на 13.01.13

Так что то, что мой запрос это не учитывает, наводит на мысль! Как же не учитывает:

date > DATE_SUB(CURDATE(), INTERVAL (DAYOFWEEK(CURDATE()) -1) DAY)    AND date < DATE_ADD(CURDATE(), INTERVAL (9 - DAYOFWEEK(CURDATE())) DAY)

ваш запрос не учитывает 2013-01-07 00:00:00, но это не сложно поправить

> Обещаю запрос не будет сложным. Только это вряд ли струсите и все
Наверняка так и будет. Но вы все же попробуйте.

Остальное комментировать не буду. Это похоже на какую-то истерику.

Строго говоря, я и не вникал в ваш запрос. Я просто поставил 13.01.13 и запустил его на исполнение. Он мне выдал результат за период с 13го по 19ое.

Вы хотите чтобы я после этого сказал что все верно?

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

select  DATE_SUB("2013-01-13 00:00:00", INTERVAL WEEKDAY("2013-01-13 00:00:00") DAY)

2013-01-07 00:00:00

Ну в общем мое решение такое:

SELECT date_format( max(`date`), '%d.%m.%Y' )`date`      , `user`      , `descr`      , coalesce(sum(if( `type`='nal', `summ`, null )),'')`nal`      , coalesce(sum(if( `type`='beznal', `summ`, null )),'')`beznal` FROM `charge` WHERE `date` BETWEEN concat( CURDATE() -INTERVAL datediff( CURDATE(), '19700105' )%7 DAY, ' 00:00:00' )                  AND concat( CURDATE() +INTERVAL 6-datediff( CURDATE(), '19700105' )%7 DAY, ' 23:59:59' ) GROUP BY if( substr( `descr`, 1, 5 )='Дог.№', 1, UUID() ), `user`, `descr` ORDER BY `date`

Sfinks зачем в общий доступ елы-палы.....

Теперь буду смотреть ковырять ваш запрос.

Так Вы же сами в воскресенье написали:
"если к концу недели у меня также не наступит вдохновение - будет интересным посмотреть ваш запрос"
Я подумал, что речь о прошедшей неделе. Поторопился =(

Sfinks в результате я бы ваш запрос не сделал, потому что понял его не так, в частности
вы группируете по дате те записи, в которых в поле описание встречается подстрока Дог.№
Я же представлял себе, что группируем записи по дате, у которых номер договора одинаковый.

Что там с датой не смотрел, посмотрю отпишусь.

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

if( substr( `descr`, 1, 5 )='Дог.№', 1, UUID() )

служит для того, чтобы ничего кроме строк начинающихся с "Дог.№" не могло сгруппироваться в принципе. А уже после этого, если один и тот же `user` и `descr`(т.е. в этом случае номер договора одинаковый), то происходит группировка.

Ну еще бы UUID() это конечно круто.

я не дошел до substr, пытался Дог. с помощью LIKE вычленить

у которых номер договора одинаковый
а оно так и группирует

ничего подобного

опять вы спорите :)
запрос выберет для группировки все строки в которых встречается Дог.№
но группировка будет проходить не вся в куче, а именно по номерам договоров, т.е. Дог.№24 ну никак не сгруппируется с Дог.№25

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

GROUP BY 1

Учитывая

SELECT date_format( max(`date`), '%d.%m.%Y' )`date`

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

Какое еще воображаемое поле ?

ну хотите присвойте ему alias например voobrazhaemoe_pole и даже в выборку его можете поместить наверно

SELECT date_format( max(`date`), '%d.%m.%Y' )`date`      , `user`      , `descr`      , coalesce(sum(if( `type`='nal', `summ`, null )),'')`nal`      , coalesce(sum(if( `type`='beznal', `summ`, null )),'')`beznal`      , voobrazhaemoe_pole FROM `charge` WHERE `date` BETWEEN concat( CURDATE() -INTERVAL datediff( CURDATE(), '19700105' )%7 DAY, ' 00:00:00' )                  AND concat( CURDATE() +INTERVAL 6-datediff( CURDATE(), '19700105' )%7 DAY, ' 23:59:59' ) GROUP BY if( substr( `descr`, 1, 5 )='Дог.№', 1, UUID() ) AS voobrazhaemoe_pole, `user`, `descr` ORDER BY `date`

Валик, не буду помещать. Не хочу.

Суть то я понял, но понял только из того, для какой предметной области реализована данная кухня.

Нет ваш запрос не катит, он синтаксически ошибочен.

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

SELECT date_format( max(`date`), '%d.%m.%Y' )`date`       , if( substr( `descr`, 1, 5 )='Дог.№', 1, UUID() ) AS voobrazhaemoe_pole      , `user`       , `descr`       , coalesce(sum(if( `type`='nal', `summ`, null )),'')`nal`       , coalesce(sum(if( `type`='beznal', `summ`, null )),'')`beznal`  FROM `charge`  WHERE `date` BETWEEN concat( CURDATE() -INTERVAL datediff( CURDATE(), '19700105' )%7 DAY, ' 00:00:00' )                   AND concat( CURDATE() +INTERVAL 6-datediff( CURDATE(), '19700105' )%7 DAY, ' 23:59:59' )  ORDER BY `date`

т.е. ДА, вы правы, у всех договоров начинающихся с 'Дог.№' voobrazhaemoe_pole будет =1, но НЕТ, вы не правы, что сгруппируются договора с разными номерами, т.к. группировка происходит по трем полям одно из которых `descr`, а оно у договоров с разными номерами разное.

Понять группировку всегда проще если ее отменить
а еще лучше её отменять поэтапно

GROUP BY if( substr( `descr`, 1, 5 )='Дог.№', 1, UUID() ), `user`, `descr`

GROUP BY if( substr( `descr`, 1, 5 )='Дог.№', 1, UUID() ), `user`

GROUP BY if( substr( `descr`, 1, 5 )='Дог.№', 1, UUID() )

Sfinks & Valick хорош умничать, идея ясна.