SQLinfo.ru - Все о MySQL Highload++ 2017

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 08.10.2017 18:13:11

jron
Завсегдатай
Зарегистрирован: 03.10.2017
Сообщений: 25

Вывод некорректной даты при запросе.

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

Делаю по частям, чтобы потом сделать вложенные запросы.

1.    Подсчитаем суммы за каждый день, указанный в таблице

mysql> select * from (SELECT paydate, SUM(AMOUNT) AS sumAmount FROM payments GROUP BY paydate) as d;
+---------------------+-----------+
| paydate             | sumAmount |
+---------------------+-----------+
| 2014-11-30 11:25:00 |  20001.00 |
| 2015-11-30 10:25:00 |  15000.50 |
| 2016-10-30 15:00:00 |  35000.50 |
| 2017-09-29 10:00:00 | 373605.50 |
| 2017-11-30 15:25:00 |  25000.50 |
+---------------------+-----------+
5 rows in set (0.00 sec)


2. Отфильтруем, и выберем только запись где сумма платежей наибольшая


mysql> select max(sumamount) from (SELECT paydate, SUM(AMOUNT) AS sumAmount FROM payments GROUP BY paydate) as d;
+----------------+
| max(sumamount) |
+----------------+
|      373605.50 |
+----------------+
1 row in set (0.00 sec)


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


mysql> select paydate,max(sumamount)
from (SELECT paydate, SUM(AMOUNT) AS sumAmount FROM payments GROUP BY paydate) as d;
+---------------------+----------------+
| paydate             | max(sumamount) |
+---------------------+----------------+
| 2014-11-30 11:25:00 |      373605.50 |
+---------------------+----------------+
1 row in set (0.00 sec)


Дальше соответственно не знаю что делать ???

Пробовал так но получил ошибку.

SELECT paydate FROM payments WHERE amount in (SELECT paydate, SUM(AMOUNT) FROM payments GROUP BY paydate);


Ну а здесь почему не работает

ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> SELECT paydate, SUM(AMOUNT) as sumAmount  FROM payments GROUP BY paydate;
+---------------------+-----------+
| paydate             | sumAmount |
+---------------------+-----------+
| 2014-11-30 11:25:00 |  20001.00 |
| 2015-11-30 10:25:00 |  15000.50 |
| 2016-10-30 15:00:00 |  35000.50 |
| 2017-09-29 10:00:00 | 373605.50 |
| 2017-11-30 15:25:00 |  25000.50 |
+---------------------+-----------+
5 rows in set (0.00 sec)

mysql>
mysql> select paydate, max(amount) from payments where paydate =
    -> (SELECT paydate, SUM(AMOUNT) as sumAmount  FROM payments GROUP BY paydate) ;
ERROR 1241 (21000): Operand should contain 1 column(s)

Отредактированно jron (08.10.2017 18:19:57)

Неактивен

 

#2 08.10.2017 18:21:12

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5269

Re: Вывод некорректной даты при запросе.

почему день не правильный см http://sqlinfo.ru/articles/info/18.html

Неактивен

 

#3 08.10.2017 18:22:06

jron
Завсегдатай
Зарегистрирован: 03.10.2017
Сообщений: 25

Re: Вывод некорректной даты при запросе.

Заменил оператор  =   на оператор IN , который работает если выводится несколько строк, но опять ошибка

mysql> select paydate, max(amount) from payments where paydate in
    -> (SELECT paydate, SUM(AMOUNT) as sumAmount  FROM payments GROUP BY paydate) ;
ERROR 1241 (21000): Operand should contain 1 column(s)

Неактивен

 

#4 08.10.2017 18:38:46

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5269

Re: Вывод некорректной даты при запросе.

потому, что вы одно значение paydate пытаетесь сравнить со строкой из двух значений (paydate, SUM(AMOUNT))

Неактивен

 

#5 08.10.2017 21:14:17

jron
Завсегдатай
Зарегистрирован: 03.10.2017
Сообщений: 25

Re: Вывод некорректной даты при запросе.

Но ведь по логике

SELECT paydate, SUM(AMOUNT)  FROM payments GROUP BY paydate


Эта команда выдает мне суммы платежей за каждый указанный день в таблице;

Если where нет в состоянии справится с таким вложенным запросом

SELECT paydate, SUM(AMOUNT)  FROM payments GROUP BY paydate
,
тогда  я попробывал вынести этот запрос во from и запутался...

SELECT paydate, `SUM(AMOUNT)` FROM (SELECT paydate, SUM(AMOUNT) FROM payments GROUP BY paydate) as d


Подскажите пожайлуста, как найти выход, не обязательно готовое решение, я вроде понимаю алгоритм как оно должно из базы доставаться, но семантика sql запроса в mysql  пока недоступна для моего понимания.
  На данный момент по курсу знаю только
