[MYSQL] SolveSql Aggregate

✔ Aggregate

데이터 그룹으로 묶기

[SolveSql group-by]

/*
SELECT : 콰르텟, x 평균, x 표본 분산, y 평균, y 표본 분산
FROM : points 테이블
GROUP BY : quartet 컬럼
*/
SELECT
  quartet,
  ROUND(AVG(x), 2) AS 'x_mean',
  ROUND(VARIANCE(x), 2) AS 'x_var',
  ROUND(AVG(y), 2) AS 'y_mean',
  ROUND(VARIANCE(y), 2) AS 'y_var'
FROM
  points
GROUP BY
  quartet

우리 플랫폼에 정착한 판매자 1

[SolveSql settled-sellers-1]

/*
 SELECT : 판매자 ID, 판매자가 판매한 주문 건수
 FROM : olist_order_items_dataset 테이블
 GROUP BY : 판매자 ID
 HAVING : 총 주문이 100건 이상
*/
SELECT
  seller_id,
  COUNT(DISTINCT (order_id)) AS 'orders'
FROM
  olist_order_items_dataset
GROUP BY
  seller_id
HAVING
  orders >= 100

최고의 근무일을 찾아라

[SolveSql best-working-day]

/*
 SELECT : 팁이 가장 많았던 요일, 요일 별 팁 총액
 FROM : tips 테이블
 GROUP BY : 요일별
 ORDER BY : 팁 총액 내림차순 정렬
*/
SELECT
  day,
  ROUND(SUM(tip), 3) AS 'tip_daily'
FROM
  tips
GROUP BY
  day
ORDER BY
  tip_daily DESC
LIMIT
  1

첫 주문과 마지막 주문

[SolveSqlfirst-and-last-orders]

/*
 SELECT : 가장 작은 주문 일자의 날짜, 가장 큰 주문 일자의 날짜
 FROM : olist_orders_dataset 테이블
*/
SELECT
  DATE(MIN(order_purchase_timestamp)) AS 'first_order_date',
  DATE(MAX(order_purchase_timestamp)) AS 'last_order_date'
FROM
  olist_orders_dataset

레스토랑의 일일 평균 매출액 계산하기

[SolveSql sales-summary]

/*
SELECT : 일일 평균
FROM : tips 테이블에서 일일 매출을 추출한 테이블
*/
SELECT
  ROUND(AVG(sales), 2) AS 'avg_sales'
FROM
  (
    SELECT
      day,
      SUM(total_bill) AS 'sales'
    FROM
      tips
    GROUP BY
      day
  )

레스토랑 웨이터의 팁 분석

[SolveSql tip-analysis]

/*
 SELECT : 요일의 시간대 별 평균 팁, 평균 일행 수
 FROM : tips 테이블
 GROUP BY : 요일, 시간
 ORDER BY : 요일, 시간대의 알파벳 순 오름차순 정렬
*/
SELECT
  day,
  time,
  ROUND(AVG(tip), 2) AS 'avg_tip',
  ROUND(AVG(size), 2) AS 'avg_size'
FROM
  tips
GROUP BY
  day,
  time
ORDER BY
  day,
  time

일별 블로그 방문자 수 집계

[SolveSql blog-counter]

/*
 SELECT : 2021년 8월 2일부터 2021년 8월 9일까지의 방문일, 방문자 수
 FROM : ga 테이블
 GROUP BY : 날짜별
 HAVING : 2021년 8월 2일부터 2021년 8월 9일
 ORDER BY : 방문일을 기준으로 오름차순 정렬
*/
SELECT
  event_date_kst AS 'dt',
  COUNT(DISTINCT user_pseudo_id) AS 'users'
FROM
  ga
GROUP BY
  dt
HAVING
  dt >= '2021-08-02'
  AND dt <= '2021-08-09'
ORDER BY
  dt

우리 플랫폼에 정착한 판매자 2

[SolveSql settled-sellers-2]

