Quantcast

Generalized SQLite stored procedure style pivot table exhibit.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Generalized SQLite stored procedure style pivot table exhibit.

petern
Here I revisit the pivot table problem using the SQLite stored procedure
pattern and pure unmodified SQLite.  I am seeking feedback for improving
the brevity of the stored procedure presented here.  This time around I
went with generating pivot views instead of pivot tables since that was the
more popular modality in the remarks to my previous pivot table exhibit
using a TCL enabled version of shell.c.

Please feel free to use/modify/extend the stored procedure presented to
generate pivot view analytics for your own data tables and share any
improvements.  It will work without modification under any recent release
of SQLite for any suitable data table with uncomplicated column names.

Below, in three sections, I follow the usual composition format of this
forum.
Section 1: a simple data table specimen used in subsequent examples.
Section 2: exemplars of how to call the stored procedure.
Section 3: the stored procedure listing.

--SECTION 1-----------------------------------------------
--A trivial(for illustration) fruit sales table specimen--

sqlite> INSERT INTO quarterly_sales
VALUES(2016,2,'mangos',3),(2016,3,'peaches',2),(2017,2,'apples',7),(2017,2,'peaches',6),(2016,3,'mangos',12);

sqlite> SELECT * FROM quarterly_sales;
year        quarter     fruit       tons
----------  ----------  ----------  ----------
2016        2           mangos      3
2016        3           peaches     2
2017        2           apples      7
2017        2           peaches     6
2016        3           mangos      12

--SECTION 2----------------------------------------------------------------
--Examplar SQLite stored procedure pivot view generation calls--

--Total annual sales by fruit--
sqlite> INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','sum');

sqlite> SELECT * FROM pivot_view_quarterly_sales_year_fruit_tons_sum;
year        apples      mangos      peaches
----------  ----------  ----------  ----------
2016                    15          2
2017        7                       6

--Minimum quarterly sales by fruit--
sqlite> INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','min');

sqlite> SELECT * FROM pivot_view_quarterly_sales_year_fruit_tons_min;
year        apples      mangos      peaches
----------  ----------  ----------  ----------
2016                    3           2
2017        7                       6

--Maximum quarterly sales by fruit--
sqlite> INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','max');

sqlite> SELECT * FROM pivot_view_quarterly_sales_year_fruit_tons_max;
year        apples      mangos      peaches
----------  ----------  ----------  ----------
2016                    12          2
2017        7                       6

--Average quarterly sales by fruit--
sqlite> INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','avg');

sqlite> SELECT * FROM pivot_view_quarterly_sales_year_fruit_tons_avg;
year        apples      mangos      peaches
----------  ----------  ----------  ----------
2016                    7.5         2.0
2017        7.0                     6.0

--SECTION 3--------------------------------------------------------
--SQLite Stored Procedure "generate_pivot1_view" listing--

--Note that "pivot1" is the simplest case with just one(1) pivoting
aggregate.
--Pivot2,pivot3,...pivotN are left as an exercise for the reader.
--Other trivial improvements such as additional $row columns and HAVING
clause are also too obvious so yes, I am aware of those missing details.
Those minor things were also left as an exercise and omitted for brevity.

--However, please do comment with your suggestions for improving the
brevity of the procedure body in the areas of nesting depth of the string
replace or deep insights about quotation mark escaping/elimination that
will shorten this code.  As well, if an external helper extension function
would make this family of stored procedures easier to write, that code
would have to 1) be robust and 2) be made available, and 3) be trivial to
compile and load.

DROP VIEW IF EXISTS generate_pivot1_view;
CREATE VIEW generate_pivot1_view AS SELECT
(null)tbl,(null)row,(null)col1,(null)val1,(null)aggfn1;

DROP TRIGGER IF EXISTS generate_pivot1_view;
CREATE TRIGGER generate_pivot1_view INSTEAD OF INSERT ON
generate_pivot1_view
BEGIN
SELECT eval(replace(replace(replace(replace(replace(replace(
'DROP VIEW IF EXISTS pivot_view_$tbl_$row_$col1_$val1_$aggfn1;CREATE VIEW
pivot_view_$tbl_$row_$col1_$val1_$aggfn1 '
||'AS SELECT $row, $cols FROM $tbl GROUP BY $row'
,'$tbl',new.tbl),'$row',new.row),'$col1',new.col1),'$val1',new.val1),'$aggfn1',new.aggfn1)
,'$cols',(
  SELECT eval(replace(replace(replace(replace(
    'SELECT group_concat(''$aggfn1(CASE WHEN $col1=''||quote(col)||'' THEN
$val1 END)''||col) '
    ||'FROM (SELECT DISTINCT ($col1)col FROM $tbl ORDER BY col)'

,'$tbl',new.tbl),'$col1',new.col1),'$val1',new.val1),'$aggfn1',new.aggfn1))cols

  )
))ddl;
END;

--------------------------------------------------------------------------------------
[FYI.  The SQLite eval extension is part of the SQLite distribution and is
presently located at https://www.sqlite.org/src/artifact/f971962e92ebb8b0
]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Generalized SQLite stored procedure style pivot table exhibit.

E.Pasma
15 mei 2017, 07:34 petern:

> Here I revisit the pivot table problem using the SQLite stored  
> procedure
> pattern and pure unmodified SQLite.  I am seeking feedback for  
> improving
> the brevity of the stored procedure presented here.

