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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
