Merge and aggregate datasets: Difference between revisions
Content added Content deleted
(Harbour: minor fix for multiple values) |
(Harbour: use VALUES for all INSERT statements) |
||
Line 800: | Line 800: | ||
<syntaxhighlight lang="xbase"> |
<syntaxhighlight lang="xbase"> |
||
&& SQL-like INSERT command (note the variable number of arguments) |
|||
⚫ | |||
⚫ | |||
#xcommand INSERT INTO <table> ( <uFieldList,...> ) VALUES ( <uValList1,...> ) [, ( <uValListN,...> )] => ; |
#xcommand INSERT INTO <table> ( <uFieldList,...> ) VALUES ( <uValList1,...> ) [, ( <uValListN,...> )] => ; |
||
INSERT INTO <table> ( <uFieldList> ) VALUE ( <uValList1> ) ; |
INSERT INTO <table> ( <uFieldList> ) VALUE ( <uValList1> ) ; |
||
[; INSERT INTO <table> ( <uFieldList> ) VALUE ( <uValListN> )] |
[; INSERT INTO <table> ( <uFieldList> ) VALUE ( <uValListN> )] |
||
&& Singular cases (so we can use VALUES for all instances) |
|||
⚫ | |||
#xcommand INSERT INTO <table> (<uField>) VALUE (<uVal>) => ; |
|||
⚫ | |||
<table>->(dbAppend()); <table>-><uField> := <uVal> |
|||
#xcommand INSERT INTO <table> ( <uFieldList,...> ) VALUES ( <uValList,...> ) => ; |
|||
INSERT INTO <table> ( <uFieldList> ) VALUE ( <uValList> ) |
|||
PROCEDURE Main() |
PROCEDURE Main() |
||
Line 821: | Line 827: | ||
vStruct := {{"patient_id", "n", 8, 0}, {"visit_date", "d", 10, 0}, {"score", "n", 8, 1}} |
vStruct := {{"patient_id", "n", 8, 0}, {"visit_date", "d", 10, 0}, {"score", "n", 8, 1}} |
||
dbCreate( "visit", vStruct,, .T., "visit" ) |
dbCreate( "visit", vStruct,, .T., "visit" ) |
||
INSERT INTO visit (patient_id, visit_date, score) |
INSERT INTO visit (patient_id, visit_date, score) VALUES (2002, ctod("2020-09-10"), 6.8) |
||
INSERT INTO visit (patient_id, visit_date, score) |
INSERT INTO visit (patient_id, visit_date, score) VALUES (1001, ctod("2020-09-07"), 5.5) |
||
INSERT INTO visit (patient_id, visit_date, score) |
INSERT INTO visit (patient_id, visit_date, score) VALUES (4004, ctod("2020-09-24"), 8.4) |
||
INSERT INTO visit (patient_id, visit_date) |
INSERT INTO visit (patient_id, visit_date) VALUES (2002, ctod("2020-10-08")) |
||
INSERT INTO visit (patient_id, score) |
INSERT INTO visit (patient_id, score) VALUES (1001, 6.6) |
||
INSERT INTO visit (patient_id, visit_date) |
INSERT INTO visit (patient_id, visit_date) VALUES (3003, ctod("2020-11-12")) |
||
INSERT INTO visit (patient_id, visit_date, score) |
INSERT INTO visit (patient_id, visit_date, score) VALUES (4004, ctod("2020-11-12"), 7.0) |
||
INSERT INTO visit (patient_id, visit_date, score) |
INSERT INTO visit (patient_id, visit_date, score) VALUES (1001, ctod("2020-11-19"), 5.3) |
||
INDEX ON patient_id TO visit_id |
INDEX ON patient_id TO visit_id |
||
Line 842: | Line 848: | ||
aAgg := {0, 0.0, ctod("19000101")} && initial values |
aAgg := {0, 0.0, ctod("19000101")} && initial values |
||
DO WHILE ! Eof() .AND. xCurId == patient_id |
DO WHILE ! Eof() .AND. xCurId == patient_id |
||
aAgg := {1+aAgg[1], score+aAgg[2], max(visit_date, aAgg[3])} && update |
|||
SKIP |
|||
ENDDO |
ENDDO |
||
INSERT INTO report (patient_id, n, sum_score, avg_score, max_date) ; |
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 |
ENDDO |
||