Medicare-Daten

So werden öffentliche Datasets mit BigQuery abgefragt

BigQuery ist eine vollständig verwaltete Data Warehouse- und Analyseplattform. Öffentliche Datasets lassen sich mit SQL-Abfragen analysieren. Sie können auf öffentliche BigQuery-Datasets über die Webbenutzeroberfläche, das Befehlszeilentool oder durch Aufrufe an die BigQuery REST API mit vielen Clientbibliotheken wie Java, .NET oder Python zugreifen.

Derzeit werden öffentliche BigQuery-Datasets am Standort US gespeichert, der mehrere Regionen umfasst. Wenn Sie ein öffentliches Dataset abfragen, geben Sie in der Befehlszeile das Flag --location=US an, wählen in der BigQuery-Webbenutzeroberfläche US als Verarbeitungsort aus oder geben bei Verwendung der API im Abschnitt jobReference der Jobressource die Property location an. Da die öffentlichen Datasets in den USA gespeichert werden, können Sie Abfrageergebnisse für öffentliche Daten nicht in eine Tabelle in einer anderen Region schreiben und Tabellen in öffentlichen Datasets nicht mit Tabellen in einer anderen Region zusammenführen.

Um ein öffentliches BigQuery-Dataset verwenden zu können, müssen Sie zuerst ein Projekt erstellen oder auswählen. Das erste pro Monat verarbeitete Terabyte Daten ist kostenlos, sodass Sie mit der Abfrage von öffentlichen Datasets beginnen können, ohne die Abrechnung zu aktivieren. Wenn Sie jedoch beabsichtigen, das kostenlose Kontingent zu überschreiten, sollten Sie die Abrechnung aktivieren.

  1. Melden Sie sich in Ihrem Google-Konto an.

    Wenn Sie noch kein Konto haben, registrieren Sie sich hier für ein neues Konto.

  2. Wählen Sie ein GCP-Projekt aus oder erstellen Sie eines.

    Zur Seite "Ressourcen verwalten"

  3. Die Abrechnung für Ihr Projekt muss aktiviert sein.

    Informationen zum Aktivieren von Abrechnungen

  4. BigQuery ist in neuen Projekten automatisch aktiviert. So aktivieren Sie BigQuery in einem bereits vorhandenen Projekt: Aktivieren Sie die BigQuery erforderliche API.

    Aktivieren Sie die API.

Dataset-Übersicht

Dieses öffentliche Dataset wurde von den "Centers for Medicare & Medicaid Services" erzeugt. Die Daten fassen die Anwendung und Zahlungen von Untersuchungen, Dienstleistungen und verschreibungspflichtigen Medikamenten zusammen, die Medicare-Begünstigte von bestimmten stationären und ambulanten Kliniken, Ärzten und anderen Anbietern erhalten haben. Das Dataset enthält die folgenden Daten:

  • Häufige stationäre und ambulante Dienstleistungen
  • Alle Untersuchungen und Dienstleistungen von Ärzten und anderen Anbietern
  • Alle Verschreibungen vom Typ "Part D".

Die Anbieter bestimmen, welche Gebühren sie für die Positionen, Dienstleistungen und Untersuchungen, die sie den Patienten bieten, erheben. Diese Gebühren entsprechen dem Betrag, den die Anbieter für die Position, Dienstleistung oder Untersuchung in Rechnung stellen.

Sie können sich diese Daten in der BigQuery-Konsole genauer ansehen:

Zum Medicare Dataset wechseln

Beispielabfragen

Im Folgenden finden Sie einige Beispiele für SQL-Abfragen, die Sie für diese Daten in BigQuery ausführen können.

In diesen Beispielen wird die Unterstützung von Standard-SQL in BigQuery genutzt. Verwenden Sie das Tag #standardSQL, damit BigQuery erkennt, dass Sie Standard-SQL verwenden möchten. Weitere Informationen zum Präfix #standardSQL finden Sie unter Abfragepräfix festlegen.

Wie viele Medikamente wurden in den einzelnen US-Bundesstaaten insgesamt verschrieben?

Die folgende Abfrage berechnet die Gesamtanzahl an Anforderungen, die in jedem Staat gestellt wurden. Die Ausgabetabelle zeigt die Gesamtanzahl an Anforderungen in den fünf Staaten mit der höchsten Verschreibungsquote.

Web-UI

Folgende Abfrage in der Webbenutzeroberfläche öffnen

#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;

Befehlszeile

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;'

Die Ergebnisse werden hier angezeigt:

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

Welches Medikament wurde in den einzelnen Staaten am häufigsten verschrieben?

Diese Abfrage nutzt die vorherige Abfrage, um für jeden Staat das Medikament herauszufinden, dessen Anforderungszahl am höchsten ist.

