Merge and aggregate datasets: Difference between revisions
Content added Content deleted
m (Added language identifier.) |
(Harbour task - initial implementation) |
||
Line 796: | Line 796: | ||
4004 | Wirth | 2020-11-05 | 15.4 | 7.7 |
4004 | Wirth | 2020-11-05 | 15.4 | 7.7 |
||
5005 | Kurtz | | | </pre> |
5005 | Kurtz | | | </pre> |
||
=={{header|Harbour}}== |
|||
<syntaxhighlight lang="xbase"> |
|||
#include "dbstruct.ch" |
|||
// SQL-like INSERT command (note the variable number of arguments) |
|||
#xcommand INSERT INTO <table> ( <uField1>[, <uFieldN> ] ) VALUES ( <uVal1>[, <uValN> ] ) => ; |
|||
<table>->(dbAppend()); <table>-><uField1> := <uVal1> [; <table>-><uFieldN> := <uValN> ] |
|||
PROCEDURE Main() |
|||
LOCAL pStruct, vStruct, rStruct, xCurId, aAgg |
|||
SET DATE FORMAT "yyyy-mm-dd" |
|||
// create and population the patient table |
|||
pStruct := {{"patient_id", "n", 8, 0}, {"lastname", "c", 10, 0 }} |
|||
dbCreate( "patient", pStruct,, .T., "patient" ) |
|||
INSERT INTO patient (patient_id, lastname) VALUES (1001, "Hopper") |
|||
INSERT INTO patient (patient_id, lastname) VALUES (2002, "Wirth") |
|||
INSERT INTO patient (patient_id, lastname) VALUES (3003, "Kemeny") |
|||
INSERT INTO patient (patient_id, lastname) VALUES (4004, "Gosling") |
|||
INSERT INTO patient (patient_id, lastname) VALUES (5005, "Kurtz") |
|||
// create and population the visit table |
|||
vStruct := {{"patient_id", "n", 8, 0}, {"visit_date", "d", 10, 0}, {"score", "n", 8, 1}} |
|||
dbCreate( "visit", vStruct,, .T., "visit" ) |
|||
INSERT INTO visit (patient_id, visit_date, score) VALUES (2002, ctod("2020-09-10"), 6.8) |
|||
INSERT INTO visit (patient_id, visit_date, score) VALUES (1001, ctod("2020-09-07"), 5.5) |
|||
INSERT INTO visit (patient_id, visit_date, score) VALUES (4004, ctod("2020-09-24"), 8.4) |
|||
INSERT INTO visit (patient_id, visit_date) VALUES (2002, ctod("2020-10-08")) |
|||
INSERT INTO visit (patient_id, score) VALUES (1001, 6.6) |
|||
INSERT INTO visit (patient_id, visit_date) VALUES (3003, ctod("2020-11-12")) |
|||
INSERT INTO visit (patient_id, visit_date, score) VALUES (4004, ctod("2020-11-12"), 7.0) |
|||
INSERT INTO visit (patient_id, visit_date, score) VALUES (1001, ctod("2020-11-19"), 5.3) |
|||
INDEX ON patient_id TO visit_id |
|||
// 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") |
|||
SELECT visit |
|||
// populate the result table |
|||
DO WHILE ! Eof() |
|||
xCurId := patient_id // grouping variable |
|||
aAgg := {0, 0.0, ctod("19000101")} // initial values |
|||
// within a group: |
|||
DO WHILE ! Eof() .AND. xCurId == patient_id |
|||
aAgg := {1+aAgg[1], score+aAgg[2], max(visit_date, aAgg[3])} // update |
|||
SKIP // skip to next row |
|||
ENDDO |
|||
INSERT INTO report (patient_id, n, sum_score, avg_score, max_date) ; |
|||
VALUES (xCurId, aAgg[1], aAgg[2], aAgg[2]/aAgg[1], aAgg[3]) |
|||
ENDDO |
|||
SELECT report |
|||
JOIN WITH patient TO report2 FOR patient_id == patient->patient_id ; |
|||
FIELDS patient_id, patient->lastname, n, sum_score, avg_score, max_date |
|||
USE report2 |
|||
? "NUM", "PATIENT_ID", "LASTNAME", "N", "SUM_SCORE", "AVG_SCORE", "MAX_DATE" |
|||
LIST patient_id, lastname, n, sum_score, avg_score, max_date |
|||
RETURN</syntaxhighlight> |
|||
With output: |
|||
<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</pre> |
|||
=={{header|J}}== |
=={{header|J}}== |