使用复杂筛选的 SQL 查询示例

以下示例演示如何使用具有复杂筛选功能的 Amazon Athena SQL 查询从数据存储中查找 FHIR 数据。 HealthLake

例根据人口统计数据创建筛选标准

在创建患者群组时，确定正确的患者人口统计数据非常重要。此示例查询演示了如何使用 Trino 点符号和json_extract筛选数据存储中的 HealthLake 数据。


SELECT
    id
    , CONCAT(name[1].family, ' ', name[1].given[1]) as name
    , (year(current_date) - year(date(birthdate))) as age
    , gender as gender
    , json_extract(extension[1], '$.valueString') as MothersMaidenName
    , json_extract(extension[2], '$.valueAddress.city') as birthPlace
    , maritalstatus.coding[1].display as maritalstatus
    , address[1].line[1] as addressline
    , address[1].city as city
    , address[1].district as district
    , address[1].state as state
    , address[1].postalcode as postalcode
    , address[1].country as country
    , json_extract(address[1].extension[1], '$.extension[0].valueDecimal') as latitude
    , json_extract(address[1].extension[1], '$.extension[1].valueDecimal') as longitude
    , telecom[1].value as telNumber
    , deceasedboolean as deceasedIndicator
    , deceaseddatetime
FROM database.patient;

使用 Athena 控制台，您可以进一步排序和下载结果。

例为患者及其相关病症创建过滤器

以下示例查询演示了如何查找和排序在 HealthLake 数据存储中发现的患者的所有相关病症。


SELECT
	patient.id as patientId
    , condition.id  as conditionId
    , CONCAT(name[1].family, ' ', name[1].given[1]) as name
    , condition.meta.tag[1].display
    , json_extract(condition.modifierextension[1], '$.valueDecimal') AS confidenceScore
    , category[1].coding[1].code as categoryCode
    , category[1].coding[1].display as categoryDescription
    , code.coding[1].code as diagnosisCode
    , code.coding[1].display as diagnosisDescription
    , onsetdatetime
    , severity.coding[1].code as severityCode
    , severity.coding[1].display as severityDescription
    , verificationstatus.coding[1].display as verificationStatus
    , clinicalstatus.coding[1].display as clinicalStatus
    , encounter.reference as encounterId
    , encounter.type as encountertype
FROM database.patient, condition
WHERE CONCAT('Patient/', patient.id) = condition.subject.reference
ORDER BY name;

您可以使用 Athena 控制台对结果进行进一步排序或下载以进行进一步分析。

例为患者及其相关观察结果创建过滤器

以下示例查询演示如何对 HealthLake 数据存储中发现的患者的所有相关观察结果进行查找和排序。


SELECT
	patient.id as patientId
    , observation.id as observationId
    , CONCAT(name[1].family, ' ', name[1].given[1]) as name
    , meta.tag[1].display
    , json_extract(modifierextension[1], '$.valueDecimal') AS confidenceScore
    , status
    , category[1].coding[1].code as categoryCode
    , category[1].coding[1].display as categoryDescription
    , code.coding[1].code as observationCode
    , code.coding[1].display as observationDescription
    , effectivedatetime
    , CASE
		WHEN valuequantity.value IS NOT NULL THEN CONCAT(CAST(valuequantity.value AS VARCHAR),' ',valuequantity.unit)
      	WHEN valueCodeableConcept.coding [ 1 ].code IS NOT NULL THEN CAST(valueCodeableConcept.coding [ 1 ].code AS VARCHAR)
      	WHEN valuestring IS NOT NULL THEN CAST(valuestring AS VARCHAR)
      	WHEN valueboolean IS NOT NULL THEN CAST(valueboolean AS VARCHAR)
      	WHEN valueinteger IS NOT NULL THEN CAST(valueinteger AS VARCHAR)
      	WHEN valueratio IS NOT NULL THEN CONCAT(CAST(valueratio.numerator.value AS VARCHAR),'/',CAST(valueratio.denominator.value AS VARCHAR))
      	WHEN valuerange IS NOT NULL THEN CONCAT(CAST(valuerange.low.value AS VARCHAR),'-',CAST(valuerange.high.value AS VARCHAR))
      	WHEN valueSampledData IS NOT NULL THEN CAST(valueSampledData.data AS VARCHAR)
      	WHEN valueTime IS NOT NULL THEN CAST(valueTime AS VARCHAR)
      	WHEN valueDateTime IS NOT NULL THEN CAST(valueDateTime AS VARCHAR)
      	WHEN valuePeriod IS NOT NULL THEN valuePeriod.start
      	WHEN component[1] IS NOT NULL THEN CONCAT(CAST(component[2].valuequantity.value AS VARCHAR),' ',CAST(component[2].valuequantity.unit AS VARCHAR), '/', CAST(component[1].valuequantity.value AS VARCHAR),' ',CAST(component[1].valuequantity.unit AS VARCHAR))
    END AS observationvalue
	, encounter.reference as encounterId
    , encounter.type as encountertype
