maria-discuss team mailing list archive

[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