트랜드 분석 - 요소 비교

연도와 비즈니스 종류에 따라 그루핑한 후 서점, 굿즈, 취미 및 게임 상점에 해당하는 값들의 연도, 종류, 매출의 합계를 살펴본다.

SELECT
	DATE_PART('year', sales_month) AS sales_year,
	kind_of_business,
	SUM(sales) AS sales
FROM retail_sales
WHERE kind_of_business IN ('Book stores', 'Sporting goods stores', 'Hobby, toy, and game stores')
GROUP BY DATE_PART('year', sales_month), kind_of_business
ORDER BY DATE_PART('year', sales_month) ASC, kind_of_business ASC;

남성과 여성의 옷가게의 매출을 살펴본다.

SELECT sales_month, kind_of_business, sales
FROM retail_sales
WHERE kind_of_business IN ('Men''s clothing stores', 'Women''s clothing stores')
ORDER BY sales_month ASC, kind_of_business ASC;

연도별, 남성과 여성 옷 가게별로 매출 합계를 살펴본다.

SELECT 
	DATE_PART('year', sales_month) AS sales_year,
	kind_of_business,
	SUM(sales) AS sales
FROM retail_sales
WHERE kind_of_business IN ('Men''s clothing stores', 'Women''s clothing stores')
GROUP BY DATE_PART('year', sales_month), kind_of_business
ORDER BY DATE_PART('year', sales_month) ASC, kind_of_business ASC;

연도별로 여성 옷가게일 경우의 매출합계와 남성 옷가게일 경우의 매출 합계를 계산한다. 아래의 쿼리에서 SELECT절에 SUM(sales)를 추가해줄 경우 여성과 남성의 매출을 합한 값이 나타난다.

SELECT
	DATE_PART('year', sales_month) AS sales_year,
	SUM(CASE WHEN kind_of_business='Women''s clothing stores' THEN sales END) AS women,
	SUM(CASE WHEN kind_of_business='Men''s clothing stores' THEN sales END) AS Men
FROM retail_sales
WHERE kind_of_business IN ('Men''s clothing stores', 'Women''s clothing stores')
GROUP BY DATE_PART('year', sales_month)
ORDER BY DATE_PART('year', sales_month) ASC;

연도별 여성 - 남성 의류 매출 차이와 남성 - 여성 의류 매출 차이에 대해 살펴본다.

SELECT
	sales_year,
	(women - men) AS women_minus_men,
	(men - women) AS men_minus_men
FROM
	(SELECT
		DATE_PART('year', sales_month) AS sales_year,
		SUM(CASE WHEN kind_of_business='Women''s clothing stores' THEN sales END) AS women,
		SUM(CASE WHEN kind_of_business='Men''s clothing stores' THEN sales END) AS men
	FROM retail_sales
	WHERE kind_of_business IN ('Men''s clothing stores', 'Women''s clothing stores')
		AND sales_month <= '2019-12-01'
	GROUP BY DATE_PART('year', sales_month)
	) AS sub
ORDER BY sales_year ASC;

위 결과에서 여성 의류 매출의 합계가 더 높은 걸 확인할 수 있다.
따라서 연도별로 여성 의류 매출에서 남성 의류 매출을 뺀 값을 보여주는 쿼리를 작성한다.

SELECT
	DATE_PART('year', sales_month) AS sales_year,
	SUM(CASE WHEN kind_of_business='Women''s clothing stores' THEN sales END)
	-
	SUM(CASE WHEN kind_of_business='Men''s clothing stores' THEN sales END) AS women_minus_men
FROM retail_sales
WHERE kind_of_business IN ('Men''s clothing stores', 'Women''s clothing stores')
	AND sales_month <= '2019-12-01'
GROUP BY DATE_PART('year', sales_month)
ORDER BY DATE_PART('year', sales_month) ASC