My solution works, and it replaces a lot of crappy Swift code to transform the data.
The SQL produces data that does not need any transformation at all.
However, the solution is not very 'swifty', any ideas how to improve?
This is about a graph, showing the frequency of waiting times for my wife's patients, i.e., the difference between the create data and the visit date of an visit in the Mac Calendar.
The issue in the graph data is that the data range may not be continuous, i.e., for a given time period, there are no visits with a waiting time of X weeks, like in the image for 0, 2 and 3 weeks of visit's age. Solved by left joining table 'alle' with a generated data range 'visitAgeRange'.
In addition to the graph shown in the image, I also want the sale graph as a running total up to 100%.
Solved with a window function in 'alleCum', using 'total' from another CTE table.
visitAges = try db.read { db in
let visitAgeRangeCTE = CommonTableExpression(
recursive: true,
named: "visitAgeRange",
columns: ["type", "visitAge", "visitCount"],
literal:
"""
WITH RECURSIVE
visitAgeRange(visitAge) AS (
VALUES(0)
UNION ALL
SELECT visitAge+1 FROM visitAgeRange WHERE visitage <
(
SELECT max(visitAge)
FROM visit
WHERE visit.visitDate BETWEEN \(dateStart) AND \(dateEnd)
AND visitCalendar IN ('Marieke', 'Marieke nieuwe')
)
),
visitCountTotal AS (
SELECT CAST(count(*) AS FLOAT) AS total
FROM visit
WHERE visitDate BETWEEN \(dateStart) AND \(dateEnd)
AND visitCalendar IN ('Marieke', 'Marieke nieuwe')
),
alle AS (
SELECT visitAgeRange.visitAge, count(visit.visitAge) AS visitCount
FROM visitAgeRange
LEFT JOIN visit
ON visitAgeRange.visitage = visit.visitAge
AND visit.visitDate BETWEEN \(dateStart) AND \(dateEnd)
AND visitCalendar IN ('Marieke', 'Marieke nieuwe')
GROUP BY visitAgeRange.visitAge
)
SELECT 'alle' AS type, visitAge, visitCount
FROM alle
UNION
SELECT 'alleCum' AS type, visitAge,
SUM(visitCount / total) OVER (ORDER BY visitAge) AS visitCount
FROM alle, visitCountTotal
ORDER BY type, visitAge
"""
)
let request = visitAgeRangeCTE.all().with(visitAgeRangeCTE)
return try VisitAges1.fetchAll(db, request)
By the way, two notes:
I had probleem with data interpolation and it took me a day to see the difference of a SQL Request with sql: and literal:; maybe you can emphasise that in the documentation.
I found a small typo in the CTE documentation:
// An association from LeftRecord to rightCTE
let rightCTE = ...
let association = LeftRecord.association(
to: rightCTE,
on: { left, right in
left[Column("x")] = right[Column("y")]
})
The = must be a double ==.
As said I replace a lot of complex Swift .map. .reduce and .filter to transform the original data.
However, any ideas to do the CTE in a more Swifty way?
What did you do?
Built a query using recursive CTE
What did you expect to happen?
My solution works, and it replaces a lot of crappy Swift code to transform the data. The SQL produces data that does not need any transformation at all. However, the solution is not very 'swifty', any ideas how to improve?
What happened instead?
Environment
GRDB flavor(s): (GRDB) GRDB version: master Installation method: (SPM) Xcode version: latest Swift version: 5 Platform(s) running GRDB: (MacCatalyst) macOS version running Xcode: latest
Demo Project
This is about a graph, showing the frequency of waiting times for my wife's patients, i.e., the difference between the create data and the visit date of an visit in the Mac Calendar.
The issue in the graph data is that the data range may not be continuous, i.e., for a given time period, there are no visits with a waiting time of X weeks, like in the image for 0, 2 and 3 weeks of visit's age. Solved by left joining table 'alle' with a generated data range 'visitAgeRange'.
In addition to the graph shown in the image, I also want the sale graph as a running total up to 100%. Solved with a window function in 'alleCum', using 'total' from another CTE table.
By the way, two notes:
The = must be a double ==.
As said I replace a lot of complex Swift .map. .reduce and .filter to transform the original data. However, any ideas to do the CTE in a more Swifty way?
Regards, André Hartman