Can I use Window (Analytic) function?

[koji-m]

Hello.
I am very interested in this project. And I'm trying to write some BigQuery SQL queries in Logica.
So, I would like to ask you, how can Logica realize what can be achieved by using the Window (Analytic) function?
For example, how should I write a query that uses the RANK function as shown below in Logica?

SELECT
  id,
  amount,
  RANK() OVER (ORDER BY amount DESC) AS ranking
FROM
  my_dataset.pos_data

The RANK function seems to be parsed, but I don't know how to write it.
https://github.com/EvgSkv/logica/blob/main/common/data/processed_functions.py#L216

[EvgSkv]

Hello.
Yes, you can!
Since we are still not sure what's the best syntax for those in Logica for now this part needs to be written in SQL with SqlExpr.
Here is how your query can be written:

Summary(id:, amount:, ranking:) :-
  my_dataset.pos_data(id:, amount:),
  ranking == SqlExpr("RANK() OVER (ORDER BY amount DESC)", {});

which compiles to

SELECT
  my_dataset_pos_data.id AS id,
  my_dataset_pos_data.amount AS amount,
  RANK() OVER (ORDER BY amount DESC) AS ranking
FROM
  my_dataset.pos_data AS my_dataset_pos_data;

Please let me know if you have further questions.

[koji-m]

Thank you so much for your reply.
I understand how to write the Window (Analytic) function for now.
And I did well.

I would like to ask you an additional question.

Based on the method you taught me, I wrote some queries and some queries did not work well.
The query calculates the median using Window function and Aggregation function.

The result of the Median function was different from what I expected, so I wanted to have the following SQL executed.

WITH meds AS (
SELECT
  store_cd,
  PERCENTILE_CONT(amount, 0.5) OVER (PARTITION BY store_cd) AS median
FROM
  my_dataset.pos_data
)
SELECT 
  store_cd,
  ANY_VALUE(median) AS median
FROM 
  meds 
GROUP BY 
  store_cd

So I wrote as follows.

Medians(store_cd:, median:) :-
  median == SqlExpr("PERCENTILE_CONT(amount, 0.5) OVER (PARTITION BY store_cd)", {}),
  my_project.my_dataset.pos_data(store_cd:, amount:);

Summary(store_cd:, median? AnyValue= median) distinct :-
  Medians(store_cd:, median:);

However, the SQL was as follows and a syntax error occurred.
(Analytic functions cannot be arguments to aggregate functions)

SELECT
   my_project_my_dataset_pos_data.store_cd AS store_cd,
   ANY_VALUE(PERCENTILE_CONT(amount, 0.5) OVER (PARTITION BY store_cd)) AS median  -- SYNTAX ERROR
 FROM
   my_project.my_dataset.pos_data AS my_project_my_dataset_pos_data
 GROUP BY store_cd ;

Do you have any ideas on how to avoid this?

[EvgSkv]

You are very welcome.

The way you wrote the query is semantically good and in the future I would like to make Logica compile it to a working SQL.

At this moment we need to explicitly ask Logica to put your first predicate into a "with" statement, so that SQL engine doesn't get confused. This is how to do it:

@With(Medians);
Medians(store_cd:, median:) :-
  median == SqlExpr("PERCENTILE_CONT({amount}, 0.5) OVER (PARTITION BY {store_cd})",
                    {amount:, store_cd:}),
  my_project.my_dataset.pos_data(store_cd:, amount:);

Summary(store_cd:, median? AnyValue= median) distinct :-
  Medians(store_cd:, median:);

Note that we should also be explicitly passing amount and store_cd to SqlExpr. Otherwise if we add another predicate to Medians definition, with the same fields, then it would break.

I don't have your dataset, so I can't meaningfully test the query, but the SQL looks structurally identical to what you intended.

WITH t_0_Medians AS (SELECT
  my_project_my_dataset_pos_data.store_cd AS store_cd,
  PERCENTILE_CONT(my_project_my_dataset_pos_data.amount, 0.5) OVER (PARTITION BY my_project_my_dataset_pos_data.store_cd) AS median
FROM
  my_project.my_dataset.pos_data AS my_project_my_dataset_pos_data)
SELECT
  Medians.store_cd AS store_cd,
  ANY_VALUE(Medians.median) AS median
FROM
  t_0_Medians AS Medians
GROUP BY store_cd;

Please let me know if this solves your problem.

[koji-m]

By explicitly specifying the @With, I was able to get the result I intended.

I will continue to try some queries.

Thank you very much!

[EvgSkv]

You are very welcome! Let me know if you get more questions.

[scottedwards2000]

What an interesting topic @EvgSkv of how to best express Window Functions in Logic Programming! Does the wider Datalog community have anything that would apply or is this something you and the Logica team will be inventing from scratch? Can't wait to see what you come up with!

[EvgSkv]

@scottedwards2000 thanks for supportive feedback!
With analytic functions Logica follows high level approach of SQL (similarly to aggregation).
It's indeed an interesting question to investigate, how does that relate with model approach to this in logic programming, if such attempts were made.