04/22 9일차

4.상품/주문관리
주문된 상품별 판매량, 판매금액 출력
조건:판매량이 높은 순으로 정렬
상품번호 상품명 총판매량 총판매금액

 

SELECT g.good_seq, g.good_name, SUM(og.order_price), SUM(og.order_amount)
FROM goods g, orders_goods og
WHERE g.good_seq = og.good_seq
GROUP BY g.good_seq, g.good_name
ORDER BY SUM(og.order_price) DESC;

 

5. 사용자별 구매 통계
회원아이디  총구매횟수   총구매금액
조건1 : 구매금액이 높은 순 출력

SELECT s.user_id, COUNT(o.order_code), SUM(o.tot_price)
FROM users s, oreder o
WHERE s.user_seq = o.user_seq
GROUP BY s.user_id
ORDER BY SUM(o.tot_price) DESC;

 

6. 휴면회원 통계
구매실적이 전혀 없는 회원 목록 출력

--minus
SELECT user_id, user_name FROM users; // 전체 회원
SELECT DISTINCT u.user_id, u.user_name FROM users u, orders o WHERE u.user_seq = o.user_seq; // 물건 주문한 고객

//전체회원 - 물건 주문한 고객
(SELECT user_id, user_name FROM users)
	MINUS
(SELECT DISTINCT u.user_id, u.user_name FROM users u, orders o WHERE u.user_seq = o.user_seq);

--not in
SELECT user_id, user_name
FROM users
WHERE user_seq NOT IN (SELECT DISTINCT u.user_seq FROM users u, orders o WHERE u.user_seq = o.user_seq);

 

7. 전체 회원 목록 중 휴먼 회원이 차지하는 비율?
조건1 : 관리자 제외
조건2: 휴먼회원은 구매 실적이 전혀 없는 회원
회원수   휴먼회원비율

7. 전체 회원 목록 중 휴먼 회원이 차지하는 비율?
조건1 : 관리자 제외
조건2: 휴먼회원은 구매 실적이 전혀 없는 회원
회원수   휴먼회원비율

SELECT COUNT(user_seq)
FROM users 
WHERE user_seq NOT IN (SELECT DISTINCT u.user_seq
					   FROM users u, orders o
                       WHERE u.user_seq = o.user_seq)// 휴면회원 수
                       
SELECT COUNT(user_seq)
FROM users
WHERE user_gubun = 'u'; // 일반 회원 수

SELECT cnt1 ||'/'\\ cnt2, cnt1/cnt2 * 100 || '%'
FROM (SELECT COUNT(user_seq)
	  FROM users 
	  WHERE user_seq NOT IN (SELECT DISTINCT u.user_seq
					   		 FROM users u, orders o
                       	     WHERE u.user_seq = o.user_seq)) as cnt1, (SELECT COUNT(user_seq)
															           FROM users
												                       WHERE user_gubun = 'u') as cnt2

 

8. 각 회원별로 매니저-회원 관계를 출력하시오
조건1: 관리자 제외
조건2: 매니저번호 오름차순 회원번호 오름차순 정렬

SELECT m.user_id, u.user_id
FROM users u, users m
WHERE u.user_mgr = m.user_id
	  AND u.user_gubun = 'u';
ORDER BY m.user_id ASC, u.user_id ASC;

 

9. 주문/상품/업체 대시보드 현황판

총주문수량 총주문금액  총회원수  총업체수 총상품수
58         1025000     5         7        12

    AMT      PRICE       UCNT       CCNT       GCNT
---------- ---------- ---------- ---------- ----------
      48     244000          5          7         10

SELECT
(SELECT SUM(order_amount)FROM orders_goods) as amt,
(SELECT SUM(order_price) FROM orders_goods) as price,
(SELECT COUNT(user_id) FROM users) as UCNT,
(SELECT COUNT(com_seq) FROM company) as CCNT,
(SELECT COUNT(good_seq) FROM goods) as GCNT
FROM dual;

 

10.월별 판매 실적....
1월   2월   3월   4월  
20000  12000  50000

