Medicare 데이터

BigQuery를 사용하여 공개 데이터세트를 쿼리하는 방법

BigQuery는 완전 관리형 데이터 웨어하우스이자 분석 플랫폼입니다. SQL 쿼리로 분석할 수 있는 공개 데이터세트가 제공됩니다. 웹 UI 또는 명령줄 도구를 사용하거나 자바, .NET, Python과 같은 다양한 클라이언트 라이브러리BigQuery REST API를 호출하여 BigQuery 공개 데이터 세트에 액세스할 수 있습니다.

현재 BigQuery 공개 데이터세트는 US 다중 지역 위치에 저장됩니다. 공개 데이터세트를 쿼리할 때는 명령줄에 --location=US 플래그를 제공하거나, BigQuery 웹 UI에서 처리 위치로 US를 선택하거나, API를 사용할 때 job 리소스jobReference 섹션에서 location 속성을 지정합니다. 공개 데이터세트는 미국에 저장되므로 공개 데이터 쿼리 결과를 다른 지역의 테이블에 쓸 수 없으며, 공개 데이터세트의 테이블을 다른 지역의 테이블과 조인할 수도 없습니다.

BigQuery 공개 데이터세트를 사용하려면 먼저 프로젝트를 만들거나 선택해야 합니다. 매월 처리되는 데이터의 1TB까지는 무료이므로 결제를 사용 설정하지 않고 공개 데이터세트 쿼리를 시작할 수 있습니다. 데이터 처리량이 무료 등급을 초과하면 결제 기능도 사용 설정해야 합니다.

  1. Google 계정에 로그인합니다.

    아직 계정이 없으면 새 계정을 등록하세요.

  2. Google Cloud Platform 프로젝트를 선택하거나 만듭니다.

    리소스 관리 페이지로 이동

  3. Google Cloud Platform 프로젝트에 결제가 사용 설정되어 있는지 확인하세요.

    결제 사용 설정 방법 알아보기

  4. 새 프로젝트에서는 BigQuery가 자동으로 사용 설정됩니다. 기존 프로젝트에서 BigQuery를 활성화하는 방법은 다음과 같습니다. 필요한 BigQuery API를 사용 설정합니다.

    API사용 설정

데이터세트 개요

이 공개 데이터세트는 미국 의료보험제도·국민의료보장제도센터(Centers for Medicare & Medicaid Services)에서 만들었습니다. 이 데이터는 특수 입원 환자, 외래 환자, 의사, 기타 공급업체에 의해 Medicare 보험 수혜자에게 제공된 절차, 서비스, 처방약의 사용률 및 지불에 대해 요약하고 있습니다. 데이터세트에는 다음 데이터가 포함됩니다.

  • 일반적인 입원 및 외래 환자 서비스
  • 모든 의사 및 기타 공급업체 절차와 서비스
  • 모든 파트 D 처방전

제공업체는 환자에게 제공되는 품목, 서비스 및 절차에 대해 청구할 항목을 결정하며, 이러한 청구는 품목, 서비스, 절차에 대해 제공업체가 청구하는 금액이 됩니다.

BigQuery 콘솔에서 이 데이터를 탐색할 수 있습니다.

Medicare 데이터세트로 이동

샘플 쿼리

다음은 BigQuery에서 이 데이터를 대상으로 실행할 수 있는 SQL 쿼리의 몇 가지 예입니다.

이 샘플에서는 BigQuery의 표준 SQL 지원을 사용합니다. #standardSQL 태그를 사용해 표준 SQL 사용 의사를 BigQuery에 알립니다. #standardSQL 프리픽스에 대한 자세한 내용은 쿼리 프리픽스 설정을 참조하세요.

각 주에서 처방된 총 약물 수는 얼마인가요?

다음 쿼리는 각 주에서 접수된 총 신청 건수를 계산합니다. 상위 5개 주의 총 신청 건수가 출력 테이블에 표시됩니다.

웹 UI

