요소 비교
연도와 비즈니스 종류에 따라 그루핑한 후 서점, 굿즈, 취미 및 게임 상점에 해당하는 값들의 연도, 종류, 매출의 합계를 살펴본다.
1
2
3
4
5
6
7
8
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;
남성과 여성의 옷가게의 매출을 살펴본다.
1
2
3
4
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;
연도별, 남성과 여성 옷 가게별로 매출 합계를 살펴본다.
1
2
3
4
5
6
7
8
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)를 추가해줄 경우 여성과 남성의 매출을 합한 값이 나타난다.
1
2
3
4
5
6
7
8
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;
연도별 여성 - 남성 의류 매출 차이와 남성 - 여성 의류 매출 차이에 대해 살펴본다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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;
위 결과에서 여성 의류 매출의 합계가 더 높은 걸 확인할 수 있다. 따라서 연도별로 여성 의류 매출에서 남성 의류 매출을 뺀 값을 보여주는 쿼리를 작성한다.
1
2
3
4
5
6
7
8
9
10
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
