Localització del contingut dels grups de més valor en SQL

Codi SQL (Structured Query Language) al monitor de l’ordinador i al fons de la sala del servidor. Exemple de codi SQL per consultar dades d’una base de dades.

Sovint, mentre s’ocupa de l’SQL per preparar-se per a les entrevistes, un es troba amb la qüestió de trobar l’empleat amb el salari més alt o el segon més alt unint una taula que conté informació dels empleats amb una altra que conté informació del departament. Això ens planteja la pregunta: què passa amb trobar l’empleat que guanya l’enèsimo salari més alt? departamentalment?

També pot haver-hi un escenari en què un departament o departaments concrets tinguin/no continguin un enèsimo valor més alt, per exemple, un departament amb només dos empleats no tindrà un empleat que guanyen el tercer salari més alt. Aquest article tendeix a emfatitzar aquest escenari.

Per tant, comencem creant dues taules:

1. Departament

Aquesta taula inclou camps com ara: dept_id i dept_name.

CREATE TABLE department (
    dept_id int,
    dept_name varchar(60)
);

Inseriu diversos departaments a la nova taula.

INSERT INTO department (dept_id,dept_name)
VALUES (780,'HR');
INSERT INTO department (dept_id,dept_name)
VALUES (781,'Marketing');
INSERT INTO department (dept_id,dept_name)
VALUES (782,'Sales');
INSERT INTO department (dept_id,dept_name)
VALUES (783,'Web Dev');
imatge-15
Fig 1. Taula de departaments

2. Empleat

Aquesta taula incorpora els camps: first_name, last_name, dept_id i salari.

CREATE TABLE employee (
    first_name varchar(100),
    last_name varchar(100),
    dept_id int,
    salary int
);

Inserció de valors a la taula:

INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Sam','Burton',781,80000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Peter','Mellark',780,90000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Happy','Hogan',782,110000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Steve','Palmer',782,120000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Christopher','Walker',783,140000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Richard','Freeman',781,85000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Alex','Wilson',782,115000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Harry','Simmons',781,90000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Thomas','Henderson',780,95000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Ronald','Thompson',783,130000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('James','Martin',783,135000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Laurent','Fisher',780,100000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Tom','Brooks',780,85000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Tom','Bennington',783,140000);
imatge-16
Figura 2. Taula d’empleats ordenada per dept_id

De la taula d’empleats es dedueix el següent:

Department_ID:Nombre d’empleats:

780:4

781:3

782:3

783:4

Per entendre què pretén aconseguir aquest article, observem la sortida desitjada per a diferents entrades.

Tenint en compte que volem la informació del segon empleat amb més ingressos de diferents departaments juntament amb el nom del seu departament (tenint en compte l’ús de DENSE_RANK), la sortida serà la següent:

imatge-17
Figura 3. Informació del segon empleat amb més guanys per departament

Si apliquem la mateixa consulta per trobar els quarts empleats amb més ingressos, el resultat serà el següent:

imatge-18
Fig 4.

Observeu que tot i que el departament 783 té quatre empleats, n’hi ha dos que seran classificats com els tercers empleats amb més ingressos d’aquest departament. Ara, a la pregunta principal: què passaria si volguéssim mostrar el dept_ID i el dept_name amb valors nuls per als camps relacionats amb els empleats quan els departaments tinguin/no tinguin un enèsim empleat amb més ingressos? Per a això, el resultat representat a la Fig. 4 es modifica de la manera següent:

imatge-19
Fig 5.

La taula que es mostra a la Fig. 5 és el que volem obtenir quan determinats departaments no tinguin l’enèsimo empleat amb més ingressos.

La consulta final que ajuda a obtenir la taula de la figura 5 és la següent:

SELECT * FROM (WITH null1 AS (select A.dept_id, A.dept_name, A.first_name, A.last_name, A.salary
from (SELECT * FROM (
SELECT department.dept_id, department.dept_name, employee.first_name, employee.last_name,
employee.salary, DENSE_RANK() OVER (PARTITION BY employee.dept_id ORDER BY employee.salary DESC) AS Rank1
FROM employee INNER JOIN department
ON employee.dept_id=department.dept_id) AS k
WHERE rank1=4)A),
full1 AS (SELECT dept_id, dept_name FROM department WHERE dept_id NOT IN (SELECT dept_id FROM null1 WHERE dept_id IS NOT null)),
nulled AS(SELECT
CASE WHEN null1.dept_id IS NULL THEN full1.dept_id ELSE null1.dept_id END,
CASE WHEN null1.dept_name IS NULL THEN full1.dept_name ELSE null1.dept_name END,
first_name,last_name,salary
FROM null1 RIGHT JOIN full1 ON null1.dept_id=full1.dept_id)
SELECT * from null1
UNION
SELECT * FROM nulled
ORDER BY dept_id)
B;

Pot semblar aclaparador, però anem a desglossar-ho pas a pas:

(i) Ús de DENSE_RANK() per mostrar informació de l’empleat i del departament (no implica nul per l’absència de l’enè membre amb més guanys):

SELECT * FROM (
  SELECT department.dept_id, department.dept_name, employee.first_name, employee.last_name,
   employee.salary, DENSE_RANK() OVER (PARTITION BY employee.dept_id ORDER BY employee.salary DESC) AS Rank1
   FROM employee INNER JOIN department
   ON employee.dept_id=department.dept_id) AS k
   WHERE rank1=4;

Sortida:

imatge-20
Fig 6.

(ii) Excloent la columna “rank1” de la taula de la figura 6:

select A.dept_id, A.dept_name, A.first_name, A.last_name, A.salary
    from (SELECT * FROM (
  SELECT department.dept_id, department.dept_name, employee.first_name, employee.last_name,
   employee.salary, DENSE_RANK() OVER (PARTITION BY employee.dept_id ORDER BY employee.salary DESC) AS Rank1
   FROM employee INNER JOIN department
   ON employee.dept_id=department.dept_id) AS k
   WHERE rank1=4)A;

Sortida:

imatge-21
Fig 7.

(iii) Assenyalant els departaments de la taula de departaments que no tenen un enèsim empleat amb més ingressos:

SELECT * FROM (WITH null1 AS (select A.dept_id, A.dept_name, A.first_name, A.last_name, A.salary
    from (SELECT * FROM (
  SELECT department.dept_id, department.dept_name, employee.first_name, employee.last_name,
   employee.salary, DENSE_RANK() OVER (PARTITION BY employee.dept_id ORDER BY employee.salary DESC) AS Rank1
   FROM employee INNER JOIN department
   ON employee.dept_id=department.dept_id) AS k
   WHERE rank1=4)A),
full1 AS (SELECT dept_id, dept_name FROM department WHERE dept_id NOT IN (SELECT dept_id FROM null1 WHERE dept_id IS NOT null))
SELECT * FROM full1)B;

Sortida:

imatge-22
Fig 8. full1 taula

Substituïu el “full1” a l’última línia del codi anterior per “null1”:

SELECT * FROM (WITH null1 AS (select A.dept_id, A.dept_name, A.first_name, A.last_name, A.salary
    from (SELECT * FROM (
  SELECT department.dept_id, department.dept_name, employee.first_name, employee.last_name,
   employee.salary, DENSE_RANK() OVER (PARTITION BY employee.dept_id ORDER BY employee.salary DESC) AS Rank1
   FROM employee INNER JOIN department
   ON employee.dept_id=department.dept_id) AS k
   WHERE rank1=4)A),
full1 AS (SELECT dept_id, dept_name FROM department WHERE dept_id NOT IN (SELECT dept_id FROM null1 WHERE dept_id IS NOT null))
SELECT * FROM null1)B;
imatge-23
Fig 9. taula null1

Ara, hem d’omplir els valors nuls de dept_id i dept_name de la taula de la figura. 9 amb els valors corresponents a la taula de la Fig. 8.

SELECT * FROM (WITH null1 AS (select A.dept_id, A.dept_name, A.first_name, A.last_name, A.salary
    from (SELECT * FROM (
  SELECT department.dept_id, department.dept_name, employee.first_name, employee.last_name,
   employee.salary, DENSE_RANK() OVER (PARTITION BY employee.dept_id ORDER BY employee.salary DESC) AS Rank1
   FROM employee INNER JOIN department
   ON employee.dept_id=department.dept_id) AS k
   WHERE rank1=4)A),
full1 AS (SELECT dept_id, dept_name FROM department WHERE dept_id NOT IN (SELECT dept_id FROM null1 WHERE dept_id IS NOT null)),
nulled AS(SELECT
CASE WHEN null1.dept_id IS NULL THEN full1.dept_id ELSE null1.dept_id END,
CASE WHEN null1.dept_name IS NULL THEN full1.dept_name ELSE null1.dept_name END,
first_name,last_name,salary
FROM null1 RIGHT JOIN full1 ON null1.dept_id=full1.dept_id)
SELECT * from nulled) B;
imatge-24
Fig 10. El resultat de la consulta ‘nul·lada’

La consulta “nul·lada” utilitza CASE WHEN als nulls trobats a les columnes dept_id i dept_name de la taula “null1” per substituir-los pels valors corresponents a la taula “full1”. Ara, tot el que hem de fer és aplicar UNION a les taules obtingudes a la Fig. 7 i Fig. 10. Això es pot aconseguir declarant l’última consulta al codi anterior utilitzant WITH i després UNIONitzant-la amb ‘null1’.

SELECT * FROM (WITH null1 AS (select A.dept_id, A.dept_name, A.first_name, A.last_name, A.salary
from (SELECT * FROM (
SELECT department.dept_id, department.dept_name, employee.first_name, employee.last_name,
employee.salary, DENSE_RANK() OVER (PARTITION BY employee.dept_id ORDER BY employee.salary DESC) AS Rank1
FROM employee INNER JOIN department
ON employee.dept_id=department.dept_id) AS k
WHERE rank1=4)A),
full1 AS (SELECT dept_id, dept_name FROM department WHERE dept_id NOT IN (SELECT dept_id FROM null1 WHERE dept_id IS NOT null)),
nulled AS(SELECT
CASE WHEN null1.dept_id IS NULL THEN full1.dept_id ELSE null1.dept_id END,
CASE WHEN null1.dept_name IS NULL THEN full1.dept_name ELSE null1.dept_name END,
first_name,last_name,salary
FROM null1 RIGHT JOIN full1 ON null1.dept_id=full1.dept_id)
SELECT * from null1
UNION
SELECT * FROM nulled
ORDER BY dept_id)
B;
imatge-25
Fig 11. El resultat final

Es pot inferir de la Fig. 11 que “Màrqueting”, “Vendes” i “Desenvolupament web” són els departaments on cap empleat guanya el quart salari més alt.

Leave a Comment

Your email address will not be published. Required fields are marked *