Compressed Mode

Compressed Mode

문제

1. 최빈값 찾아내는 SQL

2. 가장 많이 발생한 주문 빈도

3. 동률 일 때 item_count asc

 

풀이

select item_count as mode
from 
(SELECT *,
  rank() over (order by order_occurrences desc) as rn
FROM items_per_order
) sub1
where rn = 1
order by item_count asc

1️⃣ sub1: order_occurrences 높은 기준으로 rank

2️⃣ where: rn = 1인 경우

3️⃣ order by: item_count 오름차순 정렬

 

다른 풀이

1️⃣ max 활용

SELECT item_count AS mode
FROM items_per_order
WHERE order_occurrences = (
  SELECT MAX(order_occurrences) 
  FROM items_per_order
)
ORDER BY item_count;

1. Where: order_occurrences에서 최대로 많이 나타난 값 가져오기
2. order by: item_count로 정렬
3. 변수명 변경

2️⃣ Mode 활용

SELECT item_count AS mode
FROM items_per_order
WHERE order_occurrences = (
  SELECT MODE() WITHIN GROUP (ORDER BY order_occurrences DESC) 
  FROM items_per_order
)
ORDER BY item_count;

 

WITHIN GROUP

SELECT <aggregate_function> WITHIN GROUP (ORDER BY <sort_expression>) FROM <table>;

Understanding PostgreSQL WITHIN GROUP | Timescale