SQL Query Covid19-aantallen per Gemeente
De Covid19-aantallen zijn ook beschikbaar per Nederlandse gemeente, inclusief gemeentecode:
--
-- Aantallen gemeente per dag.
--
create or replace table casussen_gemeente@inmemorystorage
as
select to_date(j.date_of_report_c, 'YYYY-MM-DD HH24:MI:SS')
date_of_report
, to_date(j.date_of_publication_c, 'YYYY-MM-DD')
date_of_publication
, j.municipality_code
, j.municipality_name
, j.province
, j.security_region_code
, j.security_region_name
, j.municipal_health_service
, j.roaz_region
, j.total_reported
, j.hospital_admission
, j.deceased
from HTTPDOWNLOAD@DataDictionary('https://data.rivm.nl/covid-19/COVID-19_aantallen_gemeente_per_dag.json') htp
join jsontable
( '[*]'
passing htp.content_clob
columns date_of_report_c varchar2 path 'Date_of_report'
, date_of_publication_c varchar2 path 'Date_of_publication'
, municipality_code varchar2 path 'Municipality_code'
, municipality_name varchar2 path 'Municipality_name'
, province varchar2 path 'Province'
, security_region_code varchar2 path 'Security_region_code'
, security_region_name varchar2 path 'Security_region_name'
, municipal_health_service varchar2 path 'Municipal_health_service'
, roaz_region varchar2 path 'ROAZ_region'
, total_reported int32 path 'Total_reported'
, hospital_admission int32 path 'Hospital_admission'
, deceased int32 path 'Deceased'
) j
SQL Query Covid19-aantallen per Gemeente
De cumulatieve aantallen per gemeente (Covid19-besmettingen, ziekenhuisopnames en overleden) volgens RIVM zet je in een tabel met:
--
-- Aantallen gemeente cumulatief.
--
create or replace table casussen_gemeente_cumulatief@inmemorystorage
as
select to_date(j.date_of_report_c, 'YYYY-MM-DD HH24:MI:SS')
date_of_report
, j.municipality_code
, j.municipality_name
, j.province
, j.total_reported
, j.hospital_admission
, j.deceased
from HTTPDOWNLOAD@DataDictionary('https://data.rivm.nl/covid-19/COVID-19_aantallen_gemeente_cumulatief.json') htp
join jsontable
( '[*]'
passing htp.content_clob
columns date_of_report_c varchar2 path 'Date_of_report'
, municipality_code varchar2 path 'Municipality_code'
, municipality_name varchar2 path 'Municipality_name'
, province varchar2 path 'Province'
, total_reported int32 path 'Total_reported'
, hospital_admission int32 path 'Hospital_admission'
, deceased int32 path 'Deceased'
) j
SQL Query Verdenkingen door Huisartsen
Huisartsen verzamelen verdenkingen en die worden beschikbaar gesteld als tabel:
--
-- Verdenkingen huisartsen.
--
create or replace table nl_verdenkingen_huisartsen@inmemorystorage
as
select from_unixtime(j.week_unix)
week
, from_unixtime(j.week_start_unix)
date_week_start
, from_unixtime(j.week_end_unix)
date_week_end
, j.incidentie
, j.geschat_aantal
--
, from_unixtime(j.date_of_insertion_unix)
date_insertion
, from_unixtime(j.control_last_generated_unix)
control_date_last_generated
, j.control_proto_name
, j.control_name
, j.control_code
from HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join jsontable
( 'verdenkingen_huisartsen.values[*]'
passing htp.content_clob
columns control_last_generated_unix int64 path '::ancestor.::ancestor.last_generated'
, control_proto_name varchar2 path '::ancestor.::ancestor.proto_name'
, control_name varchar2 path '::ancestor.::ancestor.name'
, control_code varchar2 path '::ancestor.::ancestor.code'
, week_unix int64 path 'week_unix'
, week_start_unix int64 path 'week_start_unix'
, week_end_unix int64 path 'week_end_unix'
, incidentie number path 'incidentie'
, geschat_aantal number path 'geschat_aantal'
, date_of_insertion_unix int64 path 'date_of_insertion_unix'
) j
order
by control_date_last_generated
, week
SQL Query Covid19-ziekenhuisopnames
De Covid19-ziekenhuisopnames leg je in een tabel vast met:
--
-- Intake hospital ma.
--
create or replace table nl_intake_hospital_ma@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
date_report
, j.moving_average_hospital
--
, from_unixtime(j.date_of_insertion_unix)
date_insertion
, from_unixtime(j.control_last_generated_unix)
control_date_last_generated
, j.control_proto_name
, j.control_name
, j.control_code
from HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join jsontable
( 'intake_hospital_ma.values[*]'
passing htp.content_clob
columns control_last_generated_unix int64 path '::ancestor.::ancestor.last_generated'
, control_proto_name varchar2 path '::ancestor.::ancestor.proto_name'
, control_name varchar2 path '::ancestor.::ancestor.name'
, control_code varchar2 path '::ancestor.::ancestor.code'
, date_of_report_unix int64 path 'date_of_report_unix'
, moving_average_hospital number path 'moving_average_hospital'
, date_of_insertion_unix int64 path 'date_of_insertion_unix'
) j
order
by control_date_last_generated
, date_report
SQL Query Covid19-besmettingen
De tellingen van bevestigde Covid19-besmette personen schrijf je naar een tabel met:
--
-- Infectious people count.
--
create or replace table nl_infectious_people_counts@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
date_report
, j.infectious_low
, j.infectious_avg
, j.infectious_high
--
, from_unixtime(j.date_of_insertion_unix)
date_insertion
, from_unixtime(j.control_last_generated_unix)
control_date_last_generated
, j.control_proto_name
, j.control_name
, j.control_code
from HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join jsontable
( 'infectious_people_count.values[*]'
passing htp.content_clob
columns control_last_generated_unix int64 path '::ancestor.::ancestor.last_generated'
, control_proto_name varchar2 path '::ancestor.::ancestor.proto_name'
, control_name varchar2 path '::ancestor.::ancestor.name'
, control_code varchar2 path '::ancestor.::ancestor.code'
, date_of_report_unix int64 path 'date_of_report_unix'
, infectious_low number path 'infectious_low'
, infectious_avg number path 'infectious_avg'
, infectious_high number path 'infectious_high'
, date_of_insertion_unix int64 path 'date_of_insertion_unix'
) j
order
by control_date_last_generated
, date_report
SQL Query Genormaliseerde versie besmettingen
En de genormaliseerde versie hiervan met onder- en bovengrenzen met:
--
-- Infectious people count normalized.
--
create or replace table nl_infectious_people_counts_normalized@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
date_report
, j.infectious_low_normalized
, j.infectious_avg_normalized
, j.infectious_high_normalized
--
, from_unixtime(j.date_of_insertion_unix)
date_insertion
, from_unixtime(j.control_last_generated_unix)
control_date_last_generated
, j.control_proto_name
, j.control_name
, j.control_code
from HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join jsontable
( 'infectious_people_count_normalized.values[*]'
passing htp.content_clob
columns control_last_generated_unix int64 path '::ancestor.::ancestor.last_generated'
, control_proto_name varchar2 path '::ancestor.::ancestor.proto_name'
, control_name varchar2 path '::ancestor.::ancestor.name'
, control_code varchar2 path '::ancestor.::ancestor.code'
, date_of_report_unix int64 path 'date_of_report_unix'
, infectious_low_normalized number path 'infectious_low_normalized'
, infectious_avg_normalized number path 'infectious_avg_normalized'
, infectious_high_normalized number path 'infectious_high_normalized'
, date_of_insertion_unix int64 path 'date_of_insertion_unix'
) j
order
by control_date_last_generated
, date_report
SQL Query Intensive Care Opnames
Vergelijkbaar vraag je de opnames op de Intensive Care op en stop je die in een databasetabel met:
--
-- Intake intensive care ma.
--
create or replace table nl_intake_intensive_care_ma@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
date_report
, j.moving_average_ic
--
, from_unixtime(j.date_of_insertion_unix)
date_insertion
, from_unixtime(j.control_last_generated_unix)
control_date_last_generated
, j.control_proto_name
, j.control_name
, j.control_code
from HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join jsontable
( 'intake_intensivecare_ma.values[*]'
passing htp.content_clob
columns control_last_generated_unix int64 path '::ancestor.::ancestor.last_generated'
, control_proto_name varchar2 path '::ancestor.::ancestor.proto_name'
, control_name varchar2 path '::ancestor.::ancestor.name'
, control_code varchar2 path '::ancestor.::ancestor.code'
, date_of_report_unix int64 path 'date_of_report_unix'
, moving_average_ic number path 'moving_average_ic'
, date_of_insertion_unix int64 path 'date_of_insertion_unix'
) j
order
by control_date_last_generated
, date_report
SQL Query Covid19-Dagstanden
De Covid19-dagstanden verwerk je tot een tabel met:
--
-- Infected people nursery count daily.
--
create or replace table nl_infected_people_nursery_counts_daily@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
date_report
, j.infected_nursery_daily
--
, from_unixtime(j.date_of_insertion_unix)
date_insertion
, from_unixtime(j.control_last_generated_unix)
control_date_last_generated
, j.control_proto_name
, j.control_name
, j.control_code
from HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join jsontable
( 'infected_people_nursery_count_daily.values[*]'
passing htp.content_clob
columns control_last_generated_unix int64 path '::ancestor.::ancestor.last_generated'
, control_proto_name varchar2 path '::ancestor.::ancestor.proto_name'
, control_name varchar2 path '::ancestor.::ancestor.name'
, control_code varchar2 path '::ancestor.::ancestor.code'
, date_of_report_unix int64 path 'date_of_report_unix'
, infected_nursery_daily number path 'infected_nursery_daily'
, date_of_insertion_unix int64 path 'date_of_insertion_unix'
) j
order
by control_date_last_generated
, date_report
SQL Query Covid19 Overlijdensgevallen Zorg
En de aan Covid19-overleden personen in de zorg met:
--
-- Deceased people nursery count daily.
--
create or replace table nl_deceased_people_nursery_counts_daily@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
date_report
, j.deceased_nursery_daily
--
, from_unixtime(j.date_of_insertion_unix)
date_insertion
, from_unixtime(j.control_last_generated_unix)
control_date_last_generated
, j.control_proto_name
, j.control_name
, j.control_code
from HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join jsontable
( 'deceased_people_nursery_count_daily.values[*]'
passing htp.content_clob
columns control_last_generated_unix int64 path '::ancestor.::ancestor.last_generated'
, control_proto_name varchar2 path '::ancestor.::ancestor.proto_name'
, control_name varchar2 path '::ancestor.::ancestor.name'
, control_code varchar2 path '::ancestor.::ancestor.code'
, date_of_report_unix int64 path 'date_of_report_unix'
, deceased_nursery_daily number path 'deceased_nursery_daily'
, date_of_insertion_unix int64 path 'date_of_insertion_unix'
) j
order
by control_date_last_generated
, date_report
SQL Query Covid19 Locaties
De totale aantallen gemelde locaties schrijf je naar een databasetabel met het volgende SQL-statement:
--
-- Total reported locations.
--
create or replace table nl_reported_locations_total@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
date_report
, j.total_reported_locations
--
, from_unixtime(j.date_of_insertion_unix)
date_insertion
, from_unixtime(j.control_last_generated_unix)
control_date_last_generated
, j.control_proto_name
, j.control_name
, j.control_code
from HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join jsontable
( 'total_reported_locations.values[*]'
passing htp.content_clob
columns control_last_generated_unix int64 path '::ancestor.::ancestor.last_generated'
, control_proto_name varchar2 path '::ancestor.::ancestor.proto_name'
, control_name varchar2 path '::ancestor.::ancestor.name'
, control_code varchar2 path '::ancestor.::ancestor.code'
, date_of_report_unix int64 path 'date_of_report_unix'
, total_reported_locations number path 'total_reported_locations'
, date_of_insertion_unix int64 path 'date_of_insertion_unix'
) j
order
by control_date_last_generated
, date_report
SQL Query Nieuwe Locaties
Het nieuw aantal gemelde locatie schrijf je naar een tabel met:
--
-- Total newly reported locations.
--
create or replace table nl_reported_locations_newly@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
date_report
, j.total_new_reported_locations
--
, from_unixtime(j.date_of_insertion_unix)
date_insertion
, from_unixtime(j.control_last_generated_unix)
control_date_last_generated
, j.control_proto_name
, j.control_name
, j.control_code
from HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join jsontable
( 'total_newly_reported_locations.values[*]'
passing htp.content_clob
columns control_last_generated_unix int64 path '::ancestor.::ancestor.last_generated'
, control_proto_name varchar2 path '::ancestor.::ancestor.proto_name'
, control_name varchar2 path '::ancestor.::ancestor.name'
, control_code varchar2 path '::ancestor.::ancestor.code'
, date_of_report_unix int64 path 'date_of_report_unix'
, total_new_reported_locations number path 'total_new_reported_locations'
, date_of_insertion_unix int64 path 'date_of_insertion_unix'
) j
order
by control_date_last_generated
, date_report
SQL Query Besmette Personen
Net zoals je de totalen qua besmette personen vastlegt met:
--
-- Infected people total.
--
create or replace table nl_infected_people_total@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
date_report
, j.infected_daily_total
--
, from_unixtime(j.date_of_insertion_unix)
date_insertion
, from_unixtime(j.control_last_generated_unix)
control_date_last_generated
, j.control_proto_name
, j.control_name
, j.control_code
from HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join jsontable
( 'infected_people_total.values[*]'
passing htp.content_clob
columns control_last_generated_unix int64 path '::ancestor.::ancestor.last_generated'
, control_proto_name varchar2 path '::ancestor.::ancestor.proto_name'
, control_name varchar2 path '::ancestor.::ancestor.name'
, control_code varchar2 path '::ancestor.::ancestor.code'
, date_of_report_unix int64 path 'date_of_report_unix'
, infected_daily_total number path 'infected_daily_total'
, date_of_insertion_unix int64 path 'date_of_insertion_unix'
) j
order
by control_date_last_generated
, date_report
SQL Query Besmettingen (genormaliseerd, verschil)
De genormaliseerde delta verwerk je met:
--
-- Infected people delta normalized.
--
create or replace table nl_infected_people_delta_normalized@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
date_report
, j.infected_daily_increase
--
, from_unixtime(j.date_of_insertion_unix)
date_insertion
, from_unixtime(j.control_last_generated_unix)
control_date_last_generated
, j.control_proto_name
, j.control_name
, j.control_code
from HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join jsontable
( 'infected_people_delta_normalized.values[*]'
passing htp.content_clob
columns control_last_generated_unix int64 path '::ancestor.::ancestor.last_generated'
, control_proto_name varchar2 path '::ancestor.::ancestor.proto_name'
, control_name varchar2 path '::ancestor.::ancestor.name'
, control_code varchar2 path '::ancestor.::ancestor.code'
, date_of_report_unix int64 path 'date_of_report_unix'
, infected_daily_increase number path 'infected_daily_increase'
, date_of_insertion_unix int64 path 'date_of_insertion_unix'
) j
order
by control_date_last_generated
, date_report
SQL Query Covid19-instroom per Leeftijdsgroep
De instroom per leeftijdsgroep vind je met:
--
-- Intake share age groups.
--
create or replace table nl_intake_share_age_groups@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
date_report
, j.agegroup
, j.infected_per_agegroup_increase
--
, from_unixtime(j.date_of_insertion_unix)
date_insertion
, from_unixtime(j.control_last_generated_unix)
control_date_last_generated
, j.control_proto_name
, j.control_name
, j.control_code
from HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join jsontable
( 'intake_share_age_groups.values[*]'
passing htp.content_clob
columns control_last_generated_unix int64 path '::ancestor.::ancestor.last_generated'
, control_proto_name varchar2 path '::ancestor.::ancestor.proto_name'
, control_name varchar2 path '::ancestor.::ancestor.name'
, control_code varchar2 path '::ancestor.::ancestor.code'
, date_of_report_unix int64 path 'date_of_report_unix'
, agegroup varchar2 path 'agegroup'
, infected_per_agegroup_increase number path 'infected_per_agegroup_increase'
, date_of_insertion_unix int64 path 'date_of_insertion_unix'
) j
order
by control_date_last_generated
, date_report
SQL Query Covid19-reproductiegetal
Een ander versie van de reproductiegetallen stop je in een tabel met het volgende SQL-statement:
--
-- Reproduction index.
--
create or replace table nl_reproduction_indexes@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
date_report
, j.reproduction_index_low
, j.reproduction_index_avg
, j.reproduction_index_high
--
, from_unixtime(j.date_of_insertion_unix)
date_insertion
, from_unixtime(j.control_last_generated_unix)
control_date_last_generated
, j.control_proto_name
, j.control_name
, j.control_code
from HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join jsontable
( 'reproduction_index.values[*]'
passing htp.content_clob
columns control_last_generated_unix int64 path '::ancestor.::ancestor.last_generated'
, control_proto_name varchar2 path '::ancestor.::ancestor.proto_name'
, control_name varchar2 path '::ancestor.::ancestor.name'
, control_code varchar2 path '::ancestor.::ancestor.code'
, date_of_report_unix int64 path 'date_of_report_unix'
, reproduction_index_low number path 'reproduction_index_low'
, reproduction_index_avg number path 'reproduction_index_avg'
, reproduction_index_high number path 'reproduction_index_high'
, date_of_insertion_unix int64 path 'date_of_insertion_unix'
) j
order
by control_date_last_generated
, date_report
SQL Query Gemiddelde R-waardes
De uitgemiddelde R-waardes maak je met de volgende Invantive SQL query:
--
-- Reproduction index last known average.
--
create or replace table nl_reproduction_indexes_last_known_avg@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
date_report
, j.reproduction_index_low
, j.reproduction_index_avg
, j.reproduction_index_high
--
, from_unixtime(j.date_of_insertion_unix)
date_insertion
, from_unixtime(j.control_last_generated_unix)
control_date_last_generated
, j.control_proto_name
, j.control_name
, j.control_code
from HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join jsontable
( 'reproduction_index_last_known_average.values[*]'
passing htp.content_clob
columns control_last_generated_unix int64 path '::ancestor.::ancestor.last_generated'
, control_proto_name varchar2 path '::ancestor.::ancestor.proto_name'
, control_name varchar2 path '::ancestor.::ancestor.name'
, control_code varchar2 path '::ancestor.::ancestor.code'
, date_of_report_unix int64 path 'date_of_report_unix'
, reproduction_index_low number path 'reproduction_index_low'
, reproduction_index_avg number path 'reproduction_index_avg'
, reproduction_index_high number path 'reproduction_index_high'
, date_of_insertion_unix int64 path 'date_of_insertion_unix'
) j
order
by control_date_last_generated
, date_report