Hi, initially I got: near "eval": syntax error. This is after saving  
the mail message as a text file and taking that in the editor two  
become an sql script. This apparently leaves some two-byte spaces,  
causing this humanly invisable error.
How to fix this in VI I don't know.
Downloading the text by simple copy and paste cures fixed the problem.
The procedure works great and can easily be customized.
I'd generate a temporary view as one must not be tempted to keep it  
(and miss new fruits).
Thanks, E. Pasma

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Generalized SQLite stored procedure style pivot table exhibit.

J. King-3
On May 16, 2017 7:32:41 PM EDT, "E.Pasma" <[hidden email]> wrote:

>15 mei 2017, 07:34 petern:
>
>> Here I revisit the pivot table problem using the SQLite stored  
>> procedure
>> pattern and pure unmodified SQLite.  I am seeking feedback for  
>> improving
>> the brevity of the stored procedure presented here.
>
>Hi, initially I got: near "eval": syntax error. This is after saving  
>the mail message as a text file and taking that in the editor two  
>become an sql script. This apparently leaves some two-byte spaces,  
>causing this humanly invisable error.
>How to fix this in VI I don't know.
>Downloading the text by simple copy and paste cures fixed the problem.
>The procedure works great and can easily be customized.
>I'd generate a temporary view as one must not be tempted to keep it  
>(and miss new fruits).
>Thanks, E. Pasma
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

The eval function is provided by an extension:

<http://www.sqlite.org/cgi/src/artifact/f971962e92ebb8b0>
--
J. King
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Generalized SQLite stored procedure style pivot table exhibit.

petern
In reply to this post by E.Pasma
Some text presentation software may be sensitive to the necessary quote
escaping regime.  In this case, double single quotes are necessary to
escape interior single quotes of the nested literals.  Perhaps one or both,
mine or yours, email client(s) is producing an undesired transformation of
nearby characters or quotation marks?

About view synchronization. This is practical point I forgot to mention.
Pivot views are conveniently synchronized by a data table trigger.  For
example:

-----------
DROP TRIGGER IF EXISTS quarterly_sales_fruit_insert;
CREATE TRIGGER quarterly_sales_fruit_insert AFTER INSERT ON quarterly_sales
BEGIN
  INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','sum');
  INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','min');
  INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','max');
  INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','avg');
END;

--Now add 7 tons of banana sales to Q3 of 2017 and see what happens--
INSERT INTO quarterly_sales VALUES(2017,3,'bananas',7);

sqlite> SELECT * FROM pivot_view_quarterly_sales_year_fruit_tons_sum;

year        apples      bananas     mangos      peaches
----------  ----------  ----------  ----------  ----------
2016                                30          4
2017        14          7                       12
-----------

[The quarterly_sales table could itself also be a view.  Then the view
synchronizing trigger could be more precisely applied on a normalized
fruit_type table.]














On Tue, May 16, 2017 at 4:32 PM, E.Pasma <[hidden email]> wrote:

> 15 mei 2017, 07:34 petern:
>
> Here I revisit the pivot table problem using the SQLite stored procedure
>> pattern and pure unmodified SQLite.  I am seeking feedback for improving
>> the brevity of the stored procedure presented here.
>>
>
> Hi, initially I got: near "eval": syntax error. This is after saving the
> mail message as a text file and taking that in the editor two become an sql
> script. This apparently leaves some two-byte spaces, causing this humanly
> invisable error.
> How to fix this in VI I don't know.
> Downloading the text by simple copy and paste cures fixed the problem.
> The procedure works great and can easily be customized.
> I'd generate a temporary view as one must not be tempted to keep it (and
> miss new fruits).
> Thanks, E. Pasma
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Generalized SQLite stored procedure style pivot table exhibit.

petern
In reply to this post by E.Pasma
It just occurred to me that I didn't completely finish my thought in the
earlier reply.
The suggested pivot views synchronization TRIGGER is named
quarterly_sales_fruit_insert instead of simply quarterly_sales_insert for a
specific reason.  There is no need to regenerate pivot views unless the
fruit set changes.

So, the more complete solution is to use the implied monotonic rowid column
to condition view regeneration if and only if a new fruit type was added to
the quarterly_sales table.  See the WHEN clause below for how that works.
[Also, obviously, similar UPDATE and DELETE triggers will also needed if
the sales table must accommodate changes to existing records.]

DROP TRIGGER IF EXISTS quarterly_sales_fruit_insert;
CREATE TRIGGER quarterly_sales_fruit_insert AFTER INSERT ON quarterly_sales
WHEN new.fruit NOT IN (SELECT DISTINCT fruit from quarterly_sales WHERE
rowid<new.rowid)
BEGIN
  INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','sum');
  INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','min');
  INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','max');
  INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','avg');
END;


On Tue, May 16, 2017 at 4:32 PM, E.Pasma <[hidden email]> wrote:

> 15 mei 2017, 07:34 petern:
>
> Here I revisit the pivot table problem using the SQLite stored procedure
>> pattern and pure unmodified SQLite.  I am seeking feedback for improving
>> the brevity of the stored procedure presented here.
>>
>
> Hi, initially I got: near "eval": syntax error. This is after saving the
> mail message as a text file and taking that in the editor two become an sql
> script. This apparently leaves some two-byte spaces, causing this humanly
> invisable error.
> How to fix this in VI I don't know.
> Downloading the text by simple copy and paste cures fixed the problem.
> The procedure works great and can easily be customized.
> I'd generate a temporary view as one must not be tempted to keep it (and
> miss new fruits).
> Thanks, E. Pasma
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...