Trabalhar com medidas

Neste documento, mostramos como definir e consultar medidas nos gráficos. É possível usar medidas para garantir que as agregações sejam realizadas corretamente em todas as mesclagens.

Visão geral

Uma medida é uma propriedade agregada definida na cláusula PROPERTIES de uma tabela de nós ou de bordas. As medições são definidas por uma expressão de medição, que é criada usando a palavra-chave MEASURE em torno de um dos seguintes itens:

  • uma expressão agregada
  • uma expressão que combina funções de agregação com operações escalares
  • uma medida derivada, que é uma combinação de medidas unidas na função AGG definidas na mesma tabela de elementos;

As medições são compatíveis com as seguintes funções de agregação:

  • SUM
  • AVG
  • COUNT
  • MIN
  • MAX
  • AGG (para medições derivadas)

Todas as funções de agregação, exceto AGG, são compatíveis com a palavra-chave DISTINCT. Para mais informações sobre expressões de medição compatíveis, leia sobre o tipo de medição.

As medições definem a agregação em referência ao KEY da tabela de nós ou de bordas em que estão definidas. Isso significa que, ao consultar uma medida, a agregação é realizada corretamente, mesmo que a tabela subjacente seja mesclada de uma maneira que cause a duplicação de linhas.

Não é possível referenciar uma propriedade definida por uma medida em uma consulta GQL. Em vez disso, você acessa as medições chamando a TVF GRAPH_EXPAND para consultar uma representação relacional do seu gráfico. Por padrão, GRAPH_EXPAND retorna uma representação de tabela simplificada do gráfico com uma coluna por propriedade. Também é possível especificar flatten_results => FALSE para retornar uma tabela hierárquica com colunas STRUCT aninhadas. Se o gráfico tiver várias tabelas de nó raiz, especifique o argumento start_node para escolher qual tabela raiz será percorrida.

Essa função não aceita todos os tipos de gráficos. Para mais informações sobre quais gráficos formam uma entrada válida, consulte as limitações de entrada. Para executar consultas GQL, é preciso ter uma reserva que use a edição Enterprise ou Enterprise Plus. Se você usa preços sob demanda, pode chamar a função GRAPH_EXPAND para executar consultas SQL no gráfico.

É possível chamar a função AGG na saída da TVF GRAPH_EXPAND para agregar as propriedades de acordo com a agregação definida na medição.

Definir medições

As medições são definidas na cláusula PROPERTIES de uma definição de tabela de nós ou arestas usando a palavra-chave MEASURE() em torno de uma expressão de medição.

O exemplo a seguir cria um conjunto de dados chamado university e tabelas para faculdades, departamentos e cursos:

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);

A instrução a seguir cria um gráfico chamado SchoolGraph que define medidas em propriedades nos nós Department e Course. É necessário fornecer um alias para as propriedades definidas por uma medida.

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)
  );

Uma visualização do gráfico SchoolGraph.

A medida total_budget é definida como MEASURE(SUM(budget)). A medida bloqueia a agregação para KEY, que é dept_id.

Medições derivadas

Uma métrica derivada é uma métrica que faz referência a outras métricas definidas na mesma tabela de elementos usando a função AGG(). Essa funcionalidade permite criar hierarquias de medição reutilizáveis.

Por exemplo, em university.Course, a medição computed_avg_credits calcula a proporção entre as medições total_credits e course_count:

MEASURE(SAFE_DIVIDE(AGG(total_credits), NULLIF(AGG(course_count), 0))) AS computed_avg_credits

As medidas derivadas mantêm a propriedade de bloqueio de granularidade da chave da tabela de elementos subjacente. As dependências de medição são resolvidas no tempo de compilação do gráfico. As referências circulares entre as medições retornam um erro.

Noções básicas sobre contagem excessiva

Quando você mescla tabelas, os dados são repetidos sempre que há uma relação de um para muitos nos dados. Por exemplo, se você mesclar as tabelas Course, Department e College, um departamento com vários cursos vai aparecer em várias linhas na saída:

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 |
 +------------------------+------------------------+-----------------+----------*/

Se você tentar calcular o orçamento total por faculdade usando SUM(budget), o orçamento do departamento de Ciência da Computação será contado duas vezes. Para evitar esse problema, consulte a tabela Department diretamente, mas essa abordagem não funciona se você quiser calcular várias agregações de tabelas diferentes que contribuem para os dados mesclados. A seção a seguir mostra como as medidas resolvem esse problema.

Consultar um gráfico com medidas

É possível consultar um gráfico com medidas usando GQL, mas a consulta não pode referenciar diretamente nenhuma propriedade definida por uma medida. Por exemplo, a consulta a seguir faz referência a nós que têm propriedades de medição definidas, mas não usa nem retorna nenhum dos campos de medição:

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    |
 +------------------------+------------------------+-----------------*/

