J'ai 2 tables.
Les enregistrements de la Table 'Lab_obs' résultent lorsqu'un patient a été testé pour Covid.
La table 'Médicaments' contient des enregistrements de tous les médicaments prises par tous les patients (testés ou non).
Voici le schéma simplifié: Je veux écrire un SQL qui me donnera (patient_id, med)
pour les patients ayant testé positif et testé par la suite négatif
et tous les médicaments prises pendant le temps entre le test positif et le test négatif ultérieur. Fondamentalement, la requête devrait me donner tous les médicaments prises par des patients qui se sont retrouvés de Covid !!
3 Réponses :
Je pense que c'est ce que vous pourriez utiliser. En utilisant la fonction de décalage pour vérifier l'enregistrement précédent. Je l'ai couru dans SQL Server. En outre, veuillez utiliser des codes d'observation car l'orthographe peut provoquer des problèmes par postivité au lieu de positif.
SELECT m.patient_id, m.med , m.given_day
FROM medications m
JOIN (SELECT patient_id,
CASE WHEN ob = 'negative'
AND lag(ob) OVER (partition by patient_id order by l.ob_day) = 'postive'
THEN 1
ELSE 0
END as qualify
FROM lab_obs l) l ON l.patient_id = m.patient_id AND l.qualify = 1
Cela fonctionne avec SparkSQL (réf. github.com/assamese / Spark-Python-HL7 / BLOB / ... )
Essayé avec PostgreSQL (pas sûr de quelles SGBDM utilisez-vous). Vous auriez besoin de fonctions de fenêtre et de CTES pour cela:
WITH lagged_obs AS (
SELECT patient_id,
ob_day,
ob,
LAG(ob) OVER (PARTITION BY patient_id ORDER BY ob_day) AS lagged_ob
FROM lab_obs
)
, patient_status_change AS (
-- Look for status changes, useful for consecutive tests with same result
SELECT patient_id,
ob_day AS ob_change_day,
ob
FROM lagged_obs
WHERE ob IS DISTINCT FROM lagged_ob
)
, patient_infected_periods AS (
-- Build time periods
SELECT patient_id,
ob_change_day AS start_date,
LEAD(ob_change_day)
OVER (PARTITION BY patient_id ORDER BY ob_change_day) AS end_date,
ob AS period_status
FROM patient_status_change
)
SELECT pip.patient_id, m.given_day, m.med
FROM patient_infected_periods pip
INNER JOIN medications m
ON pip.patient_id=m.patient_id AND pip.start_date <= m.given_day AND m.given_day < pip.end_date
WHERE pip.period_status='postive'
C'est un peu délicat. Si une personne ne teste que positive une fois, alors:
select m.*, l.*
from medications m join
(select l.*,
lead(ob_day) over (partition by patient_id order by ob_day) as next_ob_day
from (select l.*,
lag(ob) over (partition by patient_id order by ob_day) as prev_ob
from lab_obs l
) l
where prev_ob is null or prev_ob <> ob
) l
on m.patient_id = l.patient_id and
m.given_date >= l.ob_day and
m.given_date < l.next_ob_day and
l.ob = 'positive';
@Bruno Gordonlinoff Varunt Initialement, je vais tester la requête à Postgres, mais je vais éventuellement utiliser la requête dans Sparksql, alors je ne fonctionnerai donc que 1 va réellement travailler .... laissez-moi essayer toutes les requêtes suggérées et ensuite j'accepterai le 1 qui fonctionne dans SparksQL
Malheureusement, SparkSQL n'autorise pas "Toute clause" ( Stackoverflow.com / Questions / 55690999 / Spark-SQL-with-any-cla utilise )
@Sanjaydas. . . Intéressant. C'est tellement standard, je n'y ai même pas pensé. J'ai modifié la réponse.
Qu'est-ce que
médicaments a à voir avec cette question?@Gordonlinoff Je pense qu'il veut que le nom de médicament qui a été donné au patient soit sélectionné aussi.
J'ai édité la question ... j'espère que c'est clair maintenant?
Égalisez également votre base de données
Veuillez étiqueter votre question avec la base de données que vous utilisez: PostgreSQL, Oracle, SQL-Server ...?
Vous ne savez pas si la structure de données est la vôtre, mais avez-vous envisagé d'utiliser un bit
booléen bit pour les résultats du test par exemple?Salut, avez-vous résolu votre problème?
Oui, j'ai accepté la réponse ci-dessous (ref. GitHub. com / assamais / spark-python-hl7 / blob / ... )