maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

nice, it's like two tables and run optimized queires in each table
i will consider this as a solution

if i need flexviews (materialized views) with this first / last function,
could this use the window functions of shard query? sorry i didn't tested
flexview + shard query yet


2015-10-03 20:55 GMT-03:00 Justin Swanhart <greenlion@xxxxxxxxx>:

> Hi,
>
> If you only need first/last values, and your lists are short (smaller than
> max_allowed_packet, given group_concat_max_len is max allowed packet) then
> group_concat works fine.
>
> Window functions work /on the result set/ itself.  They are kind of like
> sub select over the entire result of the SELECT statement, something a
> subquery in the SELECT clause is unable to do.  As such window functions
> are not usually combined with GROUP BY.
>
> This is because  window functions let you see the detail and the summary
> at the same time. They are kind of like spreadsheet expressions in that way.
>
> For example:
> SELECT date,value,last_value(value) over (partition by date order by value
> asc),first_value(value) over(partition by date),
> sum(value) over (partition by date),
> lag(value,2) over (partition by date)
> From table
>
> Shard-Query always stores the resultset in a table, so it can update the
> table for each window function result, which is calculated by a query.  You
> can look at the wf_* functions in shard-query for an implementation of each
> window function.  You could even add your own if you like.  If there is
> interest I can make that pluggable.
>
> You can see lots of examples in the test directory too, and s simple test
> data set.
>
> --justin
>
> Sent from my iPhone
>
> On Oct 2, 2015, at 8:26 PM, Roberto Spadim <roberto@xxxxxxxxxxxxx> wrote:
>
> 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
>
>


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