Para trabalhar com métricas, use a função com valor de tabela (TVF) GRAPH_EXPAND para consultar seu gráfico como uma tabela relacional:

GRAPH_EXPAND(
  graph_name [, start_node => 'NodeName'] [, flatten_results => TRUE|FALSE]
)

A função aceita os seguintes argumentos:

  • graph_name: uma STRING que contém o nome do gráfico.
  • start_node: uma STRING opcional que especifica a tabela de nós inicial para a travessia. Esse argumento é obrigatório se o gráfico tiver várias tabelas de nós raiz.
  • flatten_results: um BOOL opcional (o padrão é TRUE):
    • TRUE: nivela todas as propriedades de nós e arestas em colunas de nível superior com prefixos <element>_<property>.
    • FALSE: retorna as propriedades do nó raiz como colunas de nível superior sem prefixo e tabelas vizinhas mescladas como colunas STRUCT.

Usar saída nivelada

Quando flatten_results é definido como TRUE (o modo padrão), as colunas na tabela de saída são nomeadas concatenando o rótulo da tabela de nós ou arestas e o nome da propriedade, por exemplo, Course_course_name ou Department_total_budget. A consulta a seguir mostra um exemplo de saída da TVF GRAPH_EXPAND:

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       |
 +------------------------+------------------------+-------------------+--------------------*/

A função GRAPH_EXPAND produz a tabela simplificada aplicando uma série de operações LEFT JOIN às tabelas de nós e arestas. Se você não especificar um nó inicial, o gráfico de entrada precisará ter uma única tabela de nó raiz no gráfico de relacionamento de esquema. De forma equivalente, a tabela do nó raiz precisa ter uma relação de muitos para um com todas as outras tabelas de nós com que ela compartilha uma aresta. Os dados que não podem ser acessados na tabela do nó raiz por uma série de mesclagens não aparecem na saída. No exemplo anterior, Course é a tabela do nó raiz. O departamento Research é omitido da saída porque não tem cursos.

Não é possível selecionar diretamente uma coluna para uma propriedade definida por uma medida. Em vez disso, é preciso uni-las na função AGG(). Essa função garante que a agregação definida nas medições seja realizada exatamente uma vez por chave.

A consulta a seguir calcula simultaneamente o orçamento total e o número total de cursos para cada faculdade:

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             |
 +------------------------+----------------+---------------*/

Usar saída hierárquica

