Consultas de Selección: Muestras aleatorias. Common Table Expressions.¶
Conexión a la Base de Datos¶
[1]:
import pandas as pd
import psycopg2
import sqlalchemy
import matplotlib as plt
%matplotlib inline
from sqlalchemy import create_engine
POSTGRES_ADDRESS = 'localhost' # Este es el servidor, puede ser una IP
POSTGRES_PORT = '5432'
POSTGRES_USERNAME = 'postgres'
POSTGRES_PASSWORD = '1234'
POSTGRES_DBNAME = 'dvdrental'
# Ahora se configura la cadena de conexión.
# Esta es una cadena que contiene los parámetros necesarios para establecer una
# conexión con posgres.
postgres_str = ('postgresql://{username}:{password}@{ipaddress}:{port}/{dbname}'.format(username=POSTGRES_USERNAME,
password=POSTGRES_PASSWORD,
ipaddress=POSTGRES_ADDRESS,
port=POSTGRES_PORT,
dbname=POSTGRES_DBNAME))
# A continuación se crea la conexión
cnx = create_engine(postgres_str)
Muestras aleatorias de Filas¶
En diversas ocasiones no es útil trabajar con todo el universo de datos de una tabla. Se puede tomar una muestra aleatoria de la misma mediante SQL.
[2]:
# Consultar cuántos registros se tienen en la tabla
sql = '''
SELECT count(*) FROM film;
'''
pd.read_sql_query(sql, cnx)
[2]:
| count | |
|---|---|
| 0 | 1000 |
[3]:
# Tomar 10 registros al azar
sql = '''
SELECT * FROM film order by RANDOM() limit 10;
'''
pd.read_sql_query(sql, cnx)
[3]:
| film_id | title | description | release_year | language_id | rental_duration | rental_rate | length | replacement_cost | rating | last_update | special_features | fulltext | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 399 | Happiness United | A Action-Packed Panorama of a Husband And a Fe... | 2006 | 1 | 6 | 2.99 | 100 | 23.99 | G | 2013-05-26 14:50:58.951 | [Deleted Scenes] | 'action':5 'action-pack':4 'ancient':21 'femin... |
| 1 | 662 | Paths Control | A Astounding Documentary of a Butler And a Cat... | 2006 | 1 | 3 | 4.99 | 118 | 9.99 | PG | 2013-05-26 14:50:58.951 | [Trailers, Behind the Scenes] | 'ancient':18 'astound':4 'butler':8 'cat':11 '... |
| 2 | 252 | Dream Pickup | A Epic Display of a Car And a Composer who mus... | 2006 | 1 | 6 | 2.99 | 135 | 18.99 | PG | 2013-05-26 14:50:58.951 | [Trailers, Commentaries, Behind the Scenes] | 'car':8 'compos':11 'display':5 'dream':1 'epi... |
| 3 | 200 | Curtain Videotape | A Boring Reflection of a Dentist And a Mad Cow... | 2006 | 1 | 7 | 0.99 | 133 | 27.99 | PG-13 | 2013-05-26 14:50:58.951 | [Trailers, Commentaries, Deleted Scenes, Behin... | 'agent':18 'bore':4 'chase':15 'cow':12 'curta... |
| 4 | 681 | Pirates Roxanne | A Stunning Drama of a Woman And a Lumberjack w... | 2006 | 1 | 4 | 0.99 | 100 | 20.99 | PG | 2013-05-26 14:50:58.951 | [Commentaries, Deleted Scenes] | 'canadian':20 'drama':5 'lumberjack':11 'must'... |
| 5 | 518 | Liaisons Sweet | A Boring Drama of a A Shark And a Explorer who... | 2006 | 1 | 5 | 4.99 | 140 | 15.99 | PG | 2013-05-26 14:50:58.951 | [Commentaries, Behind the Scenes] | 'bore':4 'canadian':20 'drama':5 'explor':12 '... |
| 6 | 677 | Pianist Outfield | A Intrepid Story of a Boy And a Technical Writ... | 2006 | 1 | 6 | 0.99 | 136 | 25.99 | NC-17 | 2013-05-26 14:50:58.951 | [Trailers, Commentaries, Deleted Scenes] | 'boy':8 'intrepid':4 'lumberjack':17 'monaster... |
| 7 | 879 | Telegraph Voyage | A Fateful Yarn of a Husband And a Dog who must... | 2006 | 1 | 3 | 4.99 | 148 | 20.99 | PG | 2013-05-26 14:50:58.951 | [Commentaries] | 'battl':14 'boat':20 'dog':11 'fate':4 'husban... |
| 8 | 591 | Monsoon Cause | A Astounding Tale of a Crocodile And a Car who... | 2006 | 1 | 6 | 4.99 | 182 | 20.99 | PG | 2013-05-26 14:50:58.951 | [Commentaries, Behind the Scenes] | 'astound':4 'boat':21 'car':11 'caus':2 'croco... |
| 9 | 704 | Pure Runner | A Thoughtful Documentary of a Student And a Ma... | 2006 | 1 | 3 | 2.99 | 121 | 25.99 | NC-17 | 2013-05-26 14:50:58.951 | [Trailers, Deleted Scenes] | 'challeng':14 'documentari':5 'madman':11 'man... |
[17]:
# Tomar 5% registros al azar
# https://wiki.postgresql.org/wiki/TABLESAMPLE_Implementation
# The percentage of result set size to the total tuple size will be sometimes larger,
# sometimes smaller than the percentage specified.
# You might use "limit <number>" to get the top <number> of tuples.
sql = '''
SELECT * FROM film TABLESAMPLE SYSTEM(5) Limit 1000 * 0.05;
'''
pd.read_sql_query(sql, cnx)
[17]:
| film_id | title | description | release_year | language_id | rental_duration | rental_rate | length | replacement_cost | rating | last_update | special_features | fulltext | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 430 | Hook Chariots | A Insightful Story of a Boy And a Dog who must... | 2006 | 1 | 7 | 0.99 | 49 | 23.99 | G | 2013-05-26 14:50:58.951 | [Trailers, Commentaries, Behind the Scenes] | 'australia':18 'boy':8,16 'chariot':2 'dog':11... |
| 1 | 450 | Idols Snatchers | A Insightful Drama of a Car And a Composer who... | 2006 | 1 | 5 | 2.99 | 84 | 29.99 | NC-17 | 2013-05-26 14:50:58.951 | [Trailers] | 'car':8 'compos':11 'drama':5 'fight':14 'idol... |
| 2 | 431 | Hoosiers Birdcage | A Astounding Display of a Explorer And a Boat ... | 2006 | 1 | 3 | 2.99 | 176 | 12.99 | G | 2013-05-26 14:50:58.951 | [Trailers, Commentaries, Deleted Scenes] | 'astound':4 'birdcag':2 'boat':11 'car':16 'di... |
| 3 | 432 | Hope Tootsie | A Amazing Documentary of a Student And a Sumo ... | 2006 | 1 | 4 | 2.99 | 139 | 22.99 | NC-17 | 2013-05-26 14:50:58.951 | [Trailers, Deleted Scenes, Behind the Scenes] | 'amaz':4 'documentari':5 'hope':1 'must':14 'o... |
| 4 | 433 | Horn Working | A Stunning Display of a Mad Scientist And a Te... | 2006 | 1 | 4 | 2.99 | 95 | 23.99 | PG | 2013-05-26 14:50:58.951 | [Trailers] | 'display':5 'horn':1 'mad':8 'monkey':18 'must... |
| 5 | 434 | Horror Reign | A Touching Documentary of a A Shark And a Car ... | 2006 | 1 | 3 | 0.99 | 139 | 25.99 | R | 2013-05-26 14:50:58.951 | [Deleted Scenes, Behind the Scenes] | 'build':15 'car':12 'documentari':5 'horror':1... |
| 6 | 435 | Hotel Happiness | A Thrilling Yarn of a Pastry Chef And a A Shar... | 2006 | 1 | 6 | 4.99 | 181 | 28.99 | PG-13 | 2013-05-26 14:50:58.951 | [Behind the Scenes] | 'challeng':16 'chef':9 'happi':2 'hotel':1 'ma... |
| 7 | 436 | Hours Rage | A Fateful Story of a Explorer And a Feminist w... | 2006 | 1 | 4 | 0.99 | 122 | 14.99 | NC-17 | 2013-05-26 14:50:58.951 | [Trailers, Deleted Scenes] | 'explor':8 'fate':4 'feminist':11 'georgia':20... |
| 8 | 437 | House Dynamite | A Taut Story of a Pioneer And a Squirrel who m... | 2006 | 1 | 7 | 2.99 | 109 | 13.99 | R | 2013-05-26 14:50:58.951 | [Commentaries, Deleted Scenes, Behind the Scenes] | 'battl':14 'dynamit':2 'georgia':19 'hous':1 '... |
| 9 | 438 | Human Graffiti | A Beautiful Reflection of a Womanizer And a Su... | 2006 | 1 | 3 | 2.99 | 68 | 22.99 | NC-17 | 2013-05-26 14:50:58.951 | [Trailers, Behind the Scenes] | 'administr':18 'beauti':4 'chase':15 'databas'... |
| 10 | 439 | Hunchback Impossible | A Touching Yarn of a Frisbee And a Dentist who... | 2006 | 1 | 4 | 4.99 | 151 | 28.99 | PG-13 | 2013-05-26 14:50:58.951 | [Trailers, Deleted Scenes] | 'ancient':18 'compos':16 'dentist':11 'fight':... |
| 11 | 440 | Hunger Roof | A Unbelieveable Yarn of a Student And a Databa... | 2006 | 1 | 6 | 0.99 | 105 | 21.99 | G | 2013-05-26 14:50:58.951 | [Deleted Scenes, Behind the Scenes] | 'abandon':20 'administr':12 'databas':11 'hung... |
| 12 | 441 | Hunter Alter | A Emotional Drama of a Mad Cow And a Boat who ... | 2006 | 1 | 5 | 2.99 | 125 | 21.99 | PG-13 | 2013-05-26 14:50:58.951 | [Commentaries, Deleted Scenes, Behind the Scenes] | 'agent':18 'alter':2 'boat':12 'cow':9 'drama'... |
| 13 | 442 | Hunting Musketeers | A Thrilling Reflection of a Pioneer And a Dent... | 2006 | 1 | 6 | 2.99 | 65 | 24.99 | NC-17 | 2013-05-26 14:50:58.951 | [Trailers, Deleted Scenes] | 'abandon':19 'dentist':11 'hunt':1 'mine':20 '... |
| 14 | 443 | Hurricane Affair | A Lacklusture Epistle of a Database Administra... | 2006 | 1 | 6 | 2.99 | 49 | 11.99 | PG | 2013-05-26 14:50:58.951 | [Trailers, Commentaries, Behind the Scenes] | 'abandon':20 'administr':9 'affair':2 'databas... |
| 15 | 444 | Hustler Party | A Emotional Reflection of a Sumo Wrestler And ... | 2006 | 1 | 3 | 4.99 | 83 | 22.99 | NC-17 | 2013-05-26 14:50:58.951 | [Trailers, Commentaries, Behind the Scenes] | 'conquer':15 'desert':21 'emot':4 'hustler':1 ... |
| 16 | 445 | Hyde Doctor | A Fanciful Documentary of a Boy And a Woman wh... | 2006 | 1 | 5 | 2.99 | 100 | 11.99 | G | 2013-05-26 14:50:58.951 | [Trailers, Deleted Scenes] | 'boat':20 'boy':8 'doctor':2 'documentari':5 '... |
| 17 | 446 | Hysterical Grail | A Amazing Saga of a Madman And a Dentist who m... | 2006 | 1 | 5 | 4.99 | 150 | 19.99 | PG | 2013-05-26 14:50:58.951 | [Trailers, Commentaries, Deleted Scenes] | 'amaz':4 'build':14 'car':16 'dentist':11 'gra... |
| 18 | 447 | Ice Crossing | A Fast-Paced Tale of a Butler And a Moose who ... | 2006 | 1 | 5 | 2.99 | 131 | 28.99 | R | 2013-05-26 14:50:58.951 | [Deleted Scenes] | 'butler':10 'cross':2 'fast':5 'fast-pac':4 'i... |
| 19 | 506 | Lady Stage | A Beautiful Character Study of a Woman And a M... | 2006 | 1 | 4 | 4.99 | 67 | 14.99 | PG | 2013-05-26 14:50:58.951 | [Trailers, Deleted Scenes, Behind the Scenes] | 'beauti':4 'boat':22 'charact':5 'explor':17 '... |
| 20 | 507 | Ladybugs Armageddon | A Fateful Reflection of a Dog And a Mad Scient... | 2006 | 1 | 4 | 0.99 | 113 | 13.99 | NC-17 | 2013-05-26 14:50:58.951 | [Deleted Scenes] | 'armageddon':2 'dog':8 'fate':4 'ladybug':1 'm... |
| 21 | 508 | Lambs Cincinatti | A Insightful Story of a Man And a Feminist who... | 2006 | 1 | 6 | 4.99 | 144 | 18.99 | PG-13 | 2013-05-26 14:50:58.951 | [Trailers, Behind the Scenes] | 'australia':18 'cincinatti':2 'compos':16 'fem... |
| 22 | 509 | Language Cowboy | A Epic Yarn of a Cat And a Madman who must Van... | 2006 | 1 | 5 | 0.99 | 78 | 26.99 | NC-17 | 2013-05-26 14:50:58.951 | [Trailers, Deleted Scenes] | 'abandon':19 'amus':20 'cat':8 'cowboy':2 'den... |
| 23 | 510 | Lawless Vision | A Insightful Yarn of a Boy And a Sumo Wrestler... | 2006 | 1 | 6 | 4.99 | 181 | 29.99 | G | 2013-05-26 14:50:58.951 | [Deleted Scenes, Behind the Scenes] | 'boy':8 'car':17 'insight':4 'lawless':1 'must... |
| 24 | 511 | Lawrence Love | A Fanciful Yarn of a Database Administrator An... | 2006 | 1 | 7 | 0.99 | 175 | 23.99 | NC-17 | 2013-05-26 14:50:58.951 | [Commentaries, Deleted Scenes, Behind the Scenes] | 'administr':9 'berlin':20 'cow':13 'databas':8... |
| 25 | 512 | League Hellfighters | A Thoughtful Saga of a A Shark And a Monkey wh... | 2006 | 1 | 5 | 4.99 | 110 | 25.99 | PG-13 | 2013-05-26 14:50:58.951 | [Trailers] | 'ancient':19 'china':20 'hellfight':2 'leagu':... |
| 26 | 513 | Leathernecks Dwarfs | A Fateful Reflection of a Dog And a Mad Cow wh... | 2006 | 1 | 6 | 2.99 | 153 | 21.99 | PG-13 | 2013-05-26 14:50:58.951 | [Trailers, Commentaries, Behind the Scenes] | 'abandon':20 'cow':12 'dog':8 'dwarf':2 'fate'... |
| 27 | 514 | Lebowski Soldiers | A Beautiful Epistle of a Secret Agent And a Pi... | 2006 | 1 | 6 | 2.99 | 69 | 17.99 | PG-13 | 2013-05-26 14:50:58.951 | [Commentaries, Deleted Scenes] | 'agent':9 'ancient':19 'astronaut':17 'beauti'... |
| 28 | 515 | Legally Secretary | A Astounding Tale of a A Shark And a Moose who... | 2006 | 1 | 7 | 4.99 | 113 | 14.99 | PG | 2013-05-26 14:50:58.951 | [Trailers, Commentaries, Behind the Scenes] | 'astound':4 'desert':21 'legal':1 'meet':15 'm... |
| 29 | 516 | Legend Jedi | A Awe-Inspiring Epistle of a Pioneer And a Stu... | 2006 | 1 | 7 | 0.99 | 59 | 18.99 | PG | 2013-05-26 14:50:58.951 | [Commentaries, Deleted Scenes] | 'awe':5 'awe-inspir':4 'crocodil':18 'epistl':... |
| 30 | 517 | Lesson Cleopatra | A Emotional Display of a Man And a Explorer wh... | 2006 | 1 | 3 | 0.99 | 167 | 28.99 | NC-17 | 2013-05-26 14:50:58.951 | [Deleted Scenes, Behind the Scenes] | 'boy':16 'build':14 'cleopatra':2 'display':5 ... |
| 31 | 518 | Liaisons Sweet | A Boring Drama of a A Shark And a Explorer who... | 2006 | 1 | 5 | 4.99 | 140 | 15.99 | PG | 2013-05-26 14:50:58.951 | [Commentaries, Behind the Scenes] | 'bore':4 'canadian':20 'drama':5 'explor':12 '... |
| 32 | 519 | Liberty Magnificent | A Boring Drama of a Student And a Cat who must... | 2006 | 1 | 3 | 2.99 | 138 | 27.99 | G | 2013-05-26 14:50:58.951 | [Commentaries, Deleted Scenes, Behind the Scenes] | 'baloon':20 'bore':4 'cat':11 'drama':5 'liber... |
| 33 | 520 | License Weekend | A Insightful Story of a Man And a Husband who ... | 2006 | 1 | 7 | 2.99 | 91 | 28.99 | NC-17 | 2013-05-26 14:50:58.951 | [Deleted Scenes, Behind the Scenes] | 'husband':11 'insight':4 'licens':1 'madman':1... |
| 34 | 521 | Lies Treatment | A Fast-Paced Character Study of a Dentist And ... | 2006 | 1 | 7 | 4.99 | 147 | 28.99 | NC-17 | 2013-05-26 14:50:58.951 | [Commentaries, Deleted Scenes, Behind the Scenes] | 'charact':7 'compos':19 'defeat':17 'dentist':... |
| 35 | 522 | Life Twisted | A Thrilling Reflection of a Teacher And a Comp... | 2006 | 1 | 4 | 2.99 | 137 | 9.99 | NC-17 | 2013-05-26 14:50:58.951 | [Commentaries, Deleted Scenes] | 'compos':11 'find':14 'first':19 'life':1 'man... |
| 36 | 523 | Lights Deer | A Unbelieveable Epistle of a Dog And a Woman w... | 2006 | 1 | 7 | 0.99 | 174 | 21.99 | R | 2013-05-26 14:50:58.951 | [Commentaries] | 'confront':14 'deer':2 'dog':8 'epistl':5 'gul... |
| 37 | 560 | Mars Roman | A Boring Drama of a Car And a Dog who must Suc... | 2006 | 1 | 6 | 0.99 | 62 | 21.99 | NC-17 | 2013-05-26 14:50:58.951 | [Commentaries, Deleted Scenes] | 'bore':4 'car':8 'dog':11 'drama':5 'georgia':... |
| 38 | 524 | Lion Uncut | A Intrepid Display of a Pastry Chef And a Cat ... | 2006 | 1 | 6 | 0.99 | 50 | 13.99 | PG | 2013-05-26 14:50:58.951 | [Trailers, Deleted Scenes] | 'ancient':20 'cat':12 'chef':9 'china':21 'dis... |
| 39 | 525 | Loathing Legally | A Boring Epistle of a Pioneer And a Mad Scient... | 2006 | 1 | 4 | 0.99 | 140 | 29.99 | R | 2013-05-26 14:50:58.951 | [Deleted Scenes] | 'bore':4 'epistl':5 'escap':15 'frisbe':17 'gu... |
| 40 | 526 | Lock Rear | A Thoughtful Character Study of a Squirrel And... | 2006 | 1 | 7 | 2.99 | 120 | 10.99 | R | 2013-05-26 14:50:58.951 | [Trailers, Commentaries] | 'ancient':20 'charact':5 'japan':21 'lock':1 '... |
| 41 | 527 | Lola Agent | A Astounding Tale of a Mad Scientist And a Hus... | 2006 | 1 | 4 | 4.99 | 85 | 24.99 | PG | 2013-05-26 14:50:58.951 | [Trailers, Commentaries] | 'administr':18 'agent':2 'ancient':20 'astound... |
| 42 | 528 | Lolita World | A Thrilling Drama of a Girl And a Robot who mu... | 2006 | 1 | 4 | 2.99 | 155 | 25.99 | NC-17 | 2013-05-26 14:50:58.951 | [Trailers, Deleted Scenes] | 'abandon':19 'drama':5 'girl':8 'lolita':1 'mi... |
| 43 | 529 | Lonely Elephant | A Intrepid Story of a Student And a Dog who mu... | 2006 | 1 | 3 | 2.99 | 67 | 12.99 | G | 2013-05-26 14:50:58.951 | [Trailers, Commentaries, Deleted Scenes, Behin... | 'challeng':14 'dog':11 'eleph':2 'explor':16 '... |
| 44 | 530 | Lord Arizona | A Action-Packed Display of a Frisbee And a Pas... | 2006 | 1 | 5 | 2.99 | 108 | 27.99 | PG-13 | 2013-05-26 14:50:58.951 | [Trailers] | 'action':5 'action-pack':4 'arizona':2 'boat':... |
| 45 | 531 | Lose Inch | A Stunning Reflection of a Student And a Techn... | 2006 | 1 | 3 | 0.99 | 137 | 18.99 | R | 2013-05-26 14:50:58.951 | [Trailers, Commentaries] | 'battl':15 'butler':17 'first':20 'inch':2 'lo... |
| 46 | 532 | Loser Hustler | A Stunning Drama of a Robot And a Feminist who... | 2006 | 1 | 5 | 4.99 | 80 | 28.99 | PG | 2013-05-26 14:50:58.951 | [Deleted Scenes, Behind the Scenes] | 'butler':16 'drama':5 'feminist':11 'hustler':... |
| 47 | 533 | Lost Bird | A Emotional Character Study of a Robot And a A... | 2006 | 1 | 4 | 2.99 | 98 | 21.99 | PG-13 | 2013-05-26 14:50:58.951 | [Deleted Scenes] | 'bird':2 'charact':5 'defeat':16 'emot':4 'los... |
| 48 | 534 | Louisiana Harry | A Lacklusture Drama of a Girl And a Technical ... | 2006 | 1 | 5 | 0.99 | 70 | 18.99 | PG-13 | 2013-05-26 14:50:58.951 | [Trailers] | 'drama':5 'girl':8 'harri':2 'lacklustur':4 'l... |
| 49 | 535 | Love Suicides | A Brilliant Panorama of a Hunter And a Explore... | 2006 | 1 | 6 | 0.99 | 181 | 21.99 | R | 2013-05-26 14:50:58.951 | [Trailers, Behind the Scenes] | 'abandon':19 'brilliant':4 'dentist':16 'explo... |
Expresiones de tabla comunes (CTE - Common Table Expressions)¶
Es un conjunto de resultados temporal que se puede hacer referencia en otro comando SQL incluyendo SELECT, INSERT, UPDATEo DELETE.
Es otro modo de agrupar consultas y facilitar su reuso dentro de la misma sesión.
Las expresiones de tabla comunes son temporales en el sentido de que solo existen durante la ejecución de la consulta.
[18]:
sql = '''
WITH cte_film AS (
SELECT
film_id,
title,
(CASE
WHEN length < 30 THEN 'Short'
WHEN length < 90 THEN 'Medium'
ELSE 'Long'
END) length
FROM
film
)
SELECT *
FROM
cte_film
WHERE
length = 'Long'
ORDER BY
title;
'''
pd.read_sql_query(sql, cnx)
[18]:
| film_id | title | length | |
|---|---|---|---|
| 0 | 4 | Affair Prejudice | Long |
| 1 | 5 | African Egg | Long |
| 2 | 6 | Agent Truman | Long |
| 3 | 9 | Alabama Devil | Long |
| 4 | 11 | Alamo Videotape | Long |
| ... | ... | ... | ... |
| 675 | 994 | Wyoming Storm | Long |
| 676 | 996 | Young Language | Long |
| 677 | 997 | Youth Kick | Long |
| 678 | 998 | Zhivago Core | Long |
| 679 | 999 | Zoolander Fiction | Long |
680 rows × 3 columns