Select, from, where,in, group by, агрегатные функции и немного вложенные запросы

На работе Where  внимание не заостряли и его использовал просто по примерам...
Помогите понять как состатвить алгоритм работы запроса для моей задачи, с использованием вот данных моих знаний

Неактивен

 

#6 08.10.2017 21:42:07

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5269

Re: Вывод некорректной даты при запросе.

В указанной мной статье приводится 5 способов решения, рекомендую.

По исходной задаче ещё нужно уточнить, что делать, если максимальная сумма платежей была в течении нескольких дней.

Неактивен

 

#7 09.10.2017 16:22:09

jron
Завсегдатай
Зарегистрирован: 03.10.2017
Сообщений: 25

Re: Вывод некорректной даты при запросе.

Рассуждения согласно статьи - 1-й способ

...как оказалось, есть правила синтаксиса языка SQL , которые не допускают выполнения запроса если в GROUP BY  указано одно поле, по которому нужно группировать полученные данные, а в запросе SELECT - указывается больше полей по которым производится выборка.

MySQL допускает выполнение запросов, не удовлетворяющих данному правилу.
 При этом для полей без групповых функций и не указанных в части
GROUP BY выбирается произвольная строка из группы.

 


Когда я включил строгий режим
set @@sql_mode='only_full_group_by';
то запрос
SELECT paydate, max(sum) FROM (SELECT paydate, SUM(AMOUNT) as sum FROM payments GROUP BY paydate) as d ;


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

А вот алгоритм решения задачи 1-м способом, как я его понимаю (правильно ли ?)

SELECT t.`user_id`, t.`post`, t.`time`
FROM `test_table` t JOIN
(SELECT `user_id`, max(`time`) as `mtime` FROM `test_table` GROUP BY `user_id`) as t1
ON t.`user_id`= t1.`user_id` AND t.`time` = t1.`mtime`;
 


1. Вначале выполняется сложный вложенный запрос, так как нужно сразу объединить несколько данных в одном запросе.
Оператор JOIN в запросе объединяет полученные данные и передает их в основной запрос.
.......JOIN (SELECT `user_id`, max(`time`) as `mtime` FROM `test_table` GROUP BY `user_id`) as t1
ON t.`user_id`= t1.`user_id` AND t.`time` = t1.`mtime`;
 


2.    Вложенный запрос получает псевдоним t1
(SELECT `user_id`, max(`time`) as `mtime` FROM `test_table` GROUP BY `user_id`) as t1


Здесь нужно выбрать поля
`user_id`, max(`time`)  из таблицы `test_table`
, при этом из поля `time`  нужно выбрать максимальную величину
max(`time`)
и полученные записи группировать по полю
`user_id`


max(`time`)
  получает псевдоним
`mtime`


3.    Затем основной запрос на основе полученных данных из вложенного запроса, производит выборку данных из полей
t.`user_id`, t.`post`, t.`time`


При этом фильтр-условие ON смотрит, чтобы одновременно были выведены и приведены к нужным значениям 2 условия

t.`user_id`= t1.`user_id` AND t.`time` = t1.`mtime`


t.`user_id`= t1.`user_id`
- здесь в поле
t.`user_id`
основного запроса, подставиться значение полученное из вложенного запроса

t.`time` = t1.`mtime`
- здесь в поле
t.`time`
основного запроса, подставиться значение полученное из вложенного запроса, с уже вычисленным максимальным значением в поле
`time`

Неактивен

 

#8 09.10.2017 16:39:43

jron
Завсегдатай
Зарегистрирован: 03.10.2017
Сообщений: 25

Re: Вывод некорректной даты при запросе.

Попробовал приспособить данный способ в моем случае

mysql> SELECT p.paydate, p.AMOUNT
    -> FROM payments p
    -> JOIN (SELECT paydate, SUM(AMOUNT) as sumAmount FROM payments GROUP BY paydate) as d
    -> ON p.paydate  =  d.paydate and p.AMOUNT = (select max(d.sumAmount) from d) ;
ERROR 1146 (42S02): Table 'sale.d' doesn't exist
mysql>


А что здесь не так?

Неактивен

 

#9 09.10.2017 16:43:09

jron
Завсегдатай
Зарегистрирован: 03.10.2017
Сообщений: 25

Re: Вывод некорректной даты при запросе.

vasya написал:

По исходной задаче ещё нужно уточнить, что делать, если максимальная сумма платежей была в течении нескольких дней.

Этого не требуется так как полностью одинаковых таких записей я думаю в реальной практике будет - 1 на 1 000 000.
Хотя возможно.
таблицу учебную я сам составлял, а в условии только четко обозначено так

