Merge and aggregate datasets: Difference between revisions
→{{header|TutorialD}}
(→{{header|SQL}}: Add TutorialD task) |
|||
Line 1,542:
</pre>
=={{header|TutorialD}}==
<lang>BEGIN;
TYPE Date UNION;
TYPE DateValid IS {Date POSSREP {year INTEGER, month INTEGER, day INTEGER}};
TYPE DateNone IS {Date POSSREP {}};
TYPE DateUnknown IS {Date POSSREP {}};
END;
VAR patient REAL RELATION {id INT, lastname CHAR} KEY {id};
INSERT patient RELATION
{TUPLE {id 1001, lastname 'Hopper'},
TUPLE {id 4004, lastname 'Wirth'},
TUPLE {id 3003, lastname 'Kemeny'},
TUPLE {id 2002, lastname 'Gosling'},
TUPLE {id 5005, lastname 'Kurtz'}
};
VAR visit REAL RELATION {id INT, date Date, score RATIONAL} KEY {id, date};
INSERT visit RELATION
{
TUPLE {id 2002, date DateValid(2020,09,10), score 6.8},
TUPLE {id 1001, date DateValid(2020,09,17), score 5.5},
TUPLE {id 4004, date DateValid(2020,09,24), score 8.4},
TUPLE {id 2002, date DateValid(2020,10,08), score NAN},
TUPLE {id 1001, date DateNone(), score 6.6},
TUPLE {id 3003, date DateValid(2020,11,12), score NAN},
TUPLE {id 4004, date DateValid(2020,11,05), score 7.0},
TUPLE {id 1001, date DateValid(2020,11,19), score 5.3}
};
((summarize (visit where score>0.0) by {id}: {sumscore := sum(score), avgscore := avg(score)}) union
(extend (patient {id} minus ((visit where score>0.0) {id})): {sumscore:=NaN, avgscore:=NaN})) join
(summarize visit by {id}: {maxdate := max(date)} union
(extend (patient {id} minus (visit {id})): {maxdate := DateUnknown()})) join
patient</lang>
{{out}}
<pre>
id sumscore avgscore maxdate lastname
1001 17.4 5.8 DateValid(2020, 11, 19) Hopper
2002 6.8 6.8 DateValid(2020, 10, 8) Gosling
4004 15.4 7.7 DateValid(2020, 11, 5) Wirth
3003 NaN NaN DateValid(2020, 11, 12) Kemeny
5005 NaN NaN DateUnknown() Kurtz</pre>
=={{header|Wren}}==
| |||