You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-07-30 19:23:07 +03:00
920607520c
Adds a special column which helps to differentiate data and rollups of various depts and a simple logic to row aggregation to add processing of subtotals.
70 lines
2.2 KiB
Plaintext
Executable File
70 lines
2.2 KiB
Plaintext
Executable File
#
|
|
# SELECT ... WITH ROLLUP #
|
|
# https://mariadb.com/kb/en/select-with-rollup/ #
|
|
#
|
|
# Author: Susil, susil.behera@mariadb.com #
|
|
#
|
|
-- source ../include/have_columnstore.inc
|
|
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS mcs84_db;
|
|
--enable_warnings
|
|
|
|
CREATE DATABASE mcs84_db;
|
|
USE mcs84_db;
|
|
|
|
CREATE TABLE booksales (
|
|
country CHAR(35), genre CHAR(15), year INT, sales INT)
|
|
ENGINE=Columnstore;
|
|
|
|
INSERT INTO booksales VALUES
|
|
('Senegal','fiction',2014,12234), ('Senegal','fiction',2015,15647),
|
|
('Senegal','non-fiction',2014,64980), ('Senegal','non-fiction',2015,78901),
|
|
('Paraguay','fiction',2014,87970), ('Paraguay','fiction',2015,76940),
|
|
('Paraguay','non-fiction',2014,8760), ('Paraguay','non-fiction',2015,9030);
|
|
|
|
SELECT year, SUM(sales) FROM booksales GROUP BY year ORDER BY year;
|
|
|
|
# Negative. WITH ROLLUP cannot be used with ORDER BY.
|
|
--error 1221
|
|
SELECT year, SUM(sales) FROM booksales GROUP BY year WITH ROLLUP ORDER BY year;
|
|
|
|
--sorted_result
|
|
SELECT year, sales, MAX(country) FROM booksales GROUP BY year, sales;
|
|
--sorted_result
|
|
SELECT year, MAX(country) FROM booksales GROUP BY year;
|
|
--sorted_result
|
|
SELECT year, sales, MAX(country) FROM booksales GROUP BY year, sales WITH ROLLUP;
|
|
|
|
--sorted_result
|
|
SELECT year, SUM(sales) FROM booksales GROUP BY year WITH ROLLUP;
|
|
|
|
--sorted_result
|
|
SELECT country, genre, SUM(sales) FROM booksales GROUP BY country, genre WITH ROLLUP;
|
|
|
|
CREATE TABLE three_cols ( key1 INTEGER, key2 INTEGER, value DECIMAL(38)) ENGINE=COLUMNSTORE;
|
|
|
|
INSERT INTO three_cols(key1, key2, value) VALUES
|
|
(NULL, NULL, NULL)
|
|
, (NULL, NULL, 1)
|
|
, (NULL, 1, 2)
|
|
, ( 1, 1, 3)
|
|
, ( 1, 2, 4)
|
|
, ( 1, 2, 5)
|
|
, ( 2, 3, 6)
|
|
, ( 2, 3, 7);
|
|
|
|
--sorted_result
|
|
SELECT key1, key2, SUM(value), AVG(value), MIN(value), MAX(value), COUNT(value), COUNT(*) FROM three_cols GROUP BY key1, key2 WITH ROLLUP;
|
|
|
|
--error 1815
|
|
SELECT key1, key2, GROUP_CONCAT(value) FROM three_cols GROUP BY key1, key2 WITH ROLLUP;
|
|
--error 1815
|
|
SELECT key1, key2, JSON_ARRAYAGG(value) FROM three_cols GROUP BY key1, key2 WITH ROLLUP;
|
|
|
|
--error 1815
|
|
SELECT 100, SUM(value) FROM three_cols GROUP BY 1 WITH ROLLUP;
|
|
|
|
# Clean up
|
|
DROP DATABASE IF EXISTS mcs84_db;
|