#mysql #sql
Вопрос:
Я использую mysql Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu)) .
Я создал следующую базу данных:
CREATE TABLE `transaction` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`company` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`filling_date` timestamp NULL DEFAULT NULL,
`trx_type` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`amount_range` int DEFAULT NULL,
`insider_price` decimal(30,4) DEFAULT NULL,
`qty` int DEFAULT NULL,
`transaction_value` decimal(30,4) DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO transaction (id, company, filling_date, trx_type, amount_range, insider_price, qty, transaction_value) VALUES(6, 'Apple', '2021-06-06 16:39:54.000', 'P - Purchase', 1000, 10, 100, 8000);
INSERT INTO transaction (id, company, filling_date, trx_type, amount_range, insider_price, qty, transaction_value) VALUES(7, 'Apple', '2021-06-05 15:29:34.000', 'S - Sale', 2000, 11, 200, 9000);
INSERT INTO transaction (id, company, filling_date, trx_type, amount_range, insider_price, qty, transaction_value) VALUES(8, 'Microsoft', '2021-06-05 11:22:15.000', 'P - Purchase', 2000, 10, 500, 1000);
INSERT INTO transaction (id, company, filling_date, trx_type, amount_range, insider_price, qty, transaction_value) VALUES(9, 'Apple', '2021-05-16 11:29:44.000', 'P - Purchase', 1000, 11, 1000, 10000);
INSERT INTO transaction (id, company, filling_date, trx_type, amount_range, insider_price, qty, transaction_value) VALUES(10, 'Microsoft', '2021-01-10 11:22:15.000', 'P - Purchase', 100, 30, 700, 3000);
INSERT INTO transaction (id, company, filling_date, trx_type, amount_range, insider_price, qty, transaction_value) VALUES(11, 'Microsoft', '2021-06-01 22:22:15.000', 'S - Sale', 6000, 60, 600, 4000);
Я хотел бы отфильтровать свой оператор group-by, чтобы получить все записи для каждой группы по > 1 (> count(Company) > 1 ).
Я попробовал следующее:
select
count(Company) AS RecordsPerGroup,
Company,
max(filling_date) Last_filling_date,
trx_type,
sum(amount_range) amount_range,
sum(insider_price) insider_price,
sum(qty) qty,
sum(transaction_value) transaction_value
from transaction
where filling_date >= DATE(NOW()) - INTERVAL 31 DAY
AND trx_type ='p - purchase'
AND count(Company) > 1
group by company
Когда я выполняю приведенный выше запрос, я получаю:
Query Error: Error: ER_INVALID_GROUP_FUNC_USE: Invalid use of group function
Ошибка связана со следующим утверждением AND count(Company) > 1 , которое не допускается в моем запросе.
Найдите ниже мой пример db-скрипки:
Я хотел бы получить следующий результат:
| RecordsPerGroup | Last_filling_date | amount_range | insider_price | qty | transaction_value | Company | trx_type |
| --------------- | ------------------- | ------------ | ------------- | ---- | ----------------- | --------- | ------------ |
| 2 | 2021-06-06 16:39:54 | 2000 | 21.0000 | 1100 | 18000.0000 | Apple | P - Purchase |
Есть какие-либо предложения, как отфильтровать мои инструкции по группам, чтобы отображать только все записи, у которых >2 групповых групп?
Я ценю ваши ответы!
Ответ №1:
Утверждение о количестве принадлежит HAVING предложению, а не самому WHERE предложению, а также , если вы собираетесь выбрать trx_type , то этот столбец также должен появиться в GROUP BY предложении:
SELECT
COUNT(Company) RecordsPerGroup,
Company,
MAX(filling_date) Last_filling_date,
trx_type,
SUM(amount_range) amount_range,
SUM(insider_price) insider_price,
SUM(qty) qty,
SUM(transaction_value) transaction_value
FROM `transaction`
WHERE
filling_date >= DATE(NOW()) - INTERVAL 31 DAY AND
trx_type = 'p - purchase'
GROUP BY
Company,
trx_type
HAVING
COUNT(Company) > 1;
Комментарии:
1. Из-за ТОГО, ГДЕ, другой будет работать в обычных настройках: ( db-fiddle.com/f/mxSDGn9vkXeaeg4mPudVdZ/0 ). У вас есть странствующий И; Я удалю его…
2. @Strawberry Это интересно, вы хотите сказать, что он даже работал бы в строгом режиме? В любом случае, это не лучшая практика (плюс мой ответ, вероятно, не совсем то, что нужно оператору…хотя это лучшее, что я могу предложить, основываясь на предоставленной информации).
3. Я не уверен; Я подозреваю, что есть условия, при которых ваше утверждение было бы правильным, но я не могу потрудиться его проверить. Я просто говорю/демонстрирую, что это работает для настройки MySQL 8 по умолчанию (предполагая, что это то, что использует db-fiddle)
Ответ №2:
Если вы используете GROUP BY и хотите рассматривать агрегацию как часть общих критериев «фильтра», используйте HAVING.
Напр..
SELECT
count(Company) AS RecordsPerGroup,
Company,
max(filling_date) Last_filling_date,
trx_type,
sum(amount_range) amount_range,
sum(insider_price) insider_price,
sum(qty) qty,
sum(transaction_value) transaction_value
from transaction
where filling_date >= DATE(NOW()) - INTERVAL 31 DAY
AND trx_type ='p - purchase'
group by company
HAVING COUNT(Company) > 1
Дополнительные сведения см. в документах MySQL https://www.mysqltutorial.org/mysql-having.aspx
Предложение HAVING, как и предложение WHERE, определяет условия выбора. Предложение WHERE определяет условия для столбцов в списке выбора, но не может ссылаться на агрегатные функции. Предложение HAVING определяет условия для групп, обычно формируемых предложением GROUP BY. Результат запроса включает только группы, удовлетворяющие условиям НАЛИЧИЯ. (Если ГРУППА ПО отсутствует, все строки неявно образуют единую совокупную группу.)
Предложение HAVING применяется почти в последнюю очередь, непосредственно перед отправкой товаров клиенту, без какой-либо оптимизации. (ОГРАНИЧЕНИЕ применяется после НАЛИЧИЯ.)
Стандарт SQL требует, чтобы HAVING ссылался только на столбцы в предложении GROUP BY или столбцы, используемые в агрегатных функциях. Однако MySQL поддерживает расширение для этого поведения и разрешает ссылаться на столбцы в списке ВЫБОРА и столбцы во внешних подзапросах.
Комментарии:
1. Обратите внимание, что этот запрос может даже не выполняться в зависимости от настроек сервера MySQL.