FIRST/LAST function

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

FIRST/LAST function

Bill McCormick-4
Does SQLite have FIRST and LAST  aggregate function?

I don't see it in the docs, but I thought I might ask anyway. Or maybe
someone out there knows a slick way to do this:

Anyway, it would be sort of like MIN & MAX, but would return the first
and last values of the requested field over a group of ordered records.
If there were such a FIRST/LAST function, I imagine it's use would look
something like this:

SELECT
     strftime('%Y-%m',date) AS YEAR_MONTH,
     SUM(BAR) AS SUM_BAR,
     FIRST(FOO) AS FIRST_FOO,
     LAST(FOO) AS LAST_FOO
GROUP BY YEAR_MONTH
ORDER BY DATE

... for a data set with the following columns: item|date|bar|foo.  
Basically, we want to know the value of foo at the first and last
records of the month.

Thanks!!


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

Re: FIRST/LAST function

Petite Abeille-2

On Feb 2, 2012, at 4:37 PM, Bill McCormick wrote:

> Does SQLite have FIRST and LAST  aggregate function?

No, sadly, SQLite doesn't support any analytic functions (aka window function) such as first, last, lead, lag, rank, etc, etc... [1]

To achieve the same, you will have to roll your own, which is not always a piece of cake. Oh, well...

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

Re: FIRST/LAST function

Puneet Kishor-2

On Feb 2, 2012, at 9:46 AM, Petite Abeille wrote:

>
> On Feb 2, 2012, at 4:37 PM, Bill McCormick wrote:
>
>> Does SQLite have FIRST and LAST  aggregate function?
>
> No, sadly, SQLite doesn't support any analytic functions (aka window function) such as first, last, lead, lag, rank, etc, etc... [1]
>
> To achieve the same, you will have to roll your own, which is not always a piece of cake. Oh, well...
>
> [1] http://orafaq.com/node/55

to the OP... since you are using Perl (I believe, based on your earlier emails), you can get your data out and then use the most excellent List::Util (https://metacpan.org/module/List::Util). List::MoreUtils (https://metacpan.org/module/List::MoreUtils) and Scalar::Util (https://metacpan.org/module/Scalar::Util)

--
Puneet Kishor

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

Re: FIRST/LAST function

Bill McCormick-4
Mr. Puneet Kishor wrote, On 2/2/2012 9:57 AM:

> On Feb 2, 2012, at 9:46 AM, Petite Abeille wrote:
>
>> On Feb 2, 2012, at 4:37 PM, Bill McCormick wrote:
>>
>>> Does SQLite have FIRST and LAST  aggregate function?
>> No, sadly, SQLite doesn't support any analytic functions (aka window function) such as first, last, lead, lag, rank, etc, etc... [1]
>>
>> To achieve the same, you will have to roll your own, which is not always a piece of cake. Oh, well...
>>
>> [1] http://orafaq.com/node/55
> to the OP... since you are using Perl (I believe, based on your earlier emails), you can get your data out and then use the most excellent List::Util (https://metacpan.org/module/List::Util). List::MoreUtils (https://metacpan.org/module/List::MoreUtils) and Scalar::Util (https://metacpan.org/module/Scalar::Util)
>
Yes, but I was hoping to pretty much go right from the SQL output right
into a CSV file. I'm trying to avoid any extra processing in between, a
la ...

# Open a new Text::CSV_XS object
my $csv = Text::CSV_XS->new( );

my $raw_aref = $rawData_sth->{NAME};
# Combine column names for CSV output
$csv->combine( @$raw_aref );
# Print column headers to file
print RPTRAW $csv->string() . "\n";

#
# Retrieve the rows from the SQL server
#
while( $raw_aref = $rawData_sth->fetchrow_arrayref )
{
     # Combine the fields for ouput
     $csv->combine( @$raw_aref );
     # Print the record to file
     print RPTRAW $csv->string() . "\n";
}

Obviously, I've left a few things out here, but I think you get the
idea. Short of some VERY simple solution, I save this for some other day.

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

Re: FIRST/LAST function

Igor Tandetnik
In reply to this post by Bill McCormick-4
Bill McCormick <[hidden email]> wrote:

> SELECT
>     strftime('%Y-%m',date) AS YEAR_MONTH,
>     SUM(BAR) AS SUM_BAR,
>     FIRST(FOO) AS FIRST_FOO,
>     LAST(FOO) AS LAST_FOO
> GROUP BY YEAR_MONTH
> ORDER BY DATE
>
> ... for a data set with the following columns: item|date|bar|foo.
> Basically, we want to know the value of foo at the first and last
> records of the month.

No, SQLite doesn't have this. ORDER BY applies to groups, not to rows within each group (is this different with Oracle?) Something like this should work (but is kinda hard on the eyes):

SELECT
     strftime('%Y-%m',date) AS YEAR_MONTH,
     SUM(BAR) AS SUM_BAR,
    (select FOO from MyTable t2
     where t2.date between strftime('%Y-%m',t.date) and strftime('%Y-%m-31',t.date)
     order by t2.date asc limit 1) AS FIRST_FOO,

    (select FOO from MyTable t2
     where t2.date between strftime('%Y-%m',t.date) and strftime('%Y-%m-31',t.date)
     order by t2.date desc limit 1) AS LAST_FOO
from MyTable t
GROUP BY YEAR_MONTH
ORDER BY YEAR_MONTH;

--
Igor Tandetnik

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

Re: FIRST/LAST function

Petite Abeille-2

On Feb 2, 2012, at 5:26 PM, Igor Tandetnik wrote:

> ORDER BY applies to groups, not to rows within each group (is this different with Oracle?)

analytic functions works in term of the result set itself.

Here is a simple example:

http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php
http://www.oracle-base.com/articles/misc/AnalyticFunctions.php
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: FIRST/LAST function

Igor Tandetnik
Petite Abeille <[hidden email]> wrote:

> On Feb 2, 2012, at 5:26 PM, Igor Tandetnik wrote:
>
>> ORDER BY applies to groups, not to rows within each group (is this different with Oracle?)
>
> analytic functions works in term of the result set itself.
>
> Here is a simple example:
>
> http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php
> http://www.oracle-base.com/articles/misc/AnalyticFunctions.php

So there aren't actually FIRST and LAST aggregate functions, it seems - at least not the way the OP envisions them. The syntax is completely different.
--
Igor Tandetnik

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

Re: FIRST/LAST function

Bill McCormick-4
Igor Tandetnik wrote, On 2/2/2012 10:50 AM:

> Petite Abeille<[hidden email]>  wrote:
>> On Feb 2, 2012, at 5:26 PM, Igor Tandetnik wrote:
>>
>>> ORDER BY applies to groups, not to rows within each group (is this different with Oracle?)
>> analytic functions works in term of the result set itself.
>>
>> Here is a simple example:
>>
>> http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php
>> http://www.oracle-base.com/articles/misc/AnalyticFunctions.php
> So there aren't actually FIRST and LAST aggregate functions, it seems - at least not the way the OP envisions them. The syntax is completely different.

I'm OK with what you came up with. I think I need to fix the 1st part of
the between strftime, making the 1st arg '%Y-%m-01' instead of
'%Y-%m-01'. Anyway, it works and I don't think it's too gangly, at least
when formatted neatly. I just wish I was smart enough to have come with
that :) Maybe someday!

  (select FOO from MyTable t2
      where t2.date between strftime('%Y-%m-01',t.date) and strftime('%Y-%m-31',t.date)
      order by t2.date asc limit 1) AS FIRST_FOO,

Thanks!!




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