maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

i was thinking how could i use flexviews to optimize it with materialized
views but i think i can't use cause it's a sub query:/
any idea if i could use flexview to solve this? no problem about
materialized view in this case, it's a small result set and i will always
use it, should be nice solve with flex view :)

2015-10-03 22:23 GMT-03:00 Roberto Spadim <roberto@xxxxxxxxxxxxx>:

> yes it's like:
>
> date time|value
> 2015-10-03 12:00|10 degree
> 2015-10-03 13:00|20 degree
> 2015-10-03 14:00|15 degree
> 2015-10-03 15:00|9 degree
>
> and i want know what was the first temperature, last, min and max
>
> 2015-10-03 22:11 GMT-03:00 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
>>
>>
>
>
> --
> Roberto Spadim
> SPAEmpresarial - Software ERP
> Eng. Automação e Controle
>



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