Syntax
Description of the illustration row_number.gif
Purpose
ROW_NUMBER
is an analytic function. It assigns a unique number to each row to which it is applied [either each row in the partition or each row returned by the query], in the ordered sequence of rows specified in the order_by_clause, beginning with 1.
By nesting a subquery using ROW_NUMBER inside a query that retrieves the ROW_NUMBER values for a specified range, you can find a precise subset of rows from the results of the inner query. This
use of the function lets you implement top-N, bottom-N, and inner-N reporting. For consistent results, the query must ensure a deterministic sort order.
You cannot use ROW_NUMBER or any other analytic function for expr. That is, you cannot nest analytic functions, but you can use other built-in function expressions for expr. Please refer to "About SQL Expressions" for
information on valid forms of expr.
Examples
For each department in the sample table oe.employees, the following example assigns numbers to each row in order of employee's hire date:
SELECT department_id, last_name, employee_id, ROW_NUMBER[]
OVER [PARTITION BY department_id ORDER BY employee_id] AS emp_id
FROM employees;
DEPARTMENT_ID LAST_NAME EMPLOYEE_ID EMP_ID
------------- ------------------------- ----------- ----------
10 Whalen 200 1
20 Hartstein 201 1
20 Fay 202 2
30 Raphaely 114 1
30 Khoo 115 2
30 Baida 116 3
30 Tobias 117 4
30 Himuro 118 5
30 Colmenares 119 6
40 Mavris 203 1
. . .
100 Popp 113 6
110 Higgins 205 1
110 Gietz 206 2
ROW_NUMBER is a nondeterministic function. However, employee_id is a unique key, so the results of this application of the function are deterministic.
The following inner-N query selects all rows from the employees table but returns only the fifty-first through
one-hundredth row:
SELECT last_name FROM [SELECT last_name, ROW_NUMBER[] OVER [ORDER BY last_name] R FROM employees] WHERE R BETWEEN 51 and 100;
Ở các bài truóc mình đã dùng lệnh select nhưng không giải thích về nó, vậy thì trong bài này mình sẽ giải thích kỹ hơn về lệnh này nhé. Lệnh select dùng để lấy dữ liệu từ một bảng hoặc nhiều bảng [sẽ học ở bài join].
Bài viết này được đăng tại freetuts.net, không được copy dưới mọi hình thức.
1. Cú pháp SELECT trong Oracle
Bạn có thể sử dụng SELECT để lấy dữ liệu từ table và view, cú pháp như sau:
SELECT expressions FROM tables WHERE conditions;
Trong đó:
- expressions: Là danh sách các column bạn muốn lấy, được cách nhau bởi dấu phẩy. Nếu bạn muốn lấy tất cả các field thì sử dụng dấu sao
*. - tables: Là danh sách các table mà lệnh select sẽ truy vấn vào để lấy dữ liệu
- conditions: Là đièu kiện để lọc dữ liệu.
Ví dụ: Lấy tất cả dữ liệu từ bảng customer
Bài viết này được đăng tại [free tuts .net]
2. Thêm điều kiện vào lệnh WHERE
Lệnh WHERE dùng để lọc kết quả trả về. Ví dụ bạn muốn lấy thông tin khách hàng có tên là Cường thì sẽ viết như sau:
SELECT * FROM customers WHERE customer_name = 'Cường';
Ngoài so sánh bằng [=] thì bạn có thể sử dụng các dấu: >, =, SELECT * FROM CUSTOMERS LIMIT 3;
Điều này sẽ tạo ra kết quả sau -
+----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+---------+-----+-----------+---------+
Nếu bạn đang sử dụng máy chủ MySQL, thì đây là một ví dụ tương đương -
SQL> SELECT * FROM CUSTOMERS WHERE ROWNUM SELECT TOP 3 * FROM CUSTOMERS;0 - Tổng số sản phẩm đã mua.
SQL> SELECT TOP 3 * FROM CUSTOMERS;1 - Ngày bán hàng.
Bây giờ chúng ta hãy truy vấn bảng này và xem những gì chúng ta có bên trong:
SELECT * FROM sales ORDER BY amount DESC;
Đây là kết quả:
Bây giờ giả sử bạn muốn truy vấn năm doanh số hàng đầu, hoặc năm doanh số lớn nhất. Thông thường, mọi người thực hiện truy vấn này bằng cách sử dụng Oracle Pseudocolumn
SQL> SELECT TOP 3 * FROM CUSTOMERS;2 trả về một số cho biết thứ tự hàng được chọn từ bảng. Đây là mã:
SELECT * FROM sales WHERE rownum SELECT TOP 3 * FROM CUSTOMERS;3 được thực thi trước câu lệnh
SQL> SELECT TOP 3 * FROM CUSTOMERS;4. Để khắc phục điều này, bạn sử dụng chế độ xem nội tuyến [câu lệnh
SQL> SELECT TOP 3 * FROM CUSTOMERS;5 trong mệnh đề
SQL> SELECT TOP 3 * FROM CUSTOMERS;6 của một
SQL> SELECT TOP 3 * FROM CUSTOMERS;5 khác, như được hiển thị bên dưới.] Điều này yêu cầu Oracle sắp xếp dữ liệu trước.
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+0
Và chúng tôi nhận được kết quả chính xác:
Tuy nhiên, có một vấn đề: làm thế nào chúng tôi xác định bán hàng tốt nhất. Có phải năm doanh số tốt nhất là năm đầu tiên khác biệt tốt nhất? Năm người tốt nhất không bị phân loại? Hoặc năm doanh số được xếp hạng đầu tiên? Định nghĩa về doanh số bán hàng tốt nhất sẽ đến từ khía cạnh kinh doanh của mọi thứ, nhưng việc tìm ra giải pháp là tùy thuộc vào bạn.best sale. Are the five best sales the first five distinct best? The five non-distinct best? Or the first five ranked sales? The definition of the best sales will come from the business side of things, but figuring out the solution is up to you.
Truy vấn Top-N là gì?
Bây giờ chúng tôi đã hiểu các vấn đề kinh doanh và dữ liệu, chúng ta hãy chuyển sang các truy vấn Top-N. Tôi sẽ xem xét từng loại truy vấn này:
- Top-n với
SQL> SELECT TOP 3 * FROM CUSTOMERS;
8 - Top-n khác biệt
- Top-N với
SQL> SELECT TOP 3 * FROM CUSTOMERS;
9
Các truy vấn hàng đầu không có lệnh hoặc từ khóa đặc biệt. Họ sử dụng chế độ xem nội tuyến được đặt hàng, chọn kết quả từ chế độ xem đó và sau đó giới hạn số lượng hàng bằng cách sử dụng
SQL> SELECT TOP 3 * FROM CUSTOMERS;3.
Lưu ý cho người dùng Oracle 12c: Oracle 12c đã giới thiệu một mệnh đề mới,
+----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+---------+-----+-----------+---------+1. Điều này cho phép chúng tôi viết các truy vấn Top-N bằng cách thêm mệnh đề
+----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+---------+-----+-----------+---------+1 ở cuối truy vấn cơ bản. Tôi cũng đã bao gồm các ví dụ về phương pháp này.
Top-n với Row_Number
Đây là Top-N cổ điển. Đó là không quyết định, có nghĩa là có thể có những khoảng trống trong bảng xếp hạng nếu hai hoặc nhiều giá trị giống nhau. Hàm phân tích
SQL> SELECT TOP 3 * FROM CUSTOMERS;8 trả về một hàng duy nhất cho mỗi hàng được trả về. Chúng ta có thể đưa truy vấn này đi xa hơn chúng ta có thể với
SQL> SELECT TOP 3 * FROM CUSTOMERS;3 vì khía cạnh cửa sổ của
SQL> SELECT TOP 3 * FROM CUSTOMERS;8. Để tìm hiểu thêm về các chức năng phân tích trong Oracle, hãy bắt đầu tại đây.
Truy vấn là:
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+1
Và kết quả chúng tôi nhận được là:
Cú pháp Oracle 12c
Để có được kết quả tương tự bằng cách sử dụng Oracle 12c, chúng tôi chỉ cần viết:
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+2
Và kết quả chúng tôi nhận được là:
Top-n khác biệt
Top-N với
SQL> SELECT TOP 3 * FROM CUSTOMERS;9
Các truy vấn hàng đầu không có lệnh hoặc từ khóa đặc biệt. Họ sử dụng chế độ xem nội tuyến được đặt hàng, chọn kết quả từ chế độ xem đó và sau đó giới hạn số lượng hàng bằng cách sử dụng
SQL> SELECT TOP 3 * FROM CUSTOMERS;3.
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+3
Lưu ý cho người dùng Oracle 12c: Oracle 12c đã giới thiệu một mệnh đề mới,
+----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+---------+-----+-----------+---------+1. Điều này cho phép chúng tôi viết các truy vấn Top-N bằng cách thêm mệnh đề
+----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+---------+-----+-----------+---------+1 ở cuối truy vấn cơ bản. Tôi cũng đã bao gồm các ví dụ về phương pháp này.
Top-n với Row_Number
Đây là Top-N cổ điển. Đó là không quyết định, có nghĩa là có thể có những khoảng trống trong bảng xếp hạng nếu hai hoặc nhiều giá trị giống nhau. Hàm phân tích SQL> SELECT TOP 3 * FROM CUSTOMERS;
8 trả về một hàng duy nhất cho mỗi hàng được trả về. Chúng ta có thể đưa truy vấn này đi xa hơn chúng ta có thể với SQL> SELECT TOP 3 * FROM CUSTOMERS;
3 vì khía cạnh cửa sổ của SQL> SELECT TOP 3 * FROM CUSTOMERS;
8. Để tìm hiểu thêm về các chức năng phân tích trong Oracle, hãy bắt đầu tại đây.
Truy vấn là:
Và kết quả chúng tôi nhận được là:
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+4
Cú pháp Oracle 12c
Để có được kết quả tương tự bằng cách sử dụng Oracle 12c, chúng tôi chỉ cần viết:
Cú pháp Oracle 12c
Để có được kết quả tương tự bằng cách sử dụng Oracle 12c, chúng tôi chỉ cần viết:
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+5
Để có được hàng đầu với kết quả riêng biệt [nghĩa là không có khoảng trống nào trong kết quả được xếp hạng], chúng tôi sử dụng chức năng phân tích
+----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+---------+-----+-----------+---------+6.
+----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+---------+-----+-----------+---------+6 tương tự như thứ hạng [mà chúng tôi sẽ thảo luận dưới đây], nhưng nó trả về mà không có bất kỳ khoảng trống nào trong kết quả. Chúng tôi sẽ sử dụng loại truy vấn khác biệt trên N khi chúng tôi muốn xem tất cả các giá trị và tất cả các hàng tương ứng.
Dưới đây là một ví dụ về truy vấn Top-N với
+----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+---------+-----+-----------+---------+6: