MySQL 이모저모

이윤설·2023년 4월 6일
데이터베이스
0

JSON_SEARCH 함수는 JSON 데이터에서 특정 값 또는 경로를 찾아 해당 값 또는 경로의 위치를 반환하는 함수다. JSON_SEARCH 함수의 첫 번째 매개변수는 JSON 데이터이고, 두 번째 매개변수는 검색 모드이며, 세 번째 매개변수는 검색할 값 또는 경로다.

SELECT JSON_SEARCH(@json, 'one', '성시경') AS JSON_SEARCH ;

여기서 one은 검색 모드 중 하나로, 주어진 JSON 데이터에서 최초로 일치하는 값 또는 경로를 검색한다. 따라서 위의 쿼리에서 JSON_SEARCH(@json, 'one', '성시경')는 @json 변수에 저장된 JSON 데이터에서 "성시경" 값을 가지는 요소가 포함된 경로를 검색하고 해당 경로를 반환한다.

-JSON_SEARCH의 두번째 매개변수로 들어갈 수 있는 값:

one: 주어진 JSON 데이터에서 최초로 일치하는 값을 검색합니다.
all: 주어진 JSON 데이터에서 모든 일치하는 값을 검색합니다.
first: 주어진 JSON 데이터에서 최초로 일치하는 경로를 검색합니다.
last: 주어진 JSON 데이터에서 마지막으로 일치하는 경로를 검색합니다.

숫자형 데이터와 문자열 데이터, 그리고 CAST

SELECT num, 
CONCAT(CAST(price AS CHAR(10)), 'X', 
CAST(amount AS CHAR(4)),'=' ) AS '단가X수량',
    price * amount AS '구매액'
	FROM buytbl;

위 코드에서 CAST 함수를 통해 문자열 CHAR로 변경해주었다. 숫자형으로 사용해도 문제가 없지만, 일반적으로 CONCAT 함수를 사용하여 문자열로 결합하는 이유는 여러 가지가 있다.

첫째, 문자열로 변환된 숫자형 데이터를 결합하면 결과물이 일관적인 형태를 갖게된다. 예를 들어, price와 amount는 각각 INT나 DECIMAL 등의 숫자형 데이터일 수 있다. 하지만 CONCAT 함수를 사용하여 두 값을 문자열로 결합하면 둘 다 문자열로 변환되어서 출력되므로, 출력 결과가 일관적인 형태를 갖게 된다.

둘째, 문자열로 결합한 데이터는 가독성이 좋다. CONCAT 함수를 사용하여 출력할 경우, 출력값이 하나의 문자열로 결합되어 나타나기 때문에 숫자형 데이터와 문자열 데이터가 어디서부터 어디까지인지 명확하게 구분된다. 따라서 출력 결과를 더 쉽게 이해하고 해석할 수 있습니다.

셋째, 문자열로 결합한 데이터는 일부 환경에서 더욱 안전하다. MySQL에서는 일부 연산에서 숫자형 데이터와 문자열 데이터를 혼용하여 사용할 경우, 자동으로 형변환하여 연산한다. 이 경우, 형변환이 예상과 다르게 이루어질 수 있어서 오류가 발생할 가능성이 있다. 하지만 CONCAT 함수를 사용하여 문자열로 결합한 데이터는 항상 문자열로 취급되므로, 이러한 문제를 예방할 수 있다.

-그렇다면 굳이 숫자형으로 출력하고 싶다면 코드는 어떻게 써야하는가?

SELECT num, price * amount AS '단가X수량', 
CAST(price * amount AS INT) AS '구매액'
FROM buytbl;

위 코드에서 CAST(price amount AS INT) 부분은 price amount의 결과를 INT 형태로 형변환한 것이다. INT는 소수점 이하의 값을 저장할 수 없으므로, 구매액에 소수점 이하 값이 없는 경우에는 정확한 값을 출력할 수 있다.

그래서 2개 이상의 숫자를 연산할 때, 보통 문자열로 바꾼다는 것을 기억하자.