FROM database.patient, observation
WHERE CONCAT('Patient/', patient.id) = observation.subject.reference
ORDER BY name;

例为患者及其相关程序创建筛选条件

将手术与患者联系起来是医疗保健的一个重要方面。以下 SQL 示例查询演示了如何使用 FHIR Patient 和Procedure资源类型来实现此目的。以下 SQL 查询将返回在您的 HealthLake 数据存储中找到的所有患者及其相关程序。


SELECT
	patient.id  as patientId
	, PROCEDURE.id as procedureId
	, CONCAT(name[1].family, ' ', name[1].given[1]) as name
	, status
	, category.coding[1].code as categoryCode
	, category.coding[1].display as categoryDescription
	, code.coding[1].code as procedureCode
	, code.coding[1].display as procedureDescription
	, performeddatetime
	, performer[1]
	, encounter.reference as encounterId
	, encounter.type as encountertype
FROM database.patient, procedure
WHERE CONCAT('Patient/', patient.id) = procedure.subject.reference
ORDER BY name;

您可以使用 Athena 控制台下载结果以供进一步分析，也可以对其进行排序以更好地了解结果。

例为患者及其相关处方创建筛选条件

查看患者正在服用的药物的最新清单很重要。使用 Athena，您可以编写 SQL 查询，该查询同时使用Patient数据存储中的MedicationRequest HealthLake 和资源类型。

以下 SQL 查询连接了导入到 Athena MedicationRequest 中的Patient和表。它还使用点符号将处方组织到各自的条目中。


SELECT
	patient.id  as patientId
	, medicationrequest.id  as medicationrequestid
	, CONCAT(name[1].family, ' ', name[1].given[1]) as name
	, status
	, statusreason.coding[1].code as categoryCode
	, statusreason.coding[1].display as categoryDescription
	, category[1].coding[1].code as categoryCode
	, category[1].coding[1].display as categoryDescription
	, priority
	, donotperform
	, encounter.reference as encounterId
	, encounter.type as encountertype
	, medicationcodeableconcept.coding[1].code as medicationCode
	, medicationcodeableconcept.coding[1].display as medicationDescription
	, dosageinstruction[1].text as dosage
FROM database.patient, medicationrequest
WHERE CONCAT('Patient/', patient.id ) = medicationrequest.subject.reference
ORDER BY name

您可以使用 Athena 控制台对结果进行排序或下载以进行进一步分析。

例查看`MedicationStatement`资源类型中找到的药物

以下示例查询向您展示了如何使用 SQL 整理导入到 Athena 的嵌套 JSON。该查询使用 FHIR meta 元素来指示何时通过 HealthLake集成自然语言处理 (NLP) 添加了药物。它还json_extract用于在 JSON 字符串数组中搜索数据。有关更多信息，请参阅自然语言处理。


SELECT
	medicationcodeableconcept.coding[1].code as medicationCode
	, medicationcodeableconcept.coding[1].display as medicationDescription
	, meta.tag[1].display
	, json_extract(modifierextension[1], '$.valueDecimal') AS confidenceScore
FROM medicationstatement;

您可以使用 Athena 控制台下载这些结果或对其进行排序。

例筛选特定疾病类型

该示例显示了如何找到一组年龄在18至75岁之间、被诊断出患有糖尿病的患者。


SELECT patient.id as patientId,
	condition.id as conditionId,
	CONCAT(name [ 1 ].family, ' ', name [ 1 ].given [ 1 ]) as name,
	(year(current_date) - year(date(birthdate))) AS age,
	CASE
		WHEN condition.encounter.reference IS NOT NULL THEN condition.encounter.reference
		WHEN observation.encounter.reference IS NOT NULL THEN observation.encounter.reference
	END as encounterId,
	CASE
		WHEN condition.encounter.type IS NOT NULL THEN observation.encounter.type
		WHEN observation.encounter.type IS NOT NULL THEN observation.encounter.type
	END AS encountertype,
	condition.code.coding [ 1 ].code as diagnosisCode,
	condition.code.coding [ 1 ].display as diagnosisDescription,
	observation.category [ 1 ].coding [ 1 ].code as categoryCode,
	observation.category [ 1 ].coding [ 1 ].display as categoryDescription,
	observation.code.coding [ 1 ].code as observationCode,
	observation.code.coding [ 1 ].display as observationDescription,
	effectivedatetime AS observationDateTime,
	CASE
      WHEN valuequantity.value IS NOT NULL THEN CONCAT(CAST(valuequantity.value AS VARCHAR),' ',valuequantity.unit)
      WHEN valueCodeableConcept.coding [ 1 ].code IS NOT NULL THEN CAST(valueCodeableConcept.coding [ 1 ].code AS VARCHAR)
      WHEN valuestring IS NOT NULL THEN CAST(valuestring AS VARCHAR)
      WHEN valueboolean IS NOT NULL THEN CAST(valueboolean AS VARCHAR)
      WHEN valueinteger IS NOT NULL THEN CAST(valueinteger AS VARCHAR)
      WHEN valueratio IS NOT NULL THEN CONCAT(CAST(valueratio.numerator.value AS VARCHAR),'/',CAST(valueratio.denominator.value AS VARCHAR))
      WHEN valuerange IS NOT NULL THEN CONCAT(CAST(valuerange.low.value AS VARCHAR),'-',CAST(valuerange.high.value AS VARCHAR))
      WHEN valueSampledData IS NOT NULL THEN CAST(valueSampledData.data AS VARCHAR)
      WHEN valueTime IS NOT NULL THEN CAST(valueTime AS VARCHAR)
      WHEN valueDateTime IS NOT NULL THEN CAST(valueDateTime AS VARCHAR)
      WHEN valuePeriod IS NOT NULL THEN valuePeriod.start
      WHEN component[1] IS NOT NULL THEN CONCAT(CAST(component[2].valuequantity.value AS VARCHAR),' ',CAST(component[2].valuequantity.unit AS VARCHAR), '/', CAST(component[1].valuequantity.value AS VARCHAR),' ',CAST(component[1].valuequantity.unit AS VARCHAR))
    END AS observationvalue,
	CASE
		WHEN condition.meta.tag [ 1 ].display = 'SYSTEM GENERATED' THEN 'YES'
		WHEN condition.meta.tag [ 1 ].display IS NULL THEN 'NO'
		WHEN observation.meta.tag [ 1 ].display = 'SYSTEM GENERATED' THEN 'YES'
		WHEN observation.meta.tag [ 1 ].display IS NULL THEN 'NO'
  	END AS IsSystemGenerated,
  CAST(
    json_extract(
      condition.modifierextension [ 1 ],
      '$.valueDecimal'
    ) AS int
  ) AS confidenceScore
FROM database.patient,
	database.condition,
	database.observation
WHERE CONCAT('Patient/', patient.id) = condition.subject.reference
	AND CONCAT('Patient/', patient.id) = observation.subject.reference
  	AND (year(current_date) - year(date(birthdate))) >= 18
  	AND (year(current_date) - year(date(birthdate))) <= 75
  	AND condition.code.coding [ 1 ].display like ('%diabetes%');

现在，您可以使用 Athena 控制台对结果进行排序或下载以进行进一步分析。

Javascript 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

文档惯例

使用 SQL 进行查询

监控

使用复杂筛选的 SQL 查询示例

例 根据人口统计数据创建筛选标准

例 为患者及其相关病症创建过滤器

例 为患者及其相关观察结果创建过滤器

例 为患者及其相关程序创建筛选条件

例 为患者及其相关处方创建筛选条件

例 查看MedicationStatement资源类型中找到的药物

例 筛选特定疾病类型