datetime result help

classic Classic list List threaded Threaded
35 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Re: datetime result help

Stephan Beal-3
On Thu, Jul 30, 2015 at 10:59 PM, jose isaias cabrera <
[hidden email]> wrote:

> Ok, I have one more option for you, and since you think it's fun, I need
> to exclude Saturdays and Sundays.  I have a hack in the programming side of
> things, but I would like to do it right from SQLite.  Thougths?
>

i have not yet seen you mention daylight savings time (changes in the
clock) yet, nor workers who book times in other timezones, nor local
holidays (on which booking is not allowed, at least in some countries), nor
overlapping bookings (which should generally not be allowed except for
on-call bookings (which can also span over day/month/year boundaries)). My
point is only: there's a _lot_ more detail to consider than has been (or
should be) addressed here. Have fun with that!

(i'm currently part-time maintainer of my company's time-booking system,
and am exceedingly glad that others have already implemented such gory
details.)

--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Thanks SQLite

John McKown
In reply to this post by Roman Fleysher
I have also used CTE so that I could generate a "report" just using SQL.
Something like (simplified):

WITH summary AS
   ( SELECT var, count(var) as total FROM table GROUP BY key )
SELECT a.var, 100*a.var/b.total as percentage FROM table AS a
INNER JOIN summary AS b ON a.key=b.key
ORDER BY key
;

​Yes, I could do the equivalent in the program. But that does make it more
complicated. Which makes it easier to make a mistake. And more difficult to
modify. Being a bit weird, I have even encapsulated that in a BASH shell
script which "transforms" a CSV file (simplified):

