[Maria-discuss] Group Concat and Sub Query
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
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@spadim.com.br> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
nice :) how it's "rewrite"? 2015-10-02 22:25 GMT-03:00 Justin Swanhart <greenlion@gmail.com>:
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@spadim.com.br> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
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@spadim.com.br>:
nice :)
how it's "rewrite"?
2015-10-02 22:25 GMT-03:00 Justin Swanhart <greenlion@gmail.com>:
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@spadim.com.br> 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@lists.launchpad.net 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
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@spadim.com.br> 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@spadim.com.br>:
nice :)
how it's "rewrite"?
2015-10-02 22:25 GMT-03:00 Justin Swanhart <greenlion@gmail.com>:
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@spadim.com.br> 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@lists.launchpad.net 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
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@gmail.com>:
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@spadim.com.br> 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@spadim.com.br>:
nice :)
how it's "rewrite"?
2015-10-02 22:25 GMT-03:00 Justin Swanhart <greenlion@gmail.com>:
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@spadim.com.br> 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@lists.launchpad.net 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
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@spadim.com.br> 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@gmail.com>:
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@spadim.com.br> 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@spadim.com.br>:
nice :)
how it's "rewrite"?
2015-10-02 22:25 GMT-03:00 Justin Swanhart <greenlion@gmail.com>:
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@spadim.com.br> 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@lists.launchpad.net 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
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@gmail.com>:
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@spadim.com.br> 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@gmail.com>:
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@spadim.com.br> 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@spadim.com.br>:
nice :)
how it's "rewrite"?
2015-10-02 22:25 GMT-03:00 Justin Swanhart <greenlion@gmail.com>:
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@spadim.com.br> 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@lists.launchpad.net 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
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 <roberto@spadim.com.br>:
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@gmail.com>:
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@spadim.com.br> 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@gmail.com>:
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@spadim.com.br> 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@spadim.com.br>:
nice :)
how it's "rewrite"?
2015-10-02 22:25 GMT-03:00 Justin Swanhart <greenlion@gmail.com>:
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@spadim.com.br> 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@lists.launchpad.net 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
Sorry, I see you correlate by and order by date but return value. Never mind. Sent from my iPhone
On Oct 3, 2015, at 5:43 PM, Roberto Spadim <roberto@spadim.com.br> 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@gmail.com>:
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@spadim.com.br> 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@spadim.com.br>:
nice :)
how it's "rewrite"?
2015-10-02 22:25 GMT-03:00 Justin Swanhart <greenlion@gmail.com>:
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@spadim.com.br> 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@lists.launchpad.net 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
Did you mean to write ORDER BY value ASC and ORDER BY value DESC in the subqueries? The "ORDER BY date" doesn't make sense in the subqueries as all the rows will have the same date, due to the "WHERE date=a.date" correlation. Pantelis On Sat, Oct 3, 2015 at 2:12 AM, Roberto Spadim <roberto@spadim.com.br> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
I think time is a separate field he orders on, he just mistyped the query. Sent from my iPhone
On Oct 4, 2015, at 1:20 AM, Pantelis Theodosiou <ypercube@gmail.com> wrote:
Did you mean to write
ORDER BY value ASC and ORDER BY value DESC
in the subqueries? The "ORDER BY date" doesn't make sense in the subqueries as all the rows will have the same date, due to the "WHERE date=a.date" correlation.
Pantelis
On Sat, Oct 3, 2015 at 2:12 AM, Roberto Spadim <roberto@spadim.com.br> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (3)
-
Justin Swanhart -
Pantelis Theodosiou -
Roberto Spadim