Merge and aggregate datasets: Difference between revisions
Content added Content deleted
(→{{header|SQL}}: Add TutorialD task) |
|||
Line 1,542: | Line 1,542: | ||
</pre> |
</pre> |
||
=={{header|TutorialD}}== |
=={{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}}== |
=={{header|Wren}}== |