Quando você especifica flatten_results => FALSE, GRAPH_EXPAND retorna uma relação hierárquica em vez de colunas com prefixo:

  • As propriedades do nó raiz inicial aparecem como colunas de nível superior sem prefixo (por exemplo, course_name ou credits).
  • As propriedades de tabelas vizinhas mescladas, que podem ser tabelas de nós ou de bordas, aparecem como campos STRUCT aninhados com o nome da tabela de elementos. Por exemplo, se o nó inicial for Course, use a propriedade DeptCourse.Department.CollegeDept.College.college_nameto access thecollege_name`.
  • As medições dentro de structs aninhados são consultadas usando a notação de ponto dentro de AGG(), por exemplo, AGG(DeptCourse.Department.total_budget).
  • Se uma tabela de elementos tiver uma propriedade com o mesmo nome de uma tabela de elementos adjacente, não será possível consultar o gráfico usando flatten_results => FALSE. Em vez disso, é preciso nivelar os resultados.
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;

Trabalhar com várias tabelas de nós raiz

Os gráficos geralmente contêm várias tabelas de nós raiz, também chamadas de tabelas de fatos, que compartilham tabelas de dimensões. Para consultar um gráfico com várias tabelas de nós raiz, use o argumento start_node em GRAPH_EXPAND. A função atravessa o subgrafo acessível do nó inicial.

Por exemplo, é possível adicionar uma tabela Instructor ao conjunto de dados university que também se vincula a 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);

É possível criar um novo gráfico chamado university.SchoolGraphMultiRoot que inclui a tabela de nós Instructor e uma aresta de Instructor para 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)
  );

Neste gráfico, Course e Instructor servem como tabelas de nó raiz que se vinculam a Department e College. É possível segmentar qualquer subgráfico transmitindo 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;

Para analisar métricas em várias tabelas de fatos, consulte cada subgráfico usando GRAPH_EXPAND e mescle-os nas dimensões compartilhadas no SQL padrão:

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;

Tanto AGG(C.Course_course_count) quanto AGG(I.Instructor_total_salary) calculam as agregações com precisão nos respectivos níveis de detalhamento, sem interferência da mesclagem.

Práticas recomendadas

Ao projetar um gráfico com medições, siga estas práticas recomendadas:

Definir nós e arestas em uma única tabela

Recomendamos que você use a mesma tabela para uma definição de tabela de nós e as definições de tabela de borda de conexão. A reutilização da tabela garante uma relação de um para um (1:1) entre a tabela de nós e a tabela de arestas, o que evita o fan-out e garante que a função GRAPH_EXPAND não ignore a aresta como ambígua.

Por exemplo, se você tiver uma tabela de entrada Enrollment que contém chaves externas apontando para as tabelas Student e Course, defina o nó Enrollment e as arestas de saída usando a tabela de entrada Enrollment.

Evite definir propriedades desnecessárias em tabelas de borda

Ao definir uma tabela de nós e uma tabela de arestas usando a mesma tabela física, não use a cláusula PROPERTIES na tabela de arestas, a menos que haja novas informações para capturar na aresta. Como a tabela de nós e a tabela de bordas usam a mesma tabela física, todas essas propriedades já fazem parte da tabela de nós.

Definir propriedades redundantes na tabela de borda pode fazer com que a função GRAPH_EXPAND trate a tabela de borda como uma entidade separada da tabela de nós compartilhados. Para otimizar o desempenho, especifique NO PROPERTIES na definição da tabela de borda quando não houver novas propriedades para capturar na borda.

Modelar relacionamentos de muitos para muitos como tabelas de nós

Uma relação muitos para muitos (M:N) ocorre quando uma linha na tabela A pode corresponder a várias linhas na tabela B, e uma linha na tabela B pode corresponder a várias linhas na tabela A. Por exemplo, um estudante pode se inscrever em vários cursos, e um curso pode ter muitos estudantes. É possível separar uma relação de muitos para muitos em duas relações de um para muitos usando uma tabela de junção intermediária. Por exemplo, é possível definir uma tabela Enrollment que contenha chaves externas que apontem para as tabelas Student e Course. Se você modelar uma tabela de junção intermediária como uma tabela de borda que conecta duas tabelas de nós de dimensão na instrução CREATE PROPERTY GRAPH, como no exemplo a seguir, a função GRAPH_EXPAND vai ignorar essas bordas porque a relação delas é ambígua: não é claramente de muitos para um ou de um para um.

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)
  );

Se todas as arestas em um gráfico forem ignoradas, as chamadas para GRAPH_EXPAND ou BQ.SHOW_GRAPH_EXPAND_SCHEMA vão falhar com um erro.

Para modelar corretamente uma relação M:N para medidas, promova a tabela de junção intermediária para uma tabela de nós. Nessa estrutura, a tabela de nós de junção serve como a única tabela de nós raiz (uma tabela de nós com um grau de entrada igual a zero). Em seguida, defina duas tabelas de borda de muitos para um (N:1) que compartilham a tabela de junção para conectar N:1 a cada uma das tabelas de nós de dimensão:

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)
  );

Nessa estrutura, Enrollment serve como a única tabela de nó raiz (grau de entrada zero) que conecta N:1 às tabelas de dimensão Student e Course (grau de entrada um). A função GRAPH_EXPAND pode nivelar esse gráfico percorrendo de Enrollment para Student e Course. Se você definir medidas em qualquer uma dessas tabelas, poderá chamar a função AGG nas colunas de medida para calcular corretamente as agregações sem contagem excessiva.

Escolher como as medições lidam com linhas sem correspondência

Quando uma linha não tem correspondência em uma tabela de elementos conectados, as colunas dessa tabela são NULL na saída GRAPH_EXPAND. Por exemplo, se um curso não tiver um valor dept_id, as colunas Department dessa linha serão NULL. Se uma medida no nó Department inclui essas linhas depende de onde você escreve a expressão escalar que a medida agrega.

Se você escrever a expressão dentro da função de agregação, ela será avaliada para cada linha da saída expandida, incluindo linhas sem correspondência. Quando a expressão retorna um valor em vez de NULL para uma dessas linhas, esse valor contribui para o resultado.

Se você definir a expressão como uma propriedade e referenciar essa propriedade na medição, a expressão será avaliada nas linhas da tabela de nós, e as linhas sem correspondência não vão contribuir para o resultado.

O exemplo a seguir define a mesma expressão de ambas as maneiras em 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)

Ambas as medidas substituem um orçamento de NULL por 0. O formulário inline também substitui os valores NULL produzidos para um curso que não tem departamento, de modo que esse curso seja incluído na média como um departamento com um orçamento de 0. Escolha o formulário que corresponde ao que você quer medir.

Ver esquema GRAPH_EXPAND

Para visualizar o esquema da tabela retornada pela função GRAPH_EXPAND sem chamar a função, use o procedimento do sistema BQ.SHOW_GRAPH_EXPAND_SCHEMA:

DECLARE schema STRING DEFAULT '';
CALL BQ.SHOW_GRAPH_EXPAND_SCHEMA('university.SchoolGraph', schema);
SELECT schema;

Esse procedimento preenche a variável schema com o nome, o tipo e o modo de cada coluna. Ele também indica se a propriedade referenciada pela coluna é uma medida e lista qualquer descrição ou sinônimo que você definiu nela.

A seguir