Dùng BigQuery để truy xuất dữ liệu mở COVID-19 và tạo báo cáo trên DataStudio

Trong bài viết này, đội ngũ của eMentorHub sẽ hỗ trợ ban toàn bộ các bước thực hành BigQuery để phân tích dữ liệu của tập dữ liệu mở COVID-19 (The COVID-19 open dataset). Truy vấn của chúng tôi được lấy cảm hứng từ chuỗi các sự kiện được thiết kế bởi Qwiklabs.

 

Truy vấn 1: tổng số ca mắc COVID-19 theo quốc gia và theo ngày

Câu truy vấn này sẽ trả lời cho câu hỏi “Tổng số ca mắc COVID-19 vào ngày 20/09/2021 là bao nhiêu người?”. Câu truy vấn này sẽ trả lại 1 hàng đơn chứa tổng số ca mắc COVID-19 cho từng quốc gia. Tên của cột giá trị này là total_cases.

Sao chép mã SQL bên dưới vào phần Chỉnh sửa Truy vấn và sau đó bấm vào “Run

SELECT
country_name,
  SUM(cumulative_confirmed) AS total_cases
FROM
  `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE
  date = "2021-09-20"
  and country_name in ("Vietnam","Singapore","Malaysia","Canada","United States of America")
  group by 1

Truy vấn này tổng hợp các ca mắc COVID-19 tích lũy của tất cả các trường hợp được ghi nhận vào ngày 15 tháng 4 năm 2020.

 

Truy vấn 2: Các khu vực bị COVID-19 ảnh hưởng nặng nề nhất

Đoạn truy vấn này sẽ trả lời câu hỏi: “Có bao nhiêu vùng trong các quốc gia mà chúng ta chọn có hơn 100 ca tử vong vào ngày 21/09/2021?” Truy vấn này cần liệt kê ra được kết quả trong trường số_tiểu_bang (count_of_states).

Sao chép đoạn mã SQL bên dưới vào phần Chỉnh sửa Truy vấn và sau đó bấm vào “Run

SELECT
country_name,
    COUNT(*) AS count_of_region
FROM (
SELECT
    subregion1_name AS state,
    SUM(cumulative_deceased) AS death_count
FROM
  `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE
  country_name in ("Vietnam","Singapore","Malaysia","Canada","United States of America")
  AND date='2021-09-10'
  AND subregion1_name IS NOT NULL
GROUP BY
  subregion1_name
)
WHERE death_count > 100

group by 1

Đảm bảo rằng bạn sử dụng country_name để lọc các trường hợp được ghi nhận tại Hoa Kỳ thay vì country_code, và sử dụng subregion1_name để nhóm các tiểu bang ở Hoa Kỳ.

 

Truy vấn 3: Xác định các điểm nóng

Truy vấn này dùng để “Liệt kê tất cả những vùng/region trong những quốc gia có hơn 1000 trường hợp xác nhận mắc Covid vào ngày 21/9/2021″. Truy vấn cần trả về tên vùng và các ca mắc COVID được sắp xếp theo thứ tự giảm dần.

Sao chép đoạn mã SQL bên dưới vào phần Chỉnh sửa Truy vấn và sau đó bấm vào “Run”.

SELECT
  *
