This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Examples: | |
# to_decimal("83", 12) : 44 | |
# to_decimal("01101", 2) : 22 | |
to_decimal <- function(num, base) { | |
digits <- strsplit(num, "")[[1]] | |
value <- 0 | |
sequence <- seq_along(digits) |
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=594) (actual time=14.645..14.648 rows=40 loops=1) | |
Task Count: 32 | |
Tuple data received from nodes: 2240 bytes | |
Tasks Shown: One of 32 | |
-> Task | |
Tuple data received from node: 53 bytes | |
Node: host=private-w6.[REDACTED] port=5432 dbname=citus | |
-> Nested Loop (cost=0.71..196.77 rows=1 width=94) (actual time=0.120..2.282 rows=1 loops=1) | |
Buffers: shared hit=190 | |
-> Nested Loop (cost=0.43..196.31 rows=1 width=162) (actual time=0.090..2.251 rows=1 loops=1) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
'area-' || release_date::TEXT || '-' || area_id::TEXT AS key, | |
JSONB_AGG( | |
JSONB_BUILD_OBJECT( | |
'area_code', area_code, | |
'area_type', area_type, | |
'area_name', area_name, | |
'date', to_char(date::DATE, 'YYYY-MM-DD'), | |
'metric', metric, | |
'value', value, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
[ | |
{ | |
"age": "0_to_5", | |
"rate": 175.2, | |
"value": 868 | |
}, | |
{ | |
"age": "65_to_84", | |
"rate": 2802.1, | |
"value": 32250 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
'area-' || release_date::TEXT || '-' || area_id::TEXT AS key, | |
JSONB_AGG( | |
JSONB_BUILD_OBJECT( | |
'area_code', area_code, | |
'area_type', area_type, | |
'area_name', area_name, | |
'date', to_char(date::DATE, 'YYYY-MM-DD'), | |
'metric', metric, | |
'value', value, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
area_type | area_code | area_name | |
---|---|---|---|
ltla | E06000001 | Hartlepool | |
utla | E06000001 | Hartlepool | |
ltla | E06000002 | Middlesbrough | |
utla | E06000002 | Middlesbrough | |
ltla | E06000003 | Redcar and Cleveland | |
utla | E06000003 | Redcar and Cleveland | |
ltla | E06000004 | Stockton-on-Tees | |
utla | E06000004 | Stockton-on-Tees | |
ltla | E06000005 | Darlington |
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
library(dplyr) | |
library(tidyr) | |
data <- read.csv("https://api.coronavirus.data.gov.uk/v2/data?areaType=ltla&metric=newCasesBySpecimenDateAgeDemographics&format=csv") | |
# Add the age bands you want to exclude | |
excluded_age_bands <- c("unassigned", "60+", "00_59") | |
# Add area codes you want to include - leave blank for everything. | |
included_area_codes <- c() |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH | |
location AS ( | |
SELECT id, ref.area_type, area_code, area_name, postcode, priority | |
FROM covid19.postcode_lookup | |
JOIN covid19.area_reference AS ref ON ref.id = area_id | |
JOIN covid19.area_priorities AS ap ON ref.area_type = ap.area_type | |
WHERE UPPER(REPLACE(postcode, ' ', '')) = $2 | |
), | |
metrics AS ( | |
SELECT id, metric |
NewerOlder