{ "cells": [ { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "skip" } }, "source": [ "NOTE:\n", "-----\n", "다음의 셀의 명령을 실행하고 넘어가자. 곧 필요하게 된다." ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": false, "slideshow": { "slide_type": "skip" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The sql extension is already loaded. To reload it, use:\n", " %reload_ext sql\n" ] }, { "data": { "text/plain": [ "'Connected: @None'" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%load_ext sql\n", "%sql sqlite://" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": false, "slideshow": { "slide_type": "skip" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n", "Done.\n", "Done.\n", "Done.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "Done.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "pragma foreign_keys = ON; -- WARNING: by default off in sqlite\n", "drop table if exists product; -- This needs to be dropped if exists, see why further down!\n", "drop table if exists company;\n", "create table company (\n", " cname varchar primary key, -- company name uniquely identifies the company.\n", " stockprice money, -- stock price is in money \n", " country varchar); -- country is just a string\n", "insert into company values ('GizmoWorks', 25.0, 'USA');\n", "insert into company values ('Canon', 65.0, 'Japan');\n", "insert into company values ('Hitachi', 15.0, 'Japan');\n", "create table product(\n", " pname varchar primary key, -- name of the product\n", " price money, -- price of the product\n", " category varchar, -- category\n", " manufacturer varchar, -- manufacturer\n", " foreign key (manufacturer) references company(cname));\n", "insert into product values('Gizmo', 19.99, 'Gadgets', 'GizmoWorks');\n", "insert into product values('SingleTouch', 149.99, 'Photography', 'Canon');\n", "insert into product values('PowerGizmo', 29.99, 'Gadgets', 'GizmoWorks');\n", "insert into product values('MultiTouch', 203.99, 'Household', 'Hitachi');" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "collapsed": false, "slideshow": { "slide_type": "skip" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n", "Done.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "Done.\n", "Done.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "Done.\n", "Done.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "Done.\n", "Done.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "DROP TABLE IF EXISTS franchise;\n", "CREATE TABLE franchise (name TEXT, db_type TEXT);\n", "INSERT INTO franchise VALUES ('Bobs Bagels', 'NoSQL');\n", "INSERT INTO franchise VALUES ('eBagel', 'NoSQL');\n", "INSERT INTO franchise VALUES ('BAGEL CORP', 'MySQL');\n", "\n", "DROP TABLE IF EXISTS store;\n", "CREATE TABLE store (franchise TEXT, location TEXT);\n", "INSERT INTO store VALUES ('Bobs Bagels', 'NYC');\n", "INSERT INTO store VALUES ('eBagel', 'PA');\n", "INSERT INTO store VALUES ('BAGEL CORP', 'Chicago');\n", "INSERT INTO store VALUES ('BAGEL CORP', 'NYC');\n", "INSERT INTO store VALUES ('BAGEL CORP', 'PA');\n", "\n", "DROP TABLE IF EXISTS bagel;\n", "CREATE TABLE bagel (name TEXT, price MONEY, made_by TEXT);\n", "INSERT INTO bagel VALUES ('Plain with shmear', 1.99, 'Bobs Bagels');\n", "INSERT INTO bagel VALUES ('Egg with shmear', 2.39, 'Bobs Bagels');\n", "INSERT INTO bagel VALUES ('eBagel Drinkable Bagel', 27.99, 'eBagel');\n", "INSERT INTO bagel VALUES ('eBagel Expansion Pack', 1.99, 'eBagel');\n", "INSERT INTO bagel VALUES ('Plain with shmear', 0.99, 'BAGEL CORP');\n", "INSERT INTO bagel VALUES ('Organic Flax-seed bagel chips', 0.99, 'BAGEL CORP');\n", "\n", "DROP TABLE IF EXISTS purchase;\n", "-- Note that date is an int here just to simplify things\n", "CREATE TABLE purchase (bagel_name TEXT, franchise TEXT, date INT, quantity INT, purchaser_age INT);\n", "INSERT INTO purchase VALUES ('Plain with shmear', 'Bobs Bagels', 1, 12, 28);\n", "INSERT INTO purchase VALUES ('Egg with shmear', 'Bobs Bagels', 2, 6, 47);\n", "INSERT INTO purchase VALUES ('Plain with shmear', 'BAGEL CORP', 2, 12, 24);\n", "INSERT INTO purchase VALUES ('Plain with shmear', 'BAGEL CORP', 3, 1, 17);\n", "INSERT INTO purchase VALUES ('eBagel Expansion Pack', 'eBagel', 1, 137, 5);\n", "INSERT INTO purchase VALUES ('Plain with shmear', 'Bobs Bagels', 4, 24, NULL);" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "다시 한 번 Order By\n", "================\n", "* SQL-89 의 명세에는 다음과 같은 명령을 금지하고 있다. 그렇지만, 무엇을 하려는 질의문인지는 이해할 수 있다.\n", "> SELECT pname FROM Product ORDER BY Price\n", "\n", "좀 더 비직관적인 ORDER BY 질의문을 살펴보자." ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pnamepricecategorymanufacturer
Gizmo19.99GadgetsGizmoWorks
SingleTouch149.99PhotographyCanon
PowerGizmo29.99GadgetsGizmoWorks
MultiTouch203.99HouseholdHitachi
" ], "text/plain": [ "[(u'Gizmo', 19.99, u'Gadgets', u'GizmoWorks'),\n", " (u'SingleTouch', 149.99, u'Photography', u'Canon'),\n", " (u'PowerGizmo', 29.99, u'Gadgets', u'GizmoWorks'),\n", " (u'MultiTouch', 203.99, u'Household', u'Hitachi')]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * FROM Product;" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "collapsed": false, "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pnameprice
Gizmo19.99
MultiTouch203.99
PowerGizmo29.99
SingleTouch149.99
" ], "text/plain": [ "[(u'Gizmo', 19.99),\n", " (u'MultiTouch', 203.99),\n", " (u'PowerGizmo', 29.99),\n", " (u'SingleTouch', 149.99)]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql SELECT pname,price FROM Product\n", "ORDER BY pname" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "collapsed": false, "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pname
Gizmo
PowerGizmo
SingleTouch
MultiTouch
" ], "text/plain": [ "[(u'Gizmo',), (u'PowerGizmo',), (u'SingleTouch',), (u'MultiTouch',)]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql SELECT pname FROM Product\n", "ORDER BY Price" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "collapsed": false, "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
pname
Gizmo
PowerGizmo
SingleTouch
MultiTouch
" ], "text/plain": [ "[(u'Gizmo',), (u'PowerGizmo',), (u'SingleTouch',), (u'MultiTouch',)]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql SELECT distinct pname FROM Product\n", "ORDER BY Price" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "fragment" } }, "source": [ "* Postgres 와 같은 데이터베이스는 이런 문장을 허용하지 않는다. 하지만, SQLite과 같은 경우 처리를 한다. 어째서 그럴까?" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "Set 연산자\n", "=======\n", "다음과 같이 세 개의 테이블을 생성하자:\n", "* R is {1,2,3,4,5}\n", "* S is {}\n", "* T is {1,4,7,10}\n" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "collapsed": false, "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n", "Done.\n", "Done.\n", "Done.\n", "Done.\n", "Done.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n" ] } ], "source": [ "# Create tables & insert some random numbers\n", "# Note: in Postgresql, try the generate_series function...\n", "%sql DROP TABLE IF EXISTS R; DROP TABLE IF EXISTS S; DROP TABLE IF EXISTS T;\n", "%sql CREATE TABLE R (A int); CREATE TABLE S (A int); CREATE TABLE T (A int);\n", "for i in range(1,6):\n", " %sql INSERT INTO R VALUES (:i)\n", "for i in range(1,11,3):\n", " %sql INSERT INTO T VALUES (:i)" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "$R \\cap (S \\cup T) = \\{1,4\\}$ 의 결과를 얻도록 해보자." ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "collapsed": false, "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", "
A
" ], "text/plain": [ "[]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql SELECT DISTINCT R.A FROM R, S, T\n", "WHERE R.A=S.A OR R.A=T.A" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "**결과가 왜 비었을까?**\n", "\n", "*이 질의문의 연산 순서를 다시 살펴보자:*\n", "1. `R,S,T`의 벡터곱 \n", "2. `WHERE` 절을 사용하여 중간 테이블을 필터처리한다.\n", "\n", "(1)의 결과를 보자:" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "collapsed": false, "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", "
A
" ], "text/plain": [ "[]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT DISTINCT R.A FROM R, S, T;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "fragment" } }, "source": [ "벡터곱이 공집합이다.`S` 이 비었기 때문이다!" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "Union\n", "-----\n", "\n", "`UNION` 이라는 새로운 명령을 배워보자:" ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "collapsed": false, "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
A
1
4
" ], "text/plain": [ "[(1,), (4,)]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT R.A FROM R, S WHERE R.A=S.A\n", "UNION -- this is an explicit keyword!\n", "SELECT R.A FROM R, T WHERE R.A=T.A" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "* 중복된 값이 없다 (union 은 집합이다)\n", "* 중복을 원한다면 UNION ALL을 사용한다\n", " * R = {1,2,3,4,5}\n", " * S = {1,2,3,4,5}\n", " * T = {1,4,7,10}" ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "collapsed": false, "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n", "Done.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n" ] } ], "source": [ "%sql DROP TABLE IF EXISTS S; CREATE TABLE S (A int);\n", "for i in range(1,6):\n", " %sql INSERT INTO S VALUES (:i)" ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "collapsed": false, "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
A
1
2
3
4
5
1
4
" ], "text/plain": [ "[(1,), (2,), (3,), (4,), (5,), (1,), (4,)]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "-- UNION ALL example- notice that 1 and 4 occur twice!\n", "SELECT R.A FROM R, S WHERE R.A=S.A\n", "UNION ALL\n", "SELECT R.A FROM R, T WHERE R.A=T.A" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "다른 집합 연산자: INTERSECT, EXCEPT\n", "-------------------------------------\n", "\n", "아래의 예제를 살펴보자" ] }, { "cell_type": "code", "execution_count": 14, "metadata": { "collapsed": false, "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
A
1
4
" ], "text/plain": [ "[(1,), (4,)]" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT R.A FROM R, S, T WHERE R.A = S.A\n", "INTERSECT\n", "SELECT R.A FROM R, S, T WHERE R.A = T.A" ] }, { "cell_type": "code", "execution_count": 15, "metadata": { "collapsed": false, "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
A
2
3
5
" ], "text/plain": [ "[(2,), (3,), (5,)]" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT R.A FROM R, S, T WHERE R.A = S.A\n", "EXCEPT\n", "SELECT R.A FROM R, S, T WHERE R.A = T.A" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "이 노트북에서 계속 사용될 예제: BAGELS\n", "===========================\n", "\n", "이 주제의 목차:\n", "* 중첩 질의문: 동기와 예제\n", "* Aggregation: 개요, group-by 용법\n", "* Null 값, Outer vs. Inner Joins" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "이야기:\n", "------\n", "* *eBagel* 은 핫 이슈인 NoSQL 기반 베이글 기업 공간의 새로운 기술 지향 스타트업이다.\n", "* eBagel은 $1억의 벤처캐피털 자금을 지원 받았다. 매출 실적이 줄어들고 있는 지금, 당신이 이 기업에 데이터를 분석하여 왜 그런지 알아보라는 임무를 받았다." ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "다음의 테이블의 값을 삽입하는 것으로 시작해보자(위에서 생성하였음):\n", "> Franchise(name TEXT, db_type TEXT)\n", "\n", "> Store(franchise TEXT, location TEXT)\n", "\n", "> Bagel(name TEXT, price MONEY, made_by TEXT)\n", "\n", "> Purchase(bagel_name TEXT, franchise TEXT, date INT, quantity INT, purchaser_age INT)\n", "\n", "데이터를 불러들여 보자." ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "Union\n", "-----\n", "\n", "먼저, PA **또는** NYC에 있는 franchises들을 조사해서 경쟁자들이 누군지 알아보자:" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "collapsed": false, "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
franchise
BAGEL CORP
Bobs Bagels
eBagel
" ], "text/plain": [ "[('BAGEL CORP',), ('Bobs Bagels',), ('eBagel',)]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT franchise FROM store WHERE location = 'NYC'\n", "UNION\n", "SELECT franchise FROM store WHERE location = 'PA';" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "Intersect: 미묘한 문제...\n", "--------------------------\n", "\n", "eBagel의 CEO는 여러 지역으로 성공적으로 확장한 베이글 회사들의 기술이 무엇인지 알고 싶어 한다. `INTERSECT` 연산자를 사용하여 PA **와** NYC 소재 franchises의 종류를 알아 보자:" ] }, { "cell_type": "code", "execution_count": 17, "metadata": { "collapsed": false, "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
db_type
MySQL
NoSQL
" ], "text/plain": [ "[(u'MySQL',), (u'NoSQL',)]" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT f.db_type\n", "FROM franchise f, store s \n", "WHERE f.name = s.franchise AND s.location = 'NYC'\n", "INTERSECT\n", "SELECT f.db_type\n", "FROM franchise f, store s \n", "WHERE f.name = s.franchise AND s.location = 'PA'" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "*무슨 일이 일어났나?*\n", "\n", "데이터를 보면, \"MySQL\"만 결과로 얻어야 한다:" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "collapsed": false, "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namelocationdb_type
Bobs BagelsNYCNoSQL
eBagelPANoSQL
BAGEL CORPChicagoMySQL
BAGEL CORPNYCMySQL
BAGEL CORPPAMySQL
" ], "text/plain": [ "[('Bobs Bagels', 'NYC', 'NoSQL'),\n", " ('eBagel', 'PA', 'NoSQL'),\n", " ('BAGEL CORP', 'Chicago', 'MySQL'),\n", " ('BAGEL CORP', 'NYC', 'MySQL'),\n", " ('BAGEL CORP', 'PA', 'MySQL')]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT f.name, s.location, f.db_type\n", "FROM franchise f, store s \n", "WHERE f.name = s.franchise;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "무슨 일이 일어난 것인가? 왜 이런 일이 일어나는지 질의문의 연산들을 나눠보면 알 수 있다:" ] }, { "cell_type": "code", "execution_count": 19, "metadata": { "collapsed": false, "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
db_type
NoSQL
MySQL
" ], "text/plain": [ "[(u'NoSQL',), (u'MySQL',)]" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql SELECT f.db_type \n", "FROM franchise f, store s \n", "WHERE f.name = s.franchise AND s.location = 'NYC'" ] }, { "cell_type": "code", "execution_count": 20, "metadata": { "collapsed": false, "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
db_type
NoSQL
MySQL
" ], "text/plain": [ "[(u'NoSQL',), (u'MySQL',)]" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql SELECT f.db_type\n", "FROM franchise f, store s\n", "WHERE f.name = s.franchise AND s.location = 'PA'" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "근본적으로 우리가 `INTERSECT` 연산을 우리가 실제 원하는 속성들이 아닌, 결과로 얻은 속성 값에 적용하였기 때문에 이런 문제가 일어났다." ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "중첩 질의문\n", "========\n", "\n", "위의 문제를 해결하는 한 방법으로 *중첩 질의문*을 알아보자. 중첩 질의문이란 질의문 내의 질의문으로, 안의 질의문이 내 놓은 결과를 밖의 질의문이 활용하는 연산을 말한다." ] }, { "cell_type": "code", "execution_count": 21, "metadata": { "collapsed": false, "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
db_type
MySQL
" ], "text/plain": [ "[(u'MySQL',)]" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT f.db_type\n", "FROM franchise f\n", "WHERE f.name IN (\n", " SELECT s.franchise FROM store s WHERE s.location = 'NYC')\n", " AND f.name IN (\n", " SELECT s.franchise FROM store s WHERE s.location = 'PA');" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "또 다른 중첩 질의문: eBagel의 CEO가 베이글 회사들이 지원하는 데이터베이스들 중 20 대 사람들이 많이 쓰는 데이터베이스는 무엇인지 궁금해 한다. " ] }, { "cell_type": "code", "execution_count": 22, "metadata": { "collapsed": false, "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
db_type
NoSQL
MySQL
" ], "text/plain": [ "[(u'NoSQL',), (u'MySQL',)]" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT f.db_type\n", "FROM franchise f\n", "WHERE f.name IN (\n", " SELECT b.made_by\n", " FROM bagel b, purchase p\n", " WHERE b.name = p.bagel_name \n", " AND p.purchaser_age >= 20 AND p.purchaser_age < 30);" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "중첩 질의문은 무척 강력하고 편리하다. 위의 결과를 얻기 위해 중첩 질의문을 쓰지 않고도 가능할까? 같은 결과를 돌려줄가?" ] }, { "cell_type": "code", "execution_count": 23, "metadata": { "collapsed": false, "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
db_type
MySQL
NoSQL
MySQL
NoSQL
" ], "text/plain": [ "[(u'MySQL',), (u'NoSQL',), (u'MySQL',), (u'NoSQL',)]" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT f.db_type\n", "FROM franchise f, bagel b, purchase p\n", "WHERE f.name = b.made_by \n", " AND b.name = p.bagel_name \n", " AND p.purchaser_age >= 20 AND p.purchaser_age < 30;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "fragment" } }, "source": [ "**중복 결과에 유의하자!** `DISTINCT` 추가하여 동치의 결과를 얻을 수 있다. 한 번 시도해보자. " ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "다음의 키워드들 역시 중첩 질의문의 결과에 쓸 수 있다:\n", "* `ALL`\n", "* `ANY`\n", "* `EXISTS`\n", "\n", "**불행하게도, `ALL` 과 `ANY` 는 우리가 사용 중인 SQLite에서는 지원되지 않는다 - 예제는 수업 자료 참고.** `EXISTS`의 예는 살펴볼 수 있다. 다음 질문에 답한다고 해보자. 경쟁사보다 더 저럼하게 파는 제품이 존재하는가?" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "collapsed": false, "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nameprice
eBagel Expansion Pack1.99
" ], "text/plain": [ "[('eBagel Expansion Pack', 1.99)]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT b.name, b.price\n", "FROM bagel b\n", "WHERE b.made_by = 'eBagel'\n", " AND EXISTS (SELECT name \n", " FROM bagel \n", " WHERE made_by <> 'eBagel' \n", " AND price > b.price);" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "fragment" } }, "source": [ "안 쪽의 중첩 질의의 결과가 밖의 메인 질의에서 활용이 되기 때문에 *corrleated* 질의가 된다. 변수의 활용 범위도 살펴볼 필요가 있다. 이 중첩 질의문을 하나의 SFW 질의로 바꿀 수도 있다. 한 번 생각해보자." ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "여기까지 요약 정리:\n", "--------------\n", "\n", "SQL:\n", "* 데이터를 조작하는 상위 계층의 선언적 언어이다(DML, Data Manipulation Language)\n", "* 모든 일은 SFW 블럭에서 일어난다\n", "* 집합 연산자는 강력하지만 고려해야 할 미묘한 부분이 있다!\n", "* 강력한 중첩 질의문을 사용할 수 있다\n", " * **SQLite보다 더 많은 기능을 제공하는 다른 DBMS를 쓸 때 그 진가를 알 수 있다!**" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Aggregation\n", "=======\n", "\n", "SQL 은 집계를 위한 여러 연산들을 제공한다:\n", "* `SUM`\n", "* `COUNT`\n", "* `AVG`\n", "* `MIN`\n", "* `MAX`\n", "\n", "*`COUNT`* 를 제외하고는 모든 집게 연산자들은 하나의 속성에만 적용할 수 있다" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "간단한 예제를 살펴보자:" ] }, { "cell_type": "code", "execution_count": 25, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
AVG(price)
14.99
" ], "text/plain": [ "[(14.989999999999998,)]" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT AVG(price) FROM bagel WHERE made_by = 'eBagel';" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "출력되는 스키마의 속성 이름을 재정의할 수 있다. 집계시 유용하다." ] }, { "cell_type": "code", "execution_count": 26, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
Number of Stores in PA
2
" ], "text/plain": [ "[(2,)]" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT COUNT(*) AS \"Number of Stores in PA\" FROM store WHERE location = 'PA';" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "만약 베이글 사회가 몇 개의 지역으로 이루어졌는지 알아보고자 한다면?" ] }, { "cell_type": "code", "execution_count": 27, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
COUNT(location)
5
" ], "text/plain": [ "[(5,)]" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT COUNT(location) FROM store;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "실제로 우리가 원하는 결과는 중복 값을 제외한 집계 값이다." ] }, { "cell_type": "code", "execution_count": 28, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
COUNT(DISTINCT location)
3
" ], "text/plain": [ "[(3,)]" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT COUNT(DISTINCT location) FROM store;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "산업 매출 데이터를 구했다고 해보자. 그리고 그 데이터를 이용해 베이글 산업의 규모를 알아보고 싶다. 이 문제의 답을 알기 위해 *SQL*문을 어떻게 작성하면 될까?" ] }, { "cell_type": "code", "execution_count": 29, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
net_sales
432.99
" ], "text/plain": [ "[(432.99,)]" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT SUM(b.price * p.quantity) AS net_sales\n", "FROM bagel b, purchase p\n", "WHERE b.name = p.bagel_name;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "시장에서 기회가 상당히 크다! eBagel의 CEO는 이 소식에 매우 기뻤다. 그리고 좀 더 세부적인 정보를 얻기 원한다. 회사 별 수익 규모가 어떻게 되는지 알아보고 싶다. 다행스럽게도, **군집과 집계는 같이 쓸 수 있다**" ] }, { "cell_type": "code", "execution_count": 30, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
made_byrevenue
BAGEL CORP12.87
Bobs Bagels85.98
eBagel272.63
" ], "text/plain": [ "[(u'BAGEL CORP', 12.87), (u'Bobs Bagels', 85.98), (u'eBagel', 272.63)]" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT b.made_by, SUM(b.price * p.quantity) AS revenue\n", "FROM bagel b, purchase p\n", "WHERE b.made_by = p.franchise AND b.name = p.bagel_name\n", "GROUP BY b.made_by;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "매우 흥미로운 결과이다!\n", "\n", "끝으로 분석을 마무리 지으면서, 제품 별 순이익의 분포를 알아보고 싶다. *하지만* 좀 더 큰 그림을 그리기 위해서는 12개 이상의 제품을 판매하는 판매자들의 결과들만 보기 원한다.\n", "\n", "이 문제의 해답을 얻기 위해 `HAVING` 절을 소개 한다. 이 절은 *집계*에 조건을 걸 수 있다." ] }, { "cell_type": "code", "execution_count": 31, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namesales
Plain with shmear84.51
eBagel Expansion Pack272.63
" ], "text/plain": [ "[(u'Plain with shmear', 84.50999999999999), (u'eBagel Expansion Pack', 272.63)]" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT b.name, SUM(b.price * p.quantity) AS sales\n", "FROM bagel b, purchase p\n", "WHERE b.name = p.bagel_name AND b.made_by = p.franchise\n", "GROUP BY b.name\n", "HAVING SUM(p.quantity) > 12;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "마지막으로 실행한 질의문을 나눠서 어떤 식으로 처리되는지 단계별로 살펴보자:\n", "\n", "먼저, FROM-WHERE 부분이 평가 된다:" ] }, { "cell_type": "code", "execution_count": 32, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namepricemade_bybagel_namefranchisedatequantitypurchaser_age
Plain with shmear1.99Bobs BagelsPlain with shmearBobs Bagels11228
Plain with shmear1.99Bobs BagelsPlain with shmearBobs Bagels424None
Egg with shmear2.39Bobs BagelsEgg with shmearBobs Bagels2647
eBagel Expansion Pack1.99eBageleBagel Expansion PackeBagel11375
Plain with shmear0.99BAGEL CORPPlain with shmearBAGEL CORP21224
Plain with shmear0.99BAGEL CORPPlain with shmearBAGEL CORP3117
" ], "text/plain": [ "[(u'Plain with shmear', 1.99, u'Bobs Bagels', u'Plain with shmear', u'Bobs Bagels', 1, 12, 28),\n", " (u'Plain with shmear', 1.99, u'Bobs Bagels', u'Plain with shmear', u'Bobs Bagels', 4, 24, None),\n", " (u'Egg with shmear', 2.39, u'Bobs Bagels', u'Egg with shmear', u'Bobs Bagels', 2, 6, 47),\n", " (u'eBagel Expansion Pack', 1.99, u'eBagel', u'eBagel Expansion Pack', u'eBagel', 1, 137, 5),\n", " (u'Plain with shmear', 0.99, u'BAGEL CORP', u'Plain with shmear', u'BAGEL CORP', 2, 12, 24),\n", " (u'Plain with shmear', 0.99, u'BAGEL CORP', u'Plain with shmear', u'BAGEL CORP', 3, 1, 17)]" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT *\n", "FROM bagel b, purchase p\n", "WHERE b.name = p.bagel_name AND b.made_by = p.franchise;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "(*여담: WHERE 절에서 `AND b.made_by = p.franchise` 를 제외 했다면 어떤 결과를 얻었을까? 복잡한 질의문을 쪼개서 과정을 살펴보는 것은 디버깅하는 좋은 순서이다!*)\n", "\n", "다음으로, GROUP-BY 가 적용된다:" ] }, { "cell_type": "code", "execution_count": 33, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namepricesmade_bysbagel_namefranchisesdatesquantitiespurchaser_ages
Egg with shmear2.39Bobs BagelsEgg with shmearBobs Bagels2647
Plain with shmear1.99,1.99,0.99,0.99Bobs Bagels,Bobs Bagels,BAGEL CORP,BAGEL CORPPlain with shmearBobs Bagels,Bobs Bagels,BAGEL CORP,BAGEL CORP1,4,2,312,24,12,128,24,17
eBagel Expansion Pack1.99eBageleBagel Expansion PackeBagel11375
" ], "text/plain": [ "[(u'Egg with shmear', u'2.39', u'Bobs Bagels', u'Egg with shmear', u'Bobs Bagels', u'2', u'6', u'47'),\n", " (u'Plain with shmear', u'1.99,1.99,0.99,0.99', u'Bobs Bagels,Bobs Bagels,BAGEL CORP,BAGEL CORP', u'Plain with shmear', u'Bobs Bagels,Bobs Bagels,BAGEL CORP,BAGEL CORP', u'1,4,2,3', u'12,24,12,1', u'28,24,17'),\n", " (u'eBagel Expansion Pack', u'1.99', u'eBagel', u'eBagel Expansion Pack', u'eBagel', u'1', u'137', u'5')]" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT \n", " b.name,\n", " GROUP_CONCAT(b.price, ',') AS prices,\n", " GROUP_CONCAT(b.made_by, ',') AS made_bys,\n", " bagel_name, \n", " GROUP_CONCAT(p.franchise, ',') AS franchises,\n", " GROUP_CONCAT(p.date, ',') AS dates,\n", " GROUP_CONCAT(p.quantity, ',') AS quantities,\n", " GROUP_CONCAT(p.purchaser_age, ',') AS purchaser_ages\n", "FROM bagel b, purchase p\n", "WHERE b.name = p.bagel_name AND b.made_by = p.franchise\n", "GROUP BY b.name;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*`GROUP_CONCAT` 함수는 예쁘게 표시하기 위해 썼을 뿐이다.*\n", "\n", "다음으로 `HAVING` 조건이 적용된다:" ] }, { "cell_type": "code", "execution_count": 34, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namepricesmade_bysbagel_namefranchisesdatestotal_quantitypurchaser_ages
Plain with shmear1.99,1.99,0.99,0.99Bobs Bagels,Bobs Bagels,BAGEL CORP,BAGEL CORPPlain with shmearBobs Bagels,Bobs Bagels,BAGEL CORP,BAGEL CORP1,4,2,34928,24,17
eBagel Expansion Pack1.99eBageleBagel Expansion PackeBagel11375
" ], "text/plain": [ "[(u'Plain with shmear', u'1.99,1.99,0.99,0.99', u'Bobs Bagels,Bobs Bagels,BAGEL CORP,BAGEL CORP', u'Plain with shmear', u'Bobs Bagels,Bobs Bagels,BAGEL CORP,BAGEL CORP', u'1,4,2,3', 49, u'28,24,17'),\n", " (u'eBagel Expansion Pack', u'1.99', u'eBagel', u'eBagel Expansion Pack', u'eBagel', u'1', 137, u'5')]" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT \n", " b.name,\n", " GROUP_CONCAT(b.price, ',') AS prices,\n", " GROUP_CONCAT(b.made_by, ',') AS made_bys,\n", " bagel_name, \n", " GROUP_CONCAT(p.franchise, ',') AS franchises,\n", " GROUP_CONCAT(p.date, ',') AS dates,\n", " SUM(p.quantity) AS total_quantity,\n", " GROUP_CONCAT(p.purchaser_age, ',') AS purchaser_ages\n", "FROM bagel b, purchase p\n", "WHERE b.name = p.bagel_name AND b.made_by = p.franchise\n", "GROUP BY b.name\n", "HAVING SUM(p.quantity) > 12;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "최종적으로 select 절 (projection)의 집계 함수들과 다른 연산들이 적용된다:" ] }, { "cell_type": "code", "execution_count": 35, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namesales
Plain with shmear84.51
eBagel Expansion Pack272.63
" ], "text/plain": [ "[(u'Plain with shmear', 84.50999999999999), (u'eBagel Expansion Pack', 272.63)]" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT b.name, SUM(b.price * p.quantity) AS sales\n", "FROM bagel b, purchase p\n", "WHERE b.name = p.bagel_name AND b.made_by = p.franchise\n", "GROUP BY b.name\n", "HAVING SUM(p.quantity) > 12;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "한정자: existential 과 universal\n", "------------------------------------\n", "\n", "여러 인터뷰를 통해 알게된 사실은 마켓에서 성공하기 위해서 \"바른(shmear)\" 것이 필요하다고 한다. 이름에 \"shmear\" 들어 있는 제품이 메뉴에 있는지 알아보자." ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
made_by
Bobs Bagels
BAGEL CORP
" ], "text/plain": [ "[('Bobs Bagels',), ('BAGEL CORP',)]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT DISTINCT made_by FROM bagel WHERE name LIKE '%shmear%';" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "존재 여부를 검사하는 것을 보고 **existential** 한정자라 한다. 위에서 본 것처럼, SQL에서 쉽게 작성할 수 있다. **universal** 한정자(모든 r에 대해 C(r)의 형태)는 좀 더 어렵지만, 나름 간단하게 표현할 수 있다. \n", "\n", "예를 위해, 이름에 \"shmear\"가 포함된 모든 경쟁사의 제품을 찾아보자:" ] }, { "cell_type": "code", "execution_count": 37, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
made_by
Bobs Bagels
" ], "text/plain": [ "[(u'Bobs Bagels',)]" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT DISTINCT made_by\n", "FROM bagel\n", "WHERE made_by NOT IN (\n", " SELECT made_by\n", " FROM bagel\n", " WHERE name NOT LIKE '%shmear%');" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "SQL에서 NULL 값\n", "-----------------\n", "\n", "다음의 이상한 질의문을 살펴보자:" ] }, { "cell_type": "code", "execution_count": 38, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bagel_namefranchisedatequantitypurchaser_age
Plain with shmearBobs Bagels11228
Egg with shmearBobs Bagels2647
Plain with shmearBAGEL CORP21224
Plain with shmearBAGEL CORP3117
Plain with shmearBobs Bagels424None
" ], "text/plain": [ "[(u'Plain with shmear', u'Bobs Bagels', 1, 12, 28),\n", " (u'Egg with shmear', u'Bobs Bagels', 2, 6, 47),\n", " (u'Plain with shmear', u'BAGEL CORP', 2, 12, 24),\n", " (u'Plain with shmear', u'BAGEL CORP', 3, 1, 17),\n", " (u'Plain with shmear', u'Bobs Bagels', 4, 24, None)]" ] }, "execution_count": 38, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * FROM purchase WHERE bagel_name LIKE '%shmear%';" ] }, { "cell_type": "code", "execution_count": 39, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bagel_namefranchisedatequantitypurchaser_age
Plain with shmearBobs Bagels11228
Egg with shmearBobs Bagels2647
Plain with shmearBAGEL CORP21224
Plain with shmearBAGEL CORP3117
" ], "text/plain": [ "[(u'Plain with shmear', u'Bobs Bagels', 1, 12, 28),\n", " (u'Egg with shmear', u'Bobs Bagels', 2, 6, 47),\n", " (u'Plain with shmear', u'BAGEL CORP', 2, 12, 24),\n", " (u'Plain with shmear', u'BAGEL CORP', 3, 1, 17)]" ] }, "execution_count": 39, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql SELECT * FROM purchase \n", "WHERE bagel_name LIKE '%shmear%' \n", " AND (purchaser_age >= 5 OR purchaser_age < 5);" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`NULL` 값은 특별하게 취급되고 있음을 볼 수 있다. SQL에서는 불리안 값이 세 종류이다:`TRUE`, `FALSE`, 그리고 `UNKNOWN`. `NULL` 값과 상수의 비교는 `UNKNOWN`을 되돌려 준다. 관계식은 조건이 `TRUE`일 때만 처리가 된다. 이를 다루기 위해 다음과 같이 특별한 조치를 취할 수 있다. " ] }, { "cell_type": "code", "execution_count": 40, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
bagel_namefranchisedatequantitypurchaser_age
Plain with shmearBobs Bagels11228
Egg with shmearBobs Bagels2647
Plain with shmearBAGEL CORP21224
Plain with shmearBAGEL CORP3117
Plain with shmearBobs Bagels424None
" ], "text/plain": [ "[(u'Plain with shmear', u'Bobs Bagels', 1, 12, 28),\n", " (u'Egg with shmear', u'Bobs Bagels', 2, 6, 47),\n", " (u'Plain with shmear', u'BAGEL CORP', 2, 12, 24),\n", " (u'Plain with shmear', u'BAGEL CORP', 3, 1, 17),\n", " (u'Plain with shmear', u'Bobs Bagels', 4, 24, None)]" ] }, "execution_count": 40, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql SELECT * FROM purchase\n", "WHERE bagel_name LIKE '%shmear%'\n", " AND (purchaser_age >= 5 OR purchaser_age < 5 \n", " OR purchaser_age IS NULL);" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "join 에 null 이 있으면 어떻게 되는가?" ] }, { "cell_type": "code", "execution_count": 41, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
name
Plain with shmear
Egg with shmear
eBagel Expansion Pack
" ], "text/plain": [ "[(u'Plain with shmear',), (u'Egg with shmear',), (u'eBagel Expansion Pack',)]" ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT DISTINCT b.name \n", "FROM bagel b, purchase p \n", "WHERE b.name = p.bagel_name AND b.made_by = p.franchise;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "구매된 적이 없는 베이글에 대한 정보를 잃게 된다!" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Inner 와 Outer Joins\n", "--------------------\n", "\n", "바로 앞에서 살펴 본`WHERE` 절을 사용하는 join 질의문은 `INNER JOIN`이라고 부른다. 그리고 다음과 같이 다시 작성될 수 있다." ] }, { "cell_type": "code", "execution_count": 42, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
name
Plain with shmear
Egg with shmear
eBagel Expansion Pack
" ], "text/plain": [ "[(u'Plain with shmear',), (u'Egg with shmear',), (u'eBagel Expansion Pack',)]" ] }, "execution_count": 42, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT DISTINCT b.name \n", "FROM bagel b\n", " INNER JOIN purchase p ON b.name = p.bagel_name AND b.made_by = p.franchise;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "테이블 `A`와 `B`를 join 조건 `C(A,B)`로 `INNER JOIN`한다면 `C(a,b) = TRUE`인 릴레이션 `(a,b)`을 돌려준다. 만약, 위에서 살펴 본 예와 같이 `C(a,b)`가 참인 `b`가 없다면, 결과에 `a`는 포함되지 않는다. \n", "\n", "대신 `OUTER JOIN` 을 쓸 수 있고, 세 종류가 있다: `LEFT`, `RIGHT`, 그리고 `FULL`. \n", "\n", "지금의 상황에서 우리가 필요한 것은 `LEFT OUTER JOIN` 이다. Left outer join 은 왼쪽의 릴레이션 `a`에 대해 `C(a,b) = TRUE`를 만족하는 `b`가 없다하더라도 `(a, NULL)` 의 결과를 출력한다:" ] }, { "cell_type": "code", "execution_count": 43, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
name
Plain with shmear
Egg with shmear
eBagel Drinkable Bagel
eBagel Expansion Pack
Organic Flax-seed bagel chips
" ], "text/plain": [ "[(u'Plain with shmear',),\n", " (u'Egg with shmear',),\n", " (u'eBagel Drinkable Bagel',),\n", " (u'eBagel Expansion Pack',),\n", " (u'Organic Flax-seed bagel chips',)]" ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "SELECT DISTINCT b.name \n", "FROM bagel b\n", " LEFT OUTER JOIN purchase p ON b.name = p.bagel_name AND b.made_by = p.franchise;" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [] } ], "metadata": { "celltoolbar": "Slideshow", "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.5" } }, "nbformat": 4, "nbformat_minor": 2 }