CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerydata-mining
hepzi
hepzi

Reputation: 445

How to join multiple table without using a key in bigquery

Table name and schema

    Table 1:student table
    column name : student_name, student_address, student_mark
    Table 2:staff table
    Column name: Staff_name, Staff_address, Staff_age, staff_class
    Table 3: alumni table
    Column name: alumni_name, alumni_address, alumni_year_passing, Alumni_marks, alumni_work_details

My intention to single table that containing null count of columns present all three table using Bigquery. Schema for constructing null table

student_name_null_pct float,
student_address_null int, 
student_address_null_pct float,
student_mark_null int,
Staff_name_null int,
Staff_address_null int, 
staff_class_null int,
alumni_name_null int, 
alumni_address_null int, 
alumni_year_passing_null int

Used struct in bigquery

SELECT
  ( (
    SELECT
      AS STRUCT student_name_null,
      ROUND(SAFE_DIVIDE(student_name_null,
          total_rows), 5) AS student_name_null_pct,
      student_address,
      ROUND(SAFE_DIVIDE(student_address,
          total_rows), 5) AS student_address_pct
    FROM (
      SELECT
        CURRENT_DATE() AS date,
        COUNT(*) AS total_rows,
        COUNTIF(student.student_name IS NULL) AS student_name_null,
        COUNTIF(distributors.student_address IS NULL) AS student_address,
        COUNTIF(distributors.student_mark IS NULL) AS student_mark

      FROM
        `school.student_table` AS student)),
    (
    SELECT
      AS STRUCT total_rows_staff,
      Staff_name,
      ROUND(SAFE_DIVIDE(Staff_name,
          total_rows_staff), 5) AS Staff_name_pct,
      brand_title,
      ROUND(SAFE_DIVIDE(Staff_address,
          total_rows_staff), 5) AS Staff_address_pct

    FROM (
      SELECT
        COUNT(*) AS total_rows_staff,
        COUNTIF(products.Staff_name IS NULL) AS Staff_name,
        COUNTIF(products.Staff_address IS NULL) AS Staff_address,

      FROM
        `school.staff_table` AS staff))

Getting result in struct this cant be used for schema that i am excepting. Kindly help me to solve the issues.

Upvotes: 0

Views: 1313

Answers (1)

hepzi
hepzi

Reputation: 445

SELECT
  CURRENTDATE() AS date,
  student_name_null,
  ROUND(SAFE_DIVIDE(student_name_null,
      total_rows), 5) AS student_name_null_pct,
  student_address,....
FROM (
  SELECT
    COUNT(*) AS total_rows,
    COUNTIF(student.student_name IS NULL) AS student_name_null,
    COUNTIF(distributors.student_address IS NULL) AS student_address,
    COUNTIF(distributors.student_mark IS NULL) AS student_mark
  FROM
    `school.student_table` AS student),
  (
  SELECT
    COUNT(*) AS total_rows_staff,
    COUNTIF(products.Staff_name IS NULL) AS Staff_name,
    COUNTIF(products.Staff_address IS NULL) AS Staff_address,
  FROM
    `school.staff_table` AS staff))

This solved my issues

Upvotes: 1

Related Questions