Merge and aggregate datasets: Difference between revisions
Harbour: allow for left join
(Harbour: allow for left join) |
|||
Line 800:
<syntaxhighlight lang="xbase">
&& SQL-like INSERT command (note the variable number of arguments)
#xcommand INSERT INTO <table> ( <uField1>[, <uFieldN> ] ) VALUES ( <uVal1>[, <uValN> ] ) => ;
Line 810 ⟶ 808:
SET DATE FORMAT "yyyy-mm-dd"
SET SOFTSEEK ON
&& create and populate the patient table
Line 819 ⟶ 818:
INSERT INTO patient (patient_id, lastname) VALUES (4004, "Gosling")
INSERT INTO patient (patient_id, lastname) VALUES (5005, "Kurtz")
INDEX ON patient_id TO pat_id
&& create and populate the visit table
Line 834:
&& create the result table
rStruct := { {"patient_id", "n", 8, 0}, {"
{"n", "i", 8, 0}, {"sum_score", "n", 8, 1}, ;
{"avg_score", "n", 8, 1}, {"max_date", "d", 10, 0}}
dbCreate("report", rStruct,, .T., "report")
// left join: for each patient
SELECT
DO WHILE ! Eof()
xCurId := patient_id && grouping variable
SELECT visit
LOCATE FOR patient_id==xCurId
aAgg := {0, 0.0, ctod("19000101")} && initial values▼
IF found()
&& aggregate within visits for a given patient
aAgg := {1+aAgg[1], score+aAgg[2], max(visit_date, aAgg[3])} && update
SKIP
▲ INSERT INTO report (patient_id, n, sum_score, avg_score, max_date) ;
ELSE
▲ VALUES (xCurId, aAgg[1], aAgg[2], aAgg[2]/aAgg[1], aAgg[3])
INSERT INTO report (patient_id, lastname) VALUES (xCurId, patient->lastname)
END IF
SELECT patient
SKIP
ENDDO
SELECT report
? "NUM", "PATIENT_ID", "LASTNAME", "N", "SUM_SCORE", "AVG_SCORE", "MAX_DATE"
LIST patient_id, lastname, n, sum_score, avg_score, max_date
Line 869 ⟶ 870:
<pre>NUM PATIENT_ID LASTNAME N SUM_SCORE AVG_SCORE MAX_DATE
1 1001 Hopper 3 17.4 5.8 2020-11-19
2 2002 Wirth 2 6.8 3.4 2020-10-08
3 3003 Kemeny 1 0.0 0.0 2020-11-12
4 4004 Gosling 2 15.4 7.7 2020-11-12
5 5005 Kurtz 0 0.0 0.0 - - </pre>
=={{header|J}}==
| |||