#!/bin/sh
if [ $# -le 3 ]; then
   echo "At least 3 arguments required." >2
   echo "Argument 1 is the name of the input CSV." >2
   echo "Argument 2 is the name of the output CSV. " >2
   echo "Argument 3 is the name of the \"key\" field." >2
   echo "All arguments after than are the names of other variable to
include in the output." >2
   echo "Note that variables in the input CSV which are not specifically
named are excluded." >2
   exit 1;
fi
input="$1" #name of input CSV file
shift
output="$2" #name of output CSV file
shift
key="$3" #name of the key field
shift
varlist="$@" #names of other variables to output, in order
# Put in some percentages.
sqlite3 <<EOF
.mode csv
.output "${output}"
.import "${input}" test
WITH summary AS
 (select "${key}, count("${key}") as __total, "${varlist}" FROM test GROUP
BY "${key}")
SELECT a."${key}" 100*a."${key}"/b.total as __percentage FROM test AS a
INNER JOIN summary AS b on a."${key}"=b."${key}"
ORDER BY "${key}"
;
.quit
EOF

Nice and neat. And even easier than PERL or Python.​ Hopefully I didn't
type something in wrong.


--

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Thanks SQLite

Simon Slavin-3

On 31 Jul 2015, at 1:26pm, John McKown <[hidden email]> wrote:

> WITH summary AS
>   ( SELECT var, count(var) as total FROM table GROUP BY key )
> SELECT a.var, 100*a.var/b.total as percentage FROM table AS a
> INNER JOIN summary AS b ON a.key=b.key
> ORDER BY key

Thank you at last for an example of non-recursive CTE being useful in the real world.

I am noting an overlap with the things SQLite users have been using sub-SELECTs for.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: datetime result help

R Smith
In reply to this post by jose isaias cabrera


On 2015-07-30 10:59 PM, jose isaias cabrera wrote:
>
> Ok, I have one more option for you, and since you think it's fun, I
> need to exclude Saturdays and Sundays.  I have a hack in the
> programming side of things, but I would like to do it right from
> SQLite.  Thougths?

    WITH DTBase(HrsToAdd, DayStartsAt, HrsPerDay, HrsNow) AS (SELECT
         5.5,
         8.5,
         8.5,
         (CAST(strftime('%H','now','localtime') AS
    REAL)+(CAST(strftime('%M','now','localtime') AS REAL)/60)-8.5)

    ), DTFill(HrsToFillToday,HrsRemain) AS (SELECT
         MAX(HrsPerDay-HrsNow,0),
         MAX(HrsToAdd-MAX(HrsPerDay-HrsNow,0),0)
         FROM DTBase

    ), DTDays(DaysToAdd,DayHrsToAdd) AS (SELECT
         CAST((HrsRemain/HrsPerDay) AS INT),
         (HrsRemain-(CAST((HrsRemain/HrsPerDay) AS INT)*HrsPerDay))
         FROM DTFill,DTBase

    ), DTTime(DaysToAdd,DayHrsToAdd) AS (SELECT DaysToAdd,
         (DayStartsAt+(CASE WHEN HrsRemain>0 THEN DayHrsToAdd+24 ELSE
    HrsNow+HrsToFillToday END))
         FROM DTBase,DTFill,DTDays

    ), NewDT(NewDateTime) AS (SELECT
         datetime( 'now', 'localtime', 'start of day', '+'||DaysToAdd||'
    days', '+'||DayHrsToAdd||' hours') AS NewTime
         FROM DTTime

    ), WkEnd(WEDays) AS (
         SELECT ((CAST(DaysToAdd AS INT)/7)*2) FROM DTTime
         UNION ALL
         SELECT (WEDays+1) FROM WkEnd,NewDT WHERE
    (strftime('%w',datetime(NewDateTime,'+'||WEDays||' day')) IN ('0','6'))
    )
    SELECT datetime( 'now', 'localtime', 'start of day',
    '+'||(MAX(DaysToAdd)+MAX(WEDays))||' days', '+'||MAX(DayHrsToAdd)||'
    hours') AS NewTime
    FROM DTTime, WkEnd;



Wait... Let me guess... next you want Holidays added too, right?

Well, just in case you do... here is the SQL:
(Just add your own country's Holidays in the HDays CTE)

    WITH DTBase(HrsToAdd, DayStartsAt, HrsPerDay, HrsNow) AS (SELECT
         5.5,
         8.5,
         8.5,
         (CAST(strftime('%H','now','localtime') AS
    REAL)+(CAST(strftime('%M','now','localtime') AS REAL)/60)-8.5)

    ), HDays(HoliDay) AS (
         SELECT '01-01' UNION ALL  -- New Year's
         SELECT '05-01' UNION ALL  -- Worker's
         SELECT '07-04' UNION ALL  -- Independence
         SELECT '12-25'            -- Xmas

    ), DTFill(HrsToFillToday,HrsRemain) AS (SELECT
         MAX(HrsPerDay-HrsNow,0),
         MAX(HrsToAdd-MAX(HrsPerDay-HrsNow,0),0)
         FROM DTBase

    ), DTDays(DaysToAdd,DayHrsToAdd) AS (SELECT
         CAST((HrsRemain/HrsPerDay) AS INT),
         (HrsRemain-(CAST((HrsRemain/HrsPerDay) AS INT)*HrsPerDay))
         FROM DTFill,DTBase

    ), DTTime(DaysToAdd,DayHrsToAdd) AS (SELECT DaysToAdd,
         (DayStartsAt+(CASE WHEN HrsRemain>0 THEN DayHrsToAdd+24 ELSE
    HrsNow+HrsToFillToday END))
         FROM DTBase,DTFill,DTDays

    ), NewDT(NewDateTime) AS (SELECT
         datetime( 'now', 'localtime', 'start of day', '+'||DaysToAdd||'
    days', '+'||DayHrsToAdd||' hours') AS NewTime
         FROM DTTime

    ), WkEnd(WEDays) AS (
         SELECT ((CAST(DaysToAdd AS INT)/7)*2) FROM DTTime
       UNION ALL
         SELECT (WEDays+1) FROM WkEnd,NewDT
          WHERE (strftime('%w',datetime(NewDateTime,'+'||WEDays||'
    day')) IN ('0','6')) OR
    (strftime('%m-%d',datetime(NewDateTime,'+'||WEDays||' day')) IN
    (SELECT Holiday FROM HDays))

    )
    SELECT datetime( 'now', 'localtime', 'start of day',
    '+'||(MAX(DaysToAdd)+MAX(WEDays))||' days', '+'||MAX(DayHrsToAdd)||'
    hours') AS NewTime
    FROM DTTime, WkEnd;


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Thanks SQLite

R Smith
In reply to this post by Simon Slavin-3


On 2015-07-31 02:40 PM, Simon Slavin wrote:
>
> I am noting an overlap with the things SQLite users have been using sub-SELECTs for.

Quite, with the added advantage that you can reference the same CTE
multiple times in later parts of the query, plus have two or more
different aggregations on it in two or more different follow-up CTE's or
later in the query - something not at all possible with a sub-select.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Thanks SQLite

John McKown
In reply to this post by Simon Slavin-3
On Fri, Jul 31, 2015 at 7:40 AM, Simon Slavin <[hidden email]> wrote:

>
> On 31 Jul 2015, at 1:26pm, John McKown <[hidden email]>
> wrote:
>
> > WITH summary AS
> >   ( SELECT var, count(var) as total FROM table GROUP BY key )
> > SELECT a.var, 100*a.var/b.total as percentage FROM table AS a
> > INNER JOIN summary AS b ON a.key=b.key
> > ORDER BY key
>
> Thank you at last for an example of non-recursive CTE being useful in the
> real world.
>
> I am noting an overlap with the things SQLite users have been using
> sub-SELECTs for.
>

​Dang. That should be GROUP BY var, not GROUP BY key. Knew I'd mess up
something.​



>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: datetime result help

jose isaias cabrera
In reply to this post by R Smith

"R.Smith" wrote...

>
> On 2015-07-30 10:59 PM, jose isaias cabrera wrote:
>>
>> Ok, I have one more option for you, and since you think it's fun, I need
>> to exclude Saturdays and Sundays.  I have a hack in the programming side
>> of things, but I would like to do it right from SQLite.  Thougths?
>
>    WITH DTBase(HrsToAdd, DayStartsAt, HrsPerDay, HrsNow) AS (SELECT
>         5.5,
>         8.5,
>         8.5,
>         (CAST(strftime('%H','now','localtime') AS
>    REAL)+(CAST(strftime('%M','now','localtime') AS REAL)/60)-8.5)
>
>    ), DTFill(HrsToFillToday,HrsRemain) AS (SELECT
>         MAX(HrsPerDay-HrsNow,0),
>         MAX(HrsToAdd-MAX(HrsPerDay-HrsNow,0),0)
>         FROM DTBase
>
>    ), DTDays(DaysToAdd,DayHrsToAdd) AS (SELECT
>         CAST((HrsRemain/HrsPerDay) AS INT),
>         (HrsRemain-(CAST((HrsRemain/HrsPerDay) AS INT)*HrsPerDay))
>         FROM DTFill,DTBase
>
>    ), DTTime(DaysToAdd,DayHrsToAdd) AS (SELECT DaysToAdd,
>         (DayStartsAt+(CASE WHEN HrsRemain>0 THEN DayHrsToAdd+24 ELSE
>    HrsNow+HrsToFillToday END))
>         FROM DTBase,DTFill,DTDays
>
>    ), NewDT(NewDateTime) AS (SELECT
>         datetime( 'now', 'localtime', 'start of day', '+'||DaysToAdd||'
>    days', '+'||DayHrsToAdd||' hours') AS NewTime
>         FROM DTTime
>
>    ), WkEnd(WEDays) AS (
>         SELECT ((CAST(DaysToAdd AS INT)/7)*2) FROM DTTime
>         UNION ALL
>         SELECT (WEDays+1) FROM WkEnd,NewDT WHERE
>    (strftime('%w',datetime(NewDateTime,'+'||WEDays||' day')) IN ('0','6'))
>    )
>    SELECT datetime( 'now', 'localtime', 'start of day',
>    '+'||(MAX(DaysToAdd)+MAX(WEDays))||' days', '+'||MAX(DayHrsToAdd)||'
>    hours') AS NewTime
>    FROM DTTime, WkEnd;
>
>
>
> Wait... Let me guess... next you want Holidays added too, right?
>
> Well, just in case you do... here is the SQL:
> (Just add your own country's Holidays in the HDays CTE)
>
>    WITH DTBase(HrsToAdd, DayStartsAt, HrsPerDay, HrsNow) AS (SELECT
>         5.5,
>         8.5,
>         8.5,
>         (CAST(strftime('%H','now','localtime') AS
>    REAL)+(CAST(strftime('%M','now','localtime') AS REAL)/60)-8.5)
>
>    ), HDays(HoliDay) AS (
>         SELECT '01-01' UNION ALL  -- New Year's
>         SELECT '05-01' UNION ALL  -- Worker's
>         SELECT '07-04' UNION ALL  -- Independence
>         SELECT '12-25'            -- Xmas
>
>    ), DTFill(HrsToFillToday,HrsRemain) AS (SELECT
>         MAX(HrsPerDay-HrsNow,0),
>         MAX(HrsToAdd-MAX(HrsPerDay-HrsNow,0),0)
>         FROM DTBase
>
>    ), DTDays(DaysToAdd,DayHrsToAdd) AS (SELECT
>         CAST((HrsRemain/HrsPerDay) AS INT),
>         (HrsRemain-(CAST((HrsRemain/HrsPerDay) AS INT)*HrsPerDay))
>         FROM DTFill,DTBase
>
>    ), DTTime(DaysToAdd,DayHrsToAdd) AS (SELECT DaysToAdd,
>         (DayStartsAt+(CASE WHEN HrsRemain>0 THEN DayHrsToAdd+24 ELSE
>    HrsNow+HrsToFillToday END))
>         FROM DTBase,DTFill,DTDays
>
>    ), NewDT(NewDateTime) AS (SELECT
>         datetime( 'now', 'localtime', 'start of day', '+'||DaysToAdd||'
>    days', '+'||DayHrsToAdd||' hours') AS NewTime
>         FROM DTTime
>
>    ), WkEnd(WEDays) AS (
>         SELECT ((CAST(DaysToAdd AS INT)/7)*2) FROM DTTime
>       UNION ALL
>         SELECT (WEDays+1) FROM WkEnd,NewDT
>          WHERE (strftime('%w',datetime(NewDateTime,'+'||WEDays||'
>    day')) IN ('0','6')) OR
>    (strftime('%m-%d',datetime(NewDateTime,'+'||WEDays||' day')) IN
>    (SELECT Holiday FROM HDays))
>
>    )
>    SELECT datetime( 'now', 'localtime', 'start of day',
>    '+'||(MAX(DaysToAdd)+MAX(WEDays))||' days', '+'||MAX(DayHrsToAdd)||'
>    hours') AS NewTime
>    FROM DTTime, WkEnd;

How much I owe you? :-)  The holidays was the next question. ;P  Thanks.
This will help me understand CTE much deeper/better.  Thanks.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Thanks SQLite

Gabor Grothendieck
In reply to this post by Simon Slavin-3
On Fri, Jul 31, 2015 at 8:40 AM, Simon Slavin <[hidden email]> wrote:

>
> I am noting an overlap with the things SQLite users have been using
> sub-SELECTs for.
>
>
Here is a self contained example that can be fed into the sqlite3 command
line tool.  It uses WITH to factor out the subquery; however, the annoying
part about it is that even though we have factored out the subquery we
still have to include a simplified version of the subquery, viz.
  (select avgSalary from A)
It would have been nice if we could just replace (select avgSalary from A)
with (A.avgSalary) or even (A) but those substitutions do not work:

-- based on query at: https://en.wikipedia.org/wiki/Correlated_subquery
create table Emp (emp text, salary real, dept text);
insert into Emp values ('a', 1, 'A');
insert into Emp values ('b', 2, 'A');
insert into Emp values ('c', 3, 'A');
insert into Emp values ('d', 1, 'B');
insert into Emp values ('e', 2, 'B');
insert into Emp values ('f', 3, 'B');
WITH A as
 (SELECT AVG(salary) AS avgSalary FROM Emp WHERE dept = e1.dept)
 SELECT *
 FROM Emp AS e1
 WHERE salary > (select avgSalary from A);

Here is a sample run assuming the above has been placed into a file named
test.sqlite.

C:\> sqlite3 < test.sqlite
c|3.0|A
f|3.0|B

(I am using sqlite 3.8.3 on Windows 8.1.)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Thanks SQLite

John McKown
On Fri, Jul 31, 2015 at 8:40 AM, Gabor Grothendieck <[hidden email]
> wrote:

> >
> Here is a self contained example that can be fed into the sqlite3 command
> line tool.  It uses WITH to factor out the subquery; however, the annoying
> part about it is that even though we have factored out the subquery we
> still have to include a simplified version of the subquery, viz.
>   (select avgSalary from A)
> It would have been nice if we could just replace (select avgSalary from A)
> with (A.avgSalary) or even (A) but those substitutions do not work:
>
> -- based on query at: https://en.wikipedia.org/wiki/Correlated_subquery
> ​​
> create table Emp (emp text, salary real, dept text);
> insert into Emp values ('a', 1, 'A');
> insert into Emp values ('b', 2, 'A');
> insert into Emp values ('c', 3, 'A');
> insert into Emp values ('d', 1, 'B');
> insert into Emp values ('e', 2, 'B');
> insert into Emp values ('f', 3, 'B');
> WITH A as
> ​​
>  (SELECT AVG(salary) AS avgSalary FROM Emp WHERE dept = e1.dept)
> ​​
>  SELECT *
>  FROM Emp AS e1
>  WHERE salary > (select avgSalary from A);
>
> Here is a sample run assuming the above has been placed into a file named
> test.sqlite.
>
> C:\> sqlite3 < test.sqlite
> c|3.0|A
> f|3.0|B
>
> (I am using sqlite 3.8.3 on Windows 8.1.)


​Using SQLite 3.8.11 on Linux (RedHat Fedora 22), I used the following. I
don't know that it is "better", but doesn't use sub-selects, if that is of
any use.

sqlite> WITH a AS
   ...> (SELECT dept, AVG(salary) AS avgSalary FROM Emp GROUP BY dept)
   ...> select Emp.emp, salary, Emp.dept
   ...> FROM Emp JOIN a ON Emp.dept = a.dept AND salary > avgSalary;
c|3.0|A
f|3.0|B

I did an EXPLAIN QUERY PLAN on both of those. But I don't understand, at
all, what I am being told. I have posted the output for each, in the hopes
that somebody will tell me which is better.

From the original, using the sub-select

0|0|0|SCAN TABLE Emp AS e1
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SCAN TABLE Emp

From my alternate, using the JOIN with no sub-select

1|0|0|SCAN TABLE Emp
1|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|SCAN TABLE Emp
0|1|1|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (dept=?)


--

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Thanks SQLite

R Smith
In reply to this post by Gabor Grothendieck


On 2015-07-31 03:40 PM, Gabor Grothendieck wrote:

> On Fri, Jul 31, 2015 at 8:40 AM, Simon Slavin <[hidden email]> wrote:
>
>> I am noting an overlap with the things SQLite users have been using
>> sub-SELECTs for.
>>
>>
> Here is a self contained example that can be fed into the sqlite3 command
> line tool.  It uses WITH to factor out the subquery; however, the annoying
> part about it is that even though we have factored out the subquery we
> still have to include a simplified version of the subquery, viz.
>    (select avgSalary from A)
> It would have been nice if we could just replace (select avgSalary from A)
> with (A.avgSalary) or even (A) but those substitutions do not work:

Well, you can, sort-of, if you use the CTE column naming. Consider this
simplified version of your Query (also using the CTE to do the actual
aggregate work once only):

    create table Emp (emp text, salary real, dept text);
    insert into Emp values
    ('a', 1, 'A'),
    ('b', 2, 'A'),
    ('c', 3, 'A'),
    ('d', 1, 'B'),
    ('e', 2, 'B'),
    ('f', 3, 'B');


    WITH A(dept,avgSalary) as (
         SELECT Emp.dept, AVG(salary) FROM Emp GROUP BY Emp.dept
    )
    SELECT Emp.*
       FROM Emp, A
      WHERE Emp.dept = A.dept AND Emp.salary > A.avgSalary;


       --  emp  | salary |  dept
       -- ----- | ------ | -----
       --   c   |   3.0  |   A
       --   f   |   3.0  |   B


> -- based on query at: https://en.wikipedia.org/wiki/Correlated_subquery
> create table Emp (emp text, salary real, dept text);
> insert into Emp values ('a', 1, 'A');
> insert into Emp values ('b', 2, 'A');
> insert into Emp values ('c', 3, 'A');
> insert into Emp values ('d', 1, 'B');
> insert into Emp values ('e', 2, 'B');
> insert into Emp values ('f', 3, 'B');
> WITH A as
>   (SELECT AVG(salary) AS avgSalary FROM Emp WHERE dept = e1.dept)
>   SELECT *
>   FROM Emp AS e1
>   WHERE salary > (select avgSalary from A);
>
> Here is a sample run assuming the above has been placed into a file named
> test.sqlite.
>
> C:\> sqlite3 < test.sqlite
> c|3.0|A
> f|3.0|B
>
> (I am using sqlite 3.8.3 on Windows 8.1.)
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Thanks SQLite

Gabor Grothendieck
Both the solutions transformed the correlated subquery into a join prior to
forming the CTE.  Can we conclude, in general, that CTEs do not support
correlated subqueries?

On Fri, Jul 31, 2015 at 11:30 AM, R.Smith <[hidden email]> wrote:

>
>
> On 2015-07-31 03:40 PM, Gabor Grothendieck wrote:
>
>> On Fri, Jul 31, 2015 at 8:40 AM, Simon Slavin <[hidden email]>
>> wrote:
>>
>> I am noting an overlap with the things SQLite users have been using
>>> sub-SELECTs for.
>>>
>>>
>>> Here is a self contained example that can be fed into the sqlite3 command
>> line tool.  It uses WITH to factor out the subquery; however, the annoying
>> part about it is that even though we have factored out the subquery we
>> still have to include a simplified version of the subquery, viz.
>>    (select avgSalary from A)
>> It would have been nice if we could just replace (select avgSalary from A)
>> with (A.avgSalary) or even (A) but those substitutions do not work:
>>
>
> Well, you can, sort-of, if you use the CTE column naming. Consider this
> simplified version of your Query (also using the CTE to do the actual
> aggregate work once only):
>
>    create table Emp (emp text, salary real, dept text);
>    insert into Emp values
>    ('a', 1, 'A'),
>    ('b', 2, 'A'),
>    ('c', 3, 'A'),
>    ('d', 1, 'B'),
>    ('e', 2, 'B'),
>    ('f', 3, 'B');
>
>
>    WITH A(dept,avgSalary) as (
>         SELECT Emp.dept, AVG(salary) FROM Emp GROUP BY Emp.dept
>    )
>    SELECT Emp.*
>       FROM Emp, A
>      WHERE Emp.dept = A.dept AND Emp.salary > A.avgSalary;
>
>
>       --  emp  | salary |  dept
>       -- ----- | ------ | -----
>       --   c   |   3.0  |   A
>       --   f   |   3.0  |   B
>
>
> -- based on query at: https://en.wikipedia.org/wiki/Correlated_subquery
>> create table Emp (emp text, salary real, dept text);
>> insert into Emp values ('a', 1, 'A');
>> insert into Emp values ('b', 2, 'A');
>> insert into Emp values ('c', 3, 'A');
>> insert into Emp values ('d', 1, 'B');
>> insert into Emp values ('e', 2, 'B');
>> insert into Emp values ('f', 3, 'B');
>> WITH A as
>>   (SELECT AVG(salary) AS avgSalary FROM Emp WHERE dept = e1.dept)
>>   SELECT *
>>   FROM Emp AS e1
>>   WHERE salary > (select avgSalary from A);
>>
>> Here is a sample run assuming the above has been placed into a file named
>> test.sqlite.
>>
>> C:\> sqlite3 < test.sqlite
>> c|3.0|A
>> f|3.0|B
>>
>> (I am using sqlite 3.8.3 on Windows 8.1.)
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Thanks SQLite

Richard Hipp-3
On 7/31/15, Gabor Grothendieck <[hidden email]> wrote:
> Can we conclude, in general, that CTEs do not support
> correlated subqueries?
>

For a recursive CTE, the recursive table reference must occur exactly
once at the top-level, never in a subquery.

Other than that, there are no restrictions on the use of subqueries in
SQLite's CTE implementation, at least that I recall.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Thanks SQLite

R Smith
In reply to this post by Gabor Grothendieck


On 2015-07-31 06:22 PM, Gabor Grothendieck wrote:
> Both the solutions transformed the correlated subquery into a join prior to
> forming the CTE.  Can we conclude, in general, that CTEs do not support
> correlated subqueries?

Slow down please! - Your example did work and did get the correct result
using a sub-query AND a CTE. There is no problem with that.

I've simply shown a way to use the "A.avgSalary" notation which you
mentioned would be nice to have, together with a slight optimization
(which I saw mimicked the way John suggested - obviously, he too, moved
to optimize) - but none of those examples mean that sub-queries CANNOT
exist in (or with) CTE's. It simply means that in this specific query,
the CTE is better than the sub-query.

Also, the join is formed with the CTE, I am not sure why you use the
words "prior to forming the CTE".

I hope that is more clear...
Cheers!
Ryan


>
> On Fri, Jul 31, 2015 at 11:30 AM, R.Smith <[hidden email]> wrote:
>
>>
>> On 2015-07-31 03:40 PM, Gabor Grothendieck wrote:
>>
>>> On Fri, Jul 31, 2015 at 8:40 AM, Simon Slavin <[hidden email]>
>>> wrote:
>>>
>>> I am noting an overlap with the things SQLite users have been using
>>>> sub-SELECTs for.
>>>>
>>>>
>>>> Here is a self contained example that can be fed into the sqlite3 command
>>> line tool.  It uses WITH to factor out the subquery; however, the annoying
>>> part about it is that even though we have factored out the subquery we
>>> still have to include a simplified version of the subquery, viz.
>>>     (select avgSalary from A)
>>> It would have been nice if we could just replace (select avgSalary from A)
>>> with (A.avgSalary) or even (A) but those substitutions do not work:
>>>
>> Well, you can, sort-of, if you use the CTE column naming. Consider this
>> simplified version of your Query (also using the CTE to do the actual
>> aggregate work once only):
>>
>>     create table Emp (emp text, salary real, dept text);
>>     insert into Emp values
>>     ('a', 1, 'A'),
>>     ('b', 2, 'A'),
>>     ('c', 3, 'A'),
>>     ('d', 1, 'B'),
>>     ('e', 2, 'B'),
>>     ('f', 3, 'B');
>>
>>
>>     WITH A(dept,avgSalary) as (
>>          SELECT Emp.dept, AVG(salary) FROM Emp GROUP BY Emp.dept
>>     )
>>     SELECT Emp.*
>>        FROM Emp, A
>>       WHERE Emp.dept = A.dept AND Emp.salary > A.avgSalary;
>>
>>
>>        --  emp  | salary |  dept
>>        -- ----- | ------ | -----
>>        --   c   |   3.0  |   A
>>        --   f   |   3.0  |   B
>>
>>
>> -- based on query at: https://en.wikipedia.org/wiki/Correlated_subquery
>>> create table Emp (emp text, salary real, dept text);
>>> insert into Emp values ('a', 1, 'A');
>>> insert into Emp values ('b', 2, 'A');
>>> insert into Emp values ('c', 3, 'A');
>>> insert into Emp values ('d', 1, 'B');
>>> insert into Emp values ('e', 2, 'B');
>>> insert into Emp values ('f', 3, 'B');
>>> WITH A as
>>>    (SELECT AVG(salary) AS avgSalary FROM Emp WHERE dept = e1.dept)
>>>    SELECT *
>>>    FROM Emp AS e1
>>>    WHERE salary > (select avgSalary from A);
>>>
>>> Here is a sample run assuming the above has been placed into a file named
>>> test.sqlite.
>>>
>>> C:\> sqlite3 < test.sqlite
>>> c|3.0|A
>>> f|3.0|B
>>>
>>> (I am using sqlite 3.8.3 on Windows 8.1.)
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: datetime result help

R Smith
In reply to this post by jose isaias cabrera


On 2015-07-31 03:37 PM, jose isaias cabrera wrote:
>
> How much I owe you? :-)  The holidays was the next question. ;P
> Thanks. This will help me understand CTE much deeper/better. Thanks.
>

The help is a pleasure, but those CTEs were hacked together to solve the
date-time conundrum you've posted - Please don't use them as educational
material!

I'm not aware of any great CTE tutorials for SQLite on the web, though
there must be some, and other readers may offer links to them.

We've added a few tutorial-like demos to the script editor for the
upcoming public release of SQLitespeed 2 (still  in final BETA, release
expected late August) for doing fun stuff like drawing Sine/Cosine
graphs, Mandelbrot sets and reading/expanding CSV data in a column - but
I think a real basic tutorial will help a lot of people more.

I'll post some of those tutorials here if anyone is interested, and I
hope someone could add some nice web tutorials/resources because CTEs
are just brilliant in making life easier and everyone could / should
benefit from it.  If interested in checking these out in the beta - it
can be accessed from here: www.sqlc.rifin.co.za/ but I advise hanging on
for the public release.


Two great informational pieces can be found here (although this is SQL
SERVER based CTE discussions, I think the articles are accessible to all):

http://facility9.com/2008/12/a-quick-introduction-to-common-table-expressions/

https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/

The former contains a host of reasons why CTEs are better to use and
both give examples of practical non-recursive CTEs.


Cheers,
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: datetime result help

jose isaias cabrera
"R.Smith" wrote...

>
> On 2015-07-31 03:37 PM, jose isaias cabrera wrote:
>>
>> How much I owe you? :-)  The holidays was the next question. ;P Thanks.
>> This will help me understand CTE much deeper/better. Thanks.
>>
>
> The help is a pleasure, but those CTEs were hacked together to solve the
> date-time conundrum you've posted - Please don't use them as educational
> material!
>
> I'm not aware of any great CTE tutorials for SQLite on the web, though
> there must be some, and other readers may offer links to them.
>
> We've added a few tutorial-like demos to the script editor for the
> upcoming public release of SQLitespeed 2 (still  in final BETA, release
> expected late August) for doing fun stuff like drawing Sine/Cosine graphs,
> Mandelbrot sets and reading/expanding CSV data in a column - but I think a
> real basic tutorial will help a lot of people more.
>
> I'll post some of those tutorials here if anyone is interested, and I hope
> someone could add some nice web tutorials/resources because CTEs are just
> brilliant in making life easier and everyone could / should benefit from
> it.  If interested in checking these out in the beta - it can be accessed
> from here: www.sqlc.rifin.co.za/ but I advise hanging on for the public
> release.
>
>
> Two great informational pieces can be found here (although this is SQL
> SERVER based CTE discussions, I think the articles are accessible to all):
>
> http://facility9.com/2008/12/a-quick-introduction-to-common-table-expressions/
>
> https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/
>
> The former contains a host of reasons why CTEs are better to use and both
> give examples of practical non-recursive CTEs.

Thanks. Ryan.  Wow, your tool is pretty amazing.  I did not know it existed.
The site is also very nice.  Thank for the CTE links.  I am a newbie to SQL,
and I started with SQLite for a project for work in 2006 and now it has
become bigger than I wanted/thought, which is pushing me to learn more and
more about this fancy world of SQL. :-)  Again, thanks for all the help.

josé

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12