Задачка на написание запроса к БД

Есть у меня в реальной работе задача. Уровень "продвинутый", лишних прошу не беспокоить.
Некое решение я сделал, но чувствую, что есть другие достойные способы. Чтобы не подталкивать вас к определенному ходу мыслей, своё я покажу позже.

Дано:
Таблица "событий". Пускай это извещения о совершенных заказах.

Код (Text):

1. CREATE TABLE `events` (
2.   `id` int(10) NOT NULL AUTO_INCREMENT,
3.   `event_time` DATETIME NOT NULL,
4.   `event_type` enum('order','pay') NOT NULL DEFAULT 'order',
5.   `order_no` varchar(10) NOT NULL DEFAULT '',
6.   `order_sum` decimal(10,2) NOT NULL DEFAULT '0',
7.   PRIMARY KEY (`id`),
8.   KEY (`order_no`),
9.   KEY (`event_type`,`event_time`)
10. )

Любой заказ имеет одну запись типа 'order' и любое количество, от 0 до N записей 'pay'. Мы можем сгруппировать записи одного заказа по order_no, поле уникально указывает на заказ. Поле event_time у разных записей одного заказа будет разное, оно соответствует моменту оформления либо оплаты. Поле order_sum содержит 0 в записях типа order и не 0 в записях типа pay.

Требуется:
Создать выборку за определенный период записей вида

Код (Text):

1. День, когда был оформлен заказ | Количество созданных| Количество оплаченных

если в какой-то день не было заказов, строчка должна быть, только с нулями. Если оплата произошла не в день оформления, а позже — данные всё равно относятся ко дню оформления.

Умеренное участие PHP допустимо. Хорошее решение — только средствами SQL.

 

это не таблица заказов, это "события", ок? от таблицы требуется то, что я описал, не больше. я не прошу придумывать другие задачи.

 

заказ считается оплаченным если по нему была оплата(ы) на любую сумму. для зануд: бывают только положительные суммы оплаты )))

Добавлено спустя 3 минуты 28 секунд:
"количество созданных" это количество уникальных order_no
"количество оплаченных" это количество уникальных order_no, по которым был хотябы один факт оплаты.

 

набросал несколько игровых записей: http://sqlfiddle.com/#!2/8c80da

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

 

runcore, ты мог воспользоваться моей ссылкой на sqlfiddle. извини, но я не буду пробовать твой код. 20 штук union ?…

romach, выглядит очень правдоподобно. цифры кажется верные, НО!
1)

Код (Text):

1. SELECT `event_time`, `order_no`, ...  GROUP BY `order_no`

работают обманчиво. откуда будет взят event_time ? это очень хрупкий код, непредсказуемый.
2) вот это не учитывается:

3) IMHO, с count(if()...) перебор. можно проще

 

а это можно адекватно перевести на mysql?

Добавлено спустя 1 минуту 46 секунд:
кто убил sqlfiddle, признавайтесь! сперва был недоступен, а сейчас вижу джавовский бэктрейс. катастрофа блин.

 

1. точно. я так и делаю
2. можно изготовить последовательность дат. я даже подсказывал здесь недавно
3. ага. красота!

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

ладно, вот промежуточный результат без генератора дат - для сравнения: http://sqlfiddle.com/#!2/da9fed/13
генератор вместо первого вложенного запроса ставится.

 

один из вариантов:
viewtopic.php?f=20&t=47888&p=380576#p380485
как исходник нужна последовательность чисел. я для отчетов держу такую от 0 до 9999, очень полезная штука

можно и по другому, на переменной mysql как на счетчике — c любой базовой последовательностью, например можно служебную таблицу mysql взять достаточно большую

Добавлено спустя 2 минуты 16 секунд:
p.s. тут важнее красота, я считаю. если в отчете до сотни тысяч записей вообще не надо париться про скорость. это не то, что при каждом заходе пользователя генериться, тут допустимо пару сотых секунды подождать.

 

спасибо! запрос работает, хотя план выполнения ужасен. по поводу "красоты" — он слишком уж в лоб сделан. для разового решения это нормально, а в постоянную работу я такие штуки не ставлю. это моё сугубое мнение.

 

Ну видимо все желающие и могущие уже поучаствовали Подвожу итоги.
Вариант romach мне понравился. По сути там та же техника, что и у меня — используется свойство функции COUNT(col) считать только не NULL значения col. Нет второй вложенной таблицы, но она появится если все-таки сделать "пустые даты". В моем варианте подготовка к этому уже есть. Вот и вся разница по сути.
В варианте runcore вместо COUNT() есть SUM(). Так как суммируются единички, то результат аналогичен. Тоже вариант, почему бы нет. Все справились, в общем )

Завершаю квест полным решением. Один из вариантов генерации последовательности дат я нашел на stackoverflow: date range: no loops, procedures, or temp tables.
Элегантно! Точно также можно генерировать любые числовые или временны́е последовательности: (start + i * step). Работает это быстро и не надо всякий раз создавать новую таблицу. Я у себя создал вьюшку как источник чисел, вот и на sqlfiddle сделаю через вьюшку.

Код (Text):

1. SELECT
2.   m.`date`,
3.   COUNT(e.`event_time`) AS `ordered`,
4.   COUNT(e.p) AS `paid`
5. FROM
6.   (
7.     SELECT (DATE('2014-03-03') + INTERVAL `number` DAY) AS `date`
8.     FROM `numbers`
9.     WHERE `number` < 14
10.   ) AS m LEFT JOIN
11.   (
12.      SELECT
13.        IF(SUM(`order_sum`) > 0, 1, null) AS p,
14.        MIN(`event_time`) AS `event_time`
15.      FROM `events`
16.      GROUP BY `order_no`
17.    ) AS e ON e.`event_time` BETWEEN m.`date` AND TIMESTAMP(m.`date`, '23:59:59')
18. GROUP BY 1

http://sqlfiddle.com/#!2/7a84e/5