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 <[email protected]>: > 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 <[email protected]>: > >> 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 >> >> > > > -- > 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
