DROP DATABASE IF EXISTS mcol_5932;
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;
group1	group2	ATC4PH_ID	HSTOPH_ID	PZN_ID	group3	group4	group5	VALUE_2025_01_M_430_1_1	VALUE_2025_01_M_5432_1_2	VALUE_2025_01_M_5430_1_3
$1	$1	12345	9873	54321	$1	$1	$1	100.500	120.600000	50.2500000
$1	$1	12346	12770	54322	$1	$1	$1	200.750	461.725000	110.4125000
$1	$1	12347	929	54323	$1	$1	$1	300.250	1020.850000	180.1500000
$1	$1	12348	4665	54324	$1	$1	$1	400.600	1802.700000	260.3900000
$1	$1	12349	9873	54325	$1	$1	$1	500.900	2805.040000	350.6300000
$1	$1	12350	12770	54326	$1	$1	$1	600.300	4022.010000	450.2250000
$1	$1	12352	929	54328	$1	$1	$1	800.750	7126.675000	680.6375000
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;