выводить наибольший платеж за тот день, когда сумма платежей была наибольшей

более сложный вариант от нас не требуется пока что.

Отредактированно jron (09.10.2017 16:43:40)

Неактивен

 

#10 09.10.2017 17:11:20

jron
Завсегдатай
Зарегистрирован: 03.10.2017
Сообщений: 25

Re: Вывод некорректной даты при запросе.

Кроме того я просмотрел способы, но дальше сложнее, а требуется решить задачу из имеющихся знаний

jron написал:

На данный момент по курсу знаю только
Select, from, where,in, group by, агрегатные функции и немного вложенные запросы

На работе Where  внимание не заостряли и его использовал просто по примерам...

Неактивен

 

#11 09.10.2017 17:14:35

jron
Завсегдатай
Зарегистрирован: 03.10.2017
Сообщений: 25

Re: Вывод некорректной даты при запросе.

Первую часть задачи

1.    Создайте запрос который будет
выводить сумму платежей за тот день, когда был сделан наибольший платеж


Я решил вот так

SELECT paydate,sum(amount) as `максимальный платеж`
FROM payments WHERE paydate
 IN (SELECT paydate FROM payments  WHERE amount = (SELECT MAX(amount) FROM payments ));
 


А 2-я часть не дается моему пониманию

Неактивен

 

#12 09.10.2017 17:46:49

jron
Завсегдатай
Зарегистрирован: 03.10.2017
Сообщений: 25

Re: Вывод некорректной даты при запросе.

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

mysql> SELECT p.`paydate`, p.`amount`  FROM  payments p  JOIN (SELECT `paydate`, SUM(`AMOUNT`) as `sum1` FROM payments GROUP BY `paydate`) as d
    -> ON p.`paydate` = d.`paydate` and p.`amount` = max(d.`sum1`);
ERROR 1111 (HY000): Invalid use of group function

Отредактированно jron (09.10.2017 17:52:01)

Неактивен

 

#13 09.10.2017 17:53:18

jron
Завсегдатай
Зарегистрирован: 03.10.2017
Сообщений: 25

Re: Вывод некорректной даты при запросе.

То есть агрегатный запрос не может быть так использован?
А как тогда ?

Неактивен

 

#14 09.10.2017 20:42:16

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5269

Re: Вывод некорректной даты при запросе.

jron написал:

vasya написал:

По исходной задаче ещё нужно уточнить, что делать, если максимальная сумма платежей была в течении нескольких дней.

Этого не требуется ...

тогда просто

SELECT paydate, SUM(AMOUNT) AS sumAmount FROM payments GROUP BY paydate order by 2 desc limit 1;


jron написал:

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

сравните

t.`user_id`= t1.`user_id` AND t.`time` = t1.`mtime`

и
p.paydate  =  d.paydate and p.AMOUNT = (select max(d.sumAmount) from d) ;

или
p.`paydate` = d.`paydate` and p.`amount` = max(d.`sum1`);

Неактивен

 

#15 09.10.2017 20:52:55

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5269

Re: Вывод некорректной даты при запросе.

jron написал:

А вот алгоритм решения задачи 1-м способом, как я его понимаю (правильно ли ?)

SELECT t.`user_id`, t.`post`, t.`time`
FROM `test_table` t JOIN
(SELECT `user_id`, max(`time`) as `mtime` FROM `test_table` GROUP BY `user_id`) as t1
ON t.`user_id`= t1.`user_id` AND t.`time` = t1.`mtime`;
 

подзапрос формирует временную таблицу t1, в которой содержатся данные полученные в ходе выполнения
SELECT `user_id`, max(`time`) as `mtime` FROM `test_table` GROUP BY `user_id`

JOIN это пересечение множеств (таблиц) `test_table` и  t1 по условию ON t.`user_id`= t1.`user_id` AND t.`time` = t1.`mtime`

подробней см
http://sqlinfo.ru/forum/viewtopic.php?id=3210

Неактивен

 

#16 10.10.2017 15:47:13

jron
Завсегдатай
Зарегистрирован: 03.10.2017
Сообщений: 25

Re: Вывод некорректной даты при запросе.

vasya написал:

сравните

t.`user_id`= t1.`user_id` AND t.`time` = t1.`mtime`

и
p.paydate  =  d.paydate and p.AMOUNT = (select max(d.sumAmount) from d) ;

или
p.`paydate` = d.`paydate` and p.`amount` = max(d.`sum1`);

Да здесь я неправ, нужно было указать псевдоним поля, которое будет формироваться во временной таблице d.

Я исправил, но в этом случае выдается непонятно что

