Difference between revisions of "This Is Your Brain On Informatics: Q&A"
(Created page with "==MariaDB Examples== ===Manually Creating a Table=== <p>This data table was uploaded as a TSV (tab separated values) file.</p> <p>LOAD DATA INFILE '[data table pathname]' INT...") |
|||
| (One intermediate revision by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | ==MariaDB Examples== | + | == MariaDB Examples == |
| − | ===Manually Creating a Table=== | + | === Manually Creating a Table === |
<p>This data table was uploaded as a TSV (tab separated values) file.</p> | <p>This data table was uploaded as a TSV (tab separated values) file.</p> | ||
<p>LOAD DATA INFILE '[data table pathname]' INTO TABLE `test` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES</p> | <p>LOAD DATA INFILE '[data table pathname]' INTO TABLE `test` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES</p> | ||
| − | ===Quintiles=== | + | === Quintiles === |
| − | + | Q: Find the maximums in column 2 of quintiles based on column 1 | |
| − | |||
The following will create a temporary table that will order [var1], hold [var2] and also create a column with a counter variable named "id". | The following will create a temporary table that will order [var1], hold [var2] and also create a column with a counter variable named "id". | ||
| − | + | ||
| + | |||
A: CREATE TEMPORARY TABLE `rank` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY) SELECT [var1],[var2] FROM `test` WHERE [var3] = "string" AND [var1] <10000000 ORDER BY [var1]; | A: CREATE TEMPORARY TABLE `rank` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY) SELECT [var1],[var2] FROM `test` WHERE [var3] = "string" AND [var1] <10000000 ORDER BY [var1]; | ||
| − | + | ||
| + | |||
Then the use of the next command will need to be repeated for each quintile to find the maximum for each one. | Then the use of the next command will need to be repeated for each quintile to find the maximum for each one. | ||
| − | + | ||
| + | |||
A: SELECT MAX([var2]) FROM `rank` WHERE id BETWEEN [calculated lower bound of quintile] AND [calculated upper bound of quintile]; | A: SELECT MAX([var2]) FROM `rank` WHERE id BETWEEN [calculated lower bound of quintile] AND [calculated upper bound of quintile]; | ||
| − | + | ||
| + | |||
| + | {{Template: This Is Your Brain On Informatics}} | ||
| + | |||
| + | [[Category:This Is Your Brain On Informatics]] | ||
Latest revision as of 02:35, 19 March 2014
MariaDB Examples
Manually Creating a Table
This data table was uploaded as a TSV (tab separated values) file.
LOAD DATA INFILE '[data table pathname]' INTO TABLE `test` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES
Quintiles
Q: Find the maximums in column 2 of quintiles based on column 1
The following will create a temporary table that will order [var1], hold [var2] and also create a column with a counter variable named "id".
A: CREATE TEMPORARY TABLE `rank` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY) SELECT [var1],[var2] FROM `test` WHERE [var3] = "string" AND [var1] <10000000 ORDER BY [var1];
Then the use of the next command will need to be repeated for each quintile to find the maximum for each one.
A: SELECT MAX([var2]) FROM `rank` WHERE id BETWEEN [calculated lower bound of quintile] AND [calculated upper bound of quintile];
| |||||||||||||||||||||||
