Сложный и интересный SQL запрос

greenfoxx

Полезный форум, но к сожалению не нашел подходящего решения. Поэтому прошу помощи в некоторых вопросах. Возможно моя реализация поможет остальным =)В условии есть 3 таблицы `customer`, `sales1` и аналогичная `sales2`.А также входящие данные startDate и endDate, имеющие формат dd.mm.yyyyТаблица `customer`:
+----------------------+--------------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------------------------------------------------+------+-----+---------+----------------+
| customer_id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| gender | enum('female', 'male') | NO | | NULL | |
| firstname | varchar(50) | NO | | NULL | |
| lastname | varchar(50) | NO | | NULL | |
+----------------------+--------------------------------------------------------+------+-----+---------+----------------+
Таблица `sales1`:
+----------------------+--------------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------------------------------------------------+------+-----+---------+----------------+
| sale_id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| customer_id | mediumint(8) unsigned | NO | | NULL | |
| sale_amount | decimal(10,2) | NO | | NULL | |
| sale_date | datetime | NO | | NULL | |
+----------------------+--------------------------------------------------------+------+-----+---------+----------------+
И такая же таблица `sales2`:
+----------------------+--------------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------------------------------------------------+------+-----+---------+----------------+
| sale_id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| customer_id | mediumint(8) unsigned | NO | | NULL | |
| sale_amount | decimal(10,2) | NO | | NULL | |
| sale_date | datetime | NO | | NULL | |
+----------------------+--------------------------------------------------------+------+-----+---------+----------------+
Данные таблицы `customer` такие:
+-------------+--------+-----------+----------+
| customer_id | gender | firstname | lastname |
+-------------+--------+-----------+----------+
| 1 | female | Dagmar | Bosel | 
| 2 | male | Dietmar | Maier | 
| 3 | female | Sabine | Kanter | 
+-------------+--------+-----------+----------+
Данные таблицы `sales1` такие:
+-------------+-------------+-------------+---------------------+
| sale_id | customer_id | sale_amount | sale_date |
+-------------+-------------+-------------+---------------------+
| 1 | 3 | 14.40 | 2007-04-02 11:37:06 | 
| 2 | 1 | 28.30 | 2007-05-14 11:37:18 | 
| 3 | 2 | 34.40 | 2007-05-06 11:38:14 | 
| 4 | 2 | 25.60 | 2007-05-07 11:38:39 | 
+-------------+-------------+-------------+---------------------+
Данные таблицы `sales2` такие:
+-------------+-------------+-------------+---------------------+
| sale_id | customer_id | sale_amount | sale_date |
+-------------+-------------+-------------+---------------------+
| 1 | 2 | 68.20 | 2007-04-06 11:37:06 | 
| 2 | 3 | 21.30 | 2007-04-12 11:37:18 | 
| 3 | 3 | 54.40 | 2007-05-06 11:38:14 | 
| 4 | 1 | 35.70 | 2007-05-07 11:38:39 | 
+-------------+-------------+-------------+---------------------+
Задача: Одним запросом вывести всех клиентов за указанный период startDate и endDate, где startDate и endDate входящие переменные в формате dd.mm.yyyyРезультата вывода должен быть таким:
+----------------------------------+------------------------------------------------------------------------------------------------------------+
| результат | комментарий |
+----------------------------------+------------------------------------------------------------------------------------------------------------+
| customer_id: 2 | |
| customer_name: Mr. Dietmar Maier | То что нужно юзать concat - понятно, но как сделать разрульку Mr./Ms. в sql пока что нз. Может кто знает? |
| sales_count: 3 | Количество покупок в sale1 и sale2 |
| sales_sum: 128,20 | Сумма всех покупок в sale1 и sale2 |
| sales_date: 07.05.2007 | Дата последней покупки |
+----------------------------------+------------------------------------------------------------------------------------------------------------+
Вот мой рабочий тестовый SQL запрос:
SELECT 
	c.customer_id,
	c.gender,
	concat(c.firstname, ' ', c.lastname) AS customer_name,	 
	SUM(sc) AS sales_count,
	SUM(ss) AS sales_sum,	
	DATE_FORMAT(MAX(sd), '%d.%m.%Y') AS sales_date
FROM (
 SELECT 
		COUNT(*) AS sc,
		SUM(sale_amount) AS ss,
		MAX(sale_date) AS sd
	FROM 
		sales1 AS tb1 
	WHERE 
		tb1.customer_id = 2
	AND 
		sale_date BETWEEN '2007-05-05' AND '2007-05-07 23:59:59'
 UNION	
 SELECT 
		COUNT(*) AS sc,
		SUM(sale_amount) AS ss,
		MAX(sale_date) AS sd
	FROM 
		sales2 AS tb2 
	WHERE 
		tb2.customer_id = 2
	AND 
		sale_date BETWEEN '2007-05-05' AND '2007-05-07 23:59:59'
) AS icnt,	
	customer AS c	
