[持って遊ぶMySQL]1-5条件付きグループ化
11506 ワード
1.GROUP BY-条件に従って統計値を取得します。
SELECT Country FROM Customers
GROUP BY Country;
SELECT CategoryID FROM Products
GROUP BY CategoryID;
💡 複数のカラムに基づいてグループ化することもできます。
SELECT
Country, City,
CONCAT_WS(', ', City, Country)
FROM Customers
GROUP BY Country, City;
📚 グループ関数の使用
SELECT
COUNT(*), OrderDate
FROM Orders
GROUP BY OrderDate;
SELECT
ProductID,
SUM(Quantity) AS QuantitySum
FROM OrderDetails
GROUP BY ProductID
ORDER BY QuantitySum DESC;
SELECT
CategoryID,
MAX(Price) AS MaxPrice,
MIN(Price) AS MinPrice,
TRUNCATE((MAX(Price) + MIN(Price)) / 2, 2) AS MedianPrice,
TRUNCATE(AVG(Price), 2) AS AveragePrice
FROM Products
GROUP BY CategoryID;
SELECT
CONCAT_WS(', ', City, Country) AS Location,
COUNT(CustomerID)
FROM Customers
GROUP BY Country, City;
💡 WITH ROLLUP—集約値
SELECT
Country, COUNT(*)
FROM Suppliers
GROUP BY Country
WITH ROLLUP;
HAVING-パケットデータのフィルタリング
SELECT
Country, COUNT(*) AS Count
FROM Suppliers
GROUP BY Country
HAVING Count >= 3;
💡 WHEREはパケット前のデータ、HAVINGはパケット後の集約に用いられる。
SELECT
COUNT(*) AS Count, OrderDate
FROM Orders
WHERE OrderDate > DATE('1996-12-31')
GROUP BY OrderDate
HAVING Count > 2;
SELECT
CategoryID,
MAX(Price) AS MaxPrice,
MIN(Price) AS MinPrice,
TRUNCATE((MAX(Price) + MIN(Price)) / 2, 2) AS MedianPrice,
TRUNCATE(AVG(Price), 2) AS AveragePrice
FROM Products
WHERE CategoryID > 2
GROUP BY CategoryID
HAVING
AveragePrice BETWEEN 20 AND 30
AND MedianPrice < 40;
2.DISTINCT-重複値を削除します。
SELECT DISTINCT CategoryID
FROM Products;
-- 위의 GROUP BY를 사용한 쿼리와 결과 비교
SELECT COUNT DISTINCT CategoryID
FROM Products;
-- 오류 발생
SELECT DISTINCT Country
FROM Customers
ORDER BY Country;
SELECT DISTINCT Country, City
FROM Customers
ORDER BY Country, City;
💡 GROUP BYとDISTINCTを併用
SELECT
Country,
COUNT(DISTINCT CITY)
FROM Customers
GROUP BY Country;
Reference
この問題について([持って遊ぶMySQL]1-5条件付きグループ化), 我々は、より多くの情報をここで見つけました https://velog.io/@24siefil/갖고-노는-MySQL-1-5.-조건에-따라-그룹으로-묶기テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol