maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

Hi,  

Btw, how does your first/last differ from min/max?

Sent from my iPhone

> On Oct 3, 2015, at 5:43 PM, Roberto Spadim <roberto@xxxxxxxxxxxxx> wrote:
> 
> 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