You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-08-07 03:22:57 +03:00
134 lines
4.9 KiB
Plaintext
134 lines
4.9 KiB
Plaintext
--source ../include/have_columnstore.inc
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS mcol_5932;
|
|
--enable_warnings
|
|
|
|
CREATE DATABASE mcol_5932;
|
|
USE mcol_5932;
|
|
|
|
CREATE TABLE `fact_de_odvcan_11600` (
|
|
`REGODV_ID` INT(10) UNSIGNED DEFAULT NULL,
|
|
`HSTOPH_ID` INT(11) DEFAULT NULL,
|
|
`ATC4PH_ID` INT(11) DEFAULT NULL,
|
|
`PZN_ID` INT(10) UNSIGNED DEFAULT NULL,
|
|
`WERTART_ID` SMALLINT(5) UNSIGNED DEFAULT NULL,
|
|
`NFC3_ID` SMALLINT(5) UNSIGNED DEFAULT NULL,
|
|
`SUB_ID` SMALLINT(5) UNSIGNED DEFAULT NULL,
|
|
`VAL_25_101` INT(11) DEFAULT NULL,
|
|
`VAL_25_102` INT(11) DEFAULT NULL,
|
|
`VAL_25_430` DECIMAL(12, 3) DEFAULT NULL,
|
|
`VAL_25_103` INT(11) DEFAULT NULL,
|
|
`VAL_25_104` INT(11) DEFAULT NULL,
|
|
`VAL_25_105` INT(11) DEFAULT NULL,
|
|
`ZE` DECIMAL(10, 3) DEFAULT NULL,
|
|
`ZE_CI` DECIMAL(13, 4) DEFAULT NULL,
|
|
`DDD` DECIMAL(10, 3) DEFAULT NULL,
|
|
`VM` DECIMAL(25, 17) DEFAULT NULL
|
|
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;
|
|
|
|
INSERT INTO `fact_de_odvcan_11600` (
|
|
`REGODV_ID`, `HSTOPH_ID`, `ATC4PH_ID`, `PZN_ID`, `WERTART_ID`, `NFC3_ID`, `SUB_ID`,
|
|
`VAL_25_101`, `VAL_25_102`, `VAL_25_430`, `VAL_25_103`, `VAL_25_104`, `VAL_25_105`,
|
|
`ZE`, `ZE_CI`, `DDD`, `VM`
|
|
) VALUES
|
|
(105904, 9873, 12345, 54321, 1, 178, 26037, 50, 60, 100.500, 70, 80, 90, 1.2, 3.4, 5.6, 0.1),
|
|
(100302, 12770, 12346, 54322, 1, 178, 26037, 55, 65, 200.750, 75, 85, 95, 2.3, 4.5, 6.7, 0.1),
|
|
(597802, 929, 12347, 54323, 1, 178, 26037, 60, 70, 300.250, 80, 90, 100, 3.4, 5.6, 7.8, 0.1),
|
|
(105907, 4665, 12348, 54324, 1, 178, 26037, 65, 75, 400.600, 85, 95, 105, 4.5, 6.7, 8.9, 0.1),
|
|
(106000, 9873, 12349, 54325, 1, 178, 26037, 70, 80, 500.900, 90, 100, 110, 5.6, 7.8, 9.1, 0.1),
|
|
(106001, 12770, 12350, 54326, 1, 178, 26037, 75, 85, 600.300, 95, 105, 115, 6.7, 8.9, 10.2, 0.1),
|
|
(106002, 4665, 12351, 54327, 1, 178, 26037, 80, 90, 700.150, 100, 110, 120, 7.8, 9.1, 11.3, 0.1),
|
|
(106003, 929, 12352, 54328, 1, 178, 26037, 85, 95, 800.750, 105, 115, 125, 8.9, 10.2, 12.4, 0.1);
|
|
|
|
CREATE ALGORITHM = UNDEFINED
|
|
DEFINER = `root`@`localhost`
|
|
SQL SECURITY DEFINER
|
|
VIEW `v_fact_de_odvcan_11600` AS
|
|
SELECT
|
|
`REGODV_ID`, `ATC4PH_ID`, `HSTOPH_ID`, `PZN_ID`, `VAL_25_430`,
|
|
`VAL_25_430` * `VAL_25_101` / 100 AS `VAL_25_5430`,
|
|
`VAL_25_430` * `VAL_25_102` / 100 AS `VAL_25_5431`,
|
|
`VAL_25_430` * `ZE` AS `VAL_25_5432`,
|
|
`VAL_25_430` * `DDD` AS `VAL_25_5433`,
|
|
`VAL_25_430` * `VAL_25_104` / 100 AS `VAL_25_5434`,
|
|
`VAL_25_430` * `VAL_25_105` / 100 AS `VAL_25_5435`,
|
|
`VAL_25_430` * `VM` AS `VAL_25_5436`,
|
|
`VAL_25_430` * `VAL_25_103` / 100 AS `VAL_25_5437`,
|
|
`VAL_25_430` * `ZE_CI` AS `VAL_25_5790`,
|
|
`VAL_25_430` - `VAL_25_430` + 1 AS `VAL_25_5999`,
|
|
`WERTART_ID`, `NFC3_ID`, `SUB_ID`
|
|
FROM `fact_de_odvcan_11600`;
|
|
|
|
CREATE TABLE IF NOT EXISTS `group_f38275cd_db16_4e71_9306_c20af09d4c8b` (
|
|
`key_id` BIGINT,
|
|
`eg_id` VARCHAR(40)
|
|
) ENGINE=Columnstore;
|
|
|
|
INSERT INTO `group_f38275cd_db16_4e71_9306_c20af09d4c8b`
|
|
VALUES(105904, '$1'),
|
|
(100302, '$1'),
|
|
(597802, '$1'),
|
|
(106000, '$1'),
|
|
(106001, '$1'),
|
|
(105907, '$1'),
|
|
(106002, '$1'),
|
|
(106003, '$1');
|
|
|
|
CREATE TABLE IF NOT EXISTS `group_6b1d15de_368d_4239_a5df_09a2ca386b97` (
|
|
`key_id` BIGINT,
|
|
`eg_id` VARCHAR(40)
|
|
) ENGINE=Columnstore;
|
|
|
|
INSERT INTO `group_6b1d15de_368d_4239_a5df_09a2ca386b97`
|
|
VALUES(105904, '$1'),
|
|
(100302, '$1'),
|
|
(105907, '$1'),
|
|
(106000, '$1'),
|
|
(106001, '$1'),
|
|
(597802, '$1'),
|
|
(106002, '$1'),
|
|
(106003, '$1');
|
|
|
|
CREATE TABLE IF NOT EXISTS `group_7d5e323d_69ab_413e_817b_2fde051ec00c` (
|
|
`key_id` BIGINT,
|
|
`eg_id` VARCHAR(40)
|
|
) ENGINE=Columnstore;
|
|
|
|
INSERT INTO `group_7d5e323d_69ab_413e_817b_2fde051ec00c`
|
|
VALUES(100302, '$1'),
|
|
(105502, '$1'),
|
|
(105904, '$1'),
|
|
(106000, '$1'),
|
|
(106001, '$1'),
|
|
(105907, '$1'),
|
|
(597802, '$1'),
|
|
(106003, '$1');
|
|
|
|
SELECT
|
|
CASE WHEN v.`SUB_ID` IN (26037) THEN '$1' ELSE 'REST' END AS `group1`,
|
|
CASE WHEN v.`SUB_ID` IN (26037) THEN '$1' ELSE 'REST' END AS `group2`,
|
|
v.`ATC4PH_ID`, v.`HSTOPH_ID`, v.`PZN_ID`,
|
|
COALESCE(g0.`eg_id`, 'REST') AS `group3`,
|
|
COALESCE(g1.`eg_id`, 'REST') AS `group4`,
|
|
COALESCE(g2.`eg_id`, 'REST') AS `group5`,
|
|
SUM(`VAL_25_430`) AS `VALUE_2025_01_M_430_1_1`,
|
|
SUM(`VAL_25_5432`) AS `VALUE_2025_01_M_5432_1_2`,
|
|
SUM(`VAL_25_5430`) AS `VALUE_2025_01_M_5430_1_3`
|
|
FROM `v_fact_de_odvcan_11600` AS v
|
|
JOIN `group_f38275cd_db16_4e71_9306_c20af09d4c8b` AS g0 ON g0.`key_id` = v.`REGODV_ID`
|
|
JOIN `group_6b1d15de_368d_4239_a5df_09a2ca386b97` AS g1 ON g1.`key_id` = v.`REGODV_ID`
|
|
JOIN `group_7d5e323d_69ab_413e_817b_2fde051ec00c` AS g2 ON g2.`key_id` = v.`REGODV_ID`
|
|
WHERE v.`WERTART_ID` IN (1)
|
|
AND v.`NFC3_ID` IN (178)
|
|
AND v.`SUB_ID` IN (26037)
|
|
AND v.`HSTOPH_ID` IN (9873, 12770, 929, 4665)
|
|
GROUP BY `group1`, `group2`, `ATC4PH_ID`, `HSTOPH_ID`, `PZN_ID`, `group3`, `group4`, `group5`
|
|
ORDER BY ATC4PH_ID
|
|
LIMIT 1000000;
|
|
|
|
DROP TABLE IF EXISTS `group_f38275cd_db16_4e71_9306_c20af09d4c8b`;
|
|
DROP TABLE IF EXISTS `group_6b1d15de_368d_4239_a5df_09a2ca386b97`;
|
|
DROP TABLE IF EXISTS `group_7d5e323d_69ab_413e_817b_2fde051ec00c`;
|
|
|
|
DROP DATABASE mcol_5932;
|