On 2016/05/18 12:26 AM, Jonathan wrote:
> Hi Ryan,
> Thanks for the excellent and comprehensive answer; it seems like CTE's
> are the way to go for this.
> I did a quick google and this tutorial was very helpful (for anyone
> else newly interested in CTE's -
> https://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/
>
> ) - they're a lot simpler than I was expecting based on the code
> snippets I've posted here over time. I think I can even get my head
> around them, though it may take a while to fully grok your response!
I will add a detailed description of what my response CTE does below
this mail, but any questions you may have regarding CTE's or any other
SQL functionality, please feel free to ask - we like answering.
>
> Incidentally, you mentioned that this solution didn't have the "fewest
> iterations" - can I trouble to ask what that would look like? Then I
> can try working backwards and seeing what the differences are.
Actually, I wrote that while I had another layout, and then decided to
put in the more complicated but less iterative version (but did not
amend my statement), so the one I've sent is the already trimmed down
version - apologies.
>
> (Note: having my data type of "DATE" - it worked just fine as you
> suspected it would.)
>
> Many thanks again,
> Jonathan
It's our pleasure.
---------------------------------------------------------------
CTE 101:
WITH dtRange(minDT,maxDT) AS (
SELECT MIN(time_date), '2016-01-15 00:00:00' FROM my_table
-- This first CTE table sets up a single line with 2 columns that is
essentially just values
-- we will use later, a kind of SQL version of initializing variables.
-- The variables being initialized here are minDT which is where the
calendar will start, and
-- maxDT, which is where it will end. I use the MIN() aggregate from
my_table for the prior,
-- and for the latter simply added a date by hand namely 15 January
2016. You could specify
-- any dates you like for both.
), dtAll(dt) AS (
SELECT minDT FROM dtRange
UNION ALL
SELECT datetime(dt,'+1 day') FROM dtAll,dtRange WHERE dt < maxDT
-- This second CTE basically creates a list of all the dates we are
interested in (whether
-- they exist in my_table or not). It starts by selecting the minDT
from our previously made
-- dtRange CTE, and then adds 1 day repeatedly in the recursive part
of this recursive CTE
-- until we reach the maxDT (again, from the joined dtRange table).
), my_full(time_date, num) AS (
SELECT time_date, num FROM my_table
UNION ALL
SELECT DT, 0 FROM dtAll
-- This CTE does the magic. We simply add the dates from the
original my_table along with
-- their "num" values first. We then list all the made-up dates from
the longer date list
-- CTE (dtAll) using the UNION, but with Zero values for "num" (so
that it doesn't register
-- in the SUM() aggregate later).
-- NOTE: we could be more efficient here in data-size terms by only
adding dates that
-- doesn't already exist in my_table, but in practice the time taken
for the lookup dwarfs
-- the time added by the aggregate calcs.
)
SELECT date(time_date) as time_date, sum(num) AS sum_num
FROM my_full
GROUP BY time_date
ORDER BY time_date ASC
-- Finally, in the actual SELECT part, we refer the my_full CTE and
SUM() the num values
-- which shows all the aggregates for all dates - which happen to be
Zero for all our
-- "made-up" dates, but have values for all dates found in the
my_table table.
-- It's just like any other programming language with an elaborate
for-loop construct. :)
;
Hope it helped. If you happen to be on Windows you could find the
SQLitespeed DB manager from
http://www.rifin.co.za/software/sqlc/download/
and install it with ticking the option to add the example scripts.
In there are quite a few CTE
examples and tutorials (stored to your Documents\SQLiteScripts\
folder I think).
