f MySQL Computed Column and windows function ~ 迪貝之家

Pages

MySQL Computed Column and windows function

 

MySQL 8 Cookbook
原文書我在Internet下載不到
只能找到中文版
今天測到一個書中的錯誤
描述到computed column
其實應該跟sql server 的computed column是相同功能
我手上的書的語法是這樣的

ALTER TABLE employees ADD hire_date_year YEAR AS (YEAR(hire_date )) VIRTUAL ;

只好上網找compute column的語法
修改為

alter table employees add hire_date_year char(4) GENERATED ALWAYS as (year(hire_date)) virtual;

怪了~~ 寫書的人,怎麼會語法差這多~~

書中提了一個windows function的語法
怎麼建怎麼失敗!!
後來仔細看了一下我抄的語法
我猜可能rank這個字是保留字

select hire_date_year, salary,
row_number() over( partition by hire_date_year order by salary desc) as rank,
employees join salaries
salaries.emp_no = employees.emp_no
order by salary desc limit 10;
果然....
我調整了語法如下:
select hire_date_year, salary,
row_number() over( partition by hire_date_year order by salary desc) as "rank"
from employees join salaries
on salaries.emp_no = employees.emp_no
order by salary desc limit 10;

+----------------+--------+------+
| hire_date_year | salary | rank |
+----------------+--------+------+
| 1985 | 158220 | 1 |
| 1985 | 157821 | 2 |
| 1986 | 156286 | 1 |
| 1985 | 155709 | 3 |
| 1987 | 155513 | 1 |
| 1985 | 155377 | 4 |
| 1985 | 155190 | 5 |
| 1985 | 154888 | 6 |
| 1985 | 154885 | 7 |
| 1985 | 154459 | 8 |
+----------------+--------+------+
10 rows in set (5.96 sec)


回看了書上的內容,它是用single qoute
不過我看看它最後的結論
用CTE改了一下語法
把各年度最高年薪排在最上頭

with salary_rank as (select hire_date_year, salary,
row_number() over( partition by hire_date_year order by salary desc) as salrank
from employees join salaries
on salaries.emp_no = employees.emp_no
order by salary desc limit 10)
select * from salary_rank order by salrank ;
+----------------+--------+---------+
| hire_date_year | salary | salrank |
+----------------+--------+---------+
| 1985 | 158220 | 1 |
| 1986 | 156286 | 1 |
| 1987 | 155513 | 1 |
| 1985 | 157821 | 2 |
| 1985 | 155709 | 3 |
| 1985 | 155377 | 4 |
| 1985 | 155190 | 5 |
| 1985 | 154888 | 6 |
| 1985 | 154885 | 7 |
| 1985 | 154459 | 8 |
+----------------+--------+---------+
10 rows in set (5.86 sec)

windows function好像是8 的new feature
不過保留字的問題
應該在其他的資料庫也一樣
只是我很少用到~~


第一个、最后一个和第n 个值
照書上的語法,first_value的產出值我看得懂
但其他的,我就一頭霧水
後來我用CTE改成我的語法之後
看了之後,就恍然大悟
之所以有partition 語法
就是為了要分群

with sal_rank as (
SELECT hire_date_year , salary , RANK() OVER w AS 'Rank',
FIRST_VALUE(salary) OVER w AS 'first',
NTH_VALUE(salary, 3) OVER w AS 'third ',
LAST_VALUE(salary) OVER w AS 'last'
FROM employees join salaries ON salaries.emp_no=employees.emp_no
WINDOW w AS (PARTITION BY hire_date_year ORDER BY salary DESC)
ORDER BY salary DESC LIMIT 10 )
select * from sal_rank order by 3;

+----------------+--------+------+--------+--------+--------+
| hire_date_year | salary | Rank | first | third | last |
+----------------+--------+------+--------+--------+--------+
| 1985 | 158220 | 1 | 158220 | NULL | 158220 |
| 1986 | 156286 | 1 | 156286 | NULL | 156286 |
| 1987 | 155513 | 1 | 155513 | NULL | 155513 |
| 1985 | 157821 | 2 | 158220 | NULL | 157821 |
| 1985 | 155709 | 3 | 158220 | 155709 | 155709 |
| 1985 | 155377 | 4 | 158220 | 155709 | 155377 |
| 1985 | 155190 | 5 | 158220 | 155709 | 155190 |
| 1985 | 154888 | 6 | 158220 | 155709 | 154888 |
| 1985 | 154885 | 7 | 158220 | 155709 | 154885 |
| 1985 | 154459 | 8 | 158220 | 155709 | 154459 |
+----------------+--------+------+--------+--------+--------+
10 rows in set (9.92 sec)