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
