python-flatql 0.1.7
Compile flat query language into SQL dialect for data analysis
License: MIT
Requires Python: >=3.6.0,<4.0.0
Keywords: sql,flatql,flat-ql

# Flat-QL
[![tests](https://github.com/Agile-Data/flat-ql/actions/workflows/tests.yml/badge.svg)](https://github.com/Agile-Data/flat-ql/actions/workflows/tests.yml)
[![Codecov](https://img.shields.io/codecov/c/gh/Agile-Data/flat-ql)](https://codecov.io/gh/Agile-Data/flat-ql)
[![PyPI version](https://badge.fury.io/py/python-flatql.svg)](https://pypi.org/project/python-flatql/)
[![PyPI - Python Version](https://img.shields.io/pypi/pyversions/python-flatql)](https://pypi.org/project/python-flatql/)

Flat-QL is a SQL-like language for data analysis in SQL databases, it does not have `JOIN`, `GROUP BY`, `SUBQUERY` and more structured 
statements. In Flat-QL, you don't to think of relational calculations and over-counting, which has only projections, filters, order by 
and limit. The first, you need to define your data models and relationships using `hocon` format (you can also redefine it to any 
format you want), then you just need write a SQL-like language (based on sales data structure) as follows.

## Foreign Sums And Counts

Flat-QL allows you to compute sums, counts correctly based on your join tree. This example has `Sales`, joining to `Product`, `Store` 
and `Guider`. `Slaes` has a number of `Product`, `Store` and `Guider`. Code below computes sums, counts and averages at various places 
in the join tree. See more: [sales schema definitions](https://github.com/Agile-Data/flat-ql/tree/main/tests/schemas/sales)

![img.png](https://raw.githubusercontent.com/Agile-Data/flat-ql/main/doc/1.png)

```shell
pip install python-flatql
```

```python
import os
from flatql import parse_from_hocon_path, parse_flatql
from flatql import SqlRewriter

sales_schema = parse_from_hocon_path(f"{os.path.dirname(__file__)}/schemas/sales")
sql_rewriter = SqlRewriter(sales_schema)

# As you can see, there are multi-entity(or tables) in the SQL-like language, but no joins, no subqueries
parse_flatql('SELECT Store.city AS "City", COUNT(Product.name) AS "Count Of Product", '
             'SUM(Sales.sales) AS "Sales", AVG(Sales.quantityPurchased) AS "Average Sales" FROM sales '
             'WHERE Store.city IN (\'New York\', \'Chicago\') ORDER BY "City" LIMIT 10 OFFSET 1').rewrite(sql_rewriter)
print(sql_rewriter.to_sql())
```

```sql
-- Flat-QL in the argument of parse_flatql function
SELECT Store.city                   AS "City",
       COUNT(Product.name)          AS "Count Of Product",
       SUM(Sales.sales)             AS "Total Sales",
       AVG(Sales.quantityPurchased) AS "Average Of Quantity Purchased"
FROM sales -- It is a table set who describes the structure and relationships in sales domain
WHERE Store.city IN ('New York', 'Chicago')
ORDER BY "City" LIMIT 10 OFFSET 1
```

```sql
-- Compiled SQL
SELECT "qu_0"."co_1" AS "City",
       "qu_0"."co_3" AS "Count Of Product",
       "qu_1"."co_7" AS "Total Sales",
       "qu_1"."co_8" AS "Average Of Quantity Purchased"
FROM (SELECT "qu_2"."co_1" AS "co_1", SUM("qu_3"."co_2") AS "co_3"
      FROM (SELECT "ta_0"."id" AS "co_0", "ta_1"."city" AS "co_1"
            FROM "db"."sales" AS "ta_2"
                     INNER JOIN "db"."products" AS "ta_0" ON "ta_0"."id" = "ta_2"."productId"
                     INNER JOIN "db"."stores" AS "ta_1" ON "ta_1"."id" = "ta_2"."storeId"
            WHERE "ta_1"."city" IN ('New York', 'Chicago')
            GROUP BY "co_0", "co_1") AS "qu_2"
               LEFT JOIN (SELECT "ta_0"."id" AS "co_0", COUNT("ta_0"."name") AS "co_2"
                          FROM "db"."products" AS "ta_0"
                          GROUP BY "co_0") AS "qu_3" ON "qu_2"."co_0" = "qu_3"."co_0"
      GROUP BY "co_1") AS "qu_0"
         INNER JOIN (SELECT "qu_4"."co_1" AS "co_1", SUM("qu_5"."co_5") AS "co_7", AVG("qu_5"."co_6") AS "co_8"
                     FROM (SELECT "ta_2"."id" AS "co_4", "ta_1"."city" AS "co_1"
                           FROM "db"."sales" AS "ta_2"
                                    INNER JOIN "db"."stores" AS "ta_1" ON "ta_1"."id" = "ta_2"."storeId"
                           WHERE "ta_1"."city" IN ('New York', 'Chicago')
                           GROUP BY "co_4", "co_1") AS "qu_4"
                              LEFT JOIN (SELECT "ta_2"."id"                     AS "co_4",
                                                SUM("ta_2"."sales")             AS "co_5",
                                                AVG("ta_2"."quantityPurchased") AS "co_6"
                                         FROM "db"."sales" AS "ta_2"
                                         GROUP BY "co_4") AS "qu_5" ON "qu_4"."co_4" = "qu_5"."co_4"
                     GROUP BY "co_1") AS "qu_1" ON "qu_0"."co_1" = "qu_1"."co_1"
ORDER BY "City" ASC LIMIT 10 OFFSET 1
```

In the compiled SQL above, the join relations of multi-table are calculated by Flat-QL, `COUNT` and `SUM` aggregate functions are
separated in isolated sub-queries for preventing over-counting(or duplicated data generated by Cartesian Product), and filters are 
applied to the `COUNT` and `SUM` aggregates respectively. Finally, the aggregates of subquery are joined with `INNER JOIN` for 
matching rows in both aggregates, and join condition is grouping column.

## Goals

* [x] Support normal aggregate functions(sum, count, avg. etc)
* [x] Support Logical Expressions and Binary Expressions
* [x] Normalize SQL functions for various database (ANSI SQL, Clickhouse)
* [ ] Support Pivot and Unpivot for data analysis
* [ ] Support Window Functions encapsulated as a function call in SQL-like language
* [ ] Support more functions for complex filtering