Difference between revisions of "SQL Examples"

From SICDB Doc
Jump to navigation Jump to search
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
While the SICdb dataset is provided as csv raw data, it is designed to be used in a a relational database management system. Refer to [[Get Started]] for further information building up the database and corresponding software. Our software also includes one-click SQL codes for hundreds of fields like creatinine on day one/two/..., max/min/Average lactate and much more.
While the SICdb dataset is provided as csv raw data, it is designed to be used in a a relational database management system. Refer to [[Get Started]] for further information building up the database and corresponding software. Our software includes hundreds of configurable one-click SQL snippets like creatinine on day one/two/..., max/min/average lactate and much more.




<noinclude>== Using Referenced Fields ==</noinclude>
<includeonly>=== Using Referenced Fields ===</includeonly>


== Using Referenced Fields ==
One of the most important tasks is decoding the referenced fields. While our Software does this automatically the database design makes it also quite simple in raw SQL code.
One of the most important tasks is decoding the referenced fields. While our Software does this automatically the database design makes it also quite simple in raw SQL code.


=== Simple Join Query ===
<noinclude>=== Simple Join Query ===</noinclude>
<includeonly>==== Simple Join Query ====</includeonly>
 
[[File:ExampleJoin.jpg|left|thumb]]
[[File:ExampleJoin.jpg|left|thumb]]


Line 19: Line 22:




=== Join Query including Unit-Of-Measurement ===
<noinclude>=== Join Query including Unit-Of-Measurement ===</noinclude>
<includeonly>==== Join Query including Unit-Of-Measurement ====</includeonly>


[[File:ExampleLab.jpg|thumb|left]]
[[File:ExampleLab.jpg|thumb|left]]
Line 49: Line 53:




== Subquery Examples ==


A quite flexible query solution are subqueries. These allow better configuration for the field. '''Note this is a MySQL 8.x query and may need to be adapted in other database systems and the SQL code snippets have been generated by our software.'''
<noinclude>== Subquery Examples ==</noinclude>
<includeonly>=== Subquery Examples ===</includeonly>
 
 
A quite flexible query solution are subqueries. These allow better configuration for the field. '''Note this is a MySQL 8.x query and may need to be adapted in other database systems'''. These SQL code snippets have been generated by our software.
 
<noinclude>=== Select Maximum Creatinine Example ===</noinclude>
<includeonly>==== Select Maximum Creatinine Example ====</includeonly>


=== Select Maximum Creatinine Example ===
   SELECT `cases`.`CaseID` AS CaseID ,( SELECT Max(laboratory.LaboratoryValue) FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0  AND Offset < 7 * 86400 ) AS MaximumCreatinineInFirstWeek FROM cases
   SELECT `cases`.`CaseID` AS CaseID ,( SELECT Max(laboratory.LaboratoryValue) FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0  AND Offset < 7 * 86400 ) AS MaximumCreatinineInFirstWeek FROM cases


=== Select First Creatinine Example ===
<noinclude>=== Select First Creatinine Example ===</noinclude>
<includeonly>==== Select First Creatinine Example ====</includeonly>
 
  SELECT `cases`.`CaseID` AS CaseID ,( SELECT laboratory.LaboratoryValue AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 0 ,1 ) AS FirstCreatinineAfterAdmission FROM cases
  SELECT `cases`.`CaseID` AS CaseID ,( SELECT laboratory.LaboratoryValue AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 0 ,1 ) AS FirstCreatinineAfterAdmission FROM cases


=== Select Second Creatinine Example ===
<noinclude>=== Select Second Creatinine Example ===</noinclude>
<includeonly>==== Select Second Creatinine Example ====</includeonly>
 
  SELECT `cases`.`CaseID` AS CaseID ,( SELECT laboratory.LaboratoryValue AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 1 ,1 ) AS SecondCreatinineAfterAdmission FROM cases
  SELECT `cases`.`CaseID` AS CaseID ,( SELECT laboratory.LaboratoryValue AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 1 ,1 ) AS SecondCreatinineAfterAdmission FROM cases


=== Select Second Creatine With Time Example ===
<noinclude>=== Select Second Creatine With Time Example ===</noinclude>
<includeonly>==== Select Second Creatine With Time Example ====</includeonly>


A little more advanced example including the time-of-measurement.
A little more advanced example including the time-of-measurement.
[[File:ExampleTime.jpg|thumb|right]]


   SELECT `cases`.`CaseID` AS CaseID ,( SELECT laboratory.LaboratoryValue AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 1 ,1 ) AS SecondCreatinineAfterAdmission ,( SELECT laboratory.Offset AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 1 ,1 ) / 3600 AS HoursAfterAdmission FROM cases
   SELECT `cases`.`CaseID` AS CaseID ,( SELECT laboratory.LaboratoryValue AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 1 ,1 ) AS SecondCreatinineAfterAdmission ,( SELECT laboratory.Offset AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 1 ,1 ) / 3600 AS HoursAfterAdmission FROM cases

Latest revision as of 16:50, 3 November 2022

While the SICdb dataset is provided as csv raw data, it is designed to be used in a a relational database management system. Refer to Get Started for further information building up the database and corresponding software. Our software includes hundreds of configurable one-click SQL snippets like creatinine on day one/two/..., max/min/average lactate and much more.


Using Referenced Fields

One of the most important tasks is decoding the referenced fields. While our Software does this automatically the database design makes it also quite simple in raw SQL code.

Simple Join Query

ExampleJoin.jpg
 SELECT CaseID, d_references.ReferenceValue as Survived FROM `cases` LEFT JOIN d_references ON d_references.ReferenceGlobalID=cases.HospitalDischargeType;





Join Query including Unit-Of-Measurement

ExampleLab.jpg
SELECT caseID,Offset,LaboratoryValue,d_references.ReferenceValue as LaboratoryName, d_references.ReferenceUnit as LabUnit FROM `laboratory` LEFT JOIN d_references ON d_references.ReferenceGlobalID=laboratory.LaboratoryID;














Subquery Examples

A quite flexible query solution are subqueries. These allow better configuration for the field. Note this is a MySQL 8.x query and may need to be adapted in other database systems. These SQL code snippets have been generated by our software.

Select Maximum Creatinine Example

 SELECT `cases`.`CaseID` AS CaseID ,( SELECT Max(laboratory.LaboratoryValue) FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0  AND Offset < 7 * 86400 ) AS MaximumCreatinineInFirstWeek FROM cases

Select First Creatinine Example

SELECT `cases`.`CaseID` AS CaseID ,( SELECT laboratory.LaboratoryValue AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 0 ,1 ) AS FirstCreatinineAfterAdmission FROM cases

Select Second Creatinine Example

SELECT `cases`.`CaseID` AS CaseID ,( SELECT laboratory.LaboratoryValue AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 1 ,1 ) AS SecondCreatinineAfterAdmission FROM cases

Select Second Creatine With Time Example

A little more advanced example including the time-of-measurement.

ExampleTime.jpg
 SELECT `cases`.`CaseID` AS CaseID ,( SELECT laboratory.LaboratoryValue AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 1 ,1 ) AS SecondCreatinineAfterAdmission ,( SELECT laboratory.Offset AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 1 ,1 ) / 3600 AS HoursAfterAdmission FROM cases