오늘의 목표
SQL 강의 듣기 (17~18강)
과제 및 요약 내용 정리하기
강의 내용
날짜/시간 함수 (Data/Time Functions)
| 종류 | 설명 |
|---|---|
| getdate() | 현재 로컬 날짜/시간값 반환 |
| getutcdate() | 현재 UTC 날짜/시간값 반환 |
| datepart() | 특정 날짜(시간) 부분 추출 |
| convert() | 포맷 변경 |
| datediff() | 두 날짜(시간) 사이의 간격 |
| dateadd() | 특정 날짜(시간)으로 부터 특정 간격만큼 계산 |
getdate() , getutcdate()
select
getdate() as my_time,
getutcdate() as utc_time;datepart()
select
datepart(year,'2000-6-15') as p_year,
datepart(month, '2000-6-15') as p_month,
datepart(day,'2000-6-15') as p_day,
datepart(weekday,'2000-6-15') as p_weekday,
datepart(hour,'17:52:33') as p_hour;convert()
- convert(결과의 타입, 값의 기준, 포맷 번호)
select
OrderDate,
convert(varchar(10),OrderDate,120) as o_date1,
convert(varchar(10),OrderDate,110) as o_date2
from
SalesOrder;datediff()
- datediff(기준이 될 날짜값, 날짜 범위)
select
datediff(day,'2000-01-01','2000-01-03') as d_days,
datediff(hour,'12:00:00','15:00:00') as d_hours,
datediff(week,'2000-01-01','2000-12-31') as d_weeks;dateadd()
- 미래시간, 과거시간 전부 구하기 가능
select
dateadd(day,100,'2000-01-01') as add_days,
dateadd(hour,5,'12:00:00') as add_hours,
dateadd(week,10,'2000-01-01') as add_weeks;[과제] 쿼리 작성하기
- 2008년 7월달의 주문 건 수 (결과 8)
select
SalesOrderID
from
SalesOrder
where
Year(OrderDate) = 2008 and month(OrderDate) = 7;
- 배송기간이 2일 이하 였던 주문 내역 (결과 3)
select
SalesOrderID
from
SalesOrder
where datediff(day,OrderDate,ShipDate) <= 2;
- 주문번호, 주문일자, 정상배달일자(5일이내), 실제 배달일자 모든 날짜 포맷은 “yyyy-mm-dd” 형식
select
SalesOrderID,
convert(varchar(10), OrderDate, 120) as OrderDate,
convert(varchar(10), dateadd(day, 5, OrderDate), 120) as Normal_shipDate,
convert(varchar(10), ShipDate, 120) as ShipDate
from
SalesOrder;
Group By
- 어떤 데이터들을 그룹화 함
- ~별 (ex 고객별, 제품별, 요일별, 시간대별 등등)
select CountryRegion from Customer
group by CountryRegion
select CountryRegion, City from Customer
group by CountryRegion, City;- group by 를 사용하게 되면, 전체 데이터를 보여주는 것이 아닌 그 사람들이 속한 나라만 보여주게 됨.
Group By 와 집계 함수
select CountryRegion, count(*) as Customers_by_Country
from Customer
group by CountryRegion
select SalesOrderID, sum(Subtotal) as Order_Total
from SalesOrderDetail
group by SalesOrderID;[예시] 2008년 7월 주문 건수 구하기
/* 2008년 7월의 일별 주문 합계 구하기
2008-07-01 1732.88
2008-07-02 2660.99 */
SELECT so.OrderDate, sum(sod.SubTotal) as total
From SalesOrder so inner join SalesOrderDetail sod
on (so.SalesOrderID = sod.SalesOrderID)
where so.OrderDate Between '2008-07-01' and '2008-07-31'
group by so.OrderDate;
SELECT *
From SalesOrder so inner join SalesOrderDetail sod
on (so.SalesOrderID = sod.SalesOrderID)
where so.OrderDate Between '2008-07-01' and '2008-07-31'Group by 와 Having
- 그룹화 한 것에 대한 조건이 필요한 경우 having를 사용
- where절 처럼 사용
/* 2008년 7월의 일별 주문 합계 구하기
2008-07-01 1732.88
2008-07-02 2660.99 ..
총 금액이 3000 이상인 것 */
SELECT so.OrderDate, sum(sod.SubTotal) as total
From SalesOrder so inner join SalesOrderDetail sod
on (so.SalesOrderID = sod.SalesOrderID)
where so.OrderDate Between '2008-07-01' and '2008-07-31'
group by so.OrderDate
having sum(sod.subtotal) >= 3000;Group by 와 Order by
- 결과를 가지고 정렬하기
SELECT so.OrderDate, sum(sod.SubTotal) as total
From SalesOrder so inner join SalesOrderDetail sod
on (so.SalesOrderID = sod.SalesOrderID)
where so.OrderDate Between '2008-07-01' and '2008-07-31'
group by so.OrderDate
having sum(sod.subtotal) >= 3000
order by sum(sod.subtotal);[과제] 쿼리 작성하기
- (고객)국가별 총 주문 건수
/* 고객 국가별 총 주문 건수 */
select
c.CountryRegion,
Count(so.SalesOrderID) as Orders_by_Country
From
Customer c inner join SalesOrder so
on (c.CustomerID = so.CustomerID)
group by CountryRegion;
- (고객)국가별 총 주문 건수와 총 주문 금액
- 해결할때 엄청난 시간이 소요됨 (2일)
- 계속 값을 계산하는데 countryRegion 범위가 포함이 되지 않고 Tot_orders 값이 출력이되어서 여러가지 방법을 좀 찾아봄.
- 그룹이 잘못 지어진 경우, 그룹에 SalesOrderID를 넣어보기도 했고 하다가 아예 개별값을 사용하는 것으로 방법 선회
/* 고객 국가별 총 주문 건수와 총 주문 금액 */
select
c.CountryRegion,
Count(distinct so.SalesOrderID) as Tot_Orders,
sum(sod.SubTotal) as Tot_Amount
From
Customer c
inner join (
select distinct s.SalesOrderID, s.CustomerID
from SalesOrder s
) so
on (c.CustomerID = so.CustomerID)
inner join SalesOrderDetail sod
on (so.SalesOrderID = sod.SalesOrderID)
group by c.CountryRegion;
3.월별, 국가별 총 주문 건 수
/* 월 별, 고객 국가별 총 주문 건수 */
select
datepart(year, so.OrderDate) as order_year,
datepart(month, so.OrderDate) as order_month,
c.CountryRegion,
Count(so.SalesOrderID) as Orders_by_country
from
Customer c inner join SalesOrder so
on (c.CustomerID = so.CustomerID)
group by
datepart(year, so.OrderDate),
datepart(month, so.OrderDate),
c.CountryRegion
having
datepart(year, so.OrderDate) = 2008
and datepart(month, so.OrderDate) = 6
or datepart(month, so.OrderDate) = 7;
#QA #woonmong