使用測量指標
本文說明如何在圖表上定義及查詢測量指標。您可以使用測量指標，確保匯總作業在彙整時正確執行。
總覽
測量指標是在節點或邊緣資料表的 PROPERTIES 子句中定義的匯總屬性。測量指標是由測量指標運算式定義，該運算式是使用 MEASURE 關鍵字，並搭配下列其中一項建立：
- 匯總運算式
- 結合匯總函式和純量運算的運算式
- 衍生測量指標：在相同元素資料表上定義，並以
AGG函式包裝的測量指標組合
測量指標支援下列匯總函式：
SUMAVGCOUNTMINMAXAGG(用於衍生測量指標)
除了 AGG 以外，所有匯總函式都支援 DISTINCT 關鍵字。如要進一步瞭解支援的測量運算式，請參閱測量類型。
測量指標會根據定義所在的節點或邊緣資料表的 KEY，定義匯總方式。這表示查詢測量指標時，即使基礎資料表彙整後導致資料列重複，系統仍會正確執行匯總。
您無法在 GQL 查詢中參照以測量指標定義的屬性，而是需要呼叫 GRAPH_EXPAND TVF，查詢圖表的關聯表示法，才能存取測量指標。根據預設，GRAPH_EXPAND 會傳回圖譜的扁平化資料表，每個屬性一欄。您也可以指定 flatten_results => FALSE，傳回含有巢狀 STRUCT 欄的階層式資料表。如果圖表包含多個根節點資料表，您可以指定 start_node 引數，選擇要周遊的根資料表。
這項函式不接受所有類型的圖表。如要進一步瞭解哪些圖表可做為有效輸入內容，請參閱輸入限制。如要執行 GQL 查詢，您必須擁有使用 Enterprise 或 Enterprise Plus 版本的預留項目。如果採用隨選定價模式，您可以呼叫 GRAPH_EXPAND 函式，對圖形執行 SQL 查詢。
您可以對 GRAPH_EXPAND TVF 的輸出內容呼叫 AGG 函式，根據測量指標中定義的匯總方式匯總屬性。
定義測量指標
如要定義測量指標，請在節點或邊緣資料表定義的 PROPERTIES 子句中，使用 MEASURE() 關鍵字括住測量指標運算式。
以下範例會建立名為 university 的資料集，以及學院、系所和課程的資料表：
CREATE SCHEMA IF NOT EXISTS university;
CREATE OR REPLACE TABLE university.College (
college_id INT64 PRIMARY KEY NOT ENFORCED,
college_name STRING
);
CREATE OR REPLACE TABLE university.Department (
dept_id INT64 PRIMARY KEY NOT ENFORCED,
dept_name STRING,
college_id INT64,
budget FLOAT64,
FOREIGN KEY (college_id) REFERENCES university.College(college_id) NOT ENFORCED
);
CREATE OR REPLACE TABLE university.Course (
course_id INT64 PRIMARY KEY NOT ENFORCED,
course_name STRING,
dept_id INT64,
credits INT64,
FOREIGN KEY (dept_id) REFERENCES university.Department(dept_id) NOT ENFORCED
);
INSERT INTO university.College (college_id, college_name)
VALUES (101, 'College of Engineering'),
(102, 'College of Arts');
INSERT INTO university.Department (dept_id, dept_name, college_id, budget)
VALUES (1001, 'Computer Science', 101, 500000),
(1002, 'Mechanical Engineering', 101, 400000),
(1003, 'Fine Arts', 102, 200000),
(1004, 'Research', 101, 50000);
INSERT INTO university.Course (course_id, course_name, dept_id, credits)
VALUES (1, 'Intro to CS', 1001, 3),
(2, 'Data Structures', 1001, 4),
(3, 'Thermodynamics', 1002, 3),
(4, 'Oil Painting', 1003, 2);
下列陳述式會建立名為 SchoolGraph 的圖表，定義 Department 和 Course 節點中屬性的測量結果。您必須為測量指標定義的屬性提供別名。
CREATE OR REPLACE PROPERTY GRAPH university.SchoolGraph
NODE TABLES (
university.College
KEY(college_id)
PROPERTIES(college_id, college_name),
university.Department
KEY(dept_id)
PROPERTIES(dept_id, dept_name, college_id,
budget OPTIONS(description="Department budget in USD"),
MEASURE(SUM(budget)) AS total_budget,
MEASURE(SUM(budget) * 1.10) AS budget_with_overhead OPTIONS(
description="Total department budget including 10% overhead",
extensions=[("unit", "USD"), ("tier", "planning")]
)),
university.Course
KEY(course_id)
PROPERTIES(
course_id,
course_name,
credits,
dept_id,
MEASURE(AVG(credits)) AS avg_credits,
MEASURE(SUM(credits)) AS total_credits,
MEASURE(COUNT(course_id)) AS course_count,
MEASURE(COUNT(DISTINCT CASE WHEN credits >= 3 THEN course_id ELSE NULL END)) AS major_course_count,
MEASURE(SAFE_DIVIDE(AGG(total_credits), NULLIF(AGG(course_count), 0))) AS computed_avg_credits)
)
EDGE TABLES (
university.Department AS CollegeDept
SOURCE KEY (college_id) REFERENCES College (college_id)
DESTINATION KEY (dept_id) REFERENCES Department (dept_id),
university.Course AS DeptCourse
SOURCE KEY (dept_id) REFERENCES Department (dept_id)
DESTINATION KEY (course_id) REFERENCES Course (course_id)
);
total_budget 測量指標定義為 MEASURE(SUM(budget))。測量指標會將匯總鎖定在 KEY，也就是 dept_id。
衍生測量指標
「衍生測量指標」是指使用 AGG() 函式，參照同一元素資料表上定義的其他測量指標。這項功能可讓您建立可重複使用的測量階層。
舉例來說，在 university.Course 中，computed_avg_credits 測量指標會計算 total_credits 和 course_count 測量指標之間的比率：
MEASURE(SAFE_DIVIDE(AGG(total_credits), NULLIF(AGG(course_count), 0))) AS computed_avg_credits
衍生測量指標會保留基礎元素資料表索引鍵的精細程度鎖定屬性。系統會在圖表編譯時解析測量指標依附元件。評估指標之間的循環參照會傳回錯誤。
瞭解超額計算
彙整資料表時，只要資料中有一對多關係，資料就會重複。舉例來說，如果您彙整 Course、Department 和 College 資料表，則有多門課程的系所會在輸出中顯示在多個資料列：
SELECT
college_name,
dept_name,
course_name,
budget
FROM university.Course
LEFT JOIN university.Department
ON Course.dept_id = Department.dept_id
LEFT JOIN university.College
ON Department.college_id = College.college_id;
/*------------------------+------------------------+-----------------+----------+
| college_name | dept_name | course_name | budget |
+------------------------+------------------------+-----------------+----------+
| College of Engineering | Computer Science | Intro to CS | 500000.0 |
| College of Engineering | Computer Science | Data Structures | 500000.0 |
| College of Engineering | Mechanical Engineering | Thermodynamics | 400000.0 |
| College of Arts | Fine Arts | Oil Painting | 200000.0 |
+------------------------+------------------------+-----------------+----------*/
如果嘗試使用 SUM(budget) 計算每所學院的總預算，電腦科學系的預算會計算兩次。您可以直接查詢 Department 資料表，避免這個問題，但如果您想從不同資料表計算多個匯總，而這些資料表會影響彙整資料，則這個方法不適用。下一節將說明如何透過測量指標解決這個問題。
使用測量指標查詢圖表
您可以使用 GQL 查詢含有測量指標的圖表，但查詢無法直接參照以測量指標定義的任何屬性。舉例來說，下列查詢會參照已定義測量屬性的節點，但不會使用或傳回任何測量欄位：
GRAPH university.SchoolGraph
MATCH (c:College)->(d:Department)->(course:Course)
RETURN c.college_name, d.dept_name, course.course_name;
/*------------------------+------------------------+-----------------+
| college_name | dept_name | course_name |
+------------------------+------------------------+-----------------+
| College of Engineering | Computer Science | Intro to CS |
| College of Engineering | Computer Science | Data Structures |
| College of Engineering | Mechanical Engineering | Thermodynamics |
| College of Arts | Fine Arts | Oil Painting |
+------------------------+------------------------+-----------------*/
如要使用測量指標，請透過 GRAPH_EXPAND 資料表值函式 (TVF)，將圖表當做關聯資料表查詢：
GRAPH_EXPAND(
graph_name [, start_node => 'NodeName'] [, flatten_results => TRUE|FALSE]
)
這個函式接受下列引數：
graph_name：包含圖表名稱的STRING。start_node：選用的STRING，指定周遊的起始節點資料表。如果圖表有多個根節點資料表，則必須提供這個引數。flatten_results：選用的BOOL(預設為TRUE)：TRUE：將所有節點和邊緣屬性扁平化為頂層資料欄，並加上<element>_<property>前置字串。FALSE：將根節點屬性傳回為不含前置字元的頂層資料欄，並將彙整的鄰近資料表傳回為STRUCT資料欄。
使用扁平化輸出
如果 flatten_results 設為 TRUE (預設模式)，輸出資料表中的資料欄會串連節點或邊緣資料表標籤和屬性名稱來命名，例如 Course_course_name 或 Department_total_budget。下列查詢顯示 GRAPH_EXPAND TVF 的輸出內容範例：
SELECT
College_college_name,
Department_dept_name,
Department_budget,
Course_course_name
FROM
GRAPH_EXPAND("university.SchoolGraph");
/*------------------------+------------------------+-------------------+--------------------+
| College_college_name | Department_dept_name | Department_budget | Course_course_name |
+------------------------+------------------------+-------------------+--------------------+
| College of Engineering | Computer Science | 500000.0 | Intro to CS |
| College of Engineering | Computer Science | 500000.0 | Data Structures |
| College of Engineering | Mechanical Engineering | 400000.0 | Thermodynamics |
| College of Arts | Fine Arts | 200000.0 | Oil Painting |
+------------------------+------------------------+-------------------+--------------------*/
GRAPH_EXPAND 函式會對節點和邊緣資料表套用一系列 LEFT JOIN 作業，產生扁平化資料表。如未指定起始節點，輸入圖表在結構定義關係圖中必須有單一根節點資料表。同樣地，根節點資料表必須與共用邊緣的其他每個節點資料表，建立多對一關係。如果資料無法透過一系列 join 從根節點資料表連線，就不會顯示在輸出內容中。在上述範例中，Course 是根節點資料表。輸出內容會省略 Research 系所，因為該系所沒有任何課程。
您無法直接選取以測量指標定義的屬性資料欄。而是必須將其包裝在 AGG() 函式中。這個函式可確保對每個鍵只執行一次測量指標定義的匯總作業。
下列查詢會同時計算各學院的預算總額和課程總數：
SELECT
College_college_name,
AGG(Department_total_budget) AS college_budget,
AGG(Course_course_count) AS total_courses
FROM
GRAPH_EXPAND("university.SchoolGraph")
GROUP BY
College_college_name;
/*------------------------+----------------+---------------+
| College_college_name | college_budget | total_courses |
+------------------------+----------------+---------------+
| College of Engineering | 900000.0 | 3 |
| College of Arts | 200000.0 | 1 |
+------------------------+----------------+---------------*/
使用階層式輸出
指定 flatten_results => FALSE 時，GRAPH_EXPAND 會傳回階層關係，而非前置字元資料欄：
- 起始根節點的屬性會顯示為不含前置字元的頂層資料欄 (例如
course_name或credits)。 - 已彙整的鄰近資料表 (可能是節點或邊緣資料表) 屬性，會顯示為以元素資料表命名的巢狀
STRUCT欄位。舉例來說，如果起始節點是Course，則應使用 DeptCourse.Department.CollegeDept.College.college_nameto access thecollege_name` 屬性。 - 如要查詢巢狀結構中的測量指標，請在
AGG()中使用點標記法，例如AGG(DeptCourse.Department.total_budget)。 - 如果元素資料表的屬性與相鄰元素資料表的屬性同名，則無法使用
flatten_results => FALSE查詢圖表。您必須改為扁平化結果。
SELECT
DeptCourse.Department.dept_name AS department_name,
DeptCourse.Department.CollegeDept.College.college_name,
AGG(DeptCourse.Department.total_budget) AS total_budget,
AGG(course_count) AS total_courses
FROM GRAPH_EXPAND(
"university.SchoolGraph",
start_node => "Course",
flatten_results => FALSE)
GROUP BY department_name, college_name;
使用多個根節點資料表
圖表通常包含多個根節點資料表 (也稱為事實資料表)，這些資料表會共用維度資料表。如要查詢具有多個根節點資料表的圖表，請在 GRAPH_EXPAND 中使用 start_node 引數。函式會遍歷從起始節點可到達的子圖。
舉例來說，您可以在 university 資料集中新增 Instructor 資料表，並連結至 Department：
CREATE OR REPLACE TABLE university.Instructor (
instructor_id INT64 PRIMARY KEY NOT ENFORCED,
instructor_name STRING,
dept_id INT64,
salary FLOAT64,
FOREIGN KEY (dept_id) REFERENCES university.Department(dept_id) NOT ENFORCED
);
INSERT INTO university.Instructor (instructor_id, instructor_name, dept_id, salary)
VALUES (501, 'Dr. Dana', 1001, 120000),
(502, 'Dr. Lee', 1001, 110000),
(503, 'Dr. Taylor', 1002, 115000),
(504, 'Prof. Izumi', 1003, 90000);
您可以建立名為 university.SchoolGraphMultiRoot 的新圖表，其中包含 Instructor 節點資料表，以及從 Instructor 到 Department 的邊緣：
CREATE OR REPLACE PROPERTY GRAPH university.SchoolGraphMultiRoot
NODE TABLES (
university.College
KEY(college_id)
PROPERTIES(college_id, college_name),
university.Department
KEY(dept_id)
PROPERTIES(dept_id, dept_name, college_id, budget,
MEASURE(SUM(budget)) AS total_budget),
university.Course
KEY(course_id)
PROPERTIES(
course_id, course_name, credits, dept_id,
MEASURE(COUNT(course_id)) AS course_count),
university.Instructor
KEY(instructor_id)
PROPERTIES(
instructor_id, instructor_name, dept_id, salary,
MEASURE(COUNT(instructor_id)) AS instructor_count,
MEASURE(SUM(salary)) AS total_salary)
)
EDGE TABLES (
university.Department AS CollegeDept
SOURCE KEY (college_id) REFERENCES College (college_id)
DESTINATION KEY (dept_id) REFERENCES Department (dept_id),
university.Course AS DeptCourse
SOURCE KEY (dept_id) REFERENCES Department (dept_id)
DESTINATION KEY (course_id) REFERENCES Course (course_id),
university.Instructor AS DeptInstructor
SOURCE KEY (dept_id) REFERENCES Department (dept_id)
DESTINATION KEY (instructor_id) REFERENCES Instructor (instructor_id)
);
在這個圖表中，Course 和 Instructor 都是根節點資料表，連結至 Department 和 College。您可以傳遞 start_node，指定任一子圖：
-- Expand the Course subgraph (Course -> Department -> College)
SELECT
College_college_name,
AGG(Course_course_count) AS total_courses
FROM GRAPH_EXPAND("university.SchoolGraphMultiRoot", start_node => "Course")
GROUP BY College_college_name;
-- Expand the Instructor subgraph (Instructor -> Department -> College)
SELECT
College_college_name,
AGG(Instructor_total_salary) AS total_faculty_salary
FROM GRAPH_EXPAND("university.SchoolGraphMultiRoot", start_node => "Instructor")
GROUP BY College_college_name;
如要分析多個事實資料表的指標，請使用 GRAPH_EXPAND 查詢各個子圖，並在標準 SQL 中依共用維度彙整：
SELECT
C.College_college_name,
AGG(C.Course_course_count) AS total_courses,
AGG(I.Instructor_total_salary) AS total_faculty_salary
FROM GRAPH_EXPAND("university.SchoolGraphMultiRoot", start_node => "Course") AS C
INNER JOIN GRAPH_EXPAND("university.SchoolGraphMultiRoot", start_node => "Instructor") AS I
ON C.Department_dept_id = I.Department_dept_id
GROUP BY C.College_college_name;
AGG(C.Course_course_count) 和 AGG(I.Instructor_total_salary) 都會以各自的精細程度準確計算匯總，不受彙整作業干擾。
最佳做法
設計含有測量指標的圖表時，請遵循下列最佳做法：
在同一個資料表中定義節點和邊緣
建議您在同一個資料表中定義節點和相關聯的邊緣，重複使用資料表可確保節點資料表和邊緣資料表之間存在一對一 (1:1) 關係，進而防止扇出，並確保 GRAPH_EXPAND 函式不會忽略邊緣，以免造成混淆。
舉例來說，如果輸入資料表 Enrollment 包含指向 Student 和 Course 資料表的外鍵，請使用 Enrollment 輸入資料表定義 Enrollment 節點及其傳出邊緣。
避免在邊緣資料表定義不必要的屬性
使用同一個實體資料表定義節點資料表和邊緣資料表時，除非邊緣有新的資訊要擷取，否則請勿在邊緣資料表上使用 PROPERTIES 子句。由於節點資料表和邊緣資料表使用相同的實體資料表，因此所有這些屬性都已是節點資料表的一部分。
在邊緣資料表定義多餘的屬性，可能會導致 GRAPH_EXPAND 函式將邊緣資料表視為與共用節點資料表不同的實體。如要最佳化效能，請在邊緣資料表定義中指定 NO PROPERTIES，前提是邊緣沒有要擷取的新屬性。
將多對多關係建立為節點資料表
當資料表 A 中的資料列可對應至資料表 B 中的多個資料列，且資料表 B 中的資料列可對應至資料表 A 中的多個資料列時，就會發生多對多 (M:N) 關係。例如，一名學生可以註冊多門課程，而一門課程可以有多名學生。您可以使用中介連接資料表，將多對多關係拆分為兩個一對多關係。舉例來說，您可以定義 Enrollment 資料表，其中包含指向 Student 和 Course 資料表的外鍵。如果您在 CREATE PROPERTY GRAPH 陳述式中，將中繼連接資料表建立為連接兩個維度節點資料表的邊緣資料表 (如下列範例所示)，則 GRAPH_EXPAND 函式會忽略這些邊緣，因為其關係不明確：並非明確的多對一或一對一關係。
CREATE PROPERTY GRAPH university.SchoolGraph
NODE TABLES (
university.Student,
university.Course
)
EDGE TABLES (
-- Modeling the junction table as a standalone edge causes ambiguity
university.Enrollment AS StudentEnrollment
SOURCE KEY (student_id) REFERENCES Student (student_id)
DESTINATION KEY (course_id) REFERENCES Course (course_id)
);
如果圖表中的所有邊緣都遭到忽略，呼叫 GRAPH_EXPAND 或 BQ.SHOW_GRAPH_EXPAND_SCHEMA 時會失敗並顯示錯誤。
如要正確建立測量指標的多對多關係模型，請將中間連接資料表升級為節點資料表。在這個結構中，接合節點資料表會做為單一根節點資料表 (入度為零的節點資料表)。接著，定義兩個共用連接資料表的多對一 (N:1) 邊緣資料表，將 N:1 連結至各個維度節點資料表：
CREATE PROPERTY GRAPH university.SchoolGraph
NODE TABLES (
university.Student KEY (student_id),
university.Course KEY (course_id),
-- Promote the junction table to a node table (acting as the single root node)
university.Enrollment KEY (enrollment_id)
)
EDGE TABLES (
-- Share (reuse) the Enrollment table to define N:1 edges to each dimension
university.Enrollment AS EnrollmentToStudent
SOURCE KEY (enrollment_id) REFERENCES Enrollment (enrollment_id)
DESTINATION KEY (student_id) REFERENCES Student (student_id),
university.Enrollment AS EnrollmentToCourse
SOURCE KEY (enrollment_id) REFERENCES Enrollment (enrollment_id)
DESTINATION KEY (course_id) REFERENCES Course (course_id)
);
在這個結構中，Enrollment 是單一根節點資料表 (入度為零)，以多對一的方式連結至 Student 和 Course 維度資料表 (入度為一)。GRAPH_EXPAND 函式可以從 Enrollment 向外周遊至 Student 和 Course，藉此扁平化這個圖表。如果您在上述任一資料表定義測量指標，可以對測量指標欄呼叫 AGG 函式，正確計算匯總值，避免重複計算。
選擇測量指標處理不相符資料列的方式
如果資料列在連結的元素資料表中沒有相符項目，則該資料表的資料欄在 GRAPH_EXPAND 輸出中會是 NULL。舉例來說，如果課程沒有 dept_id 值，則該列的 Department 欄會是 NULL。Department 節點上的測量指標是否包含這些資料列，取決於您在何處編寫測量指標匯總的純量運算式。
如果您在匯總函式中編寫運算式，系統會針對展開輸出的每一列 (包括不相符的列) 評估運算式。如果運算式為其中一個資料列傳回值 (而非 NULL)，該值就會計入結果。
如果將運算式定義為屬性，並從測量指標參照該屬性，系統就會在節點資料表的資料列上評估運算式，不相符的資料列不會影響結果。
以下範例在 university.Department 上以兩種方式定義相同的運算式：
university.Department
KEY(dept_id)
PROPERTIES(dept_id, dept_name, college_id, budget,
COALESCE(budget, 0) AS budget_or_zero,
-- Includes courses that have no department, as a budget of 0.
MEASURE(AVG(COALESCE(budget, 0))) AS avg_budget_inline,
-- Averages only departments that exist.
MEASURE(AVG(budget_or_zero)) AS avg_budget_property)
兩項措施都會將 NULL 預算替換為 0。內嵌表單也會取代沒有部門的課程產生的 NULL 值，因此該課程會平均為預算為 0 的部門。選擇符合評估目標的表單。
查看 GRAPH_EXPAND 結構定義
如要查看 GRAPH_EXPAND 函式傳回的資料表結構定義，而不呼叫函式，請使用 BQ.SHOW_GRAPH_EXPAND_SCHEMA 系統程序：
DECLARE schema STRING DEFAULT '';
CALL BQ.SHOW_GRAPH_EXPAND_SCHEMA('university.SchoolGraph', schema);
SELECT schema;
這個程序會將每個資料欄的名稱、類型和模式填入 schema 變數。此外，也會指出資料欄參照的屬性是否為測量指標，並列出您為該屬性定義的任何說明或同義詞。