/*
 SELECT : 판매자 ID, 판매자가 판매한 주문 건수
 FROM : olist_order_items_dataset 테이블
 WHERE : 상품 가격이 50달러 이상
 GROUP BY : 판매자별
 HAVING : 100건 이상
 ORDER BY : 주문건수 내림차순 정렬
*/
SELECT
  seller_id,
  COUNT(DISTINCT order_id) AS 'orders'
FROM
  olist_order_items_dataset
WHERE
  price >= 50
GROUP BY
  seller_id
HAVING
  orders >= 100
ORDER BY
  orders DESC

레스토랑의 일일 매출

[SolveSql daily-revenue]

/*
 SELECT : 요일, 요일 별 매출 총액
 FROM : tips 테이블
 GROUP BY : 요일별
 HAVING : 1000달러 이상 판매
 ORDER BY : 매출 총액 내림차순
*/
SELECT
  day,
  SUM(total_bill) AS 'revenue_daily'
FROM
  tips
GROUP BY
  day
HAVING
  revenue_daily >= 1000
ORDER BY
  revenue_daily DESC

버뮤다 삼각지대로 들어가버린 택배

[SolveSql shipment-in-bermuda]

/*
 SELECT : 배송 시작 날짜, 택배사에 도착했지만 고객에게 배송되지 않은 주문 건수
 FROM : olist_orders_dataset 테이블
 WHERE : 배송 시작 시각이 2017년 1월, 배송 완료 시각이 null
 GROUP BY : 배송 시작 시각별
 ORDER BY : 배송 시작일 오름차순 정렬
*/
SELECT
  DATE(order_delivered_carrier_date) AS 'delivered_carrier_date',
  COUNT(order_delivered_carrier_date) AS 'orders'
FROM
  olist_orders_dataset
WHERE
  order_delivered_carrier_date BETWEEN '2017-01-01' AND '2017-02-01'
  AND order_delivered_customer_date IS NULL
GROUP BY
  delivered_carrier_date
ORDER BY
  order_delivered_carrier_date

점검이 필요한 자전거 찾기

[SolveSql inspection-needed-bike]

/*
 SELECT : 자전거 ID
 FROM : rental_history 테이블
 WHERE : 2021년 1월
 GROUP BY : 자전거 ID별
 HAVING : 총 주행 거리가 50km 이상
*/
SELECT
  bike_id
FROM
  rental_history
WHERE
  DATE(rent_at) BETWEEN '2021-01-01' AND '2021-01-31'
GROUP BY
  bike_id
HAVING
  SUM(distance) >= 50000

레스토랑의 대목

[SolveSql high-season-of-restaurent]

/*
 SELECT : 모든 컬럼
 FROM : tips 테이블
 WHERE : 매출이 1500 달러인 요일
*/
SELECT
  *
FROM
  tips
WHERE
  day IN (
    SELECT
      day
    FROM
      tips
    GROUP BY
      day
    HAVING
      SUM(total_bill) >= 1500
  )

레스토랑의 요일별 VIP

[SolveSql restaurent-vip]

/*
 SELECT : 모든 컬럼
 FROM : tips 테이블
 WHERE : 요일별로 가장 높은 금액
*/
SELECT
  *
FROM
  tips
WHERE
  total_bill IN (
    SELECT
      MAX(total_bill)
    FROM
      tips
    GROUP BY
      day
  )

할부는 몇 개월로 해드릴까요

[SolveSql installment-month]

/*
 SELECT : 할부 개월 수, 주문 수, 최소 결제 금액, 최대 결제 금액, 평균 결제 금액
 FROM : olist_order_payments_dataset 테이블
 WHERE : 신용카드로 주문
 GROUP BY : 할부 개월 수 별
*/
SELECT
  payment_installments,
  COUNT(DISTINCT order_id) AS 'order_count',
  MIN(payment_value) AS 'min_value',
  MAX(payment_value) AS 'max_value',
  AVG(payment_value) AS 'avg_value'
FROM
  olist_order_payments_dataset
WHERE
  payment_type = 'credit_card'
GROUP BY
  payment_installments