mysql>  SELECT p.`paydate`, p.`AMOUNT`
    ->      FROM payments p
    ->     JOIN (SELECT `paydate`, SUM(AMOUNT) as `sumAmount` FROM payments GROUP BY `paydate`) as d
    ->     ON p.`paydate`  =  d.`paydate` and p.AMOUNT = d.`sumAmount` ;
+---------------------+----------+
| paydate             | AMOUNT   |
+---------------------+----------+
| 2016-10-30 15:00:00 | 35000.50 |
| 2017-11-30 15:25:00 | 25000.50 |
| 2015-11-30 10:25:00 | 15000.50 |
+---------------------+----------+
3 rows in set (0.00 sec)
 

Неактивен

 

#17 10.10.2017 16:20:14

jron
Завсегдатай
Зарегистрирован: 03.10.2017
Сообщений: 25

Re: Вывод некорректной даты при запросе.

Теперь учитывая что я правильно фильтрую нужные мне данные

mysql> SELECT paydate, SUM(AMOUNT) AS sumAmount FROM payments GROUP BY paydate order by 2 desc limit 1;
+---------------------+-----------+
| paydate             | sumAmount |
+---------------------+-----------+
| 2017-09-29 10:00:00 | 423605.50 |


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

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

SELECT d.`paydate`
     FROM  (SELECT paydate, SUM(AMOUNT) AS sumAmount FROM payments GROUP BY paydate order by 2 desc limit 1) as d


3.    Теперь нужно вывести значение максимального платежа за данный день.

mysql> select `paydate`, max(amount) from payments
    -> where `paydate` = (SELECT d.`paydate`
    -> FROM  (SELECT paydate, SUM(AMOUNT) AS sumAmount FROM payments GROUP BY paydate order by 2 desc limit 1) as d);
+---------------------+-------------+
| paydate             | max(amount) |
+---------------------+-------------+
| 2017-09-29 10:00:00 |    85000.50 |
+---------------------+-------------+
1 row in set (0.00 sec)


Задача решена, спасибо.

p.s. А почему вы все имена полей заключаете в обратные кавычки  `` ?

Ну понятно если это русское слово или слово с пробелом, но английские за чем ?

И еще, ксати заметил, что не нужно употреблять слово DISTINCT, mysql сам распознает если выводятся одинаковые строки и показывает одну.
Так например за этот день в таблице 3 таких платежа с одинаковой суммой 85000,
а можно mysql заставить чтобы он показал и стальные одинаковые строки, если таковые имеются ?

Отредактированно jron (10.10.2017 16:26:33)

Неактивен

 

#18 10.10.2017 16:28:10

jron
Завсегдатай
Зарегистрирован: 03.10.2017
Сообщений: 25

Re: Вывод некорректной даты при запросе.

На всякий случай, может кому надо расшифровать

SELECT paydate, SUM(AMOUNT) AS sumAmount FROM payments GROUP BY paydate order by 2 desc limit 1;

 order by 2 desc – результат формируется в таблицу и группируется по полю sumAmount , оно указа-но в запросе, в позиции 2. Результат вывести по убывающей, то есть начиная с максимального зна-чения.
 limit 1 – в окончательный результат вывести только 1-ю строку.
 

Неактивен

 

#19 10.10.2017 19:08:15

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5269

Re: Вывод некорректной даты при запросе.

jron написал:

Я исправил, но в этом случае выдается непонятно что

это я невнимательно прочитал условие задачи

jron написал:

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

SELECT d.`paydate`
     FROM  (SELECT paydate, SUM(AMOUNT) AS sumAmount FROM payments GROUP BY paydate order by 2 desc limit 1) as d

можно без подзапроса

SELECT paydate FROM payments GROUP BY paydate order by SUM(AMOUNT) desc limit 1


jron написал:

p.s. А почему вы все имена полей заключаете в обратные кавычки  `` ?

Ну понятно если это русское слово или слово с пробелом, но английские за чем ?

на случай ключевых слов, например, time в статье.

jron написал:

И еще, ксати заметил, что не нужно употреблять слово DISTINCT, mysql сам распознает если выводятся одинаковые строки и показывает одну.
Так например за этот день в таблице 3 таких платежа с одинаковой суммой 85000,
а можно mysql заставить чтобы он показал и стальные одинаковые строки, если таковые имеются ?

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

select p.paydate, p.amount from payments p join
(select `paydate`, max(amount) ma from payments where `paydate` = (SELECT paydate FROM payments GROUP BY paydate order by SUM(AMOUNT) desc limit 1)) x
on p.paydate = x.paydate and p.amount = x.ma;

Неактивен

 

#20 11.10.2017 17:24:52

gotlib
Участник
Зарегистрирован: 29.09.2016
Сообщений: 6

Re: Вывод некорректной даты при запросе.

Не имеет смысла хранить в базе одинаковые строки.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson