Built-in solution for grouping and aggregating TClientDataSet data into another data set

136 Views Asked by At

I have access log to my API with request time and URI. I load the data into TClientDataSet component.

time req
2021-05-01 10:01:01 /identify
2021-05-01 10:01:02 /identify
2021-05-01 10:01:03 /identify
2021-05-01 10:02:01 /add-bio
2021-05-01 10:02:01 /identify

Now I want to display the requests per minute statistics in a chart. For that I have to aggregate the data into another TClientDataSet (let's call it cdsRequestsPerMinute) like this:

minute req count
2021-05-01 10:01:00 /identify 3
2021-05-01 10:02:00 /add-bio 1
2021-05-01 10:02:00 /identify 1

There is lots of info on grouping and aggregation of TClientDataSet records but the result is more like "window functions" produce in SQL and not the grouping returning unique records for grouping criteria.

I understand I could go record by record and add the record to the cdsRequestsPerMinute if not yet present or increase count otherwise. Is there a built-in solution?

EDIT 1

To get the minute I use this function:

function TruncSeconds(const AVal: TDateTime): TDateTime;
var
  _H, _M, _Sec, _MSec: Word;
begin
  DecodeTime(AVal, _H, _M, _Sec, _MSec);
  Result := Trunc( AVal ) + EncodeTime(_H, _M, 0, 0);
end;

It simply removes the seconds from the TDateTime value.

Naive approach is for every request I:

procedure TForm1.AddRequest(const _Req: TRequestInfo);
begin
  //request list
  cdsRequests.Insert;
  cdsRequestsstart.Value := _Req.Start;
  cdsRequestsuri.Value := _Req.Uri;
  cdsRequestsduration_ms.Value := _Req.DurationMs;
  cdsRequeststhread.Value := _Req.Thread;
  cdsRequestsclient.Value := _Req.Client;
  cdsRequests.Post;

  //statistics
  if cdsReqsPerMinute.Locate(
    cdsReqsPerMinuteminute.FieldName+';'+cdsReqsPerMinuterequest.FieldName,
    VarArrayOf([TruncSeconds(_Req.Start), _Req.Uri]),
    []) then
  begin
    cdsReqsPerMinute.Edit;
    cdsReqsPerMinutecount.Value := cdsReqsPerMinutecount.Value + 1;
    cdsReqsPerMinute.Post;
  end
  else
  begin
    cdsReqsPerMinute.Insert;
    cdsReqsPerMinuteminute.Value := TruncSeconds(_Req.Start);
    cdsReqsPerMinuterequest.Value := _Req.Uri;
    cdsReqsPerMinutecount.Value := 1;
    cdsReqsPerMinute.Post;
  end;
end;
0

There are 0 best solutions below