Skip to content
/ phea Public

R package for finding patients in SQL databases using formulas and without using SQL joins

License

Notifications You must be signed in to change notification settings

fabkury/phea

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

phea

PHEnotyping Algebra

R package to perform electronic patient phenotyping using formulas and without using SQL joins.

Phea connects to any SQL table or query providing patient data in long form, that is, one row per event. At a minimum the data must have a patient identifier column and a time stamp column. From there, you can provide Phea with a formula such as body_mass_index = weight / (height * height), and it will produce the SQL query that computes that formula, or formulas.

Install with devtools:
devtools::install_github('fabkury/phea')
library(phea)

Learn how to use

The materials below showcase Phea's features and teach how to use. Not all features are depicted. Some features are only explained in the help pages within R (use the ?function_name command).

Compatibility with SQL dialects

Phea leverages the dbplyr framework in R but also extends it in some parts. Phea's SQL generation engine offers two modes: compatibility mode, and regular mode. Compatibility mode has less features but works on more SQL dialects. Regular mode has more features and produces queries that are more efficient. The table below gives the mode currently available for each SQL dialect.

Engine Current mode Expected final mode Notes
postgres regular Phea was developed using Postgres 15. Minimum required version is 11. Regular mode requires access rights to install user-defined functions (UDFs), which are already implemented.
mysql compatibility regular Regular mode requires access rights to install user-defined functions, which have not yet been implemented.
redshift compatibility compatibility Regular mode will not be possible until AWS Redshift supports RANGE clause for window functions.
databricks (spark SQL) regular
oracle not tested regular
bigquery not tested regular
sqlserver not tested unknown Testing could show that regular mode is not possible: "Depending on the ranking, aggregate, or analytic function used with the OVER clause, ORDER BY clause and/or the ROWS and RANGE clauses may not be supported." (https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver16)

By Fabrício Kury
Author contact: [email protected]

About

R package for finding patients in SQL databases using formulas and without using SQL joins

Topics

Resources

License

Stars

Watchers

Forks

Languages