Cómo realizar consultas SQL en la BD de Poker Tracker. Parte III.
18 años 9 meses
11.048
En este artículo voy a profundizar en el uso de consultas a la base de datos de Poker Tracker, explicando detalladamente la consulta usada en la primera parte y creando nuevas para obtener más información no disponible en el programa que pueda resultar interesante. El objetivo es que cualquiera pueda crearse su propia consulta a partir de unas sencillas instrucciones. Recomiendo ir realizando las consultas para ver los resultados (en mi blog se ven las imágenes).
En el primero de la serie, explicaba simplemente cómo hacer una consulta SQL en Access; y en el segundo, la estructura de la base de datos de PT para saber la forma en la que almacena la información y cómo se divide ésta en las diferentes tablas y campos. En esta tercera y última parte, se comprenderán muchas cosas que en los anteriores podían resultar confusas. Lo intentaré hacer despacito, como le gusta a las mujeres.
Si no has estado despistado, sabrás que el lenguaje con el que se hacen las consultas se llama SQL. No es más que una serie de instrucciones que sabe interpretar el motor de la base de datos y que se emplea para manipular la información contenida en ella. En este breve tutorial voy a centrarme exclusivamente en las órdenes de consulta, es decir, en la recuperación de datos.
Hay una serie de palabras que conforman el lenguaje SQL y que equivalen a instrucciones sobre la base de datos. Para hacer una consulta, se utiliza la palabra SELECT (del inglés, seleccionar). La estructura de una consulta SQL es la siguiente:
SELECT (lo que se quiera seleccionar de la BD)
FROM (la tabla o tablas donde se encuentran los datos que se quieren seleccionar)
WHERE (las condiciones que tienen que cumplir los datos que se quieren seleccionar)
La parte del SELECT es la de selección. A continuación se debe indicar lo que se desea recuperar de la base de datos. Básicamente se ponen los campos de la tabla (cuyo nombre pondremos a continuación en la parte FROM) que se quieren recuperar separados por comas (supongo que te acordarás que las tablas se dividen en campos que contienen la información del tipo con el que se haya creado ese campo); otra opción es poner "*" que significa que queremos recuperar todos los campos.
La parte del FROM es la de origen. Se escribe el nombre de la tabla donde se encuentran esos campos que queremos recuperar. La parte del WHERE es la de condición. Es opcional (no es necesario que aparezca) y funciona mediante expresiones aritméticas o booleanas que condicionan la selección.
Con un ejemplo sencillo se verá todo más claro. Poco a poco iré explicando detalles más complicados.
Voy a obtener todos los campos de la tabla game_level:
SELECT *
FROM game_level
Así de fácil. Esta consulta mostrará todos los campos de la tabla (la explicación del significado de las tablas y los campos que las componen están en el segundo artículo de la serie). Si solo se quiere que muestre los campos de identificador de nivel y su descripción, bastaría con poner:
SELECT game_level_id, game_level_desc
FROM game_level
Como se ve, solo aparecen los campos seleccionados en la parte de selección. Cabe destacar, que si no se usa la parte de condición, la selección se hace sobre todas las filas de la tabla. El número de columnas de la tabla resultado es el número de elementos que tenemos en la selección separados por comas; en este caso, dos. El título de la columna de la tabla resultado es el del nombre del campo.
En la parte de selección se pueden utilizar una serie de funciones que hacen operaciones sobre los campos, se escribe la palabra que representa a la función y entre paréntesis el campo al que se le aplica. Las funciones son:
* Max(campo): obtiene el valor máximo del campo.
* Min(campo): obtiene el valor mínimo del campo.
* Sum(campo): suma los registros del campo elegido.
* Avg(campo): obtiene la media de las filas del campo elegido.
* Count(*): muestra el total de filas seleccionadas.
Si por ejemplo se quiere obtener el máximo bote ganado en las manos almacenadas de cash habría que usar la consulta (el bote máximo sin importar quién lo ha ganado). Te recuerdo que el resumen de las manos se guardan en la tabla game:
SELECT MAX(pot)
FROM game
Si te fijas, el nombre de la columna no es descriptivo. Esto se debe a que como no es un campo, sino una operación sobre un campo, no tiene un nombre fijo asignado. Para que aparezca un título en la columna de la tabla resultado, y lo que es más importante, para que se pueda utilizar este cálculo temporal posteriormente dentro de la selección en consultas más elaboradas, es preciso usar un alias que identificará el resultado temporal. Para ello basta con escribir la palabra AS y el nombre que se le quiere asignar (también se puede utilizar con los campos para que aparezca con otro nombre la columna resultado y para volver a usar esos campos):
SELECT MAX(pot) AS bote_máximo
FROM game
Si ahora se quiere obtener el bote máximo ganado por cada jugador, se puede utilizar una nueva instrucción que agrupa las filas que tienen el mismo valor en un campo. Esa instrucción se representa en SQL mediante las palabras GROUP BY. Para hacer esta consulta, habrá que agrupar las filas que tengan el mismo valor en el campo player_id (que es el que identifica al jugador). Para que aparezca también el identificador del jugador, se añade ese campo en la selección:
SELECT player_id, MAX(pot) AS bote_máximo_por_jugador
FROM game
GROUP BY player_id
Como se puede apreciar, aparece un identificador del jugador que es un número. Esto es debido a que es así cómo se guarda en el resto de tablas que no es la de jugadores (players) para diferenciarlos. Si se quieren obtener más datos del jugador, hay que mirar también en la tabla players, pero eso lo dejo para más adelante.
Ahora voy a tratar la parte condicional de las consultas. Hasta aquí, las consultas estaban realizadas sobre todas las filas de la tabla. Si se quiere seleccionar sobre solo una fracción de ellas, es preciso usar la parte del WHERE. A continuación es preciso establecer las condiciones que debe cumplir la búsqueda. Para ello se usan los campos o los alias (entre corchetes) de la parte de la selección y las relaciones entre ellos. Estas relaciones pueden ser de comparación: mayor que, menor que, mayor o igual que, menor o igual que, distinto, igual (>, , >=, =, >, =); lógicas: y, o, no (And, Or, Not) y otros operadores como intervalo (Between And), filas diferentes (Distinct), similares (Like), conjunto (In, Not In), si el campo es vacío (Is Null), etc.
Para entenderlo mejor, voy a hacer la consulta del máximo bote de cash menor de 50:
SELECT MAX(pot) AS bote_máximo
FROM game
WHERE pot '' 50
Si se quieren ver los datos de un jugador como todos los identificadores de jugador, los alias y las salas que se corresponden con un nombre de usuario (en mi caso suelo usar "spainfull"), bastaría hacer la siguiente selección (estos datos se encuentran en la tabla players, que almacena en cada fila los datos de un jugador en una sala; el identificador de sala es un valor que se corresponde con una sala que se puede ver en la tabla poker_sites):
SELECT player_id AS jugador_id, main_site_id AS sala, alias_id AS alias
FROM players
WHERE screen_name = "spainfull"
Si se quieren ver todos los identificadores de un jugador, la sala a la que corresponde ese identificador, el nombre de usuario en esa sala, conociendo el número empleado como alias_id (en mi caso 1) se pueden obtener con la siguiente consulta:
SELECT player_id AS jugador_id, main_site_id AS sala, screen_name AS nombre
FROM players
WHERE alias_id = 1
Como en la fila del identificador original que se emplea como alias_id, el campo alias_id está vacío, no aparece en los resultados. Para que así suceda, es preciso añadir una condición además de la que se ha puesto en el ejemplo anterior, que el alias sea 1 o que el identificador del jugador sea 1:
SELECT player_id AS jugador_id, main_site_id AS sala, screen_name AS nombre
FROM players
WHERE alias_id = 1 OR player_id = 1
La siguiente consulta será el bote medio conseguido por mí en cualquier nivel de juego almacenado en la tabla:
SELECT SUM(pot)/COUNT(game_id) AS bote_medio
FROM game
WHERE player_id = 1
Se suman todos los botes y se divide por el número de filas en las que he ganado (en el campo player_id de la tabla game se almacena el ganador de la mano). Si además se desea saber el bote medio obtenido en un nivel concreto (los niveles se guardan en la tabla game_level), por ejemplo en $1/$2 que corresponde con identificador 5 (game_level_id establece el nivel de la mano en la tabla game):
SELECT SUM(pot)/COUNT(game_id) AS bote_medio
FROM game
WHERE player_id = 1 AND game_level_id = 5
Si además se quiere ver el total y el número de botes ganados para que quede más informativa y clara la consulta:
SELECT SUM(pot) AS bote_total, COUNT(game_id) AS botes_ganados, [bote_total]/[botes_ganados] AS bote_medio
FROM game
WHERE player_id = 1 AND game_level_id = 5
Como se puede comprobar, sabiendo qué campos elegir y cómo hacer condiciones, con todo lo visto hasta ahora se pueden hacer consultas sencillas.
Para obtener datos que se encuentran en más de una tabla que están unidos por un identificador (como por ejemplo player_id en varias tablas) hay que utilizar lo que se conoce como unión. Por ejemplo, si en la consulta realizada anteriormente del bote máximo de cada jugador se quieren obtener más datos por jugador, hay que mirar en la tabla players. Si por ejemplo se quiere mostrar además el nombre del jugador al que pertenece ese identificador, habrá que obtenerlo de esa tabla. Para conseguirlo, en la parte de selección se señala de qué tabla proviene cada campo anteponiendo al nombre del campo el nombre de la tabla a la que pertenece y un punto. En la parte de origen, se ponen los nombres de las tablas separados por la instrucción INNER JOIN y se especifica qué campos son los que unen las tablas (los que significan lo mismo, son del mismo tipo aunque puedan tener distinto nombre) tras la palabra ON:
SELECT game.player_id, players.screen_name, Max(game.pot) AS bote_máximo_por_jugador
FROM game INNER JOIN players ON game.player_id = players.player_id
GROUP BY game.player_id, players.screen_name
Esta consulta se podría poner de otra manera en la que se ve claro lo que hace el INNER JOIN:
SELECT game.player_id, players.screen_name, Max(game.pot) AS bote_máximo_por_jugador
FROM game, players
WHERE game.player_id = players.player_id
GROUP BY game.player_id, players.screen_name
Por último, voy a diseccionar la consulta que mostré como ejemplo en el primer artículo, que calculaba las veces que mejoraba nuestra pareja a trío en el flop:
SELECT gp.hole_cards AS Par, count(*) AS Veces, SUM(
IIF(left(g.flop_1,1)=left(gp.hole_card_1,1),1,
IIF(left(g.flop_2,1)=left(gp.hole_card_1,1),1,
IIF(left(g.flop_3,1)=left(gp.hole_card_1,1),1,
0)))) AS [Trío en flop]
FROM game_players AS gp INNER JOIN game AS g ON gp.game_id = g.game_id
WHERE gp.player_id = (select pref_value from prefs where pref_key = 'RP')
AND gp.pair_hand = 1
AND gp.saw_flop_n = 1
GROUP BY gp.hole_cards, gp.card_order1
ORDER BY gp.card_order1 DESC
Antes de empezar a hacer una consulta se debe tener claro qué es lo que se quiere conseguir y dónde están esos datos. Lo que se pretende es ver cuándo nuestra pareja de mano ha mejorado a trío. Las cartas comunes de cada mano se guardan en la tabla game, pero las cartas de cada jugador en cada mano se almacenan en la tabla game_players. Por lo tanto, está claro que habrá que usar estas dos tablas. El campo que une estas dos tablas es el game_id, que identifica unívocamente cada mano. Así, tenemos la parte de origen que queda de la siguiente manera:
FROM game_players AS gp INNER JOIN game AS g ON gp.game_id = g.game_id
Se emplea un alias para cada tabla para no tener que volver a escribir el nombre de las tablas antes de cada campo y que quede demasiado largo.
En la parte de selección se detallan las columnas que se quieren visualizar como resultado. En este caso, la pareja de mano que se tiene (en la tabla game_players el campo hole_cards indica la mano que tiene el jugador), el número de veces que se ha dado cada pareja cuando se ha visto el flop, y el número de veces que ha mejorado a trío. Si se hiciera la consulta sin la parte condicional, la selección se haría sobre todas las manos de la tabla game, pero lo que se quiere es que se trate de una mano nuestra, que sea una pareja de mano y que se haya visto el flop. Pero volviendo a la parte de selección, tenemos:
SELECT gp.hole_cards AS Par, count(*) AS Veces, SUM(
IIF(left(g.flop_1,1)=left(gp.hole_card_1,1),1,
IIF(left(g.flop_2,1)=left(gp.hole_card_1,1),1,
IIF(left(g.flop_3,1)=left(gp.hole_card_1,1),1,
0)))) AS [Trío en flop]
Los dos primeros elementos están claros, la mano inicial (que serán parejas por las condiciones que se pondrán después) y el número de veces que nos han repartido esas parejas cuando hemos visto el flop (también significará esto por las condiciones de la parte WHERE que se añadirán). La tercera columna es más sencilla de lo que parece; para que nuestra pareja se convierta en trío, precisa que una de las cartas del flop sea igual a cualquiera de las que tenemos en mano; eso es lo que indica la expresión que está dentro del SUM.
Las cartas que nos han repartido están en la tabla game_players en los campos hole_card_1 y hole_card_2 y las del flop en la tabla game en los campos flop_1, flop_2 y flop_3. Estos campos son alfanuméricos (letras y números) y cada carta se representa por su valor (un número del 2 al 9 o una letra mayúscula si es un A, T, J, Q o K seguido de una letra minúscula que indica el palo); por tanto se debe obtener el valor de la carta sin importar el palo, es decir, el primer carácter de los campos a seleccionar. Para ello se cuenta con la instrucción de SQL left(campo, x) que obtiene x caracteres empezando por la izquierda en un campo alfanumérico. En este caso nos interesa mirar si el valor de una de nuestras cartas de mano (como es una pareja no importa con cuál comparemos) es igual a cualquiera de las tres del flop. Con este objetivo se utiliza left(gp.hole_card_1, 1) para nuestra mano y se compara con left(g.flop_x,1). IIF evalúa una condición y si se cumple se da el primer valor, y si no el segundo: IIF(condición, valor_1, valor_2); en este caso, si coincide la comparación que se hace entre nuestra carta de mano y la primera del flop, el valor es uno y se suma y se sale de la expresión (como vez que conseguimos trío, se suma porque se emplea SUM encerrando a la expresión completa), si no coincide se mira la segunda del flop, si coincide se suma y se sale de la expresión, si no se mira la tercera y si coincide se suma y se sale de la expresión, y por fin, si no es que no se consiguió trío y el valor que se obtiene y se suma es 0. Puede parecer complicado, pero con un poco de paciencia se entiende sin problemas.
Ahora la parte de la condición:
WHERE gp.player_id = (select pref_value from prefs where pref_key = 'RP')
AND gp.pair_hand = 1
AND gp.saw_flop_n = 1
GROUP BY gp.hole_cards, gp.card_order1
ORDER BY gp.card_order1 DESC
La primera condición es que sea nuestro jugador. Se podría poner el número que se ha obtenido en una de las consultas anteriores, pero en este caso se ha anidado otra consulta (se debe escribir entre paréntesis) que coge de la tabla de preferencias prefs nuestro identificador de jugador, almacenado en el campo pref_value y que se asocia al valor "RP" del campo pref_key. La siguiente condición es que sea una pareja de mano, aspecto almacenado en la tabla game_players en el campo pair (1 si es pareja de mano, 0 si no). La siguiente, que yo haya visto el flop, guardado en el campo saw_flop de la misma tabla (1 si se ve el flop, 0 si no). Por último, se agrupan las cartas de mano que sean iguales y también por el valor de la mano. Para mostrarlas ordenadas por el valor de la mano se emplea la instrucción ORDER BY (y para seleccionar que en orden inverso de valor la instrucción DESC -el valor de las cartas en el campo card_orderx va del 2 para el 2 al 14 para el As-).
Con esto se acaba el tutorial de cómo realizar consultas SQL en la BD de PT. Espero que te haya servido de algo. Hay mucha información disponible sobre SQL en la red que cubre aspectos y funcionalidades que no he tratado y que pueden resultar interesantes. De todas maneras, si tienes alguna duda, ¡pregunta coño!
Saludos.