Ngôn ngữ truy vấn của API Google Trực quan hoá cho phép bạn thực hiện nhiều thao tác chỉnh sửa dữ liệu với truy vấn đến nguồn dữ liệu.
Mục lục
Giới thiệu
Thông thường, hình ảnh trực quan đòi hỏi dữ liệu ở một dạng cụ thể. Ví dụ: biểu đồ hình tròn có thể hiển thị dữ liệu ở dạng 2 cột: nhãn văn bản và giá trị số. Dữ liệu trong nguồn dữ liệu có thể không khớp chính xác với cấu trúc này. Ví dụ: nguồn dữ liệu có thể có nhiều hơn 2 cột hoặc thứ tự của các cột có thể không khớp với thứ tự dự kiến trong biểu đồ hình tròn.
Ngôn ngữ truy vấn cho phép gửi các yêu cầu định dạng và thao tác dữ liệu đến nguồn dữ liệu, đồng thời đảm bảo rằng cấu trúc và nội dung dữ liệu được trả về khớp với cấu trúc dự kiến.
Cú pháp của ngôn ngữ truy vấn tương tự như SQL. Các nhà phát triển quen dùng SQL có thể nhanh chóng học và sử dụng ngôn ngữ truy vấn này. Có rất nhiều hướng dẫn về SQL có sẵn trên web. Có một số khác biệt giữa ngôn ngữ truy vấn này và SQL được mô tả trong phần syntax.
Xin lưu ý rằng các nguồn dữ liệu là không bắt buộc để triển khai ngôn ngữ truy vấn. Nếu có, thì bạn phải triển khai tất cả các tính năng của ngôn ngữ đó. Trừ phi có lý do để tin khác, bạn không nên dựa vào một nguồn dữ liệu để triển khai tất cả các tính năng của ngôn ngữ này.
Sử dụng ngôn ngữ truy vấn
Bạn có thể đính kèm chuỗi truy vấn vào yêu cầu nguồn dữ liệu theo hai cách: bằng cách đặt chuỗi truy vấn từ trong mã JavaScript, hoặc đặt chuỗi truy vấn dưới dạng tham số trong URL nguồn dữ liệu. Nếu yêu cầu của bạn không chứa chuỗi truy vấn, thì hành vi mặc định cho nguồn dữ liệu là trả về tất cả các hàng và cột theo định dạng và thứ tự hàng/cột mặc định. Bạn có thể thay đổi điều đó bằng cách đưa một chuỗi truy vấn vào yêu cầu của mình đến một nguồn dữ liệu.
Đặt truy vấn từ JavaScript
Để đặt chuỗi truy vấn từ bên trong mã JavaScript, hãy gọi phương thức setQuery
của lớp
google.visualization.Query
.
var query = new google.visualization.Query(DATA_SOURCE_URL); query.setQuery('select dept, sum(salary) group by dept'); query.send(handleQueryResponse);
Đặt truy vấn trong URL nguồn dữ liệu
Bạn có thể thêm chuỗi truy vấn vào URL nguồn dữ liệu bằng tham số tq
.
Việc đặt truy vấn trong tham số URL thay vì trong JavaScript cho phép bạn dễ dàng sử dụng hình ảnh trực quan do các nhà phát triển khác viết, mà vẫn có thể tuỳ chỉnh truy vấn.
Chuỗi truy vấn phải được mã hoá đúng cách dưới dạng tham số URL.
Bạn có thể mã hoá URL bằng hàm encodeURIComponent
của JavaScript hoặc có thể mã hoá theo cách thủ công thông qua công cụ mã hoá ở cuối phần này.
Ví dụ:
Hãy xem xét chuỗi truy vấn sau cho một Bảng tính Google. (Lưu ý rằng mã nhận dạng cột trong bảng tính luôn là chữ cái; văn bản tiêu đề cột hiển thị trong bảng tính được xuất bản là nhãn chứ không phải mã nhận dạng. Bạn phải sử dụng mã nhận dạng (không phải nhãn) trong chuỗi truy vấn.)
select A, sum(B) group by A
Khi được mã hoá, truy vấn này sẽ trở thành:
select%20A%2C%20sum(B)%20group%20by%20A
Giả sử đây là URL của bảng tính của bạn:
https://docs.google.com/a/google.com/spreadsheets/d/1r8_mfnZAvTFmT02JHi1XgOwn_-sLCR9XgmR8wEQ4uW4
Thêm /gviz/tq?tq=
YOUR_QUERY_STRING vào URL bảng tính để nhận chuỗi truy vấn cuối cùng của bạn:
https://docs.google.com/a/google.com/spreadsheets/d/1r8_mfnZAvTFmT02JHi1XgOwn_-sLCR9XgmR8wEQ4uW4/gviz/tq?tq=select%A%2C%20sum(B)%20group%20by%20A
Sử dụng công cụ dưới đây để mã hoá hoặc giải mã chuỗi truy vấn:
Lưu ý: Để truy cập vào dữ liệu bảng tính riêng tư, bạn phải chuyển thông tin uỷ quyền rõ ràng qua OAuth. Xem phần Bảng tính Google: Uỷ quyền để biết thêm chi tiết.
Cú pháp ngôn ngữ
Tổng quan
Cú pháp Ngôn ngữ truy vấn của API Google Hình ảnh hoá được thiết kế tương tự như cú pháp SQL. Tuy nhiên, đây là một tập hợp con của SQL và bạn cần tìm hiểu một số tính năng riêng của ngôn ngữ này. Nếu bạn đã quen thuộc với SQL, thì sẽ không quá khó để tìm hiểu.
Bảng dữ liệu
Tài liệu này sử dụng thuật ngữ bảng dữ liệu để đề cập đến tập hợp kết quả của truy vấn. Bảng dữ liệu bao gồm các hàng và cột. Mỗi cột trong bảng dữ liệu có các thuộc tính sau:
- Giá trị nhận dạng (hoặc mã cột). Dùng để tham chiếu các cột trong truy vấn. Xin lưu ý rằng bạn không nên tìm cách tham chiếu một cột theo nhãn trong truy vấn mà chỉ nên tham chiếu theo giá trị nhận dạng. Mẹo: Cố gắng không sử dụng bất kỳ mã nhận dạng nào có chứa dấu cách vì dấu cách rất khó quản lý và có thể khiến bạn mắc lỗi tuy nhỏ nhưng khó tìm ra lỗi trong quá trình lập trình. Ngoài ra, mã nhận dạng có dấu cách phải được đặt trong dấu ngoặc kép.
- Nhãn.
string
thường xuất hiện trước người dùng cuối. Ví dụ: chú thích trong biểu đồ hình tròn hoặc tiêu đề cột trong bảng. - Loại dữ liệu. Các loại dữ liệu được hỗ trợ là
string
,number
,boolean
,date
,datetime
vàtimeofday
. Tất cả giá trị của một cột sẽ có loại dữ liệu khớp với loại cột hoặc giá trịnull
. Các loại này tương tự, nhưng không giống với các loại JavaScript được mô tả trong phần Văn bản của trang này. - Định dạng mẫu. Nguồn dữ liệu có thể xác định các mẫu định dạng cho một số hoặc tất cả các cột của nó. Bạn có thể ghi đè mẫu này bằng cách thêm mệnh đề định dạng.
Bảng được sử dụng trong tất cả ví dụ:
Trong phần này, mọi ví dụ về truy vấn đề cập đến bảng sau. Các tiêu đề cột là giá trị nhận dạng cột.
tênstring |
phòng banstring |
lunchTimetimeofday |
tiền lương number |
hireDatedate |
độ tuổinumber |
isSeniorboolean |
seniorityStartTimedatetime |
---|---|---|---|---|---|---|---|
John | Eng | 12:00:00 | 1000 | 2005-03-19 | 35 | đúng | 2007-12-02 15:56:00 |
Dave | Eng | 12:00:00 | 500 | 2006-04-19 | 27 | false | null |
Sally | Eng | 13:00:00 | 600 | 2005-10-10 | 30 | false | null |
Ben | Bán hàng | 12:00:00 | 400 | 2002-10-10 | 32 | đúng | 2005-03-09 12:30:00 |
Tiếng Dana | Bán hàng | 12:00:00 | 350 | 2004-09-08 | 25 | false | null |
Mike | Tiếp thị | 13:00:00 | 800 | 2005-01-10 | 24 | đúng | 30-12-2007 14:40:00 |
Mệnh đề ngôn ngữ
Cú pháp của ngôn ngữ truy vấn bao gồm các mệnh đề sau. Mỗi mệnh đề bắt đầu bằng một hoặc hai từ khoá. Tất cả mệnh đề là không bắt buộc. Các mệnh đề được phân tách bằng dấu cách. Thứ tự của các mệnh đề phải như sau:
Mệnh đề | Cách sử dụng |
---|---|
select |
Chọn các cột cần trả về và thứ tự trả về. Nếu bị bỏ qua, tất cả các cột của bảng sẽ được trả về theo thứ tự mặc định. |
where |
Chỉ trả về những hàng khớp với một điều kiện. Nếu bỏ qua, tất cả các hàng sẽ được trả về. |
group by |
Tổng hợp giá trị qua các hàng. |
pivot |
Chuyển đổi các giá trị riêng biệt trong cột thành cột mới. |
order by |
Sắp xếp hàng theo giá trị trong cột. |
limit |
Giới hạn số lượng hàng được trả về. |
offset |
Bỏ qua một số lượng hàng đầu tiên đã cho. |
label |
Đặt nhãn cho cột. |
format |
Định dạng giá trị trong một số cột nhất định bằng các mẫu định dạng cho sẵn. |
options |
Đặt các tuỳ chọn bổ sung. |
from |
Mệnh đề from đã bị loại bỏ khỏi ngôn ngữ này. |
Chọn
Mệnh đề select
dùng để chỉ định các cột cần trả về và thứ tự của các cột đó.
Nếu mệnh đề này không được chỉ định hoặc nếu sử dụng select *
, thì tất cả các cột của bảng nguồn dữ liệu sẽ được trả về theo thứ tự ban đầu.
Các cột được tham chiếu theo giá trị nhận dạng (không phải theo nhãn). Ví dụ: trong Bảng tính Google, giá trị nhận dạng cột là chữ cái của cột có một hoặc hai ký tự (A, B, C, ...).
Các mục trong mệnh đề select
có thể là giá trị nhận dạng cột hoặc kết quả của hàm tổng hợp, hàm vô hướng hoặc toán tử.
Ví dụ:
select *
select dept, salary
select max(salary)
Trong ví dụ sau, dấu ngoặc kép được dùng để tham chiếu mã cột có chứa dấu cách (địa chỉ email) hoặc là các từ dành riêng (ngày):
select `email address`, name, `date`
Chạy truy vấn sau trên bảng ví dụ:
select lunchTime, name
Trả về phản hồi sau:
lunchTime | tên |
---|---|
12:00:00 | John |
12:00:00 | Dave |
13:00:00 | Sally |
12:00:00 | Ben |
12:00:00 | Tiếng Dana |
13:00:00 | Mike |
Địa điểm
Mệnh đề where
dùng để chỉ trả về các hàng khớp với một điều kiện đã chỉ định.
Các toán tử so sánh đơn giản là <=, <, >, >=, =,
!=, <>
. Cả hai toán tử so sánh != <>
đều có nghĩa là không bằng nhau. Các chuỗi được so sánh theo giá trị từ điển. Xin lưu ý rằng đẳng thức được biểu thị bằng =
, không phải ==
như trong hầu hết các ngôn ngữ trên máy tính.
Bạn có thể so sánh với null
bằng cách sử dụng is null
hoặc is not null
.
Bạn có thể kết hợp nhiều điều kiện bằng cách sử dụng toán tử logic and
, or
và not
. Bạn có thể dùng dấu ngoặc đơn để xác định mức độ ưu tiên rõ ràng.
Mệnh đề where cũng hỗ trợ một số toán tử so sánh chuỗi phức tạp hơn.
Các toán tử này lấy 2 chuỗi làm đối số; mọi đối số không phải chuỗi (ví dụ: ngày hoặc số) sẽ được chuyển đổi thành chuỗi trước khi so sánh. Việc so khớp chuỗi có phân biệt chữ hoa chữ thường (bạn có thể dùng các hàm vô hướng upper()
hoặc lower()
để giải quyết vấn đề đó).
contains
– Khớp chuỗi con. toàn bộcontains
phần là true nếu part nằm ở bất kỳ đâu trong toàn bộ. Ví dụ:where name contains 'John'
khớp với "John", "John Adams", "Long John Silver" nhưng không khớp với "john adams".starts with
– Một tiền tố trùng khớp. valuestarts with
tiền tố là true nếu tiền tố đứng đầu value. Ví dụ:where dept starts with 'engineering'
khớp với "kỹ thuật" và "người quản lý kỹ thuật".where dept starts with 'e'
khớp với "engineering", "eng" và "e".ends with
– Một hậu tố khớp. valueends with
hậu tố là true nếu hậu tố nằm ở cuối value. Ví dụ:where role ends with 'y'
khớp với "cowboy", "boy" và "y".matches
– Khớp biểu thức chính quy (preg). haystackmatches
needle true nếu biểu thức chính quy trong needle khớp với haystack. Ví dụ:where country matches '.*ia'
khớp với Ấn Độ và Nigeria, nhưng không khớp với Ấn Độ. Lưu ý rằng đây không phải là tìm kiếm toàn cầu, vì vậy,where country matches 'an'
sẽ không khớp với 'Canada'.like
– Một tính năng tìm kiếm văn bản hỗ trợ 2 ký tự đại diện: % (khớp với 0 ký tự trở lên thuộc bất kỳ loại nào) và _ (dấu gạch dưới) khớp với một ký tự bất kỳ. Điều này tương tự như toán tử LIKE của SQL. Ví dụ:where name like fre%
khớp với "fre", "fred" và "freddy".
Ví dụ:
where salary >= 600 where dept != 'Eng' and date '2005-01-21' < hireDate where (dept<>'Eng' and isSenior=true) or (dept='Sales') or seniorityStartTime is null
Chạy truy vấn sau trên bảng ví dụ:
select name where salary > 700
Trả về phản hồi sau:
tên |
---|
John |
Mike |
Nhóm theo
Mệnh đề group by
dùng để tổng hợp giá trị trên các hàng.
Hệ thống sẽ tạo một hàng cho mỗi tổ hợp giá trị riêng biệt trong mệnh đề nhóm theo.
Dữ liệu được tự động sắp xếp theo các cột nhóm, trừ phi mệnh đề order by
có quy định khác.
Lưu ý: Nếu bạn sử dụng mệnh đề group by
, thì mọi cột được liệt kê trong mệnh đề select
phải được liệt kê trong mệnh đề group by
hoặc được bao bọc bằng một hàm tổng hợp.
Ví dụ:
select dept, max(salary) group by dept
Chạy truy vấn sau trên bảng ví dụ:
select lunchTime, avg(salary), count(age) group by isSenior,lunchTime
Trả về phản hồi sau:
lunchTime | mức lương trung bình | số-tuổi |
---|---|---|
12:00:00 | 425 | 2 |
13:00:00 | 600 | 1 |
12:00:00 | 700 | 2 |
13:00:00 | 800 | 1 |
Tổng hợp
Mệnh đề pivot
dùng để chuyển đổi các giá trị riêng biệt trong các cột thành các cột mới. Ví dụ: việc tổng hợp theo cột "năm" sẽ tạo ra một bảng có cột cho mỗi năm riêng biệt xuất hiện trong bảng gốc.
Cách này có thể hữu ích nếu chẳng hạn như hình ảnh biểu đồ dạng đường vẽ mỗi cột dưới dạng một đường riêng biệt. Nếu muốn vẽ một đường riêng biệt cho từng năm và "năm" là một trong các cột của bảng gốc, thì bạn nên sử dụng phép toán tổng hợp để chuyển đổi dữ liệu cần thiết.
Lưu ý: Nếu bạn sử dụng mệnh đề pivot
, thì mọi cột được liệt kê trong mệnh đề select
phải được liệt kê trong mệnh đề group by
hoặc được gói bằng một hàm tổng hợp
Vì nhiều hàng có thể chứa cùng giá trị cho các cột tổng hợp, nên bảng tổng hợp có ý nghĩa là tổng hợp. Lưu ý rằng khi sử dụng pivot
mà không sử dụng group by
, bảng kết quả sẽ chứa đúng một hàng.
Ví dụ: chạy truy vấn sau trên bảng ví dụ:
select sum(salary) pivot dept
Trả về phản hồi sau:
Tiền lương tiếng Anh | Tổng lương cho hoạt động tiếp thị | Tổng tiền lương cho doanh số |
---|---|---|
2100 | 800 | 750 |
Vì 2100 là tổng tiền lương của bộ phận tiếng Anh, 800 cho bộ phận Tiếp thị, v.v.
Việc sử dụng pivot
cùng với group by
có thể hữu ích hơn nữa vì công cụ này sẽ tạo một bảng trong đó mỗi ô đều chứa kết quả của quá trình tổng hợp cho hàng liên quan và cột có liên quan. Ví dụ: chạy truy vấn sau trên bảng ví dụ:
select dept, sum(salary) group by dept pivot lunchTime
Trả về phản hồi sau:
phòng ban | 12:00:00 Tổng lương | 13:00:00 Tổng tiền lương |
---|---|---|
Eng | 1500 | 600 |
Tiếp thị | null | 800 |
Bán hàng | 750 | null |
Bạn cũng có thể "đảo ngược" bảng này, chuyển đổi các cột và hàng bằng cách chuyển đổi giữa các cột pivot
và group by
. Chạy truy vấn sau trên bảng ví dụ:
select lunchTime, sum(salary) group by lunchTime pivot dept
Trả về phản hồi sau:
lunchTime | Tiền lương tiếng Anh | Tổng lương cho hoạt động tiếp thị | Tổng tiền lương cho doanh số |
---|---|---|---|
12:00:00 | 1500 | null | 750 |
13:00:00 | 600 | 800 | null |
Bạn cũng có thể sử dụng nhiều cột trong mệnh đề pivot
. Trong trường hợp như vậy, các cột của bảng phản hồi bao gồm mọi tổ hợp giá trị riêng biệt trong các cột tồn tại trong bảng gốc. Ví dụ: chạy truy vấn sau trên bảng ví dụ:
select sum(salary) pivot dept, lunchTime
Trả về phản hồi sau:
Eng,12:00:00 tiền lương tổng | Eng,13:00:00 tiền lương tổng | Tiếp thị,13:00:00 tổng tiền lương | Bán hàng,12:00:00 tổng tiền lương |
---|---|---|---|
1500 | 600 | 800 | 750 |
Xin lưu ý rằng chỉ những tổ hợp xuất hiện trong bảng gốc mới được cung cấp các cột trong bảng phản hồi. Đây là lý do tại sao không có cột cho mục Tiếp thị,12:00:00 hoặc cho Bộ phận bán hàng,13:00:00.
Bạn cũng có thể sử dụng nhiều dữ liệu tổng hợp. Ví dụ: chạy truy vấn sau trên bảng ví dụ:
select sum(salary), max(lunchTime) pivot dept
Trả về phản hồi sau:
Tiền lương tiếng Anh | Tổng lương cho hoạt động tiếp thị | Tổng tiền lương cho doanh số | Giờ ăn trưa tối đa tiếng Anh | Thời gian ăn trưa tối đa cho hoạt động tiếp thị | Thời gian ăn trưa tối đa của doanh số |
---|---|---|---|---|---|
2100 | 800 | 750 | 13:00:00 | 13:00:00 | 12:00:00 |
Bạn có thể kết hợp nhiều dữ liệu tổng hợp trong mệnh đề select
, nhiều cột trong mệnh đề group by
và nhiều cột trong mệnh đề pivot
. Về phía nội bộ, quá trình tổng hợp được thực hiện bằng cách nối các cột trong mệnh đề nhóm theo và mệnh đề tổng hợp.
Các cột được chỉ định trong mệnh đề pivot
có thể không xuất hiện trong mệnh đề select
, group by
hoặc order by
.
Khi pivot
được sử dụng, mệnh đề order by
không được chứa bất kỳ cột tổng hợp nào. Lý do là vì đối với mỗi dữ liệu tổng hợp được chỉ định trong mệnh đề select
, nhiều cột sẽ được tạo trong bảng kết quả. Tuy nhiên, bạn có thể định dạng các cột tổng hợp khi sử dụng pivot
. Kết quả của việc định dạng như vậy là tất cả các cột mới liên quan đến dữ liệu tổng hợp cụ thể (do thao tác tổng hợp tạo ra) đều được định dạng theo mẫu đã chỉ định. Trong ví dụ trên, việc thêm format sum(salary) "some_format_string"
sẽ ảnh hưởng đến các cột sau: Eng sum-salary, Marketing sum-salary và
Sales sum-salary (Tổng lương của doanh số).
Bạn có thể gắn nhãn các cột tổng hợp. Nếu không có nhãn nào được chỉ định trong mệnh đề label
, thì nhãn của cột được tạo do việc tổng hợp sẽ bao gồm danh sách giá trị trong các cột tổng hợp, loại tổng hợp (tối thiểu, tối đa, tổng, ...) và nhãn của cột tổng hợp.
Ví dụ: "Eng,12:00:00 sum cảm." Nếu bạn chỉ chỉ định một giá trị tổng hợp trong mệnh đề select
, thì phần tổng hợp sẽ bị xoá khỏi nhãn và chỉ danh sách giá trị trong các cột tổng hợp được giữ lại. Ví dụ: "Eng,12:00:00". Khi mệnh đề label
chỉ định nhãn cho một cột tổng hợp, nhãn được yêu cầu sẽ được thêm vào danh sách giá trị, cả khi chỉ có một giá trị tổng hợp trong mệnh đề select
và khi có nhiều giá trị. Ví dụ: label sum(salary) "sumsal"
sẽ dẫn đến các nhãn cột "Eng,12:00:00 sumsal", "Eng,13:00:00 sumsal", v.v.
Sắp xếp theo
Mệnh đề order by
dùng để sắp xếp hàng theo giá trị trong các cột đã chỉ định.
Các mục trong mệnh đề order by
có thể là giá trị nhận dạng cột hoặc kết quả của hàm tổng hợp, hàm vô hướng hoặc toán tử.
Ví dụ:
order by dept, salary desc
select dept, max(salary) group by dept order by max(salary)
Giới hạn
Mệnh đề limit
dùng để giới hạn số lượng hàng được trả về.
Ví dụ:
limit 100
Mức chênh lệch
Mệnh đề offset
dùng để bỏ qua một số hàng đầu tiên nhất định.
Nếu bạn sử dụng mệnh đề limit
, thì offset
sẽ được áp dụng trước tiên: ví dụ: limit 15 offset
30
sẽ trả về các hàng từ 31 đến 45.
Ví dụ:
offset 10 limit 30 offset 210
Nhãn
Mệnh đề label
dùng để thiết lập nhãn cho một hoặc nhiều cột.
Lưu ý rằng bạn không thể sử dụng giá trị nhãn thay cho ID trong truy vấn.
Các mục trong mệnh đề label
có thể là giá trị nhận dạng cột hoặc kết quả của hàm tổng hợp, hàm vô hướng hoặc toán tử.
Cú pháp:
label column_id label_string [,column_id label_string]
column_id
- Giá trị nhận dạng của cột được gán nhãn.
label_string
- Nhãn để gán cho cột đó. Nhiều hình ảnh trực quan sử dụng nhãn cột làm văn bản để hiển thị cho người dùng cuối, chẳng hạn như nhãn chú giải trong biểu đồ hình tròn. Nhãn là giá trị cố định kiểu chuỗi và tuân theo các quy tắc cú pháp đó.
Ví dụ:
Ví dụ sau đây đặt nhãn cho cột phòng ban thành "Phòng ban", nhãn của cột tên thành "Tên nhân viên" và nhãn của cột vị trí thành "Vị trí nhân viên":
label dept 'Department', name "Employee Name", location 'Employee Location'
Định dạng
Mệnh đề format
dùng để chỉ định một giá trị được định dạng cho các ô trong một hoặc nhiều cột. Dữ liệu được trả về phải bao gồm cả giá trị thực tế và giá trị được định dạng cho từng ô trong một cột được định dạng. Nhiều hình ảnh trực quan sử dụng giá trị chưa được định dạng để tính toán, nhưng dùng giá trị được định dạng để hiển thị. Các mẫu mà bạn chỉ định trong mệnh đề này thường được trả về trong thuộc tính pattern (mẫu) của các cột tương ứng.
Cú pháp mẫu:
number
,date
,timeofday
,datetime
- Mẫu ngày và số do ICU xác định.
-
boolean
- Mẫu là một
string
ở định dạng "value-if-true:value-if-false".
Ví dụ:
format salary '#,##0.00', hireDate 'dd-MMM-yyyy', isSenior 'Yes!:Not yet'
Lựa chọn
Mệnh đề options
được dùng để kiểm soát các tuỳ chọn bổ sung cho việc thực thi truy vấn. Các từ khoá có thể tuân theo mệnh đề options
là:
-
no_format
Loại bỏ các giá trị được định dạng khỏi kết quả và chỉ để lại các giá trị cơ bản. Có thể được dùng khi hình ảnh trực quan cụ thể không sử dụng các giá trị đã định dạng để giảm kích thước của phản hồi. -
no_values
Xoá các giá trị cơ bản khỏi kết quả và chỉ để lại các giá trị được định dạng. Có thể được dùng khi hình ảnh cụ thể chỉ sử dụng các giá trị được định dạng để giảm kích thước của phản hồi.
Hàm thao tác dữ liệu
Có một số loại toán tử và hàm cho phép bạn thao tác hoặc tổng hợp dữ liệu trong một cột, hoặc so sánh hay kết hợp dữ liệu trên nhiều cột. Ví dụ: sum() (để cộng tất cả giá trị trong một cột), max (để tìm giá trị lớn nhất trong một cột) và + (để cộng các giá trị của hai cột lại với nhau trong cùng một hàng).
Một số hàm có thể xuất hiện trong bất kỳ mệnh đề nào; một số có thể xuất hiện trong tập hợp con các mệnh đề. Việc này được ghi lại bên dưới.
Ví dụ:
Dựa trên bảng này... | Nếu chúng ta áp dụng truy vấn này... | Chúng tôi nhận được kết quả này. | ||||||||||||||||||||||||
|
select upper(name), year(startDate) |
|
Các hàm thao tác dữ liệu sau đây được xác định theo ngôn ngữ truy vấn của API Google trực quan hoá:
Hàm tổng hợp
Các hàm tổng hợp được truyền một giá trị nhận dạng cột duy nhất và thực hiện một thao tác trên tất cả giá trị trong mỗi nhóm (các nhóm được chỉ định bằng mệnh đề group by
hoặc pivot
hoặc tất cả các hàng nếu các mệnh đề đó không được sử dụng).
Ví dụ:
select max(salary) // Returns a table with one column and one row. select max(salary) group by dept // Returns a table with the max salary for each dept column value. select max(salary) pivot dept // Returns a one-row table with a column for each dept, // and the max salary for that department.
Bạn có thể dùng các hàm tổng hợp trong các mệnh đề select
, order by
, label
, format
.
Các toán tử này không thể xuất hiện trong các mệnh đề where
, group by
, pivot
, limit
, offset
hoặc options
.
Sau đây là các hàm tổng hợp được hỗ trợ:
Tên | Nội dung mô tả | Các loại cột được hỗ trợ | Loại dữ liệu trả về |
---|---|---|---|
avg() |
Trả về giá trị trung bình của tất cả các giá trị trong cột của một nhóm. | number |
number |
count() |
Trả về số lượng phần tử trong cột được chỉ định cho một nhóm. Các ô rỗng sẽ không được tính. | Mọi loại | number |
max() |
Trả về giá trị lớn nhất trong cột của một nhóm. Ngày được so sánh với ngày trước đó nhỏ hơn, các string được so sánh theo thứ tự bảng chữ cái, có phân biệt chữ hoa chữ thường. |
Mọi loại | Cùng loại với cột |
min() |
Trả về giá trị nhỏ nhất trong cột của một nhóm. Ngày được so sánh với ngày trước đó nhỏ hơn, string được so sánh theo thứ tự bảng chữ cái, có phân biệt chữ hoa chữ thường |
Mọi loại | Cùng loại với cột |
sum() |
Trả về tổng tất cả giá trị trong cột của một nhóm. | number |
number |
Lưu ý: Hàm tổng hợp chỉ có thể lấy giá trị nhận dạng cột làm đối số:
max(startDate) // OK min(firstScore) + min(secondScore) // OK max(year(startDate)) // INVALID. max requires column ID sum(salary + perks) // INVALID. sum requires column ID.
Hàm vô cấp
Hàm vô hướng hoạt động trên 0 hoặc nhiều tham số để tạo ra một giá trị khác. Bạn có thể truyền bất kỳ biểu thức nào đánh giá tham số thuộc kiểu thích hợp cho các hàm vô hướng. Xin lưu ý rằng các loại này là những loại được xác định trong phần Văn bản của tài liệu này, có thể hơi khác so với các đối tượng JavaScript có tên tương tự.
Xin lưu ý rằng tên cột sẽ được thay đổi bằng cách gói cột đó bằng một hàm vô hướng.
Các hàm vô hướng có thể lấy bất kỳ tham số nào làm tham số để đánh giá một giá trị duy nhất:
year(max(startDate)) datediff(now(), todate(1234567890000))
Bạn có thể dùng hàm vô hướng trong mệnh đề bất kỳ sau đây: select
, where
, group by
, pivot
, order by
, label,
và
format
.
Tên | |
---|---|
year() |
Trả về giá trị năm từ giá trị ngày hoặc ngày giờ. Ví dụ: Thông số: Một thông số thuộc loại
date hoặc datetime Loại trả lại hàng:
number |
month() |
Trả về giá trị tháng dựa trên 0 từ giá trị ngày hoặc ngày giờ. Ví dụ: Thông số: Một thông số thuộc loại
date hoặc datetime Loại trả lại hàng:
number |
day() |
Trả về ngày trong tháng từ giá trị Thông số: Một thông số thuộc loại
date hoặc datetime Loại trả lại hàng:
number |
hour() |
Trả về giá trị giờ từ một ngày giờ hoặc giá trị Thông số: Một thông số thuộc loại
datetime hoặc timeofday Loại trả lại hàng:
number |
minute() |
Trả về giá trị phút từ giá trị Thông số: Một thông số thuộc loại
datetime hoặc timeofday Loại trả lại hàng:
number |
second() |
Trả về giá trị thứ hai từ giá trị Thông số: Một thông số thuộc loại
datetime hoặc timeofday Loại trả lại hàng:
number |
millisecond() |
Trả về phần mili giây của giá trị Thông số: Một thông số thuộc loại
datetime hoặc timeofday Loại trả lại hàng:
number |
quarter() |
Trả về quý từ giá trị Thông số: Một thông số thuộc loại
date hoặc datetime Loại trả lại hàng:
number |
dayOfWeek() |
Trả về ngày trong tuần từ giá trị Thông số: Một thông số thuộc loại
date hoặc datetime Loại trả lại hàng:
number |
now() |
Trả về giá trị ngày giờ đại diện cho Thông số: Không có
Loại trả lại hàng:
datetime |
dateDiff() |
Trả về số ngày chênh lệch giữa hai giá trị Thông số: Hai thông số thuộc loại
date hoặc datetime (có thể là một thông số cho mỗi thông số)Loại trả lại hàng:
number |
toDate() |
Chuyển đổi giá trị đã cho thành giá trị
Tham số: Một tham số thuộc loại
date , datetime hoặc number Loại trả lại hàng:
date |
upper() |
Trả về Thông số: Một thông số thuộc loại
string Loại trả lại hàng:
string |
lower() |
Trả về Thông số: Một thông số thuộc loại
string Loại trả lại hàng:
string |
Toán tử số học
Bạn có thể sử dụng các toán tử số học để thực hiện các phép toán dựa trên bất kỳ giá trị nào đánh giá một số duy nhất (tức là kết quả của các hàm tổng hợp, toán tử hoặc hằng số thích hợp).
Ví dụ:
select empSalary - empTax select 2 * (max(empSalary) / max(empTax))
Bạn có thể xác định các toán tử sau:
Tên | Nội dung mô tả | Thông số | Loại dữ liệu trả về |
---|---|---|---|
+ |
Trả về tổng của hai giá trị number . |
2 number |
number |
- |
Trả về hiệu giữa hai giá trị number . |
2 number |
number |
* |
Trả về tích của hai number . |
2 number |
number |
/ |
Trả về thương của 2 number . Phép chia cho 0 sẽ trả về giá trị rỗng. |
2 number |
number |
Phần tử ngôn ngữ
Từ ngữ
Giá trị cố định là các giá trị dùng để so sánh hoặc chỉ định. Giá trị cố định có thể là string
, số, giá trị boolean hoặc nhiều loại ngày/giờ. Dưới đây là một số ví dụ về giá trị cố định được sử dụng trong cú pháp truy vấn:
where startDate < date "2008-03-18" // date "2008-03-18" is a date literal
limit 30 // 30 is a numeric literal
format salary '#,##0.00', isSenior 'not yet:of course!' // '#,##0.00' and 'not yet:of course!' are both string
literals
Dưới đây là các định dạng cho mỗi loại giá trị cố định:
string
Bạn nên đặt giá trị cố định
string
trong dấu ngoặc đơn hoặc dấu ngoặc kép. Ví dụ:"fourteen" 'hello world' "It's raining"
.-
number
- Giá trị cố định dạng số được chỉ định dưới dạng ký hiệu thập phân.
Ví dụ:
3 3.0 3.14 -71 -7.2 .6
-
boolean
- Giá trị cố định boolean là
true
hoặcfalse
. -
date
- Dùng từ khoá
date
, theo sau là giá trị cố địnhstring
ở định dạngyyyy-MM-dd
. Ví dụ:date "2008-03-18"
. -
timeofday
- Dùng từ khoá
timeofday
, theo sau là giá trị cố địnhstring
ở định dạngHH:mm:ss[.SSS]
Ví dụ:timeofday "12:30:45"
. -
datetime
- Ngày và giờ, sử dụng từ khoá
datetime
hoặc từ khoátimestamp
, theo sau là giá trị cố địnhstring
ở định dạngyyyy-MM-dd HH:mm:ss[.sss]
. Ví dụ:datetime '2008-03-18 12:30:34.123'
Giá trị nhận dạng
Giá trị nhận dạng (hoặc mã nhận dạng) là các string
văn bản xác định cột.
Lưu ý quan trọng: Nếu giá trị nhận dạng của bạn
- Có không gian,
- Là từ dành riêng,
- Chứa bất kỳ ký tự nào trừ ký tự chữ-số hoặc dấu gạch dưới ([a-zA-Z0-9_]), hoặc
- Bắt đầu bằng một chữ số
nội dung đó phải nằm trong dấu ngoặc kép (không phải dấu nháy đơn).
Nếu không, giá trị nhận dạng của bạn không cần phải được trích dẫn. (Lưu ý rằng không phải mọi từ khoá được xác định theo cú pháp đều là từ dành riêng; vì vậy, bạn có thể dùng "max" làm giá trị nhận dạng mà không cần phải trích dẫn lại.)
Ví dụ: col1 employee_table `start
date` `7 days traffic` `select`
Bạn không nên chọn một giá trị nhận dạng yêu cầu phải có dấu ngoặc kép vì có thể bạn sẽ dễ quên sử dụng dấu ngoặc kép hoặc vô tình sử dụng "dấu ngoặc đơn" thay vì "dấu ngoặc kép". Đây là những lỗi phổ biến và thường khó gỡ lỗi.
Độ nhạy của trường hợp
Giá trị nhận dạng và giá trị cố định kiểu chuỗi có phân biệt chữ hoa chữ thường. Tất cả các phần tử ngôn ngữ khác đều không phân biệt chữ hoa chữ thường.
Từ dành riêng
Các từ dành riêng sau đây phải được trích dẫn sau nếu được dùng làm giá trị nhận dạng:
and asc by date datetime desc false format group label limit not offset options or order pivot select timeofday timestamp true where