시각화

250729_태블로 기초, SQL(윈도우 함수 관련 문제), 파이썬(인덱스 관련 문제)

baektree 2025. 7. 29. 20:44

요약/나만의 인사이트

  • SQL ⇒ 각 가격에 판매량을 곱해서 더한 값을 전체 판매량으로 나눈 것으로 가중평균이라고 한다. 가중평균은 AVG()함수를 쓸 수 없다. 왜냐하면 AVG()는 단순히 모든수를 더한값에 개수로 나눠주기 때문이다.
  • 가중평균은 다음과 같다. SUM(가격 × 판매량) / SUM(판매량) 즉 각 상품별 판매량을 고려
  • AVG는 다음과 같다. AVG(price) (100+200) /2
  • 윈도우 함수는 행마다 도는 함수이다.
  • rank over() row_number() 모두 순위를 구하는 윈도우 함수이다.
    • rank over()은 공동순위면 같은 랭킹 번호 부여 1 2 2 4
    • row_number()은 공동순위 일자라도 무조건 고유 랭킹 번호 부여한다. 1 2 3 4 이때 랭킹순서는 먼저 등장한 순서이다.

새롭게 알게 된 것

  • 태블로
    • 이중 축 사용시
      • 축 이원화 : 두가지 모두 증감 추세 보고 싶을 때
      • 축 동기화 : 두가지 비교해서 볼 때
    • 행 수준 계산 vs 집계 수준 계산행 수준 계산 집계 수준 계산 
      개념 행 마다 계산 집계 된 후 계산
      특징 세부데이터 기준으로 값이 계산됨 SUM, AVG, MIN, MAX 같은 집계 함수 사용
      용도 행별 파생 변수 생성 전체 평균, 총합 대비 비율 계산
      예시 매출 * 0.1 SUM([매출]) /SUM([전체매출])
    • 매개변수
      • 대시보드 상에서 사용자가 선택하면 그 값이 계산식, 필터, 참조선 등에 반영되어 뷰가 동적으로 변함!
      • 따라서 별도로 계산식이나 필터와 연결해야 작동
      • 매개변수 (차원) → 계산식 (측정값) 으로
        • e.g.
        1. 참조선이라는 매개변수를 만듦. 값은 500으로 (분류기준)
        2. 그것과 관련된 SUM([C_측정값 선택])>=[P_참조선] C_참조선 색상 강조 측정값을 만들면 (계산식)
        3. 참조선(500)보다 큰 값과 작은 값이 다른 색상으로 표시 (연결)
  • SQL 복습
    1. CTE
      Common Table Expression 으로 일시적으로 결과를 저장해서 메인 쿼리에서 다시 쓰기 위한 용도
      복잡한 서브쿼리보다 가독성이 좋음.
      WITH temp AS( SELECT … ) 형식임
      하나의 SQL문 내에서 임시 결과 집합을 정의하고, 메인 쿼리에서 이를 재사용할 수 있게 해준다.


    2. 상위 1%고객을 필터링 하기 위한 윈도우 함수
      윈도우 함수
      • 행 단위로 연산을 하면서도, 다른 행들의 값을 함께 고려할 수 있게 해주는 함수
      • 일반적인 GROUP BY와 달리, 집계를 하면서도 원래의 행을 유지할 수 있다.
        RANK() 동일 값이 있으면 동일 순위, 다음 순위 건너뜀 1 2 2 4
        ROW_NUMBER() 순위 1, 2, 3 …. 무조건 고유 순위 부여, 동일한 값이 나오면 나온순서대로 1 2 3 4
        NTILE(n) 전체 데이터를 n개의 그룹으로 나눔
        SUM() OVER() 윈도우 전체에서 누적합을 구함
        ROW_NUMBER() 정렬 기준에 따라 행마다 고유 번호 부여
        DENSE_RANK() 동일 값에 같은 순위 부여 (건너뛰지 않음)
        SUM(), AVG(), MAX(), MIN() 누적합, 이동 평균 등 가능
        LAG(), LEAD() 이전 또는 다음 행의 값 가져오기 
        <윈도우 함수> OVER (
  PARTITION BY <기준 컬럼>
  ORDER BY <정렬 기준>
  ROWS BETWEEN ... AND ...
)
        • PARTITION BY: 그룹을 나눠서 계산 (선택사항)
        • ORDER BY: 정렬 기준 (예: 시간 순으로 정렬)
        • ROWS BETWEEN: 윈도우의 범위를 지정 (선택사항)
        • 관련 문제 - 급여 상위 10%의 평균 나이 
          • SELECT AVG(age) as avg_age #평균을 구해라
FROM (
		SELECT *,
					NTILE(10) OVER (ORDER BY salary DESC) AS decile #급여 내림차순으로 10등분 하는데
		FROM employees
) ranked 
WHERE decile = 1; #상위 10%인 것만 가져와서 
​
             
        • 관련 문제 - 제품별로 가장 최근 주문을 제외한 모든 주문을 구하기 
          • SELECT order_id, product_id, order_date
FROM (
	SELECT *,
				ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY order_date DESC) #제품 아이디로 나눠서 주문날짜 최신순부터 랭킹을 매긴다. 동일값이 나와도 나온순서대로 고유 랭킹번호 부여
	FROM orders 
) ranked 
WHERE rn > 1; #랭킹이 1이 아닌것만 필터링

특히 어려웠던 것

  • 파이썬 문제풀이
    • 문제 카드 뭉치
      • 카드더미, 인덱스
    • 요구사항
      • 카드더미에서 순서대로 꺼낸다.
      • 그것이 goal과 맞는지 확인한다.
      • 맞으면 yes, 아니면 no이다.
    #요구사항을 해결하기위한 방법
#카드더미에서 순서대로 꺼낸다. -> 순서대로 꺼내는 것을 확인하깅 위해 각각 위치 변수를 지정
#그것이 goal과 맞는지 확인한다. -> goal에서 하나씩 꺼내서 비교, 그리고 맞으면 다음 위치로 이동해야 하니까 1씩 누적
# -> 이때 카드1과 카드2는 다른 위치 정보를 가지고 있어야 함.
#맞으면 yes, 아니면 no

def solution(cards1, cards2, goal):
    index1, index2 = 0, 0 #카드더미에서 지금 어디위치인지 나타내는 포인터, 할때마다 1씩 누적해서 다음 이동

    for word in goal: #goal에 있는 모든 단어를 하나씪 순회
      if index1 < len(cards1) and cards1[index1] == word: #len(cards1)이 3이면 인덱스 0, 1, 2 만 접근 즉 항상 길이보다 작아야 인덱스에 접근 가능함.
        index1 += 1 #맞으면 인덱스 하나 추가
      elif index2 < len(cards2) and cards2[index2] == word:
        index2 += 1 #인덱스는 카드더미마다 다르게 가져가야 처음부터 순차적으로 접근 가능
      else:
        return "No" #맞는게 없다면 그냥 no 뱉기
    return "Yes"
    • 문제 풀이
      • index1, index2 = 0, 0
      • 각 카드 뭉치에서 지금 어디까지 사용했는지 나타내는 포인터 , 단어를 하나 꺼낼 때마다 다음으로 이동하도록 함
      • index1 < len(cards1)
      • 인덱스 오류를 방지하기 위한 조건
      • word =cards[index1]과 같다면 해당카드 사용
      • index += 1 카드사용했으니 다음 카드로 넘어가기 위해 포인터 증가
  • SQL 문제풀이
    • 문제 Average Selling Price
    • 정답
    # Write your MySQL query statement below
# 각 제품의 평균 가격을 구하기(소수점 둘째자리까지 반올림)
# 판매된 제품이 없는 경우, 평균가격은 0으로 가정

# 판매날짜가 가격표 날짜에 포함되어 있는 것으로 조인하기
# 행끼리 곱해서 더하기
# 만약 판매리스트가 없다면 0으로 하기 (LEFT JOIN하고 NULL값에 0을 넣어야겠는걸?)

SELECT P.product_id, 
        ROUND(IFNULL(SUM(P.price*U.units)/ SUM(U.units), 0),2) AS average_price
FROM Prices P LEFT JOIN UnitsSold U 
ON P.product_id = U.product_id
AND U.purchase_date BETWEEN P.start_date AND P.end_date
GROUP BY P.product_id;
    • 문제 구조화 / 해당하는 쿼리
      • 각 제품의 평균 가격을 구하기(소수점 둘째자리까지 반올림) → gorup by, round(값, 2)
      • 판매이력이 없는 경우, 평균가격은 0으로 가정 → 가격표를 기준으로 left join(판매 이력이 없는 것도 보이게) product_id 기준으로 묶기, 또한 해당 시기에 맞는 가격과 매칭되게 판매일이 start, end안에 포함되어야 있어야 함
      • → ifnull(null값이면 , 0으로)
      • 가격은 변동하고 있음 같은 제품이라도 가격테이블표에 따라 가격이 달라짐 (어떤 가격으로 계산되었는지 날짜를 잘 봐야함)
    • 코드 한번 더 연습
    • SELECT 
	P.product_id,
	ROUND(IFNULL(SUM(P.price * U.units) / SUM(U.units), 0), 2) AS average_price #각 가격에 판매량을 곱해서 평균을 내야 하기 때문에 AVG()는 사용못함
FROM 
	Prices P 
LEFT JOIN 
	UnitsSold U
	ON P.product_id = U.product_id
	AND U.purchase_date BETWEEN P.start_date AND P.end_date #판매일이 가격적용시점에 알맞게 매칭되도록 함

GROUP BY P.product_id

다음에 적용해 볼 것

  • [ ] 위클리 파이썬 풀어봐야지~

'시각화' 카테고리의 다른 글

250730_태블로 기초, 파이썬(pop())  (5) 2025.07.30

'시각화'의 다른글

  • 현재글250729_태블로 기초, SQL(윈도우 함수 관련 문제), 파이썬(인덱스 관련 문제)

관련글

티스토리툴바