SQLでn番目に大きいグループごとの値の有無を表示する方法

クエリの段階的な詳細。

面接の準備のために SQL をざっと読んでいると、よく次の質問に遭遇します。従業員情報を含むテーブルと部門情報を含む別のテーブルを結合して、最高または (2 番目に高い) 給与を持つ従業員を見つけます。これにより、さらに疑問が生じます。部門全体で n 番目最も高い給与を稼ぐ従業員を見つけるのはどうでしょうか?

ここで、より複雑なシナリオを提示したいと思います。部門に n 番目に高い給与を稼ぐ従業員がいない場合はどうなるでしょうか?たとえば、従業員が 2 人しかいない部門には、3 番目に高い給与を稼ぐ従業員は存在しません。

この質問に対する私のアプローチは次のとおりです。

部門と従業員のテーブルを作成する

dept_id や dept_name などのフィールドを含むテーブルを作成します。

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

次に、さまざまな部門を新しいテーブルに挿入します。

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');

図 1. 部門テーブル (Mohammed Kamil Khan、CC BY-SA 4.0)

次に、フィールド first_name、last_name、dept_id、および salary を組み込んだ別のテーブルを作成します。

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

次に、テーブルに値を挿入します。

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);

図 2. 部門 ID ごとに並べられた従業員の表 (Mohammed Kamil Khan、CC BY-SA 4.0)

このテーブル (部門 ID:従業員数) を使用して、各部門の従業員数を推測できます。

780:4
781:3
782:3
783:4

さまざまな部門の 2 番目に収入の高い従業員とその部門の名前を表示したい場合 (DENSE_RANK を使用)、テーブルは次のようになります。

図 3. 各部門で 2 番目に収入の高い従業員 (Mohammed Kamil Khan、CC BY-SA 4.0)

同じクエリを適用して 4 番目に収入の高い従業員を検索すると、出力は部門 780 (HR) の給与 85,000 ドルの Tom Brooks のみになります。

図 4. 4 番目に収入の高い従業員 (Mohammed Kamil Khan、CC BY-SA 4.0)

部門 783 (Web 開発) には 4 人の従業員がいますが、上位 2 人の給与が同じであるため、2 人 (James Martin と Ronald Thompson) がその部門で 3 番目に高収入の従業員として分類されます。

n 番目に高い値を見つける

ここで、主な質問に移ります。n 番目に高い部門がない部門の従業員関連フィールドの dept_ID と dept_name を null 値で表示したい場合はどうすればよいでしょうか。稼げる社員？

図 5. n 番目に高収入の従業員がいるかどうかに関係なく、リストされたすべての部門 (Mohammed Kamil Khan、CC BY-SA 4.0)

図 5 に表示されている表は、特定の部門に n 番目に高収入の従業員がいない場合に取得しようとしているものです。マーケティング、営業、Web 開発部門がリストされていますが、名前と給与フィールドには NULL 値が含まれます。

図 5 のテーブルを取得するのに役立つ最終的なクエリは次のとおりです。

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;

クエリの内訳

複雑さを軽減するために、クエリを細分化します。

DENSE_RANK() を使用して従業員と部門の情報を表示します (n 番目に収入の高いメンバーがいない場合は null は含まれません)。

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

出力:

図 6. 4 番目に高額な所得者 (Mohammed Kamil Khan、CC BY-SA 4.0)

図 6 のテーブルから rank1 列を除外します。この列では、別の部門に 4 人の従業員がいるにもかかわらず、4 番目に高い給与を持つ従業員が 1 人だけ特定されています。

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

出力:

図 7. ランク 1 列を除いた 4 番目に高額な所得者の表 (Mohammed Kamil Khan、CC BY-SA 4.0)

部門テーブルから、n 番目に収入の高い従業員がいない部門を指摘します。

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

出力:

図 8. 4 番目に高額な所得者がいない学部をリストした完全な表 1 (Mohammed Kamil Khan、CC BY-SA 4.0)

上記のコードの最後の行の full1 を 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

図 9. すべての部門をリストした null1 テーブル。4 番目に高額な所得者がいない部門の値は null (Mohammed Kamil Khan、CC BY-SA 4.0)

ここで、図 9 の dept_id フィールドと dept_name フィールドの null 値に、図 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;

図 10. NULL クエリの結果 (Mohammed Kamil Khan、CC BY-SA 4.0)

null クエリは、null1 テーブルの dept_id 列と dept_name 列で検出された null に対して CASE WHEN を使用し、これらを full1 テーブル内の対応する値と組み合わせます。ここで必要なのは、図 7 と図 10 で取得したテーブルに UNION を適用することだけです。これは、前のコードで WITH と WITH を使用して最後のクエリを宣言することで実現できます。次に、それを null1 で UNION 化します。

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;

図 11. 最終結果 (Mohammed Kamil Khan、CC BY-SA 4.0)

図 11 から、マーケティング、販売、および Web 開発は、4 番目に高い給与を稼ぐ従業員がいない部門であると推測できます。

SQLでn番目に大きいグループごとの値の有無を表示する方法

クエリの段階的な詳細。

部門と従業員のテーブルを作成する

n 番目に高い値を見つける

クエリの内訳

関連記事: