// postgres 계정(수퍼유저)에서 사용자 생성(movie)
sudo -u postgres psql // postgres 계정으로 psql 실행
또는 아래 방식으로
su - postgres
psql // Linux postgres 계정에서 psql을 실행하면 수퍼유저로 로그인
postgres=#CREATE USER movie PASSWORD 'futuresoft#00'; // 수퍼유저에서 사용자 계정 movie 생성
postgres=#ALTER ROLE movie superuser createdb createrole; // ALTER ROLE로 사용자에 권한 부여
postgres=#\du
// Database 생성(postgres 계정 혹은 createdb 권한이 있는 사용자 계정)
CREATE DATABASE movielens WITH OWNER movie; // 수퍼유저에서 movie를 소유주 movielenst 데이터베이스 생성
// 새로 생성한 계정(movie)과 데이터베이스(movielens)로 다시 접속
psql -h localhost -p 5432 -U movie -d movielens
// Movielens Table Schema and Sample Data Load
movielens-postgres.sql // 영화 및 평점관련 테이블 생성 후 샘플 데이터 insert
export PGPASSWORD=password; // hadoop 계정에 대한 암호를 환경변수에 설정
psql -h localhost -p 5432 -U hadoop -d hadooptest < movielens-postgres.sql
(혹은 psql 로그인후 postgres=#\i movielens-postgres.sql 로 sql 파일 실행)
genre(영화분류)
movie(영화정보)
muser(고객정보)
occupation(직업정보)
movierating(영화평점)
moviegenre(영화장르)
// Schema와 Sample Data에 일부 내용 보완
ALTER TABLE user RENAME TO muser; // user 테이블명을 muser로 변경
ALTER TABLE muser ADD COLUMN name VARCHAR(40) NOT NULL DEFAULT ' '; // 이름 필드 추가
UPDATE muser SET name = '고객' || text(id); // 이름 값 설정
UPDATE muser SET age = 17 WHERE age < 18; // 나이 설정 안된 회원 정보 임의 설정
UPDATE movie SET year = 2001 WHERE year = 0; // 상영년도 설정 안된 데이터 값 임의 설정
COMMENT ON TABLE genre IS '영화분류';
COMMENT ON TABLE movie IS '영화정보';
COMMENT ON TABLE moviegenre IS '영화장르';
COMMENT ON TABLE movierating IS '영화평점';
COMMENT ON TABLE muser IS '고객정보';
COMMENT ON TABLE occupation IS '직업분류';
// 기본 Query 예제
SELECT * FROM genre; // 결과에 모든 필드를 표시, Default 정렬 순서는 PK 순서
SELECT COUNT(*) FROM genre; // 전체 레코드의 갯수 출력
SELECT COUNT(name) FROM genre; // 특정 필드값이 NULL이 아닌 레코드 갯수 출력
SELECT id AS 분류ID, name AS 분류명 FROM genre; // 필드명을 임으로 변경해서 표시(default는 필드정의 명칭)
SELECT name FROM genre; // 결과에 name 필드만 표시
SELECT substr(name, 1, 8) FROM genre;
SELECT substr(name, 3) FROM genre;
SELECT name FROM genre WHERE substr(name, 1, 2) = 'Th'; // 문자의 일부를 완전일치 검색
SELECT name FROM genre WHERE substr(name, 1, 2) LIKE 'Th%'; // Th 로 시작하는 레코드 출력
SELECT id, name FROM genre ORDER BY name ASC; // Ascending Order
SELECT id, name FROM genre ORDER BY name DESC; // Descending Order
SELECT id, name FROM genre WHERE id =1;
SELECT id, name FROM genre WHERE id =1 AND name = 'Action'; // 모든 조건이 만족하는 레코드 출력
SELECT id, name FROM genre WHERE id =1 OR name = 'Comedy'; // 어느 조건이건 만족하는 레코드 출력
SELECT id, name FROM genre WHERE id =1 AND name = 'Comedy';
SELECT id, name FROM genre WHERE name LIKE 'A%' ORDER BY name ASC; // A로 시작하는 레코드 출력
SELECT id, name FROM genre WHERE name LIKE '%e' ORDER BY name ASC; // e로 끝나는 레코드 출력
SELECT id, name FROM genre WHERE name LIKE '%me%' ORDER BY name ASC; // me가 포함된 레코드 출력
SELECT id, name, year FROM movie LIMIT 10; // 결과중 10건만 출력
SELECT id, name, year FROM movie WHERE year = 2001; // 숫자 필드 질의
SELECT id, name, year FROM movie WHERE name = 'Jumanji'; // 문자 필드 질의
SELECT id, name, year FROM movie WHERE year BETWEEN 1995 AND 1998; // 특정값 사이 결과 출력
SELECT id, name, year FROM movie WHERE year >= 2000; // 특정 값 이상 결과 출력
SELECT DISTINCT year FROM movie ORDER BY year DESC; // 특정 필드에서 고유한 값의 목록 출력
SELECT COUNT(DISTINCT year) FROM movie; // 중복 함수 결과 - 건수 표시
SELECT movieid, genreid FROM moviegenre;
SELECT id, name, gender, age FROM muser;
SELECT id, name, gender, age FROM muser WHERE id <= 10 ORDER BY id ASC;
SELECT id, name, gender, age FROM muser WHERE id > 100 ORDER BY id ASC;
SELECT * FROM muser WHERE id IN (1, 100, 1000);
SELECT * FROM muser WHERE id IN (SELECT userid FROM movierating WHERE rating >= 4); // Subquery for 고객정보
SELECT * FROM movie WHERE id IN (SELECT movieid FROM movierating WHERE rating < 3); // Subquery for 영화정보
SELECT userid, movieid, rating FROM movierating;
SELECT SUM(rating), MIN(rating), MAX(rating), AVG(rating), STDDEV(rating) FROM movierating;
SELECT userid, movieid, rating FROM movierating WHERE userid=6 AND movieid=1806;
SELECT movieid, ROUND(AVG(rating), 2) FROM movierating GROUP BY movieid; // 여러 결과를 묶어서 하나로 표시(연산 함수와 함께)
SELECT movieid, MAX(rating), MIN(rating), ROUND(AVG(rating), 2) AS avr FROM movierating GROUP BY movieid; // 수치연산 함수 적용
SELECT movieid, rating FROM movierating ORDER BY rating ASC , movieid ASC; // 복수 필드로 정렬
SELECT movieid, ROUND(AVG(rating), 2) AS average FROM movierating GROUP BY movieid ORDER BY average DESC LIMIT 10; // 숫자 표시형식 지정
SELECT movieid, ROUND(AVG(rating), 2) AS average FROM movierating GROUP BY movieid HAVING AVG(rating) > 4.50 ORDER BY average DESC; // 영화별
SELECT userid, ROUND(AVG(rating), 2) AS average FROM movierating GROUP BY userid HAVING AVG(rating) > 4.68 ORDER BY average DESC; // 고객별
SELECT id, name FROM occupation;
SELECT name FROM muser WHERE birthdate ='2020-10-20' // 날짜형에 대한 일치 검색
// SELECT name FROM muser WHERE birthdate BETWEEN '1980-01-01' AND '2000-12-31' // 날짜형에 대한 구간 검색
// JOIN Query 예제
상영된 영화의 년도별 상영 건수를 최신 년도 순으로 보여라
SELECT
year AS 상영연도,
count(*) AS 영화편수
FROM movie
GROUP BY year
ORDER BY year DESC;
년도별 상영된 영화를 최신 년도순으로 장르명 함께 보여라(영화기준)
SELECT
a.year AS 상영연도,
a.id AS 영화ID,
b.id AS 장르ID,
SUBSTR(a.name,1, 30) AS 영화제목,
b.name AS 영화분류
FROM movie a
LEFT JOIN (
SELECT c.movieid,
g.name,
g.id
FROM moviegenre c
LEFT JOIN genre g
ON c.genreid = g.id) b
ON a.id = b.movieid
ORDER BY a.year DESC, a.name ASC;
// movie를 기준으로 moviegenre와 LEFT JOIN하여 모든 데이터가 나옴
년도별 상영된 영화를 최신 년도순으로 장르명 함께 보여라(영화평가기준)
SELECT
a.year AS 상영연도,
a.id AS 영화ID,
b.id AS 장르ID,
SUBSTR(a.name,1, 30) AS 영화제목,
b.name AS 영화분류
FROM movie a
RIGHT JOIN (
SELECT
c.movieid,
g.name,
g.id
FROM moviegenre c
LEFT JOIN genre g
ON c.genreid =g.id) b
ON a.id = b.movieid
ORDER BY a.year DESC, a.name ASC;
// moviegenre를 기준으로 moviegenre에 RIGHT JOIN함. moviegenre에 movieid 3건이 movie에 없음
SELECT *
FROM moviegenre
WHERE movieid NOT IN
(
SELECT id FROM movie
);
SELECT * FROM movie WHERE id = 2821;
영화정보와 영화별 장르정보에 모두 포함된 레코드만 보여라.(아래 2개의 결과건수와 movie 기준 LEFT JOIN 건수가 동일)
SELECT *
FROM movie a
INNER JOIN moviegenre g
ON a.id = g.movieid;
SELECT *
FROM moviegenre g
INNER JOIN movie a
ON a.id = g.movieid;
상영된 영화를 년도별 장르별 건수를 년도와 장르순으로 보여라
SELECT
a.year AS 상영연도,
b.name AS 영화분류,
count(*) AS 상영건수
FROM movie a
LEFT JOIN (
SELECT c.movieid,
g.name
FROM moviegenre c
LEFT JOIN genre g
ON c.genreid = g.id) b
ON a.id = b.movieid
GROUP BY a.year, b.name
ORDER BY a.year DESC, b.name ASC;
고객들 명단을 직업과 함께 표시하라
SELECT
a.name AS 고객명,
a.gender AS 성별,
a.age AS 나이,
b.name AS 직업
FROM muser a
LEFT JOIN (
SELECT id,
name
FROM occupation) b
ON a.occupationid = b.id
ORDER BY a.name ASC, a.age ASC;
고객들의 연령별 인원수를 집계하라.
\pset numericlocale // psql에서 표시되는 숫자를 1000단위 마다 ,로 구분
SELECT b.agerange AS 연령대,
COUNT(*) AS 인원수
FROM (
SELECT
CASE
WHEN age < 20 THEN '20 이하'
WHEN age < 30 THEN '20~29'
WHEN age < 40 THEN '30~39'
WHEN age < 50 THEN '40~49'
WHEN age >= 50 THEN '50 이상'
END AS agerange
FROM muser) b
GROUP BY b.agerange
ORDER BY b.agerange ASC;
연령대별 평점 평균을 계산하라
SELECT
a.agerange AS 연령대,
COUNT(*) AS 인원수,
ROUND(AVG(a.rating),2) AS 평점
FROM (
SELECT
CASE
WHEN d.age < 20 THEN '20 이하'
WHEN d.age < 30 THEN '20~29'
WHEN d.age < 40 THEN '30~39'
WHEN d.age < 50 THEN '40~49'
WHEN d.age >= 50 THEN '50 이상'
END AS agerange,
d.rating
FROM (
SELECT b.userid,
c.age,
c.name,
b.movieid,
b.rating
FROM movierating b
LEFT JOIN muser c
ON b.userid = c.id) d
) a
GROUP BY a.agerange
ORDER BY a.agerange ASC;
성별 평점 평균을 계산하라
SELECT
a. gender AS 성별,
COUNT(*) AS 인원수,
ROUND(AVG(a.rating), 2) AS 평점
FROM (
SELECT
CASE d.gender
WHEN 'M' THEN '남성'
WHEN 'F' THEN '여성'
END AS gender,
d.rating
FROM (
SELECT b.userid,
c.gender,
b.movieid,
b.rating
FROM movierating b
LEFT JOIN muser c ON
b.userid = c.id) d
) a
GROUP BY a.gender
ORDER BY a.gender ASC;
영화별 평가가 100건 이상인 영화에 대한 평점 평균을 계산하고 평점이 높은 순 10건을 표시하라
SELECT
a.name AS 영화제목,
COUNT(*) AS 평가수,
ROUND(AVG(a.rating),2) AS 평점
FROM (
SELECT b.movieid,
c.name,
b.rating
FROM movierating b
LEFT JOIN movie c ON
b.movieid = c.id) a
GROUP BY a.name
HAVING COUNT(*) > 100
ORDER BY AVG(a.rating) DESC LIMIT 10;