[SQL] MYSQL 19~21

SUNGJIN KIM·2023년 8월 21일

TIL sql

SQL

목록 보기

8/8

오늘의 목표

강의 완강하기 (19 ~ 21)

공부 내용 정리하기

Select Top

where 및 order by 까지 적용된 최종 결과에 대하여 상위 n개 (혹은 n퍼센트) 선택
상위 3개만 보고싶은 경우

select top 3 *
from SalesOrder
order by OrderDate desc;

Top with ties

with ties (동점, 같다, 동등하다)
with ties 는 반드시 order by 조건이 필요하다.

select top 3 with ties *
from SalesOrder
order by OrderDate desc;

[실습] 1. 상위 5개 주문 ID, 주문 금액

/* 주문금액 기준 상위 5개 주문 ID, 주문 금액 */
select top 5
    SalesOrderID,
    sum(SubTotal) as Order_Total
from 
    SalesOrderDetail
group by SalesOrderID
order by Order_Total desc;

[실습] 2. 총 주문금액 상위 10% 고객ID, 금액

/* 총 주문 금액 상위 10% 고객 ID, 금액 */
select top 10 percent
    so.CustomerID,
    sum(SubTotal) as total
from
    SalesOrder so inner join SalesOrderDetail sod
    on (so.SalesOrderID = sod.SalesOrderID)
group by so.CustomerID
order by total desc;

[과제] 다음 쿼리를 작성해보세요.

총 판매 수량 top 10 제품 ID, 판매량(동일수량 포함)

/* 총 판매 수량 top 10 제품 id, 판매량 (동일수량 포함) */
select top 10
    p.ProductID,
    sum(sod.OrderQty) as Order_Qty
from
    SalesOrderDetail sod inner join Product p
    on (sod.ProductID = p.ProductID)
group by p.ProductID
order by Order_Qty desc;

총 주문 금액 상위 5% 제품 ID, 총 주문 금액

/* 총 주문 금액 상위 5% 제품 ID, 총 주문 금액 */
select top 5 percent
    p.ProductID,
    sum(subTotal) as Order_Total
from
    Product p inner join SalesOrderDetail sod
    on (p.ProductID = sod.ProductID)
group by p.ProductID
order by Order_Total desc;

20. Subqueries (서브 쿼리)

실행했던 쿼리는 하나의 쿼리만 가지고 동작, 서브쿼리는 2개이상 쿼리가 돌아가는 개념
부모가 돌아갈때, 자식도 돌아감

select *
from SalesOrder
where CustomerID in (값, 값, 값);

/* united State 에 사는 사람의 주문 내역 확인 */
select *
from SalesOrder
where CustomerID in (
    select CustomerID from Customer
    where CountryRegion = 'United States'
);

총 주문금액 상위 10%인 사람들의 주문 내역 표시

/* in 조건에 사용, 총 주문금액 상위 10% 인 사람들의 주문 내역 표시 */
select 
    SalesOrderID,
    CustomerId
from
    SalesOrder
where CustomerID in (
    select top 10 percent 
        so.customerID
    from 
        SalesOrder so inner join SalesOrderDetail sod
        on (so.SalesOrderID = sod.SalesOrderID)
group by so.CustomerID
order by sum(sod.SubTotal) desc
);

서브쿼리 테이블

select * from (
    select CustomerId, FirstName
    from Customer
)tmp_table;

/* 전체 평균 주문금액 구하기 */
select avg(Order_Total)
from (
    select
        SalesOrderID,
        sum(SubTotal) as Order_Total
    from
        SalesOrderDetail
    group by SalesOrderID
) tbl_order_total;

where 조건에 이용

/* 평균 배송기간보다 빨리 배송된 주문 건 */
select * from SalesOrder
where
    DATEDIFF(day,OrderDate,ShipDate) <= (
        select avg(DATEDIFF(day,OrderDate,ShipDate))
        from SalesOrder
    );

[과제] 다음 쿼리를 작성해보세요.

총 주문금액 기준 상위 10% 사람들이 살고 있는 국가명, 도시명, 사람 수

/* 총 주문금액 기준 상위 10% 사람들이 살고 있는 국가명, 도시명, 사람 수 */
select 
    CountryRegion, City,
    count(CustomerID) as Customers
from (
    select CountryRegion, City, CustomerID
    from Customer
) cm_table
where CustomerID in (
    select top 10 percent 
        so.customerID
    from 
        SalesOrder so inner join SalesOrderDetail sod
        on (so.SalesOrderID = sod.SalesOrderID)
    group by so.CustomerID
    order by sum(sod.SubTotal) desc
)
group by CountryRegion, City
order by Customers desc;

국가별 평균 배송 기간

/* 국가별 평균 배송 기간 */
select
    c.CountryRegion,
    avg(CAST(DATEDIFF(day, so.OrderDate, so.ShipDate) AS DECIMAL(18, 6))) as Avg_Shipping_Days
from
    Customer c inner join SalesOrder so
    on (c.CustomerID = so.CustomerID)
group by c.CountryRegion;

JSON 포맷 (JSON Format Support)

{속성:값} 의 형태로 데이터를 표현/저장하고, 서로 다른 이기종간에 데이터를 교환할 수 있는 텍스트 파일 포맷
HTTP 를 이용한 REST API 호출/응답 시 많이 사용됨
이전에는 XML로 사용했으나 JSON으로 많이 사용됨 (요즘)
쿼리 실행 결과를 JSON 포맷으로 표현하는 방식

A	B	C	D
10	11	12	X
20	21	22	Y
30	31	32	Z

[{
	"A" : 10,
	"B" : 11,
	"C" : 12,
	"D" : "X"
},{
	"A" : 20,
	"B" : 21,
	"C" : 22,
	"D" : "Y"
},{
	"A" : 12,
	"B" : 22,
	"C" : 32,
	"D" : "Z"
}]

실습

for json auto 만 붙여주면 됨.

select CustomerID, FirstName, LastName
from Customer
where City = 'San Jose'
for json auto;

for json path

json 이 depth가 있는 경우, as를 통해 묶어주면 됨

SELECT
    so.SalesOrderID,
    convert(varchar(10), so.OrderDate,120) as OrderDate,
    c.CustomerID as 'Customer.CustomerId',
    c.FirstName as 'Customer.FirstName',
    c.LastName as 'Customer.LastName'
from
    SalesOrder so inner join Customer c
    on (so.CustomerID = c.CustomerID)
where so.OrderDate = '2008-06-02'
for json path;

[과제] JSON 포맷 결과 만들기

제품 리스트

/* 다음 JSON 포맷 결과를 만들어보기 (제품 리스트) */
select 
    ProductID,
    Name,
    Color,
    Size,
    ListPrice
from Product
for json path;

주문 내역, 주문번호 = 71774

Select
    so.SalesOrderID,
    convert(varchar(10), so.OrderDate, 120) as OrderDate,
    sod.SalesOrderDetailID as 'SalesOrderDetail.SalesOrderDetailID',
    sod.ProductID as 'SalesOrderDetail.ProductID',
    sod.UnitPrice as 'SalesOrderDetail.UnitPrice',
    sod.OrderQty as 'SalesOrderDetail.OrderQty',
    sod.SubTotal as 'SalesOrderDetail.SubTotal'
from
    SalesOrder so inner join SalesOrderDetail sod
    on (so.SalesOrderID = sod.SalesOrderID)
where so.SalesOrderID  = 71774
FOR JSON PATH;