22.3.31 WILサブクエリ&ビューとグループ/ウィンドウ関数

サブクエリ

操作によるサブクエリの分類

関連サブクエリ:メインクエリのカラムはサブクエリに含まれ、メインクエリのカラムはサブクエリの特定の条件に使用されます.

非関連サブクエリ:メインクエリ列はサブクエリに含まれず、主にメインクエリに対して特定の値を提供するために使用されます.
簡単に書けば!プライマリ・クエリーの列(内容)は、サブクエリーで使用するかどうかによって分類されます.

サブクエリは、返されたデータ型に基づいて分類されます。

1.1行サブクエリ

サブクエリの結果は、1行の比較演算子(=、<、>、<=、>=)とともに使用されるローを返します.

2.マルチラインサブクエリ-EXISTSはまだ使い慣れていない!

サブクエリの結果は、複数行を返し、複数行比較演算子(IN、ALL、ANY、EXISTS)とともに使用できます.
EXISTS例

SELECT name
FROM employee a
WHERE EXISTS (
	SELECT id
	FROM employee b
	WHERE b.salary >= 10000 AND a.department_id = b.department_id
)

3.複数列サブクエリ

サブクエリの結果は、複数のカラムを返し、メインクエリの条件と同時に比較します.
複数の列の例

SELECT name, salary
FROM employee
WHERE (department_id, salary) IN
	(
	SELECT department_id, max(salary)
	FROM employee
	GROUP BY department_id
	);

スカリーのサーブ照会-私にとって難しい概念なら、よく理解してください。

1つのプロパティがあり、1行のクエリーのみが返されます.
SELECTセクション、WHEREセクション、HAVINGセクションで使用可能
スカラークエリーの使用例

-- salaries 테이블에서 직원 번호와 한 직원의 평균 급여를 중복없이 출력해보세요.
select distinct salaries.emp_no, (
        select avg(salary)
            from salaries as s
             where salaries.emp_no = s.emp_no
        group by emp_no
) as avg_salary
from salaries;

ビュー(VIEW)

ビューは、他のテーブルから派生したテーブルであり、物理的にデータを格納するのではなく、論理的に格納され、ビューを使用するクエリーはDBMSがビュー定義に基づいてクエリーを書き換えることによって実行されます.
ビューのフィーチャー

独立性・利便性・安全性保障

生成されたビューは他のビューの作成に使用可能

ビューの定義は変更できず、削除後に再生が必要

ビューによる更新が制限されており、更新は基本的にソーステーブルのプライマリ・キーを含む必要がある

ソースとなるテーブルやビューを削除すると、そのベースとなるビューも一緒に削除されます.

クエリー・ビュー定義{{くえりびゅー:ていぎ}}

CREATE (OR REPLACE) VIEW view_name AS
(
	SELECT column1, column2
	FROM table_name
	WHERE condition
);

グループ関数とウィンドウ関数

ウィンドウ関数(Window function)、集約関数(Aggregate function)、およびグループ関数(Group function)は、主にデータ解析に使用されます.

ウィンドウ関数

等級統計等行と行の関係を定義する関数-OVERを区別する必要があります
SELECT WINDOW FUNCTION OVER([ARTITIONBYcolumn][ORDER BY節][WINDOWN節])FROM表;

1.ランク関数

お客様)RANK()OVER(ORDER BY salary DESC)FROM employee;

RANK:同一値に同一の順序を付与

DENSE RANK:RANKと同じ価格で、1件につき同じランク付け

ROW NUMBER:同じ値でも唯一の順序がある

2.一般統計関数

例)AVG(Salry)OVER(PARTION BY department id)FROM employee;
従来の統計関数AVG()、MIN()、MAX()、SUM()などは、GROUP BY構文を必要とせずに使用することができる.

3.グループ内の行順関数

例)各部門で最も給料の高い人
FIRST_VALUE(salary) OVER(PARTITION BY department_id ORDER BY salary ROWS BETWEEN UNBOUNDED PROCEDING AND UNBOUNDED FOLLOWING)

FIRST VALUE:一番早い値を求める

LAST VALUE:最後の値を求める.
例)従業員番号の前に従業員名を印刷する
LAG(name, 1) OVER(ORDER BY id)

LAG:前行Xを持ってくる.

LEAD:導入後X行.

4.グループ内比率関数

例)従業員の合計報酬の各行あたりの割合
RATIO_TO_REPORT(salary) OVER() as total_salary

RATIO TO REPORT:パーティション内SUMの割合
例)賃金純価値自己の割合
PERCENT_RANK() OVER(ORDER BY salary DESC) AS percent_rank

PERCENT RANK:パーティション内順位がパーセンテージ-最高の行為0、最低の行為1

CUME DIST:パーティション内の現在の行数が以下である積算率で計算する.
例)給与に基づいて従業員を3つのグループに分ける
NTILE(3) OVER (ORDER BY salary DESC) as ntile

NTILE:パーティション内の行をN等分する.

グループ関数

データ全体の統計だけではありません.一部のデータの小計と中継を提供する

GROUPING SETS:各色についてGROUP BYで生成した統計データはUNALLで生成したものと同じである.

ROLLUP:グループに関する部分統計を提供する.

CUBE:ROLLUP関数が提供した結果を含め、CUBE関数はグループの各場合の連結可能な列数に対して多次元統計を生成する
CUBE(d.name, j.name) == ROLL UP(d.name,j.name) UNION ROLL UP(j.name,d.name) ?? -> ROLLUPとCUBEの関係を知る