CodeKata

SQL

60. 년, 월, 성별 별 상품 구매 회원 수 구하기

• 작성 쿼리

SELECT
  YEAR(os.sales_date) AS "YEAR"
  , MONTH(os.sales_date) AS "MONTH"
  , ui.gender
  , COUNT(DISTINCT os.user_id) AS "USERS"
FROM
  online_sale os
  LEFT JOIN user_info ui
  USING(user_id)
WHERE
  ui.gender IS NOT NULL
GROUP BY
  1, 2, 3
ORDER BY
  1, 2, 3
;

※ 숫자와 문자의 정렬 기준이 다르니 SUBSTR로 년, 월 꺼내면 곤란(문자는 1, 10, 11, 12, 2 순으로 정렬임) → DATE_FORMAT은 문자열이지만 날짜 형태로 추출하니까 괜찮음(단, DATE_FORMAT의 경우 %m으로 해야 맞고 %c로 하면 틀림)

참고할 만한 내용

• WITH문 이용
  • 추가: gender가 null인 것을 먼저 유저 테이블에서 제외한 뒤에, inner join을 하는 게 데이터 연산이 빠르게 나온다고 함

• ON절 : 필터링 후 조인
  • ONLINE_SALE.USERID = USER_INFO.USERID AND GENDER IS NOT NULL 조건을 만족하는 데이터만 JOIN하여 보다 적은 데이터들만 가져오게 됩니다.
• WHERE절 : 조인 후 마지막에 필터링
  • 데이터가 많을수록 필터링 시간이 길어집니다.
  • 그래서 JOIN을 효과적으로 하기 위해서 ON절에 조건을 걸 수 있다면 WHERE 대신에 ON절에 하는게 더 효율적인 쿼리가 됩니다.
• JOIN하는 테이블이 10개정도로 엄청 많다고 가정할 때 ON절을 적절히 사용하지 않으면 엄청나게 방대한 데이터를 WHERE문으로 처리하게 됩니다.

WITH INFO_TABLE AS (
  SELECT 
    YEAR(a.SALES_DATE) AS SALES_YEAR
    , MONTH(a.SALES_DATE) AS SALES_MONTH
    , b.GENDER
    , a.USER_ID
  FROM 
    ONLINE_SALE a 
    JOIN USER_INFO b
    ON a.USER_ID = b.USER_ID
    AND b.GENDER IS NOT NULL
)
SELECT
  SALES_YEAR AS "YEAR"
  , SALES_MONTH AS "MONTH"
  , GENDER
  , COUNT(DISTINCT USER_ID) AS USERS
FROM
  INFO_TABLE
GROUP BY
  SALES_YEAR
  , SALES_MONTH
  , GENDER
ORDER BY
  SALES_YEAR ASC
  , SALES_MONTH ASC
  , GENDER ASC
;

• HAVING

# 회원 정보 USER_INFO, 온라인 상품 판매 정보 ONLINE_SALE
# DISTINCT를 해야 하는 이유는 상품을 구매한 회원수이기 때문인데, ONLINE_SALE 테이블에는 중복된 유저가 존재하기 때문에 중복을 제거해야 한다.
SELECT YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH,
       GENDER, COUNT(DISTINCT S.USER_ID) AS USERS
FROM ONLINE_SALE AS S
JOIN USER_INFO AS I
ON S.USER_ID = I.USER_ID
GROUP BY 1, 2, 3
HAVING GENDER IS NOT NULL
ORDER BY 1, 2, 3;

61. 서울에 위치한 식당 목록 출력하기

• 작성 쿼리

SELECT
  ri.rest_id
  , ri.rest_name
  , ri.food_type
  , ri.favorites
  , ri.address
  , ROUND(AVG(rr.review_score),2) AS score
FROM
  rest_info ri 
  INNER JOIN rest_review rr 
  ON ri.rest_id = rr.rest_id
WHERE
  ri.address LIKE '서울%'
GROUP BY
  ri.rest_id
ORDER BY
  AVG(rr.review_score) DESC
  , ri.favorites DESC
;

SELECT
  *
FROM (
  SELECT
    rest_id
    , rest_name
    , food_type
    , favorites
    , address
  FROM
    rest_info
  WHERE address LIKE '서울%'
  ) AS a
  JOIN (
    SELECT
      rest_id
      , ROUND(AVG(review_score), 2) AS SCORE
    FROM
      rest_review
    GROUP BY
      rest_id
  ) AS b
  USING(rest_id) -- 잊지 말고 꼭 쓰기!(ON도 됨)
ORDER BY
  score DESC
  , favorites DESC
;

참고할 만한 풀이

• WITH

WITH temp as (
  SELECT
    REST_ID
    , ROUND(AVG(REVIEW_SCORE),2) as SCORE
  FROM
    REST_REVIEW 
  GROUP BY
    REST_ID
)
SELECT
  a.REST_ID
  , a.REST_NAME
  , a.FOOD_TYPE
  , a.FAVORITES
  , a.ADDRESS
  , b.SCORE
FROM
  REST_INFO a 
  LEFT JOIN temp b 
  ON(a.REST_ID = b.REST_ID)
WHERE
  a.ADDRESS LIKE '서울%'
  AND b.rest_id IS NOT NULL -- LEFT JOIN 쓸 거면 이 조건 추가해야 정답으로 처리됨
ORDER BY
  b.SCORE DESC
  , a.FAVORITES DESC
;

Python

13. 자릿수 더하기

• 작성 코드

def solution(n):
    answer = 0

    for i in str(n):
        answer += int(i)

    return answer

참고할 만한 풀이

• 재귀구조

def sum_digit(number):
    if number < 10:
        return number
    return number%10 + sum_digit(number//10)

추가 설명:

• 321%10은 123을 10으로 나눈 나머지 3을 반환합니다. 123//10은 123을 10으로 나눈 몫 12를 반환합니다. 따라서 12를 다시 함수에 넣고 돌리겠죠. (return 123%10 + sum_disgit(123//10) -> return 3 + sum_digit(12)).2번째 돌릴땐 return에는 이전에 반환한 3 에다가 + 12%10 + sum_digit(12//10). 즉 2번째에는 return 3+2+sum_digit(1)이 됩니다. 3번째에는 if문에서 1이 10보다 작으므로 return 1을 해주고 최종적으로 return 3+2+1이 됩니다.
• 'num % 10'는 첫번째 자리를 구해주고, '함수(num // 10)'는 계속 나눠주면서 분열하여 두번째, 세번째... 자리 숫자를 계속 + 할 수 있게 해줌
  123 % 10 = 3, (함수(123 // 10) = 12 ≒ 2 + 함수(12 // 10)
• Base case에 10 미만인 자연수일때는 그대로 리턴하고 Recursive method에는 각각의 자릿수를 10으로 나눈 나머지를 이용해 더하는 방식

• list sum

def sum_digit(number):
    return sum([int(i) for i in str(number)])

• map(f, iterable)
  • 각 요소가 함수 f에 의해 수행된 결과를 묶어서 리턴하는 함수
  • str(number)각 인덱스에 int()함수를 수행해서 변환 후 sum()

def sum_digit(number):
    return sum(map(int, str(number)))

데이터 리터러시

1-4 지표 설정

데이터 분석 파이썬 종합반

4주차
5주차