Re: [sqlite] Can SQLite do this in a single query?
On Tuesday, 22 October, 2019 23:24, Doug wrote: >Please explain one phrase in the select: > total(1) over (partition by city_txt, mode_txt) >Is "total" a function and does "1" refer to city_txt? >I can't wrap my head around what's going on with this phrase. total() is a built-in aggregate like sum() -- there are just some minor (but important) differences. sum() returns NULL if there was nothing to sum. Also, if the values were all integers and the result fits in an integer, an integer is returned. total() returns 0.0 if is has nothing to total, and the returned value is always floating-point. So total(1) means the floating-point value of adding up all the 1 values ... that it, it is count(*) in floating-point, or basically cast(count(*) as float) but somewhat shorter. This is because the integer expression 37/154 has the integer result 0, however the floating point expression 37.0 / 154.0 has result 0.24025974025974 -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can SQLite do this in a single query?
Please explain one phrase in the select: total(1) over (partition by city_txt, mode_txt) Is "total" a function and does "1" refer to city_txt? I can't wrap my head around what's going on with this phrase. Doug > -Original Message- > From: sqlite-users > On Behalf Of Keith Medcalf > Sent: Tuesday, October 22, 2019 7:11 AM > To: SQLite mailing list > Subject: Re: [sqlite] Can SQLite do this in a single query? > > CREATE TABLE employees(employee_id, city_id, mode_id); > CREATE TABLE citys(city_id, city_txt); > CREATE TABLE modes(mode_id, mode_txt); > > select city_txt, > mode_txt, > total(1) over (partition by city_txt, mode_txt) / > total(1) over (partition by city_txt) as percentage > from employees, citys, modes >where employees.city_id == citys.city_id > and employees.mode_id == modes.mode_id > group by city_txt, mode_txt > group by city_txt, mode_txt; > > You have to paint the output table yourself. > > -- > The fact that there's a Highway to Hell but only a Stairway to > Heaven says a lot about anticipated traffic volume. > > >-Original Message- > >From: sqlite-users > On > >Behalf Of Winfried > >Sent: Tuesday, 22 October, 2019 07:23 > >To: sqlite-users@mailinglists.sqlite.org > >Subject: Re: [sqlite] Can SQLite do this in a single query? > > > >Yes, I forgot the column mode_id in the Employee's table. > > > >Thanks, I'll read up on the features SQLite's SELECT has to > offer. At > >worst, > >I'll just run a simpler query multiple times. > > > >https://www.sqlite.org/lang_select.html > > > > > > > >-- > >Sent from: http://sqlite.1065341.n5.nabble.com/ > >___ > >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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] mailinglists.sqlite.org
I observe the following on the mailinglists.sqlite.org web server: 1. http requests are responded to normally instead of redirecting to https, which leaves users passwords exposed when modifying subscription options. 2. https requests are served up with a TLS certficate for sqlite.org, resulting in warnings from browsers. Perhaps the devs feel like tidying these items up -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Theoretical write performance for low-throughput devices
Ok, skip that. locking_mode=EXCLUSIVE (1) takes care of that. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Keith Medcalf >Sent: Tuesday, 22 October, 2019 12:46 >To: SQLite mailing list >Subject: Re: [sqlite] Theoretical write performance for low-throughput >devices > >>and only one thread accesses that connection at a time. The target >>lacks mmap(), posix file locks, and multiple address spaces, so this >>seemed like the right settings to use. > >So what happens to the shm file? Is in not normally just an ordinary >file that is mmap'ped? > >What would be the effect of not having mmap available? > >-- >The fact that there's a Highway to Hell but only a Stairway to Heaven >says a lot about anticipated traffic volume. > > > >___ >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] Theoretical write performance for low-throughput devices
>and only one thread accesses that connection at a time. The target >lacks mmap(), posix file locks, and multiple address spaces, so this >seemed like the right settings to use. So what happens to the shm file? Is in not normally just an ordinary file that is mmap'ped? What would be the effect of not having mmap available? -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Theoretical write performance for low-throughput devices
On Mon, Oct 21, 2019 at 5:47 PM Richard Hipp wrote: > > On 10/21/19, Jonathan Brandmeyer wrote: > > > > No significant change. The target filesystem only caches non-aligned > > writes, so there usually isn't anything for it to do on fsync anyway. > > > > OK. I don't have any more ideas at the moment, and without access to > your code, and your platform, I can't really debug it. But do please > know that you should only be getting less than 2x writes. I suggest > adding instrumentation and trying to come up with a simplified test > case. Should the vLogVfs be picking up writes to both the WAL and the database proper? I'm seeing a number of WRITE's in the log equal to the final number of pages plus a small percentage. For the case where the WAL is much larger than the benchmark, the number of WRITE's in the log is almost equal to the number of final pages in the database. Thanks, -- Jonathan Brandmeyer PlanetiQ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can SQLite do this in a single query?
This eliminates the duplicate denominator calculation:
select city_txt,
mode_txt,
(
select total(1)
from employees, citys, modes
where employees.city_id == citys.city_id
and employees.mode_id == modes.mode_id
and citys.city_id == C.city_id
and modes.mode_id == M.mode_id
) / employee_count as percentage
from (
select city_txt,
citys.city_id,
total(1) as employee_count
from employees, citys
where employees.city_id == citys.city_id
group by city_txt
order by city_txt
) as C, modes as M
order by city_txt, mode_txt;
and of course you can wrap it in a classifier if you like:
select city_txt,
sum(case when mode_txt = 'WALKING' then percentage end) as Walking,
sum(case when mode_txt = 'CYCLING' then percentage end) as Cycling,
sum(case when mode_txt = 'PUBLIC TRANSIT' then percentage end) as
"Public Transit",
sum(case when mode_txt = 'CAR' then percentage end) as Car,
sum(case when mode_txt not in ('WALKING', 'CYCLING', 'PUBLIC TRANSIT',
'CAR') then percentage end) as Other
from (
select city_txt,
mode_txt,
(
select total(1)
from employees, citys, modes
where employees.city_id == citys.city_id
and employees.mode_id == modes.mode_id
and citys.city_id == C.city_id
and modes.mode_id == M.mode_id
) / employee_count as percentage
from (
select city_txt,
citys.city_id,
total(1) as employee_count
from employees, citys
where employees.city_id == citys.city_id
group by city_txt
order by city_txt
) as C, modes as M
)
group by city_txt
order by city_txt;
This is "standard SQL" and should work with just about any RDBMS using SQL of
any variation.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Keith Medcalf
>Sent: Tuesday, 22 October, 2019 09:44
>To: SQLite mailing list
>Subject: Re: [sqlite] Can SQLite do this in a single query?
>
>
>Hmmm. That gives the wrong answers compared to doing it the "old
>fashioned way". You still have to "paint the fence" yourself, though now
>the table data is not sparse, thus easier to paint (since each value goes
>in the next cell, and you start a new row when the city_txt changes).
>You calculate the denominator excessively, but if really necessary you
>can optimize that.
>
> select city_txt,
> mode_txt,
> (select total(1)
>from employees, citys, modes
> where employees.city_id == citys.city_id
> and employees.mode_id == modes.mode_id
> and citys.city_id == C.city_id
> and modes.mode_id == M.mode_id) / (select total(1)
> from employees, citys
> where employees.city_id
>== citys.city_id
> and citys.city_id ==
>C.City_id) as percentage
>from citys C, modes M
>order by city_txt, mode_txt;
>
>
>and you get the column headings thusly (the left most column is of course
>your static text "City") ...
>
> select mode_txt
>from modes
>order by mode_txt
>
>or if you do not like to put static column headings in your fence
>painter, then:
>
>select 'City'
>union all
>select mode_txt
> from (select mode_txt
> from modes
> order by mode_txt);
>
>Of course, perhaps you are using arbitrary "data hiding" headings and not
>the actual data (a method often used by PHB's to prove an pre-ordained
>outcome) in which case other methods may be more appropriate to "hide"
>what you do not want to see and provide it in a format compatible with
>that pre-ordained output).
>
>--
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>>-Original Message-
>>From: sqlite-users On
>>Behalf Of Keith Medcalf
>>Sent: Tuesday, 22 October, 2019 08:11
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Can SQLite do this in a single query?
>>
>>CREATE TABLE employees(employee_id, city_id, mode_id);
>>CREATE TABLE citys(city_id, city_txt);
>>CREATE TABLE modes(mode_id, mode_txt);
>>
>> select city_txt,
>> mode_txt,
>> total(1) over (partition by city_txt, mode_txt) / total(1) over
>>(partition by city_txt) as percentage
>>from employees, citys, modes
>> where employees.city_id == citys.city_id
>> and employees.mode_id ==
Re: [sqlite] Theoretical write performance for low-throughput devices
On Tue, Oct 22, 2019 at 2:03 AM Wout Mertens wrote: > This probably won't change a thing, but I wonder why you wouldn't set the > sqlite page size to 2KB? Hopefully this means lots of aligned writes. At one point, the row blobs were exactly 1024 bytes. This isn't great for 4kB pages, but it is a particularly unfavorable row size for 2 kB database pages. -- Jonathan Brandmeyer ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can SQLite do this in a single query?
Hmmm. That gives the wrong answers compared to doing it the "old fashioned way". You still have to "paint the fence" yourself, though now the table data is not sparse, thus easier to paint (since each value goes in the next cell, and you start a new row when the city_txt changes). You calculate the denominator excessively, but if really necessary you can optimize that. select city_txt, mode_txt, (select total(1) from employees, citys, modes where employees.city_id == citys.city_id and employees.mode_id == modes.mode_id and citys.city_id == C.city_id and modes.mode_id == M.mode_id) / (select total(1) from employees, citys where employees.city_id == citys.city_id and citys.city_id == C.City_id) as percentage from citys C, modes M order by city_txt, mode_txt; and you get the column headings thusly (the left most column is of course your static text "City") ... select mode_txt from modes order by mode_txt or if you do not like to put static column headings in your fence painter, then: select 'City' union all select mode_txt from (select mode_txt from modes order by mode_txt); Of course, perhaps you are using arbitrary "data hiding" headings and not the actual data (a method often used by PHB's to prove an pre-ordained outcome) in which case other methods may be more appropriate to "hide" what you do not want to see and provide it in a format compatible with that pre-ordained output). -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Keith Medcalf >Sent: Tuesday, 22 October, 2019 08:11 >To: SQLite mailing list >Subject: Re: [sqlite] Can SQLite do this in a single query? > >CREATE TABLE employees(employee_id, city_id, mode_id); >CREATE TABLE citys(city_id, city_txt); >CREATE TABLE modes(mode_id, mode_txt); > > select city_txt, > mode_txt, > total(1) over (partition by city_txt, mode_txt) / total(1) over >(partition by city_txt) as percentage >from employees, citys, modes > where employees.city_id == citys.city_id > and employees.mode_id == modes.mode_id >group by city_txt, mode_txt >group by city_txt, mode_txt; > >You have to paint the output table yourself. > >-- >The fact that there's a Highway to Hell but only a Stairway to Heaven >says a lot about anticipated traffic volume. > >>-Original Message- >>From: sqlite-users On >>Behalf Of Winfried >>Sent: Tuesday, 22 October, 2019 07:23 >>To: sqlite-users@mailinglists.sqlite.org >>Subject: Re: [sqlite] Can SQLite do this in a single query? >> >>Yes, I forgot the column mode_id in the Employee's table. >> >>Thanks, I'll read up on the features SQLite's SELECT has to offer. At >>worst, >>I'll just run a simpler query multiple times. >> >>https://www.sqlite.org/lang_select.html >> >> >> >>-- >>Sent from: http://sqlite.1065341.n5.nabble.com/ >>___ >>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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can SQLite do this in a single query?
Or something like this using the new filter clause from 3.30. As written it
should show it with two decimal points.
(not tested for typos)
select
CITY_TXT as CITY,
round(100.0 * total(Employee_Count) filter (where MODE_TXT = 'WALKING') /
total(Employee_Count), 2) as WALKING,
round(100.0 * total(Employee_Count) filter (where MODE_TXT = 'CYCLING') /
total(Employee_Count), 2) as CYCLING,
round(100.0 * total(Employee_Count) filter (where MODE_TXT = 'PUBLIC TRANSIT')
/ total(Employee_Count), 2) as PUBLIC_TRANSIT,
round(100.0 * total(Employee_Count) filter (where MODE_TXT = 'CAR') /
total(Employee_Count), 2) as CAR,
round(100.0 * total(Employee_Count) filter (where MODE_TXT not in ('WALKING',
'CYCLING', 'PUBLIC TRANSIT', 'CAR')) / total(Employee_Count), 2) as OTHER,
from
(
select
CITY_TXT,
MODE_TXT,
count(*) as Employee_Count
from
Employees
inner join Cities
using (CITY_ID)
inner join Mode
using (MODE_ID)
group by CITY_TXT, MODE_TXT
)
group by CITY_TXT
order by CITY;
-Original Message-
From: sqlite-users On Behalf Of
Keith Medcalf
Sent: Tuesday, October 22, 2019 10:11 AM
To: SQLite mailing list
Subject: Re: [sqlite] Can SQLite do this in a single query?
CREATE TABLE employees(employee_id, city_id, mode_id);
CREATE TABLE citys(city_id, city_txt);
CREATE TABLE modes(mode_id, mode_txt);
select city_txt,
mode_txt,
total(1) over (partition by city_txt, mode_txt) / total(1) over
(partition by city_txt) as percentage
from employees, citys, modes
where employees.city_id == citys.city_id
and employees.mode_id == modes.mode_id
group by city_txt, mode_txt
group by city_txt, mode_txt;
You have to paint the output table yourself.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Winfried
>Sent: Tuesday, 22 October, 2019 07:23
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Can SQLite do this in a single query?
>
>Yes, I forgot the column mode_id in the Employee's table.
>
>Thanks, I'll read up on the features SQLite's SELECT has to offer. At
>worst,
>I'll just run a simpler query multiple times.
>
>https://www.sqlite.org/lang_select.html
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>___
>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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can SQLite do this in a single query?
CREATE TABLE employees(employee_id, city_id, mode_id); CREATE TABLE citys(city_id, city_txt); CREATE TABLE modes(mode_id, mode_txt); select city_txt, mode_txt, total(1) over (partition by city_txt, mode_txt) / total(1) over (partition by city_txt) as percentage from employees, citys, modes where employees.city_id == citys.city_id and employees.mode_id == modes.mode_id group by city_txt, mode_txt group by city_txt, mode_txt; You have to paint the output table yourself. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Winfried >Sent: Tuesday, 22 October, 2019 07:23 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Can SQLite do this in a single query? > >Yes, I forgot the column mode_id in the Employee's table. > >Thanks, I'll read up on the features SQLite's SELECT has to offer. At >worst, >I'll just run a simpler query multiple times. > >https://www.sqlite.org/lang_select.html > > > >-- >Sent from: http://sqlite.1065341.n5.nabble.com/ >___ >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] Can SQLite do this in a single query?
> Yes, I forgot the column mode_id in the Employee's table. > > Thanks, I'll read up on the features SQLite's SELECT has to offer. At > worst, > I'll just run a simpler query multiple times. > Will the results of simple query multiple times help you somehow? Think about how to join two or more tables together (not difficult to find info about SQL JOIN) My reply is to help you to try you think how SQL basically works. > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can SQLite do this in a single query?
Yes, I forgot the column mode_id in the Employee's table. Thanks, I'll read up on the features SQLite's SELECT has to offer. At worst, I'll just run a simpler query multiple times. https://www.sqlite.org/lang_select.html -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Theoretical write performance for low-throughput devices
On Mon, Oct 21, 2019 at 5:28 PM Jonathan Brandmeyer < jbrandme...@planetiq.com> wrote: > I'm working on an embedded system that uses a log-structured > filesystem on raw NAND flash. This is not your typical workstation's > managed flash (SATA/NVMe), or portable managed flash (SD/USB). It's a > bare-nekkid ONFI-speaking chip. All reads and writes are one 2kB page > at a time. There is no readahead, and no write buffering by the > driver or filesystem for page-sized writes. > This probably won't change a thing, but I wonder why you wouldn't set the sqlite page size to 2KB? Hopefully this means lots of aligned writes. Wout. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Theoretical write performance for low-throughput devices
On Mon, 21 Oct 2019 at 23:28, Jonathan Brandmeyer wrote: > Or, how many times is each page written by SQLite for an insert-heavy > test? The answer appears to be "4", but I can only account for two of > those four. > > I'm working on an embedded system that uses a log-structured > filesystem on raw NAND flash. This is not your typical workstation's > managed flash (SATA/NVMe), or portable managed flash (SD/USB). It's a > bare-nekkid ONFI-speaking chip. All reads and writes are one 2kB page > at a time. There is no readahead, and no write buffering by the > driver or filesystem for page-sized writes. > > We got the following performance numbers out of the flash storage: > > Streaming reads through the filesystem: 7.5 MB/s. > Streaming writes through the filesystem: 5.4 MB/s. > Single insert performance through SQLite: 0.2 MB/s. > Bulk insert performance through SQLIte: 1.3 MB/s, asymptotic for very > large transactions. > There's perhaps a measurement missing here: random-access/seeking writes through the filesystem. Which doesn't sound like it should be a factor based on the technology involved, but it's more reflective of sqlite's workload when updating the DB. The smallest possible insert here modifies at least one DB page for the table and one for the index, so that's 8kb written to the journal and 8kb to the main DB. But bulk inserts should greatly reduce the impact of index writes, as you said. Hm, how does deletion/truncation perform on the target filesystem? -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
