A la lliçó anterior retornaves dades tal com estan desades. Però una base de dades no desa el preu amb IVA, ni el marge d'un producte, ni l'import d'una línia de comanda: desa les peces mínimes i la resta es calcula en consultar-ho. En aquesta lliçó aprendràs a construir aquestes columnes calculades amb expressions aritmètiques i de text, a posar-los un nom llegible amb AS, i a entendre —fent servir l'ordre lògic d'execució de 02-01— per què aquest nom es pot fer servir en uns llocs de la consulta i en uns altres no. Aquí apareix, a més, l'expressió més important de tot el curs: quantitat * preu_unitari * (1 - descompte).

1. Àlies de columna amb AS
2. Quan l'àlies necessita cometes dobles
3. Àlies de taula
4. Columnes calculades: aritmètica sobre columnes
5. L'expressió estrella: l'import d'una línia de comanda
6. Concatenació de text: || i CONCAT
7. ROUND per presentar diners
8. Per què l'àlies no funciona a WHERE però sí a ORDER BY
9. Errors habituals i consells
10. Exercicis
11. Conclusió

Un àlies és el nom que vols que tingui una columna al resultat. Es declara amb AS:

Abans de res, comprovem per què cal. Sense àlies, una expressió no té nom:

?column? és la manera que té PostgreSQL de dir "això no té nom". Un informe amb aquesta capçalera no serveix de res, i un programa que accedeixi al resultat pel nom de la columna no hi trobarà res. Amb àlies:

(10 primeres de 20 files.)

La paraula AS és opcional per a les columnes. Aquestes dues línies són equivalents:

Tot i així, escriu-la sempre. Sense AS, oblidar una coma converteix una columna en l'àlies de l'anterior i la consulta s'executa sense error donant un resultat equivocat (ho vas veure a 01-03 i a 02-01):

Un àlies és un identificador, així que segueix les regles de 01-03: sense cometes es plega a minúscules i només admet lletres, dígits i guions baixos.

Aquest és l'únic ús legítim de les cometes dobles en aquest curs: àlies de presentació destinats a un informe o a una exportació. Per a qualsevol àlies que hagis de reutilitzar dins de la consulta, fes servir snake_case en minúscules i estalvia't les cometes.

Nota de dialecte: MySQL i SQLite permeten a més fer servir cometes invertides (`Preu amb IVA`) o cometes simples com a delimitador d'àlies. PostgreSQL no: cometes dobles o res. Escriu sempre la forma estàndard.

També les taules admeten àlies, i aquí AS és igualment opcional:

Amb una sola taula sembla un caprici: p.nom no aporta res enfront de nom. El seu valor apareix al mòdul 3, quan una consulta combini diverses taules que tenen columnes amb el mateix nom:

Sense els àlies p i c, nom seria ambigu i PostgreSQL respondria column reference "nom" is ambiguous. Per això, en SQL professional, gairebé totes les consultes porten àlies de taula.

Convencions útils per als àlies de taula:

Una diferència que sorprèn: si poses àlies a la taula, el nom original deixa d'estar disponible. Això falla:

Una columna calculada és una expressió a la llista del SELECT. S'avalua fila a fila, amb els valors d'aquella fila.

(10 primeres de 20 files.)

En la resta, l'escala del resultat és la més gran de les dues, així que el marge surt amb dos decimals nets. En la multiplicació no serà així.

(6 primeres de 20 files.)

Tres coses per aprendre d'aquí:

1. Els parèntesis són obligatoris. Sense ells, preu - cost / preu * 100 s'avaluaria com preu - ((cost / preu) * 100), perquè * i / tenen més prioritat que - (taula de precedència de 01-03). El resultat per al producte 1 seria 12.50 - 62.4 = -49.90. No dona error: dona un número absurd.
2. La divisió és decimal, no entera, perquè preu és NUMERIC i no INTEGER. Si les columnes fossin enteres, (preu - cost) / preu donaria 0 per a totes les files i després 0 * 100 = 0. És la fallada clàssica en calcular percentatges; es resol convertint un operand a NUMERIC amb CAST (mòdul 6).
3. La cua de decimals és lletja. PostgreSQL calcula la divisió NUMERIC amb precisió àmplia. Per presentar-ho cal ROUND (secció 7).

(Selecció de files de les 20.)

Aquí stock és INTEGER i preu és NUMERIC(10,2): en barrejar tipus, PostgreSQL promou l'enter a NUMERIC i el resultat conserva dos decimals.

Compte amb la divisió entre zero. Si en comptes de stock * preu calculessis alguna_cosa / stock, el producte 13 (stock 0) faria fallar la consulta sencera amb ERROR: division by zero. La protecció es fa amb NULLIF, que es veu al mòdul 6.

A 01-06 va quedar fixat que comandes no té columna total i que l'import d'una línia és:

Aquesta expressió apareixerà pràcticament a tots els mòduls que queden. Escrivim-la per primera vegada:

(12 primeres de 47 files.)

Desmuntem l'expressió, perquè cada peça importa:

