Tham chiếu ngôn ngữ truy vấn (Phiên bản 0.7)

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

  1. Giới thiệu
  2. Sử dụng ngôn ngữ truy vấn
    1. Đặt truy vấn từ JavaScript
    2. Đặt truy vấn trong URL nguồn dữ liệu
  3. Cú pháp ngôn ngữ
    1. Tổng quan
    2. Mệnh đề ngôn ngữ
      1. Chọn
      2. Địa điểm
      3. Nhóm theo
      4. Tổng hợp
      5. Đặt hàng theo
      6. Giới hạn
      7. Độ lệch
      8. Nhãn
      9. Định dạng
      10. Lựa chọn
    3. Hàm thao tác dữ liệu
      1. Hàm tổng hợp
      2. Hàm vô cấp
      3. Toán tử số học
    4. Phần tử ngôn ngữ
      1. Văn học
      2. Giá trị nhận dạng
      3. Độ nhạy của trường hợp
      4. Các từ dành riêng

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ên
string		 phòng ban
string		 lunchTime
timeofday
tiền lương
number
 hireDate
date		 độ tuổi
number		 isSenior
boolean		 seniorityStartTime
datetime
JohnEng12:00:00
1000
2005-03-1935đúng2007-12-02 15:56:00
DaveEng12:00:00
500
2006-04-1927falsenull
SallyEng13:00:00
600
2005-10-1030falsenull
BenBán hàng12:00:00
400
2002-10-1032đúng2005-03-09 12:30:00
Tiếng DanaBán hàng12:00:00
350
2004-09-0825falsenull
MikeTiếp thị13:00:00
800
2005-01-1024đúng30-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:00John
12:00:00Dave
13:00:00Sally
12:00:00Ben
12:00:00Tiếng Dana
13:00:00Mike

Đị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. value starts 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. value ends 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). haystack matches 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.
Tên Salary (Tiền lương) Thuế StartDate
sharon 1000 100 1/1/2009
Hoạt hình 2000 200 21/1/2008
Tiếng Moran 3000 300 12/2/2008 
select upper(name), year(startDate)
Tên năm(StartDate)
HÀNG KHÔNG 2008
Tiếng MORAN 2008
SHARON 2009

 

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ụ: year(date "2009-02-05") trả về 2009.

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ụ: month(date "2009-02-05") trả về 1. Lưu ý: các tháng dựa trên giá trị 0, vì vậy hàm này trả về 0 cho tháng 1, 1 cho tháng 2, v.v.

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ị date hoặc ngày giờ. Ví dụ: day(date "2009-02-05") trả về 5.

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ị timeofday. Ví dụ: hour(timeofday "12:03:17") trả về 12.

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ị datetime hoặc timeofday. Ví dụ: minute(timeofday "12:03:17") trả về 3.

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ị datetime hoặc timeofday. Ví dụ: second(timeofday "12:03:17") trả về 17.

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ị datetime hoặc timeofday. Ví dụ: millisecond(timeofday "12:03:17.123") trả về 123.

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ị date hoặc datetime. Ví dụ: quarter(date "2009-02-05") trả về 1. Xin lưu ý rằng các quý được lấy dựa trên 1, vì vậy, hàm sẽ trả về 1 cho quý đầu tiên, 2 cho quý thứ hai, v.v.

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ị date hoặc datetime. Ví dụ: dayOfWeek(date "2009-02-26") trả về 5. Lưu ý rằng ngày dựa trên 1, vì vậy hàm sẽ trả về 1 cho Chủ Nhật, 2 cho thứ Hai, v.v.

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 datetime hiện tại theo múi giờ GMT.

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ị date hoặc datetime. Lưu ý: Chỉ sử dụng các phần date của giá trị trong phép tính, do đó hàm luôn trả về một giá trị số nguyên. Ví dụ: dateDiff(date "2008-03-13", date "2008-02-12") trả về 29; dateDiff(date "2009-02-13", date "2009-03-13") trả về -29. Giá trị thời gian bị cắt bớt trước khi so sánh.

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ị date.

  • Với date, hàm này sẽ trả về cùng một giá trị.
  • Với datetime, phương thức này sẽ trả về phần date. Ví dụ: toDate(dateTime "2009-01-01 12:00:00") trả về "2009-01-01".
  • Với number N, hàm này sẽ trả về date N mili giây sau Thời gian bắt đầu của hệ thống. Kỷ nguyên được định nghĩa là 00:00:00 giờ GMT ngày 1 tháng 1 năm 1970. Ví dụ: toDate(1234567890000) trả về "2009-02-13".
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ề string đã cho bằng chữ hoa. Ví dụ: upper("foo") trả về "FOO".

Thông số: Một thông số thuộc loại string
Loại trả lại hàng: string
lower()

Trả về string đã cho bằng chữ thường. Ví dụ: lower("Bar") trả về "bar".

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ặc false.
date
Dùng từ khoá date, theo sau là giá trị cố định string ở định dạng yyyy-MM-dd. Ví dụ: date "2008-03-18".
timeofday
Dùng từ khoá timeofday, theo sau là giá trị cố định string ở định dạng HH: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ố định string ở định dạng yyyy-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