WHERE
	c.customer_id = '2'
который выводит следующее:
+-------------+--------+----------------+-------------+-----------+------------+
| customer_id | gender | customer_name | sales_count | sales_sum | sales_date | 
+-------------+--------+----------------+-------------+-----------+------------+
| 2 | male | Dietmar Maier | 2 | 60.00 | 07.05.200 |
+-------------+--------+----------------+-------------+-----------+------------+
Вопросы:1. Как можно упростить(оптимизировать) данный запрос? Возможно ли его как то переделать через JOIN-ы (пробовал, не получилось)?Вот как изначально делал и не вышло:
SELECT 
	c.customer_id,
	concat(c.firstname, ' ', c.lastname) AS customer_name,	
	COUNT(ss1.customer_id) + COUNT(ss2.customer_id) AS sales_count	
FROM 
	customer AS c	
LEFT JOIN 
	sales1 AS ss1 
		ON c.customer_id = ss1.customer_id
LEFT JOIN 
	sales2 AS ss2 
		ON c.customer_id = ss2.customer_id
WHERE
	c.customer_id = '2'
Непонятно мне почему второй COUNT(ss2.customer_id) не верно считает. И я так понимаю он "не видит" второй JOIN что-ли. Есть у кого-то какие то мысли?
2. Как все таки можно сделать проверку(по полю `gender`) и вывести 'Mr.' или 'Ms.' через SQL запрос? Пока что тока в php разрулено.3. В моем примере в BETWEEN я пока что проверяю в нужном MySQL формате(yyyy-mm-dd) тестовые статик данные. Как вариант, в php формировать такой формат и передавать его в SQL запрос. Как правильно это можно реализовать в SQL запросе, если формат входящих дат в SQL dd.mm.yyyy ? Предполагаю что как-то так:
AND DATE_FORMAT(sale_date, '%d.%m.%Y') BETWEEN '05.05.2007' AND '07.05.2007'
но почему то не срабатывает.На всяк, вот SQL для создания таблиц с данными:
CREATE TABLE customer (
customer_id mediumint(8) unsigned NOT NULL auto_increment,
gender enum('female','male','') NOT NULL,
firstname varchar(50) NOT NULL,
lastname varchar(50) NOT NULL,
PRIMARY KEY (customer_id)
);

INSERT INTO customer VALUES (1, 'female', 'Dagmar', 'Bosel');
INSERT INTO customer VALUES (2, 'male', 'Dietmar', 'Maier');
INSERT INTO customer VALUES (3, 'female', 'Sabine', 'Kanter');

CREATE TABLE sales1 (
sale_id mediumint(8) unsigned NOT NULL auto_increment,
customer_id mediumint(8) unsigned NOT NULL,
sale_amount decimal(10,2) NOT NULL,
sale_date datetime NOT NULL,
PRIMARY KEY (sale_id)
);

INSERT INTO sales1 VALUES (1, 3, 14.40, '2007-04-02 11:37:06');
INSERT INTO sales1 VALUES (2, 1, 28.30, '2007-05-14 11:37:18');
INSERT INTO sales1 VALUES (3, 2, 34.40, '2007-05-06 11:38:14');
INSERT INTO sales1 VALUES (4, 2, 25.60, '2007-05-07 11:38:39');

CREATE TABLE sales2 (
sale_id mediumint(8) unsigned NOT NULL auto_increment,
customer_id mediumint(8) unsigned NOT NULL,
sale_amount decimal(10,2) NOT NULL,
sale_date datetime NOT NULL,
PRIMARY KEY (sale_id)
);

INSERT INTO sales2 VALUES (1, 2, 68.20, '2007-04-06 11:37:06');
INSERT INTO sales2 VALUES (2, 3, 21.30, '2007-04-12 11:37:18');
INSERT INTO sales2 VALUES (3, 3, 54.40, '2007-05-06 11:38:14');
INSERT INTO sales2 VALUES (4, 1, 35.70, '2007-05-07 11:38:39');
4 ответа

greenfoxx

3. Для обработки входящих значений необходимо использовать STR_TO_DATE()
MariaDB[test]> select STR_TO_DATE('03.05.2011','%d.%m.%Y') as DT;
+------------+
| DT |
+------------+
| 2011-05-03 |
+------------+
1 row in set (0.00 sec)
2. для Вывода 'Mr.' или 'Ms.' - условные операторы
concat(if(c.gender = 'Male', 'Mr. ', 'Ms. ', c.firstname, ' ', c.lastname) AS customer_name
1. у вас и так JOIN, только недоделанный... да и кто вам сказал, что JOIN - это быстро? Это обычное объединение таблиц, имеющее свои накладные расходы
FROM (...) AS icnt,	
	customer AS c
/* должа быть связь */
WHERE icnt.customer_id=c.customer_id
для этого в агрегирующие подселекты добавьте customer_id
FROM (
 SELECT 	customer_id,
		COUNT(*) AS sc,
		SUM(sale_amount) AS ss,
		MAX(sale_date) AS sd
	FROM 
		sales1 AS tb1 
	WHERE 
		tb1.customer_id = 2
	AND 
		sale_date BETWEEN '2007-05-05' AND '2007-05-07 23:59:59'
 UNION
Вы показали только для Customer_id =2В общем случае нужно использовать GROUP BY customer_id


greenfoxx

Alex_Ustinov, спасибо за помощь. Очень помогло.1. Про JOIN я и не говорил что это быстрее. Просто любопытно посмотреть на вариант через JOIN. Сначала делал через JOIN так:
SELECT 
	c.customer_id,
	concat(c.firstname, ' ', c.lastname) AS customer_name,	
	COUNT(ss1.customer_id) + COUNT(ss2.customer_id) AS sales_count	
FROM 
	customer AS c	
LEFT JOIN 
	sales1 AS ss1 
		ON c.customer_id = ss1.customer_id
LEFT JOIN 
	sales2 AS ss2 
		ON c.customer_id = ss2.customer_id
WHERE
	c.customer_id = '2'
но как и писал выше, то почему то второй COUNT(ss2.customer_id) не работает если есть ниже 2-ой JOINВ поле sales_count выводится 4 почему-то а должно быть 3. Если оставить один JOIN и один COUNT, то по отдельности все правильно считает. Для меня пока что остается вопросом. Может подскажешь почему так?----Добавил связь и группировку:
FROM (
...
)
AS icnt,	
	customer AS c	
WHERE
	c.customer_id = '2'
 /* добавил */
AND 
	icnt.customer_id = c.customer_id
GROUP BY 
	c.customer_id
и добавил в подзапросе везде агрегирующие подселекты customer_id.Но MySQL ругается:Column 'customer_id' cannot be nullПробовал добавить в подзапросе везде так:
AND
		customer_id IS NOT NULL
не помогло =(2. Спасибо, подозревал что конструкцию IF нужно использовать, но вычитал что она используется в хранимых процедурах и функциях и усомнился что не подойдет.3. С датами разобрался. спс.Только вот сомневаюсь в использовании 23:59:59:
AND 
	sale_date BETWEEN STR_TO_DATE('05.05.2007', '%d.%m.%Y') AND STR_TO_DATE('07.05.2007 23:59:59', '%d.%m.%Y %H:%i:%s')
Мне нужно включительно, поэтому я так и делаю. Есть ли какой-то другой способ выбрать включительно.СПС за помощь.


greenfoxx

SELECT 
	c.customer_id,
	c.gender,
	CONCAT(IF(c.gender = 'Male', 'Mr. ','Ms. '),c.firstname, ' ', c.lastname) AS customer_name,	 
	SUM(sc) AS sales_count,
	SUM(ss) AS sales_sum,	
	DATE_FORMAT(MAX(sd), '%d.%m.%Y') AS sales_date
FROM (
 SELECT 
		`customer_id`,
		COUNT(*) AS sc,
		SUM(sale_amount) AS ss,
		MAX(sale_date) AS sd
	FROM 
		sales1 AS tb1 
	WHERE
/* так как 2007-05-07 => 2007-05-07 00:00:00 а BETWEEN это >= b <= то добавим время*/ 
		sale_date BETWEEN STR_TO_DATE('2007-05-05 00:00:00','%Y-%m-%d %H:%i:%s') AND 
		STR_TO_DATE('2007-05-07 23:59:59', '%Y-%m-%d %H:%i:%s')
	<span>GROUP</span> <span>BY</span> `customer_id`
 <span>UNION</span> <span>ALL</span>
 SELECT 
		`customer_id`,
		COUNT(*) AS sc,
		SUM(sale_amount) AS ss,
		MAX(sale_date) AS sd
	FROM 
		sales2 AS tb2 
	WHERE 
		sale_date BETWEEN STR_TO_DATE('2007-05-05 00:00:00','%Y-%m-%d %H:%i:%s') AND 
		STR_TO_DATE('2007-05-07 23:59:59', '%Y-%m-%d %H:%i:%s')
	<span>GROUP</span> <span>BY</span> `customer_id`
) AS icnt,	
	customer AS c	
WHERE
	c.customer_id = icnt.customer_id
GROUP BY `customer_id`
дальше сами


greenfoxx

Alex_Ustinov, спасибо. Именно так я и реализовал. Единственное отличие: я писал просто UNION.