-- CONCAT, CAST
MySQL에서 CONCAT과 CAST 함수의 사용 의도는 다르다.

  1. CONCAT 함수: CONCAT 함수는 문자열을 연결하는 데 사용됩니다. 여러 개의 문자열을 연결하여 하나의 문자열로 만들어준다. 예를 들어, CONCAT('Hello', ' ', 'World')는 "Hello World"라는 문자열을 반환합니다. 이 함수는 문자열 연결 작업에 유용하게 사용된다.

  2. CAST 함수: CAST 함수는 데이터 형식을 변환하는 데 사용된다. 예를 들어, CAST(column_name AS new_data_type)와 같이 사용하여 특정 열의 데이터 형식을 새로운 데이터 형식으로 변환할 수 있다. CAST 함수는 데이터 형식을 변환하여 원하는 형식으로 데이터를 사용하거나 비교할 때 유용하다.

CONCAT 함수는 문자열 연결 작업에 사용되는 반면, CAST 함수는 데이터 형식 변환에 사용된다.

count

CREATE TABLE Purchase (
   PaymentMethod VARCHAR(20)
);

INSERT INTO Purchase (PaymentMethod)
VALUES 
   ('Credit Card'), 
   ('Credit Card'), 
   ('Credit Card'), 
   ('Cash');

--> 결과값:

PaymentMethod
-------------
Credit Card
Credit Card
Credit Card
Cash

이와 같은 테이블이 있다고 할 때, Credit Card와 Cash의 숫자를 카운트 하려면 어떻게 해야하는가?

SELECT PaymentMethod, COUNT(*) as Count
FROM Purchase
GROUP BY PaymentMethod;

COUNT() 함수는 특정 테이블 또는 뷰에서 전체 레코드 수를 반환한다.
즉, Purchase 테이블에서 COUNT() 함수를 사용하면 해당 테이블에 존재하는 모든 레코드 수를 반환한다.
COUNT() 함수는 테이블의 모든 열을 검색하므로, NULL 값을 포함한 모든 레코드를 세어준다. 따라서, COUNT() 함수는 NULL 값을 포함한 전체 레코드 수를 반환한다.
하지만 count(숫자)는 (ex. count(1), count(3), count(100)) 모두 NULL 값을 포함하지 않는 레코드 갯수를 출력한다.

COUNT(숫자)는 NULL을 제외하는 갯수를 출력하는 이유는 SQL의 COUNT 함수의 동작 방식 때문이다.

COUNT(숫자)에서 숫자는 단순히 플레이스홀더{COUNT(숫자)에서 숫자 부분이 실제로는 의미가 없는 임의의 값이다. 플레이스홀더는 일반적으로 실제 값으로 대체되어야 하는 자리를 가리키는 용어다.}
로 사용되며, 숫자 값 자체는 의미가 없다. 중요한 것은 NULL이 아닌 값을 가진 레코드의 개수를 세는 것입니다. 따라서 어떤 숫자를 사용하더라도 COUNT(숫자)는 NULL을 제외하는 레코드의 개수를 반환한다. 즉, COUNT(숫자)는 SQL의 규칙에 따라 NULL을 제외하는 갯수를 출력하는 것이다.
그리고 PaymentMethod를 count 하므로 groupBy를 써주도록 한다.

select 기타


처음에 1이 여러개 출력되는지 의아했다. 그런데 잠시 생각해보니 deliverymethod, paymentmethod 세개가 각자 다르기 때문에 경우의 수에 따라 나누다 보니 세개가 출력된 것이다.

데이터마케팅 수업에서 배운 것들

  1. PaymentMethod가 'Cash'이고, 주소지가 서울인 사람이 구매한
    Brand, 구매자의 Gender, 구매횟수를 출력한다.
    Brand와 Gender를 기준으로 정렬하며,
    조건은 OrderQuantity와 UnitPrice의 평균이 1,000,000 이상인 경우만 출력한다. 
SELECT Product.Brand, Customer.Gender, count (1) AS Freq, 
AVG(Purchase.OrderQuantity * Product.UnitPrice) AS AvgPurchasePrice

FROM Purchase, Customer, Product
WHERE Purchase.CustomerID = Customer.CustomerID
AND
Purchase.ProductID = Product.ProductID

