Preface
For a while, I was wondering about applying the looping/iterable functions in QGIS's Virtual Layer. There were several cases when the application of a loop would be beneficial and useful, e.g. Intersect multiple layers at once QGIS or Incorrect result while adding points along geometry in QGIS.
There is one polyline layer that represents flows (attributes: "ORIGINE"
, "DEST"
, "FLUX"
, and "DIST_KM"
). Its attribute table looks as following.
My desire is to obtain some information based on categories and corresponding rules for each category.
By any means, this query will give what I actually need
SELECT COUNT(*) AS anzahl, SUM(FLUX) AS summe
FROM "flows_workday_GK5"
WHERE "DIST_KM" > 0 AND "DIST_KM" < 1
UNION ALL
SELECT COUNT(*) AS anzahl, SUM(FLUX) AS summe
FROM "flows_workday_GK5"
WHERE "DIST_KM" >= 1 AND "DIST_KM" < 2
UNION ALL
SELECT COUNT(*) AS anzahl, SUM(FLUX) AS summe
FROM "flows_workday_GK5"
WHERE "DIST_KM" >= 2 AND "DIST_KM" < 3
UNION ALL
SELECT COUNT(*) AS anzahl, SUM(FLUX) AS summe
FROM "flows_workday_GK5"
WHERE "DIST_KM" >= 3 AND "DIST_KM" < 4
UNION ALL
SELECT COUNT(*) AS anzahl, SUM(FLUX) AS summe
FROM "flows_workday_GK5"
WHERE "DIST_KM" >= 4
Output
In the above example, I showed only five categories, but what if I will need to work with 10, 100, 1000 categories.
Since Virtual Layer supports SQLite library it might be smart to embed a RECURSIVE
expression in the afore query. I was inspired by this article: basic recursive query on sqlite3?
My desire is to achieve a table as following
category | anzahl | summe
1 | 96 | 56996
These are several efforts were done, but somehow I am getting errors.
I. When I try with this query
WITH RECURSIVE cat(x) AS (
SELECT 0
UNION ALL
SELECT (x + 1) AS category, COUNT(*) AS anzahl, SUM(FLUX) AS summe
FROM "flows_workday_GK5", cat
WHERE "DIST_KM" > x AND "DIST_KM" < x + 1
AND x < 4
UNION ALL
SELECT (x + 1) AS category, COUNT(*) AS anzahl, SUM(FLUX) AS summe
FROM "flows_workday_GK5", cat
WHERE "DIST_KM" > x
AND x = 4
)
SELECT *
FROM cat
I end up with this error
II. Even if I simplify the query like
WITH RECURSIVE cat(x) AS (
SELECT 0
UNION ALL
SELECT (x + 1) AS category, COUNT(*) AS anzahl, SUM(FLUX) AS summe
FROM "flows_workday_GK5", cat
WHERE "DIST_KM" > x AND "DIST_KM" < x + 1
AND x < 4
)
SELECT *
FROM cat
In this case, I end up with the following error
So, my question is: How shall I adjust the query to be able to get the result by means of RECURSIVE
function?
The efforts that were made by @M Bain are absolutely brilliant and lead to the desired output. As well as I got to know CAST
-expression. Nevertheless, there are somehow several pitfalls that may lead to the deficient result.
The last 'category' might be incomplete because it won't include all the remained records, see image below. The idea is to aggregate all the rest values in the last 'category'. In my case, the 5th 'category' has to have a '2105282'-value.
ROUND(DIST_KM + 0.5, 0)
owns an inevitable 'rounding effect'. 'category' number at certain point will jump over some essential numbers, see below.- Last but not least, I am truly passionate to learn/understand the
RECURSIVE
technique.
"DIST_KM" > 243 AND "DIST_KM" < 244
=> means that there is no category243
.