maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

hum i'm thinking about the problem
for example if we have an COUNT(*) or a SUM(value) or MAX(value) or
MIN(value) or anyother functions
shouldn't be more rational allow the 'single thread' query execute and use
the GROUP_CONCAT function?
ok it's not multi thread, but could the group_concat with limit 'optimize'
the shard query or single queries like this?

2015-10-03 0:10 GMT-03:00 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
>



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