AND Purchase.PaymentMethod ='Cash'
AND Customer.Address = 'Seoul'
GROUP BY Product.Brand, Customer.Gender
HAVING AVG(Purchase.OrderQuantity * Product.UnitPrice) >= 1000000;
  1. 서울에 사는 사람을 제외한 여성 구매자들이 구매한 브랜드와 구매횟수를 출력하고, Brand를 정렬한다. 
SELECT Product.Brand, count (1) AS Freq
FROM Purchase, Customer, Product
WHERE Purchase.CustomerID = Customer.CustomerID
AND
Purchase.ProductID = Product.ProductID
AND Customer.Address Not in ('Seoul')
AND Customer.Gender = 'F'
GROUP BY Product.Brand;
  1. 여성들이 가장 구매한 브랜드 이름을 출력
SELECT Subquery.Brand
FROM (
    SELECT Product.Brand, COUNT(1) AS Freq
    FROM Purchase, Customer, Product
    WHERE Purchase.CustomerID = Customer.CustomerID
        AND Purchase.ProductID = Product.ProductID
        AND Customer.Gender = 'F'
    GROUP BY Product.Brand
) AS Subquery
WHERE Subquery.Freq = (
    SELECT MAX(Freq)
    FROM (
        SELECT COUNT(1) AS Freq
        FROM Purchase, Customer, Product
        WHERE Purchase.CustomerID = Customer.CustomerID
            AND Purchase.ProductID = Product.ProductID
            AND Customer.Gender = 'F'
        GROUP BY Product.Brand
    ) AS Subquery2
);
  1. 여성들이 가장 구매한 브랜드를 구매한 남성 고객의 CustomerId, Name 출력
SELECT Customer.CustomerID, Customer.Name
FROM Purchase, Customer, Product
WHERE Purchase.CustomerID = Customer.CustomerID
    AND Purchase.ProductID = Product.ProductID
    AND Customer.Gender = 'M'
    AND Product.Brand = (
        SELECT Brand
        FROM (
            SELECT Product.Brand, COUNT(*) AS Freq
            FROM Purchase, Customer, Product
            WHERE Purchase.CustomerID = Customer.CustomerID
                AND Purchase.ProductID = Product.ProductID
                AND Customer.Gender = 'F'
            GROUP BY Product.Brand
        ) AS Subquery
        WHERE Freq = (
            SELECT MAX(Freq)
            FROM (
                SELECT COUNT(*) AS Freq
                FROM Purchase, Customer, Product
                WHERE Purchase.CustomerID = Customer.CustomerID
                    AND Purchase.ProductID = Product.ProductID
                    AND Customer.Gender = 'F'
                GROUP BY Product.Brand
            ) AS Subquery2
        )
    )
GROUP BY Customer.CustomerID, Customer.Name;
  1. AgeGroup(15살이면 1, 25살이면 2, 35살이면 3), Gender, TotalPurchasePrice, Freq, AvgPurchasePrice 출력
SELECT int((Customer.Age)/10) AS AgeGroup, Customer.Gender,
SUM(Purchase.OrderQuantity * Product.UnitPrice) AS TotalPurchasePrice, 
count(1) AS Freq, 
AVG(Purchase.OrderQuantity * Product.UnitPrice) AS AvgPurchasePrice
FROM Customer, Product, Purchase
WHERE Purchase.CustomerID = Customer.CustomerID
AND
Purchase.ProductID = Product.ProductID
GROUP BY int((Customer.Age)/10), Customer.Gender;
  1. 각 제품 브랜드와 성별에 대해 데이터가 2개 이상이며, 구매 가격이 1,000,000 이상인 경우의 평균 구매 가격을 반환
SELECT Product.Brand, Customer.Gender, Count(1) AS Freq, 
Avg(Purchase.OrderQuantity*Product.UnitPrice) AS AvgPurchasePrice
FROM Purchase, Customer, Product
WHERE (((Purchase.CustomerID)=Customer.CustomerID) 
And ((Purchase.ProductID)=Product.ProductID))
GROUP BY Product.Brand, Customer.Gender
HAVING (((Count(1))>=2) 
And ((Avg(Purchase.OrderQuantity*Product.UnitPrice))>=1000000));
profile
이윤설
화려한 외면이 아닌 단단한 내면
이전 포스트

JSON, JSON 객체와 배열

다음 포스트

count(*), count(1), count(n)

0개의 댓글