FROM (
SELECT
  subregion1_name as state,
  sum(cumulative_confirmed) as total_confirmed_cases
FROM
  `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE
  country_code="US"
  AND date='2020-04-10'
  AND subregion1_name is NOT NULL
GROUP BY
  subregion1_name
ORDER BY
  total_confirmed_cases DESC
)
WHERE
  total_confirmed_cases > 1000

Chú ý rằng các điểm nóng được định nghĩa ở đây là tại các địa điểm mà tiểu bang đó có nhiều hơn 1000 ca mắc COVID-19 mỗi ngày.

 

Truy vấn 4: Tỷ lệ tử vong

Truy vấn sau đây là để tìm kiếm câu trả lời cho câu hỏi: “Tỷ lệ tử vong từ khi bắt đầu năm 2020 tới thời điểm hiện tại là bao nhiêu?”

Tỷ lệ tử vong được tính như sau: (SUM(cumulative_deceased)/SUM(cumulative_confirmed))*100

Sao chép đoạn mã SQL bên dưới vào phần Chỉnh sửa Truy vấn và sau đó bấm vào “Run

SELECT SUM(cumulative_confirmed) AS total_confirmed_cases, SUM(cumulative_deceased) AS total_deaths, (SUM(cumulative_deceased)/SUM(cumulative_confirmed))*100 AS case_fatality_ratio
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE country_name="Italy" AND date BETWEEN "2020-04-01" AND "2020-04-30"

 

Truy vấn 5: Tổng hợp tất cả các dữ liệu Covid-19 cơ bản theo quốc gia

Truy vấn bên dưới gom tất cả các dữ liệu cơ bản bằng cách nhóm các dữ liệu này theo quốc gia như sau:

  1. Các trường hợp mắc mới, tử vong và hồi phục: ca mắc mới_đã xác nhận, ca tử_vong_mới, ca hồi phục_mới, các ca mắc_tích lũy, các ca tử vong_tích lũy, các ca hồi phục_tích lũy, các ca tiếp xúc gần_được truy xuất.
  2. Lấy mẫu và vắc-xin: các lần lấy mẫu_tích lũy, người mới_được chích vắc xin, số_liều_vắc_xin_mới_ được_quản_lý, số_người_mới_chích_đủ_mũi_vắc_xin, số_người_chích_vắc_xin_tích_lũy, số_người_chích_đủ_mũi_vắc_xin_tích_lũy, đầu_tư_vào_vắc_xin.
  3. Các ca nhập viện: bệnh_nhân_nhập_viện_tích_lũy, bệnh_nhân_cần_điều_trị_chuyên_sâu, bệnh_nhân_nhập_viện_hiện_tại.

Sao chép đoạn mã SQL bên dưới vào phần Chỉnh sửa Truy vấn và sau đó bấm vào “Run

SELECT 
country_name, 
date,
population,

sum(new_confirmed) new_confirmed,
sum(new_deceased) new_deceased,
sum(new_recovered) new_recovered,
sum(cumulative_confirmed) cumulative_confirmed,
sum(cumulative_deceased) cumulative_deceased,
sum(cumulative_recovered) cumulative_recovered ,

sum(cumulative_tested) cumulative_tested,
sum(new_persons_vaccinated) new_persons_vaccinated,
sum(new_vaccine_doses_administered)new_vaccine_doses_administered,
sum(new_persons_fully_vaccinated) new_persons_fully_vaccinated,
sum(cumulative_persons_vaccinated)cumulative_persons_vaccinated,
sum(cumulative_persons_fully_vaccinated) cumulative_persons_fully_vaccinated,
sum(investment_in_vaccines)investment_in_vaccines, 

sum(cumulative_hospitalized_patients)cumulative_hospitalized_patients,
sum(current_intensive_care_patients)current_intensive_care_patients, 
sum(current_hospitalized_patients)current_hospitalized_patients,
sum(contact_tracing) 

FROM `bigquery-public-data.covid19_open_data.covid19_open_data` 
where country_name in ("Vietnam", "Singapore", "Canada", "United States of America")
AND date BETWEEN "2020-01-01" AND CURRENT_DATE()
group by 1,2,3

Đảm bảo rằng bạn sử dụng ORDER BY để sắp xếp kết quả theo ngày.

 

Truy vấn 6: Các ngày có số ca nhiễm gấp đôi so với số ca nhiễm trung bình

Sao chép đoạn mã SQL bên dưới vào phần Chỉnh sửa Truy vấn và sau đó bấm vào “Run

WITH cases_by_date AS (
  SELECT
  country_name,
    date,
    SUM(cumulative_confirmed) AS cases
  FROM
    `bigquery-public-data.covid19_open_data.covid19_open_data`
  WHERE country_name in
    ("Vietnam", "Singapore", "Canada", "United States of America") 
    AND date between DATE_SUB(CURRENT_DATE(),INTERVAL 1 MONTH) and CURRENT_DATE()
  GROUP BY
    1,2
  ORDER BY
    1,2 ASC 
 )
, previous_day_comparison AS 
(SELECT
country_name,
  date,
  cases,
  LAG(cases) OVER(ORDER BY date) AS previous_day,
  cases - LAG(cases) OVER(ORDER BY date) AS net_new_cases,
  (cases - LAG(cases) OVER(ORDER BY date))*100/LAG(cases) OVER(ORDER BY date) AS percentage_increase
FROM cases_by_date
)
SELECT 
country_name,
Date, 
cases as Confirmed_Cases_On_Day, 
previous_day as Confirmed_Cases_Previous_Day, 
percentage_increase as Percentage_Increase_In_Cases
FROM previous_day_comparison
WHERE percentage_increase > 10

 

Tạo một báo cáo DataStudio

1. Lưu phần mã truy vấn trong Query 5 như đã từng làm trong dự án có tên “GoogleOpenData”

2. Mở Data Studio và tạo 1 báo cáo mới gọi là “COVID -19 ĐỒ THỊ KỸ THUẬT SỐ – VIETNAM, SINGAPORE, CANADA VÀ HOA KỲ”

3. Cửa sổ cho việc kết nối dữ liệu sẽ xuất hiện một cách tự động để cho phép thêm dữ liệu vào báo cáo. Chọn “Big Query”

4. Thêm dữ liệu vào báo cáo bằng việc cho phép Data Studio kết nối nó đến phần ta vừa khởi tạo: “GoogleOpenData”

5. Bắt đầu tạo biểu đồ, công cụ và sàng lọc cho trang đầu tiên của báo cáo. Trong báo cáo Data Studio mới này, chọn Thêm 1 biểu đồ > đường. Thêm số_người_đã_chích_vắc_xin_tích_lũy và số_người_đã_chích_đủ_mũi_tích_lũy vào trường Chỉ Số Đo Lường và thêm ngày vào trường Kích Thước. Nhấn lưu để tạo 1 biểu đồ đầu tiên (dạng đường thẳng)

6. Kế tiếp, trên thanh công cụ, chọn “Thêm 1 danh sách Control/Drop-down”. Thêm tên_đất_nước vào trường “Kiểm soát” để tạo công cụ cho việc lựa chọn quốc gia trên báo cáo kỹ thuật số

 

7. Tương tự, chọn “Thêm kiểm soát” và chọn “kiểm soát pham vi ngày”

Tùy chọn hóa Thuộc Tính Kiểm Soát Phạm Vi Ngày đến 1 phạm vi mục tiêu giống như bên dưới. Bây giờ thì những công cụ đầu tiên của ta đã hoàn thành

  • Kế tiếp, chúng ta sẽ tạo 4 thẻ tính điểm như sau: ca_mắc_COVID_tích_lũy, ca_tử_vong_tích_lũy, ca_hồi_phục_tích_lũy, số_người_tiêm_đủ_mũi_vắc_xin_tích_lũy, đầu_tư_vào_vắc_xin bằng việc chọn thêm 1 biểu đồ / Dưới thẻ tính điểm, chọn 1 trong 2 sự lựa chọn (dựa vào số lượng dữ liệu, sự lựa chọn số 2 sẽ tập trung các con số).
  • Chọn những ca mắc tích lũy (hay bất kỳ phương pháp đo lường khác) vào trong trường phương pháp đo lường, chỉnh nó lên mức cao nhất để tránh việc thổi phồng dữ liệu.

    Nếu bạn thực hiện không thành công, hãy xóa tất cả dữ liệu và báo cáo khỏi bảng điều khiển DataStudio trước khi thử lại.

 

Bạn có thể tạo nhiều biểu đồ hơn, xem video hướng dẫn cho các biểu đồ sau đây:

 

 

 

Chúc mừng bạn đã hoàn thành dự án khoa học dữ liệu đầu tiên của mình trong việc phân tích để hiểu Covid-19 rõ hơn.

Hãy lưu ý rằng quyền truy cập dữ liệu chỉ hỗ trợ cho nhu cầu phân tích dữ liệu Covid-19 của bạn để học, những dữ liệu phân tích đó không đảm bảo tính chính xác thực tiễn. Do đó trước khi đưa ra bất kì quyết định mang tính sống – chết nào, hoặc khi muốn khuyên ai đó điều gì dựa trên sự phân tích của bạn, hãy xin ý kiến từ các chuyên gia sức khỏe cộng đồng.

 

Hoà Dư

Bài viết liên quan

Data Science Second Project: Analysing Covid-19 data using Data Studio dashboard

Data Science Second Project: Analysing Covid-19 data using Data Studio dashboard

[WEBINAR] Phân tích tình hình đại dịch Covid-19 bằng Google Data Studio...
Hướng dẫn tạo dự án phân tích dữ liệu Covid-19 đầu tiên trên Google Cloud Platform

Hướng dẫn tạo dự án phân tích dữ liệu Covid-19 đầu tiên trên Google Cloud Platform

BigQuery (BQ) của Google Cloud Platform (GCP) là một ứng dụng mới...
Dùng BigQuery để truy xuất dữ liệu mở COVID-19 và tạo báo cáo trên DataStudio

Dùng BigQuery để truy xuất dữ liệu mở COVID-19 và tạo báo cáo trên DataStudio

Trong bài viết này, đội ngũ của eMentorHub sẽ hỗ trợ ban...

“Thất bại là điều tất yếu với những ai không biết lên kế hoạch và mục tiêu”

Benjamin Franklin