maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

Hi,

You could use Shard-Query.  You don't need to shard the database, you can
point shard-query at one database.  It supports WINDOW functions.

select
date,
FIRST_VALUE(value) OVER (ORDER BY value ASC),
LAST_VALUE(value) OVER (ORDER BY value ASC)
FROM some_table AS a
GROUP BY date

--Justin

On Fri, Oct 2, 2015 at 6:12 PM, Roberto Spadim <roberto@xxxxxxxxxxxxx>
wrote:

> Hi guys
> I have a problem and a possible solution, that i think is relative easy to
> develop (i didn't tested but i think the source is easy to change)
>
> i have some queries like:
>
>
> SELECT
> date,
> (SELECT value FROM some_table WHERE date=a.date ORDER BY date ASC LIMIT 1)
> AS first,
> (SELECT value FROM some_table WHERE date=a.date ORDER BY date DESC LIMIT
> 1) AS last
> FROM some_table AS a
> GROUP BY date
>
>
> i want to "convert" the complex sub query, to a agregate function, and i
> thinking about group concat:
> from mysql docs:
>
> GROUP_CONCAT([DISTINCT] *expr* [,*expr* ...]
>              [ORDER BY {*unsigned_integer* | *col_name* | *expr*}
>                  [ASC | DESC] [,*col_name* ...]]
>              [SEPARATOR *str_val*])
>
>
> we have ORDER BY
> if we could include a "LIMIT" clause, i could rewrite this query to:
>
> SELECT
> date,
> GROUP_CONCAT(value ORDER BY date ASC LIMIT 1) AS first,
> GROUP_CONCAT(value ORDER BY date DESC LIMIT 1) AS last
> FROM some_table AS a
> GROUP BY date
>
>
> i know that i could have stats tables / materialized views (with flexview,
> i already tested :) ), but i want something more "easy to use" and not
> "very fast", just a "feature" to solve small problems
>
>
> it's a nice idea? does anyone have this "problem" and solve with other
> solutions?
>
> --
> Roberto Spadim
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>