Paraulogic: claus primàries amb AUTO INCREMENT i taules relacionades

De wikijoan
La revisió el 17:05, 25 gen 2022 per Joan (discussió | contribucions) (→‎Inserció de les dades)
(dif) ← Versió més antiga | Versió actual (dif) | Versió més nova → (dif)
Salta a la navegació Salta a la cerca

Introducció

Paraulogic2.png

El paraulogic és un joc de paraules que s'ha popularitzat darrerament. Es tracta de cercar paraules vàlides (han d'estar en el diccionari DIEC) combinant 7 lletres, una de les quals (la del mig) és obligatòria)

Volem introduir la solució que es publica cada dia (la solució del dia anterior), en una base de dades, de la manera més ràpida i eficient possible.

Definició de les dades

El model de dades és bastant senzill. És una relació 1:N. Cada dia representa que és un JOC, que pot tenir N paraules. Del JOC ens interessa el dia, les lletres vàlides d'aquell dia, i el número de paraules vàlides d'aquell dia.

De la PARAULA ens interessa la col·lecció de paraules, referenciades al dia que va sortir la paraula. paraula no és una bona clau primària (una paraula pot sortir diferents dies), sinó que tenim id_paraula. De la taula JOC també tenim id_joc com a clau primària. La cosa queda:

CREATE DATABASE paraulogic DEFAULT CHARACTER SET utf8;
USE paraulogic;

DROP TABLE PARAULA;
DROP TABLE JOC;

# a SQL podem utilitzar expressions regulars
# https://www.geeksforgeeks.org/mysql-regular-expressions-regexp/
CREATE TABLE JOC (
id_joc SMALLINT AUTO_INCREMENT PRIMARY KEY,
lletres CHAR(7) NOT NULL CHECK (lletres REGEXP '[A-Z]{7}'),
total TINYINT UNSIGNED,
dia DATE
);

CREATE TABLE PARAULA (
id_paraula MEDIUMINT AUTO_INCREMENT PRIMARY KEY,
id_joc SMALLINT NOT NULL,
paraula VARCHAR(25) NOT NULL,
FOREIGN KEY (id_joc) REFERENCES JOC(id_joc) ON DELETE CASCADE ON UPDATE CASCADE
);

ALTER TABLE PARAULA ADD COLUMN num_cars TINYINT UNSIGNED;

Inserció de les dades

En aquest cas ens interessa utilitzar AUTO_INCREMENT, i així ens oblidem de portar el compte dels IDs. Ara bé, la paraula l'hem de relacionar correctament amb el id_joc. Ho farem de la següent manera:

INSERT INTO JOC(lletres,total, dia) VALUES ('EGLNOTU',75,'2022/01/19');
SET @last_id = LAST_INSERT_ID();

INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'adagi',LENGTH('adagi'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'adagi',LENGTH('aganada'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'adagi',LENGTH('agnada'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'adagi',LENGTH('aguda'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'aiguada',LENGTH('aiguada'));
...

INSERT INTO JOC(lletres,total, dia) VALUES ('VAEJNRT',91,'2022/01/20');
SET @last_id = LAST_INSERT_ID();

INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'arravatar',LENGTH('arravatar'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'avant',LENGTH('avant'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'avantatjar',LENGTH('avantatjar'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'avanter',LENGTH('avanter'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'avantera',LENGTH('avantera'));
...

INSERT INTO JOC(lletres,total, dia) VALUES ('AEHIRUX',107,'2022/01/21');
SET @last_id = LAST_INSERT_ID();

INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'aeri',LENGTH('aeri'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'aèria',LENGTH('aèria'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'ahir',LENGTH('ahir'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'ai-ai',LENGTH('ai-ai'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'aïrar',LENGTH('aïrar'));
...

INSERT INTO JOC(lletres,total, dia) VALUES ('NCEORTU',126,'2022/01/22');
SET @last_id = LAST_INSERT_ID();

INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'cent',LENGTH('cent'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'centè',LENGTH('centè'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'centener',LENGTH('centener'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'centó',LENGTH('centó'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'centre',LENGTH('centre'));
...

INSERT INTO JOC(lletres,total, dia) VALUES ('GAELOPS',116,'2022/01/23');
SET @last_id = LAST_INSERT_ID();

INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'agà',LENGTH('agà'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'agalla',LENGTH('agalla'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'àgape',LENGTH('àgape'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'aglà',LENGTH('aglà'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'aglòs',LENGTH('aglòs'));
...

Procediment diari: Excel

Tenim un Excel (Libreoffice Calc) on cada dia copiem la llisa de paraules vàlides, a la primera columna.

A la segona columna tenim la fórmula:

=CONCATENA("INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'";A1;"',LENGTH('";A1;"'));")

Fixem-nos que amb la funció LENGTH també podem introduir a la bd el número de caràcters de la paraula. És una informació que pot ser rellevant si volem fer algunes estadístiques.

Per tant, tenim un procediment fàcil i ràpid d'introduir cada dia les paraules, i ens despreocupem dels valors dels índex (id_joc, id_paraula), però tenim la certesa de que la informació està ben relacionada.

Dades gener 2022

Les dades que van sortir al Paraulògic entre el 19 i el 24 de gener de 2022 les tens aquí:

Consultes

1. dies disponibles a la base de dades

mysql> select distinct dia from JOC order by dia;
+------------+
| dia        |
+------------+
| 2022-01-19 |
| 2022-01-20 |
| 2022-01-21 |
| 2022-01-22 |
| 2022-01-23 |
| 2022-01-24 |
+------------+

2. dia amb més paraules

Farem aquesta consulta pas a pas.

#1. Relacionem les dues taules.

select * from PARAULA P INNER JOIN JOC J
ON P.id_joc=J.id_joc;

#2. Només ens interessa el dia i el id_paraula:

select dia,id_paraula from PARAULA P INNER JOIN JOC J
ON P.id_joc=J.id_joc;

#3. Ara ja podem agrupar per dia:

select dia,count(id_paraula) as num from PARAULA P INNER JOIN JOC J
ON P.id_joc=J.id_joc
GROUP BY dia;

#4. Ordenem aquesta llista (ja sabem quin és el valor més gran):

select dia,count(id_paraula) as num from PARAULA P INNER JOIN JOC J
ON P.id_joc=J.id_joc
GROUP BY dia order by num DESC;

#5. Ens quedem només amb el primer valor:

select dia,count(id_paraula) as num from PARAULA P INNER JOIN JOC J
ON P.id_joc=J.id_joc
GROUP BY dia order by num DESC LIMIT 1;

+------------+-----+
| dia        | num |
+------------+-----+
| 2022-01-22 | 126 |
+------------+-----+

creat per Joan Quintana Compte, gener 2022