maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

nice :)

how it's "rewrite"?

2015-10-02 22:25 GMT-03:00 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
>>
>>
>


-- 
Roberto Spadim
SPAEmpresarial - Software ERP
Eng. Automação e Controle