Comprova la línia 6 a mà: 6 unitats × 1.95 € = 11.70 €; amb un 10 % de descompte, 11.70 × 0.90 = 10.53 €. Coincideix.

I fixa't en els quatre decimals: 10.5300, no 10.53. La quantitat és entera i no aporta escala, preu_unitari n'aporta 2 i (1 - descompte) altres 2, així que el resultat surt amb 4. Aritmèticament és correcte i per sumar és el que vols, però per ensenyar-ho a algú cal arrodonir.

Sis línies de les 47 porten descompte; aquests són els seus imports exactes:

Aquí hi ha un detall revelador: la línia 24 val 34.0200 € i la devolució de la comanda 10 registrada a devolucions és de 34.02 €. Les dades són coherents entre taules, i comprovar-ho amb una consulta serà un dels exercicis del mòdul 3.

Error freqüent: escriure quantitat * preu_unitari * descompte. Això calcula l'import descomptat, no l'import cobrat. Per a la línia 6 donaria 1.17 € en comptes de 10.53 €.

L'operador estàndard de concatenació en SQL és ||:

Pots concatenar text amb números: PostgreSQL converteix el número a text automàticament.

(3 primeres de 20 files.)

Concatenar qualsevol cosa amb NULL dona NULL. Tot el text es perd. Aquesta és una de les sorpreses més cares de SQL, i BotigaVerda té dades per demostrar-ho: set clients tenen referit_per_id a NULL.

Set files amb origen buit, no set files amb el text i un forat. El literal 'Referit pel client ' desapareix del tot, perquè la regla dels nuls és que qualsevol operació amb un valor desconegut produeix un valor desconegut.

La funció CONCAT ignora els NULL i els substitueix per una cadena buida:

(Selecció de files de les 15.)

Ja no es perd el text, però el resultat tampoc no és correcte: diu "Referit pel client" sense dir quin. La solució de debò és substituir el nul per un text sensat amb COALESCE, que s'estudia a la lliçó 06-04:

Comparativa per tenir a mà:

| Aspecte | a || b | CONCAT(a, b) | |---|---|---| | Estàndard SQL | Sí | No (però molt estesa) | | Amb NULL | Retorna NULL | Tracta el NULL com a '' | | A MySQL | Per defecte no concatena: || és l'OR lògic | És la forma habitual | | A SQL Server | No existeix; es fa servir + | Existeix des del 2012 | | A SQLite | Sí | No existeix |

Nota de dialecte important: a MySQL, 'a' || 'b' retorna 0, perquè allà || significa OR. Només es comporta com a concatenació si el servidor té activat el mode PIPES_AS_CONCAT. Si escrius SQL que hagi de funcionar a MySQL i a PostgreSQL, fes servir CONCAT.

ROUND(expressió, decimals) arrodoneix a la quantitat de decimals que indiquis:

Ara sí que és una taula presentable. I amb ella ja pots respondre a una pregunta de negoci: el producte amb més marge percentual és el raspall de dents de bambú (65.71 %), i el que menys, l'oli d'oliva verge extra (37.60 %) —el producte estrella del catàleg és, precisament, el que deixa menys marge relatiu.

El mateix per a l'import de línia:

(Les 6 línies amb descompte, de les 47.)

Observa la línia 18: el valor exacte és 35.6250 i ROUND el deixa en 35.63. Sobre tipus NUMERIC, PostgreSQL arrodoneix "mig cap amunt" (allunyant-se del zero), que és el que espera qualsevol comptable.

Regla del curs: calcula amb l'expressió completa i arrodoneix només al final, per presentar. Si arrodoneixes cada línia i després sumes, el total pot diferir en cèntims del total arrodonit. Les funcions numèriques (ROUND, CEIL, FLOOR, TRUNC, ABS) s'estudien a fons a la lliçó 06-02.

Aquí és on l'ordre lògic de 02-01 deixa de ser teoria. Intenta filtrar per una columna calculada:

La taula existeix, l'àlies està escrit just a sobre, i tot i així el motor diu que aquesta columna no existeix. L'explicació és al diagrama:

Quan s'avalua WHERE, el pas SELECT encara no s'ha executat, així que el nom preu_amb_iva encara no s'ha creat. Quan s'avalua ORDER BY, sí.

Les dues solucions per filtrar per una expressió:

I a ORDER BY l'àlies funciona sense més (lliçó 02-05):

(5 primeres de 20 files.)

Resum per memoritzar:

