sqlitemicrosoft.data.sqlite

WITH RECURSIVE looping through every day in the database and SUM number between two dates for each


I struggle with the last part below, the "with recursive". Of course I could loop over TimestampOrigin using C#, for every day in the database. Which would mean hundreds of times the same query. But it may be possible with one query using "with recursive".

Test data:

CREATE TABLE tblData(
  Id INT, ComputerName TEXT, TimestampOrigin TEXT, Timestamp TEXT, Number INT
);

DELETE FROM tblData;

INSERT INTO tblData VALUES (1, "Computer1", '2021-02-10 12:00:00', '2021-02-27 12:00:00', 35);
INSERT INTO tblData VALUES (2, "Computer2", '2021-02-10 12:00:00', '2021-02-27 12:00:00', 24);
INSERT INTO tblData VALUES (3, "Computer3", '2021-02-09 12:00:00', '2021-02-26 12:00:00', 23);
INSERT INTO tblData VALUES (3, "Computer4", '2021-02-09 12:00:00', '2021-02-26 12:00:00', null);
INSERT INTO tblData VALUES (4, "Computer5", '2021-02-08 12:00:00', '2021-02-25 12:00:00', 7);
INSERT INTO tblData VALUES (5, "Computer6", '2021-02-08 12:00:00', '2021-02-25 12:00:00', 0);
INSERT INTO tblData VALUES (7, "Computer7", '2021-02-07 12:00:00', '2021-02-24 12:00:00', 9);

Query grouped by TimestampOrigin:

SELECT DATE(TimestampOrigin) AS TimestampOrigin,
SUM(CASE WHEN Number < 1 THEN 1 ELSE 0 END) AS Less1,
SUM(CASE WHEN Number >= 0 AND Number < 10 THEN 1 ELSE 0 END) AS Less10,
SUM(CASE WHEN Number >= 10 AND Number < 25 THEN 1 ELSE 0 END) AS Less25
FROM tblData WHERE Number NOT NULL GROUP BY DATE(TimestampOrigin) ORDER BY TimestampOrigin DESC

What I need is for every day the sum for the current up to Timestamp which is current day +17 days. Example for day 2021-02-08, sum of all rows with TimestampOrigin 2021-02-08 up to 2021-02-08 +17 days (column Timestamp). Don't know if the extra column Timestamp which is noon time of TimestampOrigin +17 days is really required. But the over time query was the reason why I created it at the very beginning of the project.

SELECT DATE(TimestampOrigin) AS TimestampOrigin,
SUM(CASE WHEN Number < 1 THEN 1 ELSE 0 END) AS Less1,
SUM(CASE WHEN Number >= 0 AND Number < 10 THEN 1 ELSE 0 END) AS Less10,
SUM(CASE WHEN Number >= 10 AND Number < 25 THEN 1 ELSE 0 END) AS Less25
FROM tblData WHERE Number NOT NULL AND DATE(TimestampOrigin) >= DATE('2021-02-08') AND DATE(TimestampOrigin) <= DATE('2021-02-08', '+17 day')

Instead of executing the above query hundreds of times for each day and sum, I thought "with recursive" is the right approach". But was not able so far to make it work. Where to add the 17 days?

WITH RECURSIVE cte AS (
    SELECT Id, ComputerName, Timestamp, DATE(Timestamp,'+1 day') totime, Number, TimestampOrigin
    FROM tblData
    UNION ALL
    SELECT Id, ComputerName, DATE(Timestamp,'+1 day'), DATE(totime,'+1 day'), Number, TimestampOrigin
    FROM cte
    WHERE Number NOT NULL AND DATE(Timestamp,'+1 day') < DATE('2021-02-11')
)
    SELECT DATE(TimestampOrigin), 
    SUM(CASE WHEN Number < 1 THEN 1 ELSE 0 END) AS Less1,
    SUM(CASE WHEN Number >= 0 AND Number < 10 THEN 1 ELSE 0 END) AS Less10,
    SUM(CASE WHEN Number >= 10 AND Number < 25 THEN 1 ELSE 0 END) AS Less25
FROM cte GROUP BY DATE(TimestampOrigin) ORDER BY DATE(TimestampOrigin) DESC

Expected result would be (Like when I would run the above query for each of the 4 days in the test data):

enter image description here


Solution

  • There is no need for a recursive CTE.
    Join the distinct TimestampOrigins to the table under your condition and aggregate:

    SELECT t1.TimestampOrigin,
           SUM(t2.Number < 1) AS Less1,
           SUM(t2.Number >= 0 AND t2.Number < 10) AS Less10,
           SUM(t2.Number >= 10 AND t2.Number < 25) AS Less25
    FROM (SELECT DISTINCT DATE(TimestampOrigin) TimestampOrigin FROM tblData) t1 
    INNER JOIN tblData t2
    ON DATE(t2.TimestampOrigin) BETWEEN t1.TimestampOrigin AND DATE(t1.TimestampOrigin, '+17 days')
    GROUP BY t1.TimestampOrigin 
    ORDER BY t1.TimestampOrigin DESC
    

    See the demo.
    Results:

    TimestampOrigin Less1 Less10 Less25
    2021-02-10 0 0 1
    2021-02-09 0 0 2
    2021-02-08 1 2 2
    2021-02-07 1 3 2