Web-UI

Folgende Abfrage in der Webbenutzeroberfläche öffnen

#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;

Befehlszeile

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;'

Die Ausgabe zeigt die Top-Fünf-Staaten zusammen mit dem Namen des Medikaments, den Gesamtkosten und der Gesamtanzahl an Anforderungen.

+-------+---------------------------+-------------------+--------------+---------------------+
| 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 |
+-------+---------------------------+-------------------+--------------+---------------------+

Geschichtete zufällige Stichproben des Datasets

Die folgende Abfrage zeigt, wie ein Dataset in Schichten aufgeteilt werden kann, um dann aus jeder Schicht eine zufällige Stichprobe zu entnehmen. In diesem Fall definiert das übergreifende Produkt aus provider_state und provider_city die Liste der Schichten. Nach der Sortierung anhand einer zufälligen Zahl wird jeder Schicht eine RANK()-Auftragsnummer zugewiesen. Die Anzahl an Stichproben pro Schicht kann durch Bearbeiten des folgenden Filters angegeben werden: WHERE samples_per_strata <= 10.

Web-UI

Folgende Abfrage in der Webbenutzeroberfläche öffnen

#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;

Befehlszeile

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;'

Die Ergebnisse werden hier angezeigt:

+----------------+---------------+-----------+----------+----------+--------------------+
| 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 |
+----------------+---------------+-----------+----------+----------+--------------------+

Wie hoch sind die Kosten für eine stationäre und ambulante Behandlung im Durchschnitt pro Stadt und und Bundesstaat?

Um die durchschnittlichen Kosten für die stationäre und ambulante Behandlung pro Anbieter für die einzelnen Städte und Staaten im Jahr 2014 herauszufinden, werden zwei Tabellen benötigt: die Tabelle für stationäre Patienten im Jahr 2014 (inpatient_charges_2014) und die Tabelle für ambulante Patienten im Jahr 2014 (outpatient_charges_2014).

Die Abfrage besteht aus den folgenden Schritten.

  1. Aggregieren der Kosten in den einzelnen Tabellen anhand von provider_id. Dies geschieht unter Verwendung des gewichteten Durchschnitts der Kosten der einzelnen Behandlungen, die vom Anbieter durchgeführt wurden.

  2. Zusammenführen von provider_id, provider_city und provider_state.

  3. Sortieren anhand der zusammengeführten durchschnittlichen Kosten von stationären und ambulanten Behandlungen.

Web-UI

Folgende Abfrage in der Webbenutzeroberfläche öffnen

#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;

Befehlszeile

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;'

Die Ergebnisse werden hier angezeigt:

+-------+---------------+-------------+-----------------+-----------------+-----------------------+
| 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 |
+-------+---------------+-------------+-----------------+-----------------+-----------------------+

Ranking der Bedingungen und Vergleich der durchschnittlichen Zahlungen pro Stadt

Mit dieser Abfrage sollen die folgenden Fragen beantwortet werden:

  • Welche stationären Diagnosebedingungen treten in den USA am häufigsten auf?

  • In welchen Städten ist die größte Anzahl an Fällen der einzelnen Diagnosebedingungen aufgetreten?

  • Wie hoch sind die Zahlungen für diese Bedingungen in diesen Städten im Durchschnitt und wie schneiden sie im Vergleich mit dem nationalen Durchschnitt ab?

Web-UI

Folgende Abfrage in der Webbenutzeroberfläche öffnen

#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

Befehlszeile

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;'

Dies sind die Ergebnisse:

+--------------------------------------------------------------------------+-----------+-------+-----------+-----------------------+-------------------------------+
|                                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 %                         |
+--------------------------------------------------------------------------+-----------+-------+-----------+-----------------------+-------------------------------+

Über die Daten

Dataset-Quelle: Centers for Medicare and Medicaid Services

Kategorie: Gesundheitswesen

Verwendung: Dieses Dataset ist öffentlich verfügbar und kann unter den folgenden, von der Dataset-Quelle bereitgestellten Bedingungen verwendet werden: http://www.data.gov/privacy-policy#data_policy. Die Angaben in diesem Dataset werden ohne Gewähr auf Richtigkeit und Vollständigkeit durch Google zur Verfügung gestellt. Google lehnt jegliche Haftung für direkte oder indirekte Schäden ab, die aus der Verwendung des Datasets resultieren.

Aktualisierungsfrequenz: Jährlich

In BigQuery ansehen: Medicare-Dataset aufrufen

Hat Ihnen diese Seite weitergeholfen? Teilen Sie uns Ihr Feedback mit:

Feedback geben zu...