SELECT
(SELECT SUM(DECODE(TO_CHAR(order_date,'mm'),'01',tot_price,0)) FROM orders) as m01,
(SELECT SUM(DECODE(TO_CHAR(order_date,'mm'),'02',tot_price,0)) FROM orders) as m02,
(SELECT SUM(DECODE(TO_CHAR(order_date,'mm'),'03',tot_price,0)) FROM orders) as m03,
(SELECT SUM(DECODE(TO_CHAR(order_date,'mm'),'04',tot_price,0)) FROM orders) as m04
FROM dual;

 

--kospi table

 

 -------------------------------------------------------------
 종목 확인
 -------------------------------------------------------------
TICKER   TNAME
096770 SK이노베이션
005490 POSCO홀딩스

SELECT DISTINCT 코드, 그룹명
FROM kospi;

SELECT 코드, 그룹명
FROM kospi
GROUP BY 코드, 그룹명;

 

 -------------------------------------------------------------
 투자 기간 확인
 -------------------------------------------------------------
NDAY
--
18

SELECT TRUNK((MAX(날짜)-MIN(날짜))/30)
FROM kospi;

 

 -------------------------------------------------------------
 각 종목별 최대종가 , 최소종가 
 -------------------------------------------------------------
TICKER TNAME MAXPRICE MINPRICE
096770 SK이노베이션 216000 143500
005490 POSCO홀딩스 388000 211000

SELECT 코드, 그룹명, MAX(종가) as 최대종가, MIN(종가) as 최소종가
FROM kospi
GROUP BY 코드, 그룹명;

 

 -------------------------------------------------------------
 각 종목별 최근 종가
 -------------------------------------------------------------
TICKER TNAME  CPRICE
096770 SK이노베이션 158500
005490 POSCO홀딩스 388000
068270 셀트리온 152900
035720 카카오 49100
005930 삼성전자 72200
086520 에코프로 754000

SELECT 종목명, MAX(날짜)
FROM kospi
GROUP BY 종목명; // 종목별 최근 거래된 날짜

SELECT 코드, 종목명, 종가
FROM kospi
WHERE (종목명,날짜) IN (SELECT 종목명, MAX(날짜)
					    FROM kospi
						GROUP BY 종목명);

 

 -------------------------------------------------
  각 종목별 상승, 하락 횟수
상승 : 시가 < 종가 
하락 : 시가 > 종가 
 -------------------------------------------------
TNAME    GUBUN  CNT
POSCO홀딩스 상승 11
POSCO홀딩스 하락 7
SK이노베이션 상승 8
SK이노베이션 하락 10
삼성전자 상승 9
삼성전자 하락 9

(SELECT 종목명, '상승' as gubun, COUNT(1) FROM kospi WHERE 종가>시가 GROUP BY 종목명)
UNION
(SELECT 종목명, '상승' as gubun, COUNT(1) FROM kospi WHERE 시가>종가 GROUP BY 종목명);

 

 -------------------------------------------------
 카카오 월별 거래량 (2022년도)
 -------------------------------------------------
MM    VOL
01 67362020
02 49646154
03 48471677
04 31610295
05 35611165
06 38118975
07 30423526

SELECT TO_CHAR(날짜,'MM'), SUM(거래량)
FROM kospi
WHERE 종목명 = '카카오'
  AND 날짜 BETWEEN '2022/01/01' AND '2022/12/31'
GROUP BY TO_CHAR(날짜,'MM');

 

 -------------------------------------------------
 카카오 월별 거래량 (2022년도) 중 최대 거래량
 -------------------------------------------------
 MM  VOL
11 71561732

SELECT MAX(거래량)
FROM ( 

SELECT TO_CHAR(날짜,'MM'), 거래량
FROM kospi
WHERE 종목명 = '카카오'
  AND 날짜 BETWEEN '2022/01/01' AND '2022/12/31'
GROUP BY TO_CHAR(날짜,'MM')

)

SELECT TO_CHAR(날짜,'MM') as MM, 거래량
FROM kospi
WHERE (종목명, 거래량) = (SELECT 종목명, max(거래량)
                          FROM kospi
                          WHERE 종목명 = '카카오'
                          AND 날짜 BETWEEN '2022/01/01' AND '2022/12/31'
                          GROUP BY 종목명);