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 <[email protected]> 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 <[email protected]>: >> 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 <[email protected]> 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 <[email protected]>: >>>> nice :) >>>> >>>> how it's "rewrite"? >>>> >>>> 2015-10-02 22:25 GMT-03:00 Justin Swanhart <[email protected]>: >>>>> 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 <[email protected]> >>>>>> 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 : [email protected] >>>>>> 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
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
