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
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
