Table 분석

BRown·2023년 2월 6일

SQL

목록 보기
4/6

1. table 구성

  • show tables
    checkins : 오늘의 다짐
    courses : 웹개발 종합반, 앱개발 종합반 (ROW가 2개다)
    enrolleds
    enrolleds_detail : week 1에 1(current_order)번째 강의를 다 들었다(done = 1) cf. seen
    orders
    point_users
    test
    users

2. table 별 필드값 개수 및 해석

count()user_iddistinct(user_id)enrolled_iddistinct(enrolled_id)
orders286275--
users498498--
enrolleds341317341341
enrolleds_detail--21904324

  • 11명의 유저가 2번 결재(주문)했다.

    • select * from (select user_id, count(*) as cnt from orders group by user_id) a where cnt > 1
      (count(*) > 1 이라고 하면, count(*)을 필드명으로 인식하지 못함 -> alias 를 붙여줌)

  • users 테이블은 유저 아이디의 고유 정보를 나타낸다. (유저ID의 중복이 없다)

  • enrolleds(수강등록정보) 테이블은 enrolled 아이디의 고유 정보를 나타낸다.

  • enrolled 아이디는 유저(user_id) 당 1개 또는 2개일 수 있다. 왜냐하면 앱개발, 웹개발 모두 등록할 수 있으니까. (수강 등록 기준으로 enrolled_id 부여)

  • 24명의 유저만 앱개발과 웹개발 모두 수강등록했다.

    • select * from (select user_id, count(*) as cnt from enrolleds group by user_id) a where cnt = 2

  • 두 과목 모두 수강등록한 24명의 유저 중 11명만 두 과목 모두 결재했다.

    • with table1 as (
		select * from (
		 select user_id, count(*) as cnt_order from orders 
		 group by user_id 
		) a
		where cnt_order > 1
    	order by user_id 
), table2 as (
		select * from (
		 select user_id, count(*) as cnt_enrolled from enrolleds 
		 group by user_id 
		) b 
		where cnt_enrolled > 1
		order by user_id 
)
select * from table1 c
left join table2 d on c.user_id = d.user_id

  • 나머지 13명 중 3명만 두 과목 등록하고 1과목만 결재했다.

    • with table1 as (
	select * from (
		select user_id, count(*) as cnt_order from orders 
		group by user_id 
		) a
	where a.cnt_order = 1
), table2 as (
	select * from (
		select user_id, count(*) as cnt_enrolled from enrolleds 
		group by user_id 
		) b 
	where cnt_enrolled > 1
)
select * from table2 d
left join table1 c on d.user_id = c.user_id
where c.user_id is not NULL

  • 두 과목 모두 수강등록한 유저 중 나머지 10명은 한 번도 결재하지 않았다. 

    • with table1 as (
 	select * from (	
		select user_id, count(*) as cnt_enrolled from enrolleds e 
		group by user_id	
 	) a 
 	where cnt_enrolled = 2
), table2 as (
 	 select user_id, count(*) as cnt_order from orders o
 	 group by user_id
 )
select count(*) from table1 b 
left join table2 c on b.user_id = c.user_id 
where c.user_id is NULL

  • 결재를 한 사람은 모두 수강등록 했다. (그중에 2과목 모두 결재한 11명과, 2과목 등록하고 1과목만 결재한 3명이 포함되어 있다.)

    • with table1 as (
	  select * from (	
	   select user_id, count(*) as cnt_enrolled from enrolleds e 
	   group by user_id	
	 ) a 
), table2 as (
	 select * from (
 	  select user_id, count(*) as cnt_order from orders o
 	  group by user_id
	 ) b
)
select count(*) from table1 c 
inner join table2 d on c.user_id = d.user_id
    • select count(*) from (
 		select user_id, count(*) as cnt_order from orders o
 		group by user_id
) b

  • 그런데, 수강등록 하고 결재하지 않은 사람도 있다.
    : 그중에서 수강등록한 두과목 모두 결재하지 않은 사람은 10명이고 한과목만 수강등록하고 결재하지 않은 사람은 32명이다. (orders 테이블에 아에 user_id가 없는 사람 = 결재한 적인 없는 사람 )

    • with table1 as (
 	    select user_id, count(*) as cnt_enrolled from enrolleds e 
		group by user_id	
), table2 as (
 		select user_id, count(*) as cnt_order from orders o
 		group by user_id
)
select count(*) from table1 c 
left join table2 d on c.user_id = d.user_id 
where d.user_id is NULL
    • select count(*) from(
 	select user_id, count(*) as cnt_enrolled from enrolleds 
	group by user_id
) a

  • 참고) 수강 등록한 개수 및 결재한 개수는 1아니면 2 뿐이다. 앱개발, 웹개발 두개 뿐이니까

  • enrolleds_detail에 포함되지 않은 enrolled_id가 17개 있다.
    : 17개 enrolled_id = 16개 user_id

    • 	with table1 as (
			select * from enrolleds e 
	), table2 as (
			select distinct(enrolled_id) from enrolleds_detail ed 
	)
	select distinct(a.user_id) from table1 a
	left join table2 b on a.enrolled_id = b.enrolled_id 
	where b.enrolled_id is NULL

  • 이 16개 user_id는 결재를 안한건가? or 결재하고 안듣는 건가?
    참고) done이랑 seen이 모두 0인 enrolled_id는 없음 (enrolleds_detail에는 강의를 실제로 들은 사람만 있음)

    • 결재하고 안듣는 사람 2명 : 2638550d, db34dd24
      with table2 as (
	with table1 as (
			select distinct(enrolled_id) from enrolleds_detail ed
	)
	select distinct(e.user_id) from enrolleds e
	left join table1 a on e.enrolled_id = a.enrolled_id 
	where a.enrolled_id is NULL 
)
select * from orders o
left join table2 b on o.user_id = b.user_id 
where b.user_id is not NULL
    • 결재안하고 안듣는 사람 14명
    • 등록하고 결재를 안한 사람 42명 중 결재안하고 안듣는 14명이 포함되어 있다. 
      with table3 as (
		with table1 as (
	   		select distinct(enrolled_id) from enrolleds_detail ed
		)
		select distinct(e.user_id) from enrolleds e
		left join table1 a on e.enrolled_id = a.enrolled_id 
		where a.enrolled_id is NULL 
), table4 as (
		select distinct(e.user_id) from enrolleds e
		left join orders o on e.user_id = o.user_id 
		where o.user_id is NULL 
)
select * from table4 d
left join table3 c on d.user_id = c.user_id
where c.user_id is not NULL
    • 나머지 28명은 결재 안하고 듣는 사람
      : orders에는 없는데, enrolleds_detail에 있는 사람 29명 중 1명은 두과목 중 한과목은 결재 안하고 안보고 , 다른 한과목은 결재 안하고 보고 (92afe8fd)
      with table3 as (
	with table1 as (
	   		select distinct(enrolled_id) from enrolleds_detail ed
	)
	select distinct(e.user_id) from enrolleds e
	left join table1 a on e.enrolled_id = a.enrolled_id 
	where a.enrolled_id is not NULL 
), table4 as (
	select distinct(e.user_id) from enrolleds e
	left join orders o on e.user_id = o.user_id 
	where o.user_id is NULL 
)
select c.user_id from table3 c
left join table4 d on c.user_id = d.user_id
where d.user_id is not NULL

3. left join 후 필드값 개수 및 해석

count()left join ordersleft join usersleft join enrolledsleft join enrolleds_detail
orders-286311-
users509-520-
enrolleds363341-21921
enrolleds_detail--21904-

  • 결재한 user_id 에서 enrolled_id가 두개인 user_id 총 25개 추가 (311-286)

  • users 테이블의 user_id 에서 유저 당 추가 결재한 횟수만큼 추가해서 총 11개 추가(509-498=286-275=두과목 모두 결재한 유저 수)
    (users 테이블은 user_id가 고유한 값)

  • users 테이블의 user_id 에서 두과목 모두 수강등록한 22명 추가(520-498)

    • users 테이블에는 수강등록 안한 유저 187명 포함 
        select count(*) from users u
   left join enrolleds e on u.user_id = e.user_id 
   where e.enrolled_id is NULL
    • 왜 24명이 아니고 22명일까? : users에 없는 user_id 가 enrolleds에 2개 있다.
         select * from (
		       select u.user_id, count(*) as cnt from users u
		       left join enrolleds e on u.user_id = e.user_id
		       group by u.user_id
    ) a
    where cnt = 2
    order by user_id;  
   ```
 
      	 select * from (
					select user_id, count(*) as cnt from enrolleds e 
					group by user_id
	 ) b 
	 where cnt = 2
	 order by user_id;
      범인은.. 5f0ae408765dae0006002813, 5f11b9fe73570c0009f2498d !!!!!
profile
BRown
이전 포스트

SQL 3주차

다음 포스트

SQL 4주차

0개의 댓글