[Maria-discuss] Select optimizations
Hi guys! There's a "easy/fast" way to rewrite a query from this: Select datetime,key,value from table where ... To this: Select datetime, value1 as key1, value2 as key2, .... From table where ... I don't remember the right name in R/python (pandas) language but it's something like vector/matrix merge The ideia is, instead of one line of date key vale, use a single date, and "put" values as columns, and the columns name is the key name The table structure is something like: Create table ( Datetime datetime not null, Key varchar(255) not null, Value double not null, Primary key(datetime,key) ) Any idea is wellcome I know there's many solutions, if i have >100 columns, if datetime is sparse (only one key have values per datetime value), etc etc -- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
Pandas structure=dataframe The sql idea is full join (from what i read) Is it possible in mysql/mariadb? -- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
Hi, Roberto! On Jan 20, Roberto Spadim wrote:
Hi guys! There's a "easy/fast" way to rewrite a query from this:
Select datetime,key,value from table where ...
To this:
Select datetime, value1 as key1, value2 as key2, .... From table where ...
I don't remember the right name in R/python (pandas) language but it's something like vector/matrix merge
The ideia is, instead of one line of date key vale, use a single date, and "put" values as columns, and the columns name is the key name
The table structure is something like: Create table ( Datetime datetime not null, Key varchar(255) not null, Value double not null, Primary key(datetime,key) )
Any idea is wellcome I know there's many solutions, if i have >100 columns, if datetime is sparse (only one key have values per datetime value), etc etc
Check the PIVOT table type of the CONNECT engine. https://mariadb.com/kb/en/mariadb/connect-table-types-pivot-table-type/ Regards, Sergei Chief Architect MariaDB and security@mariadb.org -- Vote for my Percona Live 2016 talks: https://www.percona.com/live/data-performance-conference-2016/sessions/maria... https://www.percona.com/live/data-performance-conference-2016/sessions/maria...
ow, really nice :) it solve the problem, thanks sergei! 2016-01-20 10:30 GMT-02:00 Sergei Golubchik <serg@mariadb.org>:
Hi, Roberto!
On Jan 20, Roberto Spadim wrote:
Hi guys! There's a "easy/fast" way to rewrite a query from this:
Select datetime,key,value from table where ...
To this:
Select datetime, value1 as key1, value2 as key2, .... From table where ...
I don't remember the right name in R/python (pandas) language but it's something like vector/matrix merge
The ideia is, instead of one line of date key vale, use a single date, and "put" values as columns, and the columns name is the key name
The table structure is something like: Create table ( Datetime datetime not null, Key varchar(255) not null, Value double not null, Primary key(datetime,key) )
Any idea is wellcome I know there's many solutions, if i have >100 columns, if datetime is sparse (only one key have values per datetime value), etc etc
Check the PIVOT table type of the CONNECT engine. https://mariadb.com/kb/en/mariadb/connect-table-types-pivot-table-type/
Regards, Sergei Chief Architect MariaDB and security@mariadb.org -- Vote for my Percona Live 2016 talks:
https://www.percona.com/live/data-performance-conference-2016/sessions/maria...
https://www.percona.com/live/data-performance-conference-2016/sessions/maria...
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
participants (2)
-
Roberto Spadim -
Sergei Golubchik