JSON to Insights: Analyzing US healthcare Data.

"Nada é certo, exceto a morte e os impostos."

Esse não é um conjunto de dados criado com um canteiro de rosas ou grama verde bem cuidada. Um pouco mais sério. Vamos ver se podemos aprender algo rapidamente aqui. O conjunto de dados é o seguinte.

"name" : "NCHS - Principais causas de morte: Estados Unidos",
"attribution" : "National Center for Health Statistics" (Centro Nacional de Estatísticas de Saúde),

O público dataset está disponível em https://data.cdc.gov/api/views/bi63-dtpu/rows.json?accessType=DOWNLOAD

Etapa 1: Faça o download do arquivo em um arquivo local (por exemplo, health.json). Carregue esse arquivo em um dos nós do cluster do Couchbase.

Etapa 2Importar os dados para um compartimento chamado causa. Depois de criar o bucket, crie o índice primário. Você precisará dele para fazer consultas.

/opt/couchbase/bin/cbimport json -c couchbase://127.0.0.1 -u Administrator -p password -b cause -d file://health.json -g cause:0 -f sample

> CREATE PRIMARY INDEX ON cause;

Etapa 3. Inspecione a estrutura dos dados.

Todos os dados são fornecidos em um ÚNICO documento JSON. Por esse motivo, o INFER não ajuda. Você terá que inspecionar e entender a estrutura manualmente. Esses dados são um conjunto de dados típico do governo, com muitos dados em matrizes simples, com o significado de cada entidade fornecido nos metadados.

Matriz simples:

<strong>select data from cause ;</strong>

1	<forte>selecionar dados de causa ;</forte>

Ele simplesmente contém uma matriz de dados sem o esquema. Para os conjuntos de dados públicos, o esquema está no campo meta.

Vamos transformar a estrutura em pares simples de valores-chave JSON para que possamos lidar com esses bits de forma mais eficaz. Você pode saber mais sobre como essa mágica aconteceu neste artigo.



WITH cs AS (
  SELECT
    meta.`view`.columns [*].fieldName f,
    data
  FROM
    cause
)
SELECT
  o
FROM
  cs UNNEST cs.data AS d1 
LET o = OBJECT p :d1 [ARRAY_POSITION(cs.f, p)] FOR p IN cs.f END;

COM cs AS (

SELECIONAR

meta.`visualização`.colunas [*].fieldName f,

dados

causa

)

SELECIONAR

cs INÚTIL cs.dados AS d1

LET o = OBJETO p :d1 [ARRAY_POSITION(cs.f, p)] PARA p IN cs.f FIM;

Tarefa 1: Descubra a causa da maioria das mortes em um estado, por ano.

A expressão de tabela comum (CTE) na cláusula WITH (csdata) transforma os dados json complexos em JSON simples. Você pode fazer isso dinamicamente ou fazer isso uma vez e INSERTAR de volta em um bucket, como discuti no artigo sobre Nomes de bebês de Nova York. Neste artigo, eu uso CTEs.

WITH csdata as (
  WITH cs AS (
    SELECT
      meta.`view`.columns [*].fieldName f,
      data
    FROM
      cause
  )
  SELECT
    o
  FROM
    cs UNNEST cs.data AS d1 LET o = OBJECT p :d1 [ARRAY_POSITION(cs.f, p)] FOR p IN cs.f END
)
SELECT
  c.o.state,
  c.o.year,
  c.o.cause_name,
  COUNT(c.o.cause_name),
  SUM(TONUMBER(c.o.deaths)) totdeaths
FROM
  csdata as c
WHERE
  c.o.state &lt;&gt; "United States"
  and c.o.cause_name &lt;&gt; "All causes"
GROUP BY
  c.o.state,
  c.o.year,
  c.o.cause_name
ORDER BY
  totdeaths DESC,
  c.o.state,
  c.o.year

COM csdata como (

COM cs AS (

SELECIONAR

meta.`visualização`.colunas [*].fieldName f,

dados

causa

)

SELECIONAR

cs INÚTIL cs.dados AS d1 LET o = OBJETO p :d1 [ARRAY_POSITION(cs.f, p)] PARA p IN cs.f FIM

)

SELECIONAR

c.o.estado,

c.o.ano,

c.o.nome_da_causa,

CONTAGEM(c.o.nome_da_causa),

SUM(TONUMBER(c.o.mortes)) mortes totais

csdata como c

ONDE

c.o.estado <> "Estados Unidos"

e c.o.nome_da_causa <> "Todas as causas"

GRUPO BY

c.o.estado,

c.o.ano,

c.o.nome_da_causa

ORDEM BY

mortes totais DESC,

c.o.estado,

c.o.ano

Nesse caso, todas as mortes na Califórnia estão no topo, principalmente devido à sua população.

Tarefa 2. Descubra as principais causas de morte em cada estado no ano de 2016.

Consulta 2: Use o conjunto de resultados da consulta anterior e, em seguida, use a função de janela FIRST_VALUE() para determinar a causa principal. O particionamento por estado (na cláusula OVER BY) lhe dará as partições por estado e ORDER BY dx.totdeaths dentro da cláusula OVER BY lhe dará a causa principal em cada estado.

WITH csdata as (
  WITH cs AS (
    SELECT
      meta.`view`.columns [*].fieldName f,
      data
    FROM
      cause
  )
  SELECT
    o
  FROM
    cs UNNEST cs.data AS d1 LET o = OBJECT p :d1 [ARRAY_POSITION(cs.f, p)] FOR p IN cs.f END
),
d2 as(
SELECT
  c.o.state,
  c.o.year,
  c.o.cause_name,
  SUM(TONUMBER(c.o.deaths)) totdeaths
FROM
  csdata as c
WHERE
  c.o.state &lt;&gt; "United States"
  and c.o.cause_name &lt;&gt; "All causes"
  and c.o.year = "2016"
GROUP BY
  c.o.state,
  c.o.year,
  c.o.cause_name),
d3 as (
SELECT dx.state, dx.cause_name, dx.totdeaths,
  FIRST_VALUE(dx.cause_name) OVER(PARTITION BY dx.state ORDER BY dx.totdeaths DESC) topreason,
  FIRST_VALUE(dx.totdeaths) OVER(PARTITION BY dx.state ORDER BY dx.totdeaths DESC) topcount
FROM d2 dx)
SELECT d3
FROM d3
WHERE d3.topcount = d3.totdeaths
order by d3.state

COM csdata como (

COM cs AS (

SELECIONAR

meta.`visualização`.colunas [*].fieldName f,

dados

causa

)

SELECIONAR

cs INÚTIL cs.dados AS d1 LET o = OBJETO p :d1 [ARRAY_POSITION(cs.f, p)] PARA p IN cs.f FIM

d2 como(

SELECIONAR

c.o.estado,

c.o.ano,

c.o.nome_da_causa,

SUM(TONUMBER(c.o.mortes)) mortes totais

csdata como c

ONDE

c.o.estado <> "Estados Unidos"

e c.o.nome_da_causa <> "Todas as causas"

e c.o.ano = "2016"

GRUPO BY

c.o.estado,

c.o.ano,

c.o.nome_da_causa),

d3 como (

SELECIONAR dx.estado, dx.nome_da_causa, dx.mortes totais,

FIRST_VALUE(dx.nome_da_causa) SOBRE(PARTIÇÃO BY dx.estado ORDEM BY dx.mortes totais DESC) razão,

FIRST_VALUE(dx.mortes totais) SOBRE(PARTIÇÃO BY dx.estado ORDEM BY dx.mortes totais DESC) contagem máxima

DE d2 dx)

SELECIONAR d3

DE d3

ONDE d3.contagem máxima = d3.mortes totais

ordem por d3.estado

Tarefa 3. Descubra como o principal motivo mudou ao longo do ano, de 1999 a 2016, por estado.

Consulta 3: Basta gerar o relatório para todos os anos (199-2016) e, em seguida, determinar o motivo principal e, finalmente, obter o motivo mais alto agrupando por estado, ano e obtendo MAX(topcount) para a causa do motivo principal.

WITH csdata as (
  WITH cs AS (
    SELECT
      meta.`view`.columns [*].fieldName f,
      data
    FROM
      cause
  )
  SELECT
    o
  FROM
    cs UNNEST cs.data AS d1 LET o = OBJECT p :d1 [ARRAY_POSITION(cs.f, p)] FOR p IN cs.f END
),
d2 as(
SELECT
  c.o.state,
  c.o.year,
  c.o.cause_name,
  SUM(TONUMBER(c.o.deaths)) totdeaths
FROM
  csdata as c
WHERE
  c.o.state &lt;&gt; "United States" 
  and c.o.cause_name &lt;&gt; "All causes"
GROUP BY
  c.o.state,
  c.o.year,
  c.o.cause_name),
d3 as (
SELECT dx.state, dx.year,
  FIRST_VALUE(dx.cause_name) OVER(PARTITION BY dx.state, dx.year ORDER BY dx.totdeaths DESC ) topreason,
  FIRST_VALUE(dx.totdeaths) OVER(PARTITION BY dx.state, dx.year ORDER BY dx.totdeaths DESC) topcount
FROM d2 dx)
SELECT d3.state , d3.year , d3.topreason, max(d3.topcount) topcount
FROM d3
GROUP BY d3.state, d3.year, d3.topreason
order by d3.state, d3.year

COM csdata como (

COM cs AS (

SELECIONAR

meta.`visualização`.colunas [*].fieldName f,

dados

causa

)

SELECIONAR

cs INÚTIL cs.dados AS d1 LET o = OBJETO p :d1 [ARRAY_POSITION(cs.f, p)] PARA p IN cs.f FIM

d2 como(

SELECIONAR

c.o.estado,

c.o.ano,

c.o.nome_da_causa,

SUM(TONUMBER(c.o.mortes)) mortes totais

csdata como c

ONDE

c.o.estado <> "Estados Unidos"

e c.o.nome_da_causa <> "Todas as causas"

GRUPO BY

c.o.estado,

c.o.ano,

c.o.nome_da_causa),

d3 como (

SELECIONAR dx.estado, dx.ano,

FIRST_VALUE(dx.nome_da_causa) SOBRE(PARTIÇÃO BY dx.estado, dx.ano ORDEM BY dx.mortes totais DESC ) razão,

FIRST_VALUE(dx.mortes totais) SOBRE(PARTIÇÃO BY dx.estado, dx.ano ORDEM BY dx.mortes totais DESC) contagem máxima

DE d2 dx)

SELECIONAR d3.estado , d3.ano , d3.razão, máximo(d3.contagem máxima) contagem máxima

DE d3

GRUPO BY d3.estado, d3.ano, d3.razão

ordem por d3.estado, d3.ano

Aqui está o resultado parcial.

A visualização disso nos dá o seguinte histograma.

Keshav Murthy

Compartilhe este artigo

Platform

Self-Managed

Services

Capabilities

Why Couchbase?

Migrate to Capella

By Use Case

By Industry

By Application Need

Popular Docs

By Developer Role

Quickstart

Resource Center

About

Partnerships

Our Services

Partners: Register a Deal

Ready to register a deal with Couchbase?

Marriott

JSON para Insights: Analisando dados de saúde dos EUA.

Tarefa 1: Descubra a causa da maioria das mortes em um estado, por ano.

Receba atualizações do blog do Couchbase em sua caixa de entrada

Autor

Postado por Keshav Murthy

Deixe um comentário Cancelar resposta

Pronto para começar a usar o Couchbase Capella?

Iniciar a construção

Use o Capella gratuitamente

Entre em contato