GROUP BY와 CTAS - 4

Group by

데이터의 특정 열(들)을 기준으로 그룹화하는 것을 의미

예시 SQL문)

SELECT product_id
FROM sales
GROUP BY product_id;

 

Aggregate

그룹화된 데이터에 대해 특정 연산을 수행하여 단일 값으로 요약하는 것을 의미

예시 SQL문)

SELECT product_id, SUM(quantity) as total_sales
FROM sales
GROUP BY product_id;

SELECT product_id, COUNT(*) as sale_count
FROM sales
GROUP BY product_id;

SELECT product_id, AVG(quantity) as average_sales
FROM sales
GROUP BY product_id;

 

TO_CHAR

 숫자나 날짜 데이터를 문자열 형태로 변환할 때 사용

예시 SQL문)

SELECT TO_CHAR(some_date_column, 'YYYY-MM-DD') FROM some_table;

SELECT TO_CHAR(some_number_column, '999.99') FROM some_table;

 

CTAS & CTE

1) CTAS

새로운 테이블을 생성하면서 기존 테이블에서 선택된 데이터를 채워 넣는다.

CREATE TABLE new_table AS 
SELECT column1, column2, column3
FROM existing_table
WHERE condition;

 

2) CTE

임시 결과 세트를 생성하는 데 사용

WITH sales_cte (product_id, total_sales) AS 
(
  SELECT product_id, SUM(quantity)
  FROM sales
  GROUP BY product_id
)
SELECT * FROM sales_cte
WHERE total_sales > 100;