Re: [sqlite] How to group this?
Sorry, I made a typo. The windows should be ORDER BY ROWID, not ORDER BY ID. Jen On Mon, Feb 10, 2020 at 09:19:59AM -0700, Jen Pollock wrote: > I think the following works: > > SELECT s.ID 'ID', s.Date Date, Systolic, Diastolic > FROM > (SELECT ID, ENTRY_DATE Date, NUMERIC_VALUE Systolic, row_number() OVER > id_date r > FROM pressure > WHERE TERM = 'Systolic' > WINDOW id_date AS (PARTITION BY ID, ENTRY_DATE ORDER BY ID) > ) s > JOIN > (SELECT ID, ENTRY_DATE Date, NUMERIC_VALUE Diastolic, row_number() OVER > id_date r > FROM pressure > WHERE TERM = 'Diastolic' > WINDOW id_date AS (PARTITION BY ID, ENTRY_DATE ORDER BY ID) > ) d > ON s.ID = d.ID AND s.Date = d.Date AND s.r = d.r > ORDER BY s.ID, s.Date; > > You will need a fairly recent version of SQLite, older versions don't > have window functions. And I have assumed that there is a Diastolic > value for every Systolic value and vice versa. > > Jen > > On Mon, Feb 10, 2020 at 12:24:33AM +, Bart Smissaert wrote: > > ID ENTRY_DATE TERM NUMERIC_VALUE ROWID > > > > 1308 15/Mar/2013 Systolic 127 701559 > > 1308 15/Mar/2013 Diastolic 81 701568 > > 1308 27/Jun/2013 Systolic 132 701562 > > 1308 27/Jun/2013 Systolic 141 701563 > > 1308 27/Jun/2013 Systolic 143 701564 > > 1308 27/Jun/2013 Diastolic 82 701571 > > 1308 27/Jun/2013 Diastolic 85 701572 > > 1308 27/Jun/2013 Diastolic 94 701573 > > 278975701 08/Mar/2018 Systolic 136 1583551 > > 278975701 08/Mar/2018 Diastolic 99 1583591 > > 278975701 04/Apr/2018 Systolic 119 1583552 > > 278975701 04/Apr/2018 Systolic 124 1583553 > > 278975701 04/Apr/2018 Systolic 130 1583554 > > 278975701 04/Apr/2018 Diastolic 74 1583592 > > 278975701 04/Apr/2018 Diastolic 75 1583593 > > 278975701 04/Apr/2018 Diastolic 85 1583594 > > > > These are systolic and diastolic blood pressures for 2 people with the ID's > > 1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc. > > Systolic and diastolic values are a pair and should be grouped in one row. > > This is no problem if there is only one pair for one date, but sometimes > > there multiple pairs per date. > > The pairing should be based on the rowed if there are multiple pairs by > > date, so for ID 1308 > > I should get: > > > > 127/81 > > 132/82 > > 141/85 > > 143/94 > > > > What should be the SQL to group like this? > > > > RBS > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to group this?
I think the following works: SELECT s.ID 'ID', s.Date Date, Systolic, Diastolic FROM (SELECT ID, ENTRY_DATE Date, NUMERIC_VALUE Systolic, row_number() OVER id_date r FROM pressure WHERE TERM = 'Systolic' WINDOW id_date AS (PARTITION BY ID, ENTRY_DATE ORDER BY ID) ) s JOIN (SELECT ID, ENTRY_DATE Date, NUMERIC_VALUE Diastolic, row_number() OVER id_date r FROM pressure WHERE TERM = 'Diastolic' WINDOW id_date AS (PARTITION BY ID, ENTRY_DATE ORDER BY ID) ) d ON s.ID = d.ID AND s.Date = d.Date AND s.r = d.r ORDER BY s.ID, s.Date; You will need a fairly recent version of SQLite, older versions don't have window functions. And I have assumed that there is a Diastolic value for every Systolic value and vice versa. Jen On Mon, Feb 10, 2020 at 12:24:33AM +, Bart Smissaert wrote: > ID ENTRY_DATE TERM NUMERIC_VALUE ROWID > > 1308 15/Mar/2013 Systolic 127 701559 > 1308 15/Mar/2013 Diastolic 81 701568 > 1308 27/Jun/2013 Systolic 132 701562 > 1308 27/Jun/2013 Systolic 141 701563 > 1308 27/Jun/2013 Systolic 143 701564 > 1308 27/Jun/2013 Diastolic 82 701571 > 1308 27/Jun/2013 Diastolic 85 701572 > 1308 27/Jun/2013 Diastolic 94 701573 > 278975701 08/Mar/2018 Systolic 136 1583551 > 278975701 08/Mar/2018 Diastolic 99 1583591 > 278975701 04/Apr/2018 Systolic 119 1583552 > 278975701 04/Apr/2018 Systolic 124 1583553 > 278975701 04/Apr/2018 Systolic 130 1583554 > 278975701 04/Apr/2018 Diastolic 74 1583592 > 278975701 04/Apr/2018 Diastolic 75 1583593 > 278975701 04/Apr/2018 Diastolic 85 1583594 > > These are systolic and diastolic blood pressures for 2 people with the ID's > 1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc. > Systolic and diastolic values are a pair and should be grouped in one row. > This is no problem if there is only one pair for one date, but sometimes > there multiple pairs per date. > The pairing should be based on the rowed if there are multiple pairs by > date, so for ID 1308 > I should get: > > 127/81 > 132/82 > 141/85 > 143/94 > > What should be the SQL to group like this? > > RBS > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New word to replace "serverless"
"Server-free"? It's reasonably close to serverless, but doesn't have the conflicting meaning. Jen On Mon, Jan 27, 2020 at 05:18:45PM -0500, Richard Hipp wrote: > For many years I have described SQLite as being "serverless", as a way > to distinguish it from the more traditional client/server design of > RDBMSes. "Serverless" seemed like the natural term to use, as it > seems to mean "without a server". > > But more recently, "serverless" has become a popular buzz-word that > means "managed by my hosting provider rather than by me." Many > readers have internalized this new marketing-driven meaning for > "serverless" and are hence confused when they see my claim that > "SQLite is serverless". > > How can I fix this? What alternative word can I use in place of > "serverless" to mean "without a server"? > > Note that "in-process" and "embedded" are not adequate substitutes for > "serverless". An RDBMS might be in-process or embedded but still be > running a server in a separate thread. In fact, that is how most > embedded RDBMSes other than SQLite work, if I am not much mistaken. > > When I say "serverless" I mean that the application invokes a > function, that function performs some task on behalf of the > application, then the function returns, *and that is all*. No threads > are left over, running in the background to do housekeeping. The > function does send messages to some other thread or process. The > function does not have an event loop. The function does not have its > own stack. The function (with its subfunctions) does all the work > itself, using the callers stack, then returns control to the caller. > > So what do I call this, if I can no longer use the word "serverless" > without confusing people? > > "no-server"? > "sans-server"? > "stackless"? > "non-client/server"? > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Safe to use SQLite over a sketchy network?
On Tue, Sep 24, 2019 at 03:30:00PM -0700, Jens Alfke wrote: > There will be times when a program using SQLite finds itself running > over a networked filesystem, but no one should deliberately write > SQLite-based code intending to use a networked filesystem. For that > you want a client/server database. SQLite on a networked filesystem works basically fine if the SQLite database will only ever be opened read-only. But that's admittedly an edge case. Jen Pollock ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quick way to determine optimal page size?
Compressing the backups would likely save you a lot more space, and I
suspect it wouldn't be affected that much by page size; presumably empty
space at the ends of pages will compress very well.
Jen Pollock
On Thu, Aug 01, 2019 at 12:48:27AM +0300, Tony Papadimitriou wrote:
> Tens of databases (from a few MBs to almost GB), so it's good to keep them
> at their minimum size (for disk and backup savings).
>
> I often save several megabytes by going to the 'right' size, eg., just today
> I went from ~110MB down to ~80MB in one of them ('vacuum'ed before and after
> so it's just the page size making this difference). Sometimes, very small
> page sizes give best results, sometimes the other way around.
>
> Some databases do well in the same page size as new data is added, but for
> some others you need to recalculate as their content changes.
> Still, you can't know in advance which ones can do better unless you
> actually try it. And, that's the main problem.
> I have to try with ~100 DBs to get a significant benefit in just a few of
> them (about 5-10), until next time.
>
> Anyway, I thought I'd ask.
>
> -Original Message- From: David Raymond
> Sent: Wednesday, July 31, 2019 10:48 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Quick way to determine optimal page size?
>
> Not that I'm aware of no. How much of a difference are you seeing for your
> database size depending on the page size you try?
>
> -Original Message-
> From: sqlite-users On Behalf
> Of Tony Papadimitriou
> Sent: Wednesday, July 31, 2019 3:29 PM
> To: General Discussion of SQLite Database
>
> Subject: [sqlite] Quick way to determine optimal page size?
>
> Instead of brute force “pragma page_size=xxx; vacuum;” for each page size
> and each database to determine which one produces the smallest file, is
> there some quicker way?
>
> Thanks.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Have SQLite handle values of my own type
This is getting pretty far off topic, but I think at least some tools will interpret values with leading zeroes as octal, which means 001 is the same as 1, but 010 isn't 10, it's 8. On Thu, May 23, 2019 at 04:35:02PM +0200, Dominique Devienne wrote: > On Thu, May 23, 2019 at 3:50 PM Jose Isaias Cabrera > wrote: > > > I have been working network for a long time, and I have never seen any > > application that takes "zeroed left-filled" IP addresses. Just sharing... > > Thanks. > > > > Works for me with a .001 at least, as shown below. But that wasn't really > the point I was making, FWIW. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query planner: Covering index not chosen over primary key
Thank you for the suggestion! The actual schema & query are a good deal more complicated, and I'm not looking for general optimization help with them right now. Jen Pollock On Fri, May 03, 2019 at 10:11:04PM +0100, Simon Slavin wrote: > On 3 May 2019, at 9:34pm, Jen Pollock wrote: > > > SELECT filename > > FROM images > >JOIN embedded_files ON images.file_id == embedded_files.id > > WHERE type == 'png'; > > Try this: > > CREATE INDEX images (type, file_id); > ANALYZE; > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query planner: Covering index not chosen over primary key
I have a database with a schema roughly like this: CREATE TABLE embedded_files( id INTEGER PRIMARY KEY, filename TEXT, data BLOB ); CREATE TABLE images( id INTEGER PRIMARY KEY, file_id INTEGER, type TEXT, FOREIGN KEY(file_id) REFERENCES embedded_files(id) ); The following query is slow: SELECT filename FROM images JOIN embedded_files ON images.file_id == embedded_files.id WHERE type == 'png'; Part of the problem is that many of the values in embedded_files.data are quite large. I tried to improve the query's performance by creating a covering index: CREATE INDEX embedded_files_id_filename ON embedded_files(id, filename); However, the query planner won't use this index unless I force it to with INDEXED BY. Forcing it to use the index does speed up the query. I assume the problem here is that the primary key is usually a weird thing to index. I can definitely work around this, but I thought it might be worth reporting as something that could perhaps be improved in the query planner. Jen Pollock ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is FTS5 still experimental?
The release notes for 3.13.0 say that FTS5 will be "experimental" for at least one more release cycle, and more recent release notes don't say anything about that having changed, but the FTS5 documentation doesn't mention anything about it being experimental. Is it still experimental now or not? More specifically, how likely is it that future versions of FTS5 will be unable to use search indexes built by the current version of FTS5? Jen Pollock ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
