복잡한 필터링을 사용하는 SQL 쿼리 예제

다음 예제에서는 복잡한 필터링과 함께 Amazon Athena SQL 쿼리를 사용하여 HealthLake 데이터 스토어에서 FHIR 데이터를 찾는 방법을 보여줍니다.

예 인구 통계 데이터를 기반으로 필터링 기준 생성

환자 코호트를 생성할 때는 올바른 환자 인구 통계를 식별하는 것이 중요합니다. 이 샘플 쿼리는 Trino 점 표기법 및를 사용하여 HealthLake 데이터 스토어의 데이터를 필터링json_extract하는 방법을 보여줍니다.


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를 사용하면 HealthLake 데이터 스토어에 있는 Patient 및 MedicationRequest 리소스 유형을 모두 사용하는 SQL 쿼리를 작성할 수 있습니다.

다음 SQL 쿼리는 Athena로 가져온 Patient 및 MedicationRequest 테이블을 조인합니다. 또한 점 표기법을 사용하여 처방을 개별 항목으로 구성합니다.


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가 브라우저에서 비활성화되거나 사용이 불가합니다.

AWS 설명서를 사용하려면 Javascript가 활성화되어야 합니다. 지침을 보려면 브라우저의 도움말 페이지를 참조하십시오.

문서 규칙

SQL을 사용한 쿼리

모니터링