• Oblidar AS i perdre una coma. SELECT nom preu FROM productes no falla: retorna els noms sota la capçalera preu. Compta sempre les columnes del resultat.
• Àlies amb cometes simples. AS 'Preu' és un error de sintaxi a PostgreSQL. Cometes dobles per a identificadors, simples per a cadenes.
• Esperar que AS PreuIVA conservi les majúscules. Sense cometes es plega a preuiva.
• Fer servir un àlies a WHERE. column "..." does not exist. Repeteix l'expressió o fes servir una subconsulta.
• Oblidar els parèntesis al marge percentual. preu - cost / preu * 100 no dona error i retorna números sense sentit.
• Confondre el descompte amb l'import descomptat. L'import cobrat és quantitat * preu_unitari * (1 - descompte); sense l'1 - calcules justament el contrari.
• Concatenar amb || sobre columnes que poden ser nul·les. Perds tota la cadena. Fes servir CONCAT o, millor, COALESCE (mòdul 6).
• Arrodonir massa aviat. Arrodoneix només a la capa de presentació; els càlculs intermedis, amb tota la precisió.
• Consell: anomena els àlies com a columnes reals. snake_case, en minúscules, descriptius: preu_amb_iva, marge_pct, import. Els àlies "bonics" amb cometes, només per a l'informe final.
• Consell: valida l'expressió amb SELECT sense FROM. SELECT 8 * 1.95 * (1 - 0.15); et confirma en un segon si la fórmula és la que creus.

Escriu una consulta sobre productes que retorni, amb àlies llegibles: el nom del producte, el seu preu, el seu marge unitari en euros i el seu marge percentual arrodonit a un decimal. Explica per què els parèntesis del marge percentual són imprescindibles.

Sobre empleats, construeix una única columna de text anomenada fitxa amb aquest format exacte: Cognoms, Nom (Càrrec) - Ciutat. Per exemple: Alcázar Vives, Rosa (Directora general) - València.

Sobre linies_comanda, retorna l'id de la línia, la comanda a la qual pertany, l'import brut (sense aplicar descompte), l'import descomptat en euros i l'import final cobrat, els tres arrodonits a dos decimals. Comprova els teus resultats amb les sis línies que tenen descompte.

Solució 1

(10 primeres de 20 files.)

Raonament sobre els parèntesis. La precedència d'operadors de 01-03 diu que * i / s'avaluen abans que -. Sense parèntesis, preu - cost / preu * 100 significa preu - ((cost / preu) * 100). Per al producte 1: 12.50 - ((7.80/12.50) * 100) = 12.50 - 62.40 = -49.90. Un marge negatiu de -49.90 en un producte que guanya 4.70 € per unitat. La consulta s'executa sense cap avís: és un error silenciós, el pitjor tipus.

Solució 2

Raonament. S'alternen columnes i literals de text: els literals van entre cometes simples i porten a dins els espais, comes i parèntesis del format. Han sortit les 8 files completes perquè en aquesta taula cap de les quatre columnes utilitzades no és nul·la. Si ciutat ho fos —i podria ser-ho, perquè la columna admet nuls—, aquella fila retornaria NULL sencera. En una consulta destinada a producció convindria blindar-la amb COALESCE(ciutat, 'Sense assignar'), que veuràs a 06-04.

Solució 3

Les sis línies amb descompte, de les 47 que retorna la consulta:

Raonament. Les tres columnes comparteixen la base quantitat * preu_unitari; el que canvia és què es fa amb descompte: multiplicar per descompte dona el que el client s'estalvia, multiplicar per (1 - descompte) dona el que paga. La comprovació que la fórmula és correcta és que descompte_eur + import_final = import_brut a totes les files. A la línia 18 hi ha una diferència d'un cèntim aparent (1.88 + 35.63 = 37.51 ≠ 37.50): és l'efecte d'arrodonir cada columna per separat, ja que els valors exactes són 1.8750 i 35.6250. És exactament el motiu pel qual s'arrodoneix al final i no a cada pas intermedi.

Ara les teves consultes no només llegeixen: calculen.

• Un àlies amb AS dona nom a qualsevol columna o expressió; sense ell veuràs ?column?. Escriu AS sempre, encara que sigui opcional.
• Les cometes dobles en un àlies només són necessàries si conté espais, símbols o majúscules que vulguis conservar: fes-les servir únicament per a àlies de presentació.
• Els àlies de taula semblen superflus amb una sola taula i seran obligatoris al mòdul 3.
• Saps construir columnes calculades: preu amb IVA, marge en euros i marge percentual, amb els parèntesis al seu lloc i sense caure en la divisió entera.
• Domines l'expressió que sosté tot el curs: quantitat * preu_unitari * (1 - descompte), i entens per què el descompte és una fracció i per què el resultat surt amb quatre decimals.
• Concatenes text amb || i coneixes el seu parany: qualsevol operand NULL anul·la tota la cadena. CONCAT l'evita, COALESCE (mòdul 6) el resol bé.
• Presentes imports amb ROUND(expressió, 2), arrodonint només al final.
• I, sobretot, entens per l'ordre lògic d'execució per què un àlies encara no existeix a WHERE i sí a ORDER BY.

A la lliçó següent, Filtrar dades amb WHERE, afegiràs el pas 2 del diagrama: deixaràs de portar les 20 files de productes o les 47 de linies_comanda i començaràs a demanar només les que compleixen una condició. Amb comparacions sobre números, text i dates, i amb AND, OR, NOT i uns parèntesis que, un cop més, marcaran la diferència entre un resultat correcte i un de silenciosament equivocat.