In this tutorial we will demonstrate how CoginitiScript could be used to dynamically build SQL queries.
CoginitiScript have a builtin code preprocessor (similar to template engine), which runs on the code before it gets passed to the execution. This preprocessor supports interpolated expressions and control flow statements like conditionals and loops.
Suppose we have a scores
table which contains information about players and scores they got on each date.
CREATE TABLE scores AS SELECT 'John' AS name, '2012-01-01' AS score_date, 3 AS score UNION ALL SELECT 'John' AS name, '2012-01-01' AS score_date, 2 AS score UNION ALL SELECT 'John' AS name, '2012-01-02' AS score_date, 4 AS score UNION ALL SELECT 'Bill' AS name, '2012-01-01' AS score_date, 2 AS score UNION ALL SELECT 'Bill' AS name, '2012-01-02' AS score_date, 3 AS score UNION ALL SELECT 'Mila' AS name, '2012-01-01' AS score_date, 2 AS score UNION ALL SELECT 'Mila' AS name, '2012-01-02' AS score_date, 0 AS score UNION ALL SELECT 'Brian' AS name, '2012-01-01' AS score_date, 2.5 AS score UNION ALL SELECT 'Brian' AS name, '2012-01-02' AS score_date, 2 AS score_date;
name | date | score |
---|---|---|
John | 2012-01-01 | 3 |
John | 2012-01-01 | 2 |
John | 2012-01-02 | 4 |
Bill | 2012-01-01 | 2 |
Bill | 2012-01-02 | 3 |
Mila | 2012-01-01 | 2 |
Mila | 2012-01-02 | 0 |
Brian | 2012-01-01 | 2.5 |
Brian | 2012-01-02 | 2 |
And we need to output data from this table in the following form:
name | 2012-01-01 | 2012-01-02 |
---|---|---|
Bill | 2 | 3 |
Brian | 2.5 | 2 |
John | 5 | 4 |
Mila | 2 | 0 |
To do this we have to write a query to pivot data which in general form could be done in the following way:
SELECT name, SUM(CASE WHEN s.score_date = '2012-01-01' THEN s.score END) AS "2012-01-01", SUM(CASE WHEN s.score_date = '2012-01-02' THEN s.score END) AS "2012-01-02" FROM scores s GROUP BY name ORDER BY name ;
As you can see we have to hardcode every date in the SELECT statement for which we are going to produce result. If you want to add another date, you have to copy the column expression.
SELECT name, SUM(CASE WHEN s.score_date = '2012-01-01' THEN s.score END) AS "2012-01-01", SUM(CASE WHEN s.score_date = '2012-01-02' THEN s.score END) AS "2012-01-02", SUM(CASE WHEN s.score_date = '2012-01-03' THEN s.score END) AS "2012-01-03" -- we added one more day here FROM scores s GROUP BY name ORDER BY name ;
With CoginitiScript, we could simplify it and build a query dynamically, by creating a block which accepts list of dates. Then we will use #+for
loop to iterate over input dates and generate a correspondent column expressions:
#+src sql scoreData(dates) #+begin SELECT name, #+for d : dates separator "," do SUM(CASE WHEN s.score_date = '{{ d }}' THEN s.score END) AS "{{ d }}" #+end FROM scores s GROUP BY name ORDER BY name ; #+end
Now we can get a report by simple calling our scoreData
block and passing list of dates for which we want to get data. As result our block provides a simple and general API for getting a report for any number of days.
SELECT * FROM {{ scoreData(dates=["2012-01-01", "2012-01-02"]) }};
Generating score report using date range from the table
Let’s modify our example and instead of passing list of dates as literals into our scoreData
block let’s read them from the table / block. To achieve this we need to do following:
- make
scoreData
to accept a block rather then list of values - use
iterator
builtin function to iterate over block results and use obtained date to dynamically build column expression - define
GetDates
block to read date range from the table - pass
GetDates
as argument intoscoreData
#+src sql scoreData(dates) #+begin SELECT name, #+for d : iterator(dates()) separator "," do SUM(CASE WHEN s.score_date = '{{ d["report_date"] }}' THEN s.score END) AS "{{ d["report_date"] }}" #+end FROM scores s GROUP BY name ORDER BY name ; #+end #+src sql GetDates() #+begin SELECT '2012-01-01' AS report_date UNION ALL SELECT '2012-01-02' #+end SELECT * FROM {{ scoreData(dates=GetDates) }};