MSSQL Over (Partition by ... Order by ...) 範例

建立Table

CREATE TABLE sales (
    department VARCHAR(50),
    employee VARCHAR(50),
    sale_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales VALUES
('部門A', '員工1', '2023-01-01', 100.00),
('部門A', '員工1', '2023-01-02', 150.00),
('部門A', '員工2', '2023-01-01', 200.00),
('部門B', '員工3', '2023-01-01', 50.00),
('部門B', '員工3', '2023-01-02', 75.00),
('部門B', '員工4', '2023-01-01', 300.00);

資料結構:

department	employee	sale_date	amount
部門A	員工1	2023/1/1	100
部門A	員工1	2023/1/2	150
部門A	員工2	2023/1/1	200
部門B	員工3	2023/1/1	50
部門B	員工3	2023/1/2	75
部門B	員工4	2023/1/1	300

 

在分區中計算行號：

SELECT
    department,
    employee,
    sale_date,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale_date) AS row_num
FROM
    sales;

資料結果:

department	employee	sale_date	row_num
部門A	員工1	2023/1/1	1
部門A	員工2	2023/1/1	2
部門A	員工1	2023/1/2	3
部門B	員工3	2023/1/1	1
部門B	員工4	2023/1/1	2
部門B	員工3	2023/1/2	3

 

在分區中計算排名：

SELECT
    department,
    employee,
    sale_date,
    RANK() OVER (PARTITION BY department ORDER BY sale_date) AS rank
FROM
    sales

資料結果:

department	employee	sale_date	rank
部門A	員工1	2023/1/1	1
部門A	員工2	2023/1/1	1
部門A	員工1	2023/1/2	3
部門B	員工3	2023/1/1	1
部門B	員工4	2023/1/1	1
部門B	員工3	2023/1/2	3

 

在分區中計算累積總和：

SELECT
    department,
    employee,
    sale_date,
    amount,
    SUM(amount) OVER (PARTITION BY department, employee ORDER BY sale_date) AS cumulative_amount
FROM
    sales;

資料結果:

department	employee	sale_date	amount	cumulative_amount
部門A	員工1	2023/1/1	100	100
部門A	員工1	2023/1/2	150	250
部門A	員工2	2023/1/1	200	200
部門B	員工3	2023/1/1	50	50
部門B	員工3	2023/1/2	75	125
部門B	員工4	2023/1/1	300	300

 

在分區中計算平均薪水：

SELECT
    department,
    employee,
    sale_date,
    amount,
    AVG(amount) OVER (PARTITION BY department) AS avg_amount
FROM
    sales;

資料結果:

department	employee	sale_date	amount	avg_amount
部門A	員工1	2023/1/1	100	150
部門A	員工1	2023/1/2	150	1900/5/29
部門A	員工2	2023/1/1	200	1900/5/29
部門B	員工3	2023/1/1	50	1900/5/20
部門B	員工3	2023/1/2	75	1900/5/20
部門B	員工4	2023/1/1	300	1900/5/20

 

在分區中計算百分位數：

SELECT
    department,
    employee,
    sale_date,
    amount,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) OVER (PARTITION BY department) AS median_amount
FROM
    sales;

資料結果:

department	employee	sale_date	amount	median_amount
部門A	員工1	2023/1/1	100	150
部門A	員工1	2023/1/2	150	150
部門A	員工2	2023/1/1	200	150
部門B	員工3	2023/1/1	50	75
部門B	員工3	2023/1/2	75	75
部門B	員工4	2023/1/1	300	75

 

在分區中計算前後數值：

SELECT
    department,
    employee,
    sale_date,
    amount,
    LEAD(amount) OVER (PARTITION BY department, employee ORDER BY sale_date) AS next_amount,
    LAG(amount) OVER (PARTITION BY department, employee ORDER BY sale_date) AS prev_amount
FROM
    sales;

資料結果:

department	employee	sale_date	amount	next_amount	prev_amount
部門A	員工1	2023/1/1	100	150	NULL
部門A	員工1	2023/1/2	150	NULL	100
部門A	員工2	2023/1/1	200	NULL	NULL
部門B	員工3	2023/1/1	50	75	NULL
部門B	員工3	2023/1/2	75	NULL	50
部門B	員工4	2023/1/1	300	NULL	NULL