웹 UI에서 다음 쿼리 열기

#standardSQL
SELECT
  nppes_provider_state AS state,
  ROUND(SUM(total_claim_count) / 1e6) AS total_claim_count_millions
FROM
  `bigquery-public-data.medicare.part_d_prescriber_2014`
GROUP BY
  state
ORDER BY
  total_claim_count_millions DESC
LIMIT
  5;

명령줄

bq query --use_legacy_sql=false '
SELECT
  nppes_provider_state AS state,
  ROUND(SUM(total_claim_count) / 1e6) AS total_claim_count_millions
FROM
  `bigquery-public-data.medicare.part_d_prescriber_2014`
GROUP BY
  state
ORDER BY
  total_claim_count_millions DESC
LIMIT
  5;'

결과는 다음과 같이 표시됩니다.

+-------+----------------------------+
| state | total_claim_count_millions |
+-------+----------------------------+
|  CA   | 116.0                      |
|  FL   | 91.0                       |
|  NY   | 80.0                       |
|  TX   | 76.0                       |
|  PA   | 63.0                       |
+-------+----------------------------+

각 주에서 가장 많이 처방된 약물은 무엇인가요?

이 쿼리는 이전 쿼리를 토대로 각 주에서 가장 많이 신청한 약물을 찾습니다.

웹 UI

웹 UI에서 다음 쿼리 열기

#standardSQL
SELECT
  A.state,
  drug_name,
  total_claim_count,
  day_supply,
  ROUND(total_cost_millions) AS total_cost_millions
FROM (
  SELECT
    generic_name AS drug_name,
    nppes_provider_state AS state,
    ROUND(SUM(total_claim_count)) AS total_claim_count,
    ROUND(SUM(total_day_supply)) AS day_supply,
    ROUND(SUM(total_drug_cost)) / 1e6 AS total_cost_millions
  FROM
    `bigquery-public-data.medicare.part_d_prescriber_2014`
  GROUP BY
    state,
    drug_name) A
INNER JOIN (
  SELECT
    state,
    MAX(total_claim_count) AS max_total_claim_count
  FROM (
    SELECT
      nppes_provider_state AS state,
      ROUND(SUM(total_claim_count)) AS total_claim_count
    FROM
      `bigquery-public-data.medicare.part_d_prescriber_2014`
    GROUP BY
      state,
      generic_name)
  GROUP BY
    state) B
ON
  A.state = B.state
  AND A.total_claim_count = B.max_total_claim_count
ORDER BY
  A.total_claim_count DESC
LIMIT
  5;

명령줄

bq query --use_legacy_sql=false '
SELECT
  A.state,
  drug_name,
  total_claim_count,
  day_supply,
  ROUND(total_cost_millions) AS total_cost_millions
FROM (
  SELECT
    generic_name AS drug_name,
    nppes_provider_state AS state,
    ROUND(SUM(total_claim_count)) AS total_claim_count,
    ROUND(SUM(total_day_supply)) AS day_supply,
    ROUND(SUM(total_drug_cost)) / 1e6 AS total_cost_millions
  FROM
    `bigquery-public-data.medicare.part_d_prescriber_2014`
  GROUP BY
    state,
    drug_name) A
INNER JOIN (
  SELECT
    state,
    MAX(total_claim_count) AS max_total_claim_count
  FROM (
    SELECT
      nppes_provider_state AS state,
      ROUND(SUM(total_claim_count)) AS total_claim_count
    FROM
      `bigquery-public-data.medicare.part_d_prescriber_2014`
    GROUP BY
      state,
      generic_name)
  GROUP BY
    state) B
ON
  A.state = B.state
  AND A.total_claim_count = B.max_total_claim_count
ORDER BY
  A.total_claim_count DESC
LIMIT
  5;'

상위 5개 주가 약물 이름, 총 비용 및 총 신청 건수와 함께 출력됩니다.

+-------+---------------------------+-------------------+--------------+---------------------+
| state |         drug_name         | total_claim_count |  day_supply  | total_cost_millions |
+-------+---------------------------+-------------------+--------------+---------------------+
| CA    | LEVOTHYROXINE SODIUM      |         3845087.0 |  2.1174738E8 |                78.0 |
| FL    | LEVOTHYROXINE SODIUM      |         2982612.0 |  1.6338837E8 |                64.0 |
| TX    | HYDROCODONE/ACETAMINOPHEN |         2834059.0 |  6.0410516E7 |                63.0 |
| NY    | AMLODIPINE BESYLATE       |         2609896.0 | 1.23226481E8 |                21.0 |
| PA    | LEVOTHYROXINE SODIUM      |         2353845.0 | 1.09168537E8 |                44.0 |
+-------+---------------------------+-------------------+--------------+---------------------+

데이터세트의 계층화된 무작위 샘플링

다음 쿼리는 데이터세트를 계층화하고 각 계층에서 무작위 샘플을 추출하는 방법을 보여줍니다. 이 경우에는 provider_stateprovider_city의 교차곱이 계층 목록을 정의합니다. 임의의 숫자를 기준으로 정렬한 후 계층당 RANK() order 번호를 할당합니다. WHERE samples_per_strata <= 10 필터를 편집하여 계층당 샘플 수를 지정할 수 있습니다.

웹 UI

웹 UI에서 다음 쿼리 열기

#standardSQL
SELECT
  *
FROM (
  SELECT
    provider_state,
    provider_city,
    ROUND(average_covered_charges,2),
    ROUND(average_total_payments,2),
    ROUND(average_medicare_payments,2),
    RANK() OVER(PARTITION BY provider_state, provider_city ORDER BY random_number) AS samples_per_strata
  FROM (
    SELECT
      provider_state,
      provider_city,
      average_covered_charges,
      average_total_payments,
      average_medicare_payments,
      RAND() AS random_number
    FROM
     `bigquery-public-data.medicare.inpatient_charges_2014`) )
WHERE
  samples_per_strata <= 10
LIMIT
  5;

명령줄

bq query --use_legacy_sql=false '
SELECT
  *
FROM (
  SELECT
    provider_state,
    provider_city,
    ROUND(average_covered_charges,2),
    ROUND(average_total_payments,2),
    ROUND(average_medicare_payments,2),
    RANK() OVER(PARTITION BY provider_state, provider_city ORDER BY random_number) AS samples_per_strata
  FROM (
    SELECT
      provider_state,
      provider_city,
      average_covered_charges,
      average_total_payments,
      average_medicare_payments,
      RAND() AS random_number
    FROM
     `bigquery-public-data.medicare.inpatient_charges_2014`) )
WHERE
  samples_per_strata <= 10
LIMIT
  5;'

결과는 다음과 같이 표시됩니다.

+----------------+---------------+-----------+----------+----------+--------------------+
| provider_state | provider_city |    f0_    |   f1_    |   f2_    | samples_per_strata |
+----------------+---------------+-----------+----------+----------+--------------------+
| AL             | GADSDEN       | 100478.64 |  7359.86 |  6324.43 |                  3 |
| AL             | GADSDEN       |  57587.79 |  6003.93 |  5309.07 |                  1 |
| AL             | GADSDEN       |  47132.23 |   4039.9 |  2634.94 |                  5 |
| AL             | GADSDEN       |  55738.92 |  6098.92 |  5264.46 |                  2 |
| AL             | GADSDEN       |  582430.0 | 59699.62 | 58705.15 |                  4 |
+----------------+---------------+-----------+----------+----------+--------------------+

각 도시와 주에서 입원 환자와 외래 환자의 평균 치료 비용은 얼마인가요?

2014년 각 도시와 주의 제공업체별 평균 입원 및 외래 환자 비용을 확인하려면 입원 환자 2014년 테이블(inpatient_charges_2014)과 외래 환자 2014년 테이블(outpatient_charges_2014)이 필요합니다.

쿼리에는 다음 단계가 포함됩니다.

  1. provider_id로 각 테이블의 비용을 집계합니다. 이렇게 하려면 업체가 수행한 각 절차의 비용에 대한 가중 평균을 구해야 합니다.

  2. provider_id, provider_city, provider_state를 조인합니다.

  3. 입원 환자와 외래 환자 절차의 합산 평균 비용을 기준으로 정렬합니다.

웹 UI

웹 UI에서 다음 쿼리 열기

#standardSQL
SELECT
  OP.provider_state AS State,
  OP.provider_city AS City,
  OP.provider_id AS Provider_ID,
  ROUND(OP.average_OP_cost) AS Average_OP_Cost,
  ROUND(IP.average_IP_cost) AS Average_IP_Cost,
  ROUND(OP.average_OP_cost + IP.average_IP_cost) AS Combined_Average_Cost
FROM (
  SELECT
    provider_state,
    provider_city,
    provider_id,
    SUM(average_total_payments*outpatient_services)/SUM(outpatient_services) AS average_OP_cost
  FROM
    `bigquery-public-data.medicare.outpatient_charges_2014`
  GROUP BY
    provider_state,
    provider_city,
    provider_id ) AS OP
INNER JOIN (
  SELECT
    provider_state,
    provider_city,
    provider_id,
    SUM(average_medicare_payments*total_discharges)/SUM(total_discharges) AS average_IP_cost
  FROM
    `bigquery-public-data.medicare.inpatient_charges_2014`
  GROUP BY
    provider_state,
    provider_city,
    provider_id ) AS IP
ON
  OP.provider_id = IP.provider_id
  AND OP.provider_state = IP.provider_state
  AND OP.provider_city = IP.provider_city
ORDER BY
  combined_average_cost DESC
LIMIT
  10;

명령줄

bq query --use_legacy_sql=false '
SELECT
  OP.provider_state AS State,
  OP.provider_city AS City,
  OP.provider_id AS Provider_ID,
  ROUND(OP.average_OP_cost) AS Average_OP_Cost,
  ROUND(IP.average_IP_cost) AS Average_IP_Cost,
  ROUND(OP.average_OP_cost + IP.average_IP_cost) AS Combined_Average_Cost
FROM (
  SELECT
    provider_state,
    provider_city,
    provider_id,
    SUM(average_total_payments*outpatient_services)/SUM(outpatient_services) AS average_OP_cost
  FROM
    `bigquery-public-data.medicare.outpatient_charges_2014`
  GROUP BY
    provider_state,
    provider_city,
    provider_id ) AS OP
INNER JOIN (
  SELECT
    provider_state,
    provider_city,
    provider_id,
    SUM(average_medicare_payments*total_discharges)/SUM(total_discharges) AS average_IP_cost
  FROM
    `bigquery-public-data.medicare.inpatient_charges_2014`
  GROUP BY
    provider_state,
    provider_city,
    provider_id ) AS IP
ON
  OP.provider_id = IP.provider_id
  AND OP.provider_state = IP.provider_state
  AND OP.provider_city = IP.provider_city
ORDER BY
  combined_average_cost DESC
LIMIT
  10;'

결과는 다음과 같이 표시됩니다.

+-------+---------------+-------------+-----------------+-----------------+-----------------------+
| State |     City      | Provider_ID | Average_OP_Cost | Average_IP_Cost | Combined_Average_Cost |
+-------+---------------+-------------+-----------------+-----------------+-----------------------+
| IN    | MISHAWAKA     |      150177 |           399.0 |        102521.0 |              102920.0 |
| MI    | WARREN        |      230264 |           104.0 |         88620.0 |               88724.0 |
| TX    | HOUSTON       |      450674 |            88.0 |         67571.0 |               67659.0 |
| TX    | EL PASO       |      450877 |           230.0 |         45179.0 |               45409.0 |
| CO    | VAIL          |       60096 |           261.0 |         38651.0 |               38912.0 |
| TN    | MEMPHIS       |      440152 |            90.0 |         35698.0 |               35788.0 |
| LA    | BATON ROUGE   |      190128 |            87.0 |         34369.0 |               34456.0 |
| LA    | LEESVILLE     |      190297 |           560.0 |         32611.0 |               33172.0 |
| CA    | SAN FRANCISCO |       50454 |           157.0 |         32803.0 |               32959.0 |
| CA    | LOS ANGELES   |       50262 |           120.0 |         31795.0 |               31916.0 |
+-------+---------------+-------------+-----------------+-----------------+-----------------------+

도시별 평균 지불 조건 및 순위 비교

이 쿼리에서는 다음 질문에 대한 답을 찾아보겠습니다.

  • 미국에서 가장 흔한 입원 진단 조건은 무엇인가요?

  • 각 진단 조건에 대해 사례 수가 가장 많은 도시는 어디인가요?

  • 이러한 도시에서 해당 조건에 대한 평균 지불액은 얼마이며 전국 평균과 비교하면 어떤가요?

웹 UI

웹 UI에서 다음 쿼리 열기

#standardSQL
SELECT
  drg_definition AS Diagnosis,
  provider_city AS City,
  provider_state AS State,
  cityrank AS City_Rank,
  CAST(ROUND(citywise_avg_total_payments) AS INT64) AS Citywise_Avg_Payments,
  CONCAT(CAST(ROUND(citywise_avg_total_payments /national_avg_total_payments * 100, 0) AS STRING), " %") AS Avg_Payments_City_vs_National
FROM (
  SELECT
    drg_definition,
    provider_city,
    provider_state,
    cityrank,
    national_num_cases,
    citywise_avg_total_payments,
    national_sum_total_payments,
    (national_sum_total_payments /national_num_cases) AS national_avg_total_payments
  FROM (
    SELECT
      drg_definition,
      provider_city,
      provider_state,
      citywise_avg_total_payments,
      RANK() OVER (PARTITION BY drg_definition ORDER BY citywise_num_cases DESC ) AS cityrank,
      SUM(citywise_num_cases) OVER (PARTITION BY drg_definition ) AS national_num_cases,
      SUM(citywise_sum_total_payments) OVER (PARTITION BY drg_definition ) AS national_sum_total_payments
    FROM (
      SELECT
        drg_definition,
        provider_city,
        provider_state,
        SUM(total_discharges) AS citywise_num_cases,
        SUM(average_total_payments * total_discharges)/ SUM(total_discharges) AS citywise_avg_total_payments,
        SUM(average_total_payments * total_discharges) AS citywise_sum_total_payments
      FROM
        `bigquery-public-data.medicare.inpatient_charges_2014`
      GROUP BY
        drg_definition,
        provider_city,
        provider_state))
  WHERE
    cityrank <=3)  # Limit to top 3 cities for each Diagnosis
ORDER BY
  national_num_cases DESC,
  cityrank
LIMIT
  9;  # Limit Results to the top 3 cities for the top 3 diagnosis

명령줄

bq query --use_legacy_sql=false '
SELECT
  drg_definition AS Diagnosis,
  provider_city AS City,
  provider_state AS State,
  cityrank AS City_Rank,
  CAST(ROUND(citywise_avg_total_payments) AS INT64) AS Citywise_Avg_Payments,
  CONCAT(CAST(ROUND(citywise_avg_total_payments /national_avg_total_payments * 100, 0) AS STRING), " %") AS Avg_Payments_City_vs_National
FROM (
  SELECT
    drg_definition,
    provider_city,
    provider_state,
    cityrank,
    national_num_cases,
    citywise_avg_total_payments,
    national_sum_total_payments,
    (national_sum_total_payments /national_num_cases) AS national_avg_total_payments
  FROM (
    SELECT
      drg_definition,
      provider_city,
      provider_state,
      citywise_avg_total_payments,
      RANK() OVER (PARTITION BY drg_definition ORDER BY citywise_num_cases DESC ) AS cityrank,
      SUM(citywise_num_cases) OVER (PARTITION BY drg_definition ) AS national_num_cases,
      SUM(citywise_sum_total_payments) OVER (PARTITION BY drg_definition ) AS national_sum_total_payments
    FROM (
      SELECT
        drg_definition,
        provider_city,
        provider_state,
        SUM(total_discharges) AS citywise_num_cases,
        SUM(average_total_payments * total_discharges)/ SUM(total_discharges) AS citywise_avg_total_payments,
        SUM(average_total_payments * total_discharges) AS citywise_sum_total_payments
      FROM
        `bigquery-public-data.medicare.inpatient_charges_2014`
      GROUP BY
        drg_definition,
        provider_city,
        provider_state))
  WHERE
    cityrank <=3)  # Limit to top 3 cities for each Diagnosis
ORDER BY
  national_num_cases DESC,
  cityrank
LIMIT # Limit Results to the top 3 cities for the top 3 diagnosis
  9;'

결과는 다음과 같이 표시됩니다.

+--------------------------------------------------------------------------+-----------+-------+-----------+-----------------------+-------------------------------+
|                                Diagnosis                                 |   City    | State | City_Rank | Citywise_Avg_Payments | Avg_Payments_City_vs_National |
+--------------------------------------------------------------------------+-----------+-------+-----------+-----------------------+-------------------------------+
| 470 - MAJOR JOINT REPLACEMENT OR REATTACHMENT OF LOWER EXTREMITY W/O MCC | NEW YORK  | NY    |         1 |                 21656 | 144 %                         |
| 470 - MAJOR JOINT REPLACEMENT OR REATTACHMENT OF LOWER EXTREMITY W/O MCC | BOSTON    | MA    |         2 |                 18324 | 122 %                         |
| 470 - MAJOR JOINT REPLACEMENT OR REATTACHMENT OF LOWER EXTREMITY W/O MCC | BALTIMORE | MD    |         3 |                 23056 | 154 %                         |
| 871 - SEPTICEMIA OR SEVERE SEPSIS W/O MV 96+ HOURS W MCC                 | HOUSTON   | TX    |         1 |                 14592 | 106 %                         |
| 871 - SEPTICEMIA OR SEVERE SEPSIS W/O MV 96+ HOURS W MCC                 | CHICAGO   | IL    |         2 |                 16071 | 117 %                         |
| 871 - SEPTICEMIA OR SEVERE SEPSIS W/O MV 96+ HOURS W MCC                 | BROOKLYN  | NY    |         3 |                 23398 | 170 %                         |
| 291 - HEART FAILURE & SHOCK W MCC                                        | CHICAGO   | IL    |         1 |                 13287 | 122 %                         |
| 291 - HEART FAILURE & SHOCK W MCC                                        | HOUSTON   | TX    |         2 |                 12572 | 116 %                         |
| 291 - HEART FAILURE & SHOCK W MCC                                        | BALTIMORE | MD    |         3 |                 16992 | 157 %                         |
+--------------------------------------------------------------------------+-----------+-------+-----------+-----------------------+-------------------------------+

데이터 정보

데이터세트 출처: 미국 의료보험제도·국민의료보장제도센터

카테고리: 의료

사용: 이 데이터세트는 데이터세트 출처에서 제공하는 약관(http://www.data.gov/privacy-policy#data_policy)을 따르는 모든 사용자에게 공개되며 Google의 어떠한 명시적 또는 묵시적인 보증 없이 '있는 그대로' 제공됩니다. Google에서는 데이터세트 사용으로 인한 직간접적인 손해에 대해 책임을 지지 않습니다.

BigQuery에서 보기: Medicare 데이터세트로 이동

이 페이지가 도움이 되었나요? 평가를 부탁드립니다.

다음에 대한 의견 보내기...

도움이 필요하시나요? 지원 페이지를 방문하세요.