Window Analytic Functions

[smdesai]

Hello,

I have the following query which I'm trying to map to Logica but the results I get when I run both queries do not match, only the count column matches. What's the best way to represent this in Logica?

Thanks in advance

SELECT approx_percentile(cast(element_at(applogs.dynamicfields, 'runTime') as real), 0.5) AS p50, approx_percentile(cast(element_at(applogs.dynamicfields, 'runTime') as real), 0.95) AS p95, count(element_at(applogs.dynamicfields, 'runTime')) FROM applogs WHERE not (applogs.pod LIKE 'cs%') AND cast(element_at(applogs.dynamicfields, 'rulesServed') as real) > 0 AND (applogs.ts_date >= '20220201') AND (applogs.ts_date <= '20220220')")

@Engine("trino", ...);

ElementAt(m, k) =
    SqlExpr("element_at({m}, {k})", {m:, k:});

@With(Percentile);
Percentile(p50:, p95:) :-
    p50 == SqlExpr("approx_percentile(cast({runtime} as real), 0.5)", {runtime:}),
    p95 == SqlExpr("approx_percentile(cast({runtime} as real), 0.95)", {runtime:}),
    applogs(dynamicfields:),
    runtime == ElementAt(dynamicfields, "runTime");

Test(p50, p95, count? += 1) distinct :-
    applogs(dynamicfields:, ts_date:, pod:),
    Percentile(p50:, p95:),
    !Like(pod, "cs%"),
    rules_served == ElementAt(dynamicfields, "rulesServed"),
    SqlExpr("cast({rules_served} as real)", {rules_served:}) > 0,
    ts_date >= "20220201",
    ts_date <= "20220220";

[EvgSkv]

Hello @smdesai
as your query is a single aggregation it seems to make sense to have it as a single aggregate predicate like so:

ApproxPercentile(x) = SqlExpr(
    "approx_percentile({v}, {p})", {v: AsReal(x.v), p: x.p});

AsReal(t) = SqlExpr("cast({t} as real)", {t:});

ElementAt(m, k) = 
  SqlExpr("element_at({m}, {k})", {m:, k:});

Test(p50? ApproxPercentile= {v: runtime, p: 0.5},
     p95? ApproxPercentile= {v: runtime, p: 0.95},
     count? += 1) distinct :-
  applogs(dynamicfields:, ts_date:, pod:),
  runtime == ElementAt(dynamicfields, "runTime"),
  !Like(pod, "cs%"),
  rules_served == ElementAt(dynamicfields, "rulesServed"),
  AsReal(rules_served) > 0,
  ts_date >= "20220201",
  ts_date <= "20220220";

The count that you have in your query should actually be counting non-nulls? We should replace += then with an appropriate aggregation.

Let me know if it helps.

[smdesai]

Hello Evgeny,

Thank you for a quick reply and yes that yields the exact results. Still learning and getting used to the syntax nuance. Hopefully will get there at some point :)

[EvgSkv]

Happy to help! Don't hesitate to reach our if you have any other questions.