[sqlite] Summing values by date, returning 0 for missing dates
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).
[sqlite] Summing values by date, returning 0 for missing dates
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! 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. (Note: having my data type of "DATE" - it worked just fine as you suspected it would.) Many thanks again, Jonathan On 13/05/2016 23:25, R Smith wrote: > > > On 2016/05/13 4:56 PM, Jonathan Moules wrote: >> Hi Simon, >>Sorry, maybe we're crossing wires, but I'm not sure to what you're >> referring. How is defining the type as DATE impeding my attempt to >> get a value of 0 for non-existent rows? >> Ryan's response with a CTE seems to probably be what I want (not had >> the opportunity to test it yet - CTE's are entirely new to me), but >> looking at it quickly now I see that the type is actually NUMERIC >> there rather than my DATE - perhaps that's to what you're referring. > > The CTE will work great and there is some literature about CTEs in > general we could point you to. If you just want a quick fix for your > situation, just use my example, if you want to understand CTE in > general, feel free to ask or google, it's really worth learning since > it can do some real magic for you. > > As to the date type in my CTE example - apologies, I hadn't even > noticed you had it as DATE, I just used NUMERIC since that's what I > always do for dates. You can still use it as type DATE and achieve the > same results with the CTE (I'm 99% sure - haven't tested it). > > Your confusion about what Simon said might be that (I think) perhaps > Simon misunderstood what you found weird about the results in the > original post and tried to explain why you see that weirdness while > you were on about a different weirdness - so you are simply not on the > same page. > > Either way, good luck with the implementation. One note: The CTE > solution will only work after SQLite version 8.3 I think, so if you > are using a very old version, it might not work. > > > Cheers, > Ryan > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Summing values by date, returning 0 for missing dates
On 2016/05/13 4:56 PM, Jonathan Moules wrote: > Hi Simon, >Sorry, maybe we're crossing wires, but I'm not sure to what you're > referring. How is defining the type as DATE impeding my attempt to get a > value of 0 for non-existent rows? > Ryan's response with a CTE seems to probably be what I want (not had the > opportunity to test it yet - CTE's are entirely new to me), but looking at it > quickly now I see that the type is actually NUMERIC there rather than my DATE > - perhaps that's to what you're referring. The CTE will work great and there is some literature about CTEs in general we could point you to. If you just want a quick fix for your situation, just use my example, if you want to understand CTE in general, feel free to ask or google, it's really worth learning since it can do some real magic for you. As to the date type in my CTE example - apologies, I hadn't even noticed you had it as DATE, I just used NUMERIC since that's what I always do for dates. You can still use it as type DATE and achieve the same results with the CTE (I'm 99% sure - haven't tested it). Your confusion about what Simon said might be that (I think) perhaps Simon misunderstood what you found weird about the results in the original post and tried to explain why you see that weirdness while you were on about a different weirdness - so you are simply not on the same page. Either way, good luck with the implementation. One note: The CTE solution will only work after SQLite version 8.3 I think, so if you are using a very old version, it might not work. Cheers, Ryan
[sqlite] Summing values by date, returning 0 for missing dates
On 13 May 2016, at 3:56pm, Jonathan Moules wrote: > Sorry, maybe we're crossing wires, but I'm not sure to what you're > referring. How is defining the type as DATE impeding my attempt to get a > value of 0 for non-existent rows? > Ryan's response with a CTE seems to probably be what I want (not had the > opportunity to test it yet - CTE's are entirely new to me), but looking at it > quickly now I see that the type is actually NUMERIC there rather than my DATE > - perhaps that's to what you're referring. Yes, that's it. You are specifying DATE as your type. SQLite understands that as meaning NUMERIC. You then put values into the columns which look like 2016-01-14 It's perfectly understandable that some part of your setup parses the string '2016-01-14', tries to make a number out of it, and comes up with a result of '2016', since that's where the number stops. Simon.
[sqlite] Summing values by date, returning 0 for missing dates
Hi Simon, Sorry, maybe we're crossing wires, but I'm not sure to what you're referring. How is defining the type as DATE impeding my attempt to get a value of 0 for non-existent rows? Ryan's response with a CTE seems to probably be what I want (not had the opportunity to test it yet - CTE's are entirely new to me), but looking at it quickly now I see that the type is actually NUMERIC there rather than my DATE - perhaps that's to what you're referring. Cheers, Jonathan On Thu, 12 May 2016 18:29:47 +0100 Simon Slavinslavins at bigfraud.org wrote On 12 May 2016, at 3:55pm, Jonathan Moules jonathan-lists at lightpear.com wrote: I know that the "DATE" type isn't actually a type in SQLite and that there are no date-specific constraints, but I find it a convenient indicator (to me and potentially anyone else who's going to see the code) as to the type of data that is to be held in that column. I figured that's why those "type" synonyms exist (I use DATETIME as well!) What you don't mention there is that defining that column as DATE is what's causing the weird-looking results you asked about. Simon. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Summing values by date, returning 0 for missing dates
On 12 May 2016, at 3:55pm, Jonathan Moules wrote: > I know that the "DATE" type isn't actually a type in SQLite and that there > are no date-specific constraints, but I find it a convenient indicator (to me > and potentially anyone else who's going to see the code) as to the type of > data that is to be held in that column. I figured that's why those "type" > synonyms exist (I use DATETIME as well!) What you don't mention there is that defining that column as DATE is what's causing the weird-looking results you asked about. Simon.
[sqlite] Summing values by date, returning 0 for missing dates
Hi Simon, I know that the "DATE" type isn't actually a type in SQLite and that there are no date-specific constraints, but I find it a convenient indicator (to me and potentially anyone else who's going to see the code) as to the type of data that is to be held in that column. I figured that's why those "type" synonyms exist (I use DATETIME as well!) Cheers, Jonathan On Wed, 11 May 2016 23:45:44 +0100 Simon Slavinslavins at bigfraud.org wrote On 11 May 2016, at 11:20pm, Jonathan jonathan-lists at lightpear.com wrote: // time_date DATE,// SQLite has no DATE type. Put your data in that table and try this command: SELECT time_date,typeof(time_date) FROM my_table Then try it again, this time defining that column as TEXT. Simon. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Summing values by date, returning 0 for missing dates
I should add...
The initial CTE setting up minDT and maxDT can be ANY dates, it doesn't
have to come from the MIN/MAX in my_table, for example:
WITH dtRange(minDT,maxDT) AS (
SELECT '2015-12-30 00:00:00', datetime(date('now','localtime','-3
months'))
-- 30 December 2015 to today 3 months ago, which is 12th Feb 2016
for me
), dtAll(dt) AS (
SELECT minDT FROM dtRange
UNION ALL
SELECT datetime(dt,'+1 day') FROM dtAll,dtRange WHERE dt < maxDT
), my_full(time_date, num) AS (
SELECT time_date, num FROM my_table
UNION ALL
SELECT DT, 0 FROM dtAll
)
SELECT date(time_date) as time_date, sum(num) AS sum_num
FROM my_full
GROUP BY time_date
ORDER BY time_date ASC
;
-- time_date| sum_num
-- | ---
-- 2015-12-30 |0
-- 2015-12-31 |0
-- 2016-01-01 |4
-- 2016-01-02 |0
-- 2016-01-03 |2
-- 2016-01-04 |0
-- 2016-01-05 |0
-- 2016-01-06 |0
-- 2016-01-07 |0
-- 2016-01-08 |0
-- 2016-01-09 |0
-- 2016-01-10 |0
-- 2016-01-11 |0
-- 2016-01-12 |0
-- 2016-01-13 |0
-- 2016-01-14 |0
-- 2016-01-15 |0
-- 2016-01-16 |0
-- 2016-01-17 |0
-- 2016-01-18 |0
-- 2016-01-19 |0
-- 2016-01-20 |0
-- 2016-01-21 |0
-- 2016-01-22 |0
-- 2016-01-23 |0
-- 2016-01-24 |0
-- 2016-01-25 |0
-- 2016-01-26 |0
-- 2016-01-27 |0
-- 2016-01-28 |0
-- 2016-01-29 |0
-- 2016-01-30 |0
-- 2016-01-31 |0
-- 2016-02-01 |0
-- 2016-02-02 |0
-- 2016-02-03 |0
-- 2016-02-04 |0
-- 2016-02-05 |0
-- 2016-02-06 |0
-- 2016-02-07 |0
-- 2016-02-08 |0
-- 2016-02-09 |0
-- 2016-02-10 |0
-- 2016-02-11 |0
-- 2016-02-12 |0
Cheers,
Ryan
[sqlite] Summing values by date, returning 0 for missing dates
On 2016/05/12 12:20 AM, Jonathan wrote:
> Hi List,
> Let's say I have a table with a simplified structure like this:
> /create table my_table(//
> //time_date DATE,//
> //num INTEGER//
> //);/
>
> My data has three rows and looks like this:
> /2016-01-01; 3//
> //2016-01-01; 1//
> //2016-01-03; 2/
>
> I want to return the sum of "num" for any given time_date,
> grouped by day, so I have:
>
> / SELECT time_date,//
> // sum( num ) AS sum_num//
> // FROM my_table//
> //
> //GROUP BY time_date//
> //ORDER BY time_date ASC/
>
> This returns:
> /2016-01-01; 4//
> //2016-01-03; 2/
>
> But what I want is for a value of "0" to be returned for the
> "missing" date 2016-01-02:
> /2016-01-01; 4//
> //2016-01-02; 0//
> //2016-01-03; 2/
>
> Is this possible? I'm imagining a sub-query with a start and end
> date ("now") but I can't think of how to get SQLite to return data
> that it doesn't have.
> Does anyone have any suggestions for how to achieve this?
A simple CTE can do this as follows: (Note - it can be done with less
iterations, but this example makes clear what is happening)
-- SQLite version 3.9.2 [ Release: 2015-11-02 ] on SQLitespeed
version 2.0.2.4.
-- Script Items: 4 Parameter Count: 0
-- 2016-05-12 00:53:22.084 | [Info] Script Initialized,
Started executing...
--
CREATE TABLE my_table(
time_date NUMERIC,
num INTEGER
);
INSERT INTO my_table VALUES
('2016-01-01 00:00:00', 3),
('2016-01-01 00:00:00', 1),
('2016-01-03 00:00:00', 2);
-- Without CTE:
SELECT time_date, sum( num ) AS sum_num
FROM my_table
GROUP BY time_date
ORDER BY time_date ASC
;
-- time_date | sum_num
-- - | ---
-- 2016-01-01 00:00:00 |4
-- 2016-01-03 00:00:00 |2
-- With CTE:
WITH dtRange(minDT,maxDT) AS (
SELECT MIN(time_date), MAX(time_date) FROM my_table
), dtAll(dt) AS (
SELECT minDT FROM dtRange
UNION ALL
SELECT datetime(dt,'+1 day') FROM dtAll,dtRange WHERE dt < maxDT
), my_full(time_date, num) AS (
SELECT time_date, num FROM my_table
UNION ALL
SELECT DT, 0 FROM dtAll
)
SELECT date(time_date) as time_date, sum(num) AS sum_num
FROM my_full
GROUP BY time_date
ORDER BY time_date ASC
;
-- time_date| sum_num
-- | ---
-- 2016-01-01 |4
-- 2016-01-02 |0
-- 2016-01-03 |2
Cheers!
Ryan
[sqlite] Summing values by date, returning 0 for missing dates
On 11 May 2016, at 11:20pm, Jonathan wrote: > //time_date DATE,// SQLite has no DATE type. Put your data in that table and try this command: SELECT time_date,typeof(time_date) FROM my_table Then try it again, this time defining that column as TEXT. Simon.
[sqlite] Summing values by date, returning 0 for missing dates
Hi List,
Let's say I have a table with a simplified structure like this:
/create table my_table(//
//time_date DATE,//
//num INTEGER//
//);/
My data has three rows and looks like this:
/2016-01-01; 3//
//2016-01-01; 1//
//2016-01-03; 2/
I want to return the sum of "num" for any given time_date, grouped
by day, so I have:
/ SELECT time_date,//
// sum( num ) AS sum_num//
// FROM my_table//
//
//GROUP BY time_date//
//ORDER BY time_date ASC/
This returns:
/2016-01-01; 4//
//2016-01-03; 2/
But what I want is for a value of "0" to be returned for the
"missing" date 2016-01-02:
/2016-01-01; 4//
//2016-01-02; 0//
//2016-01-03; 2/
Is this possible? I'm imagining a sub-query with a start and end
date ("now") but I can't think of how to get SQLite to return data that
it doesn't have.
Does anyone have any suggestions for how to achieve this?
Thanks,
Jonathan
