use sakila;
CREATE TABLE transac
(
   id INT UNSIGNED PRIMARY KEY,
   montant INT UNSIGNED NOT NULL,
   jour DATE NOT NULL,
   codePays ENUM('FR', 'BE', 'UK', 'US', 'CA', 'JP') NOT NULL
);

DELIMITER //
CREATE PROCEDURE remplir_transaction(nbTransacs INT)
BEGIN
   DECLARE i INT DEFAULT 1;
   DECLARE nbAlea DOUBLE;
   DECLARE _jour DATE;
   DECLARE _montant INT UNSIGNED;
   DECLARE _codePays TINYINT UNSIGNED;

   WHILE i <= nbTransacs DO
      SET nbAlea = RAND();
      SET _jour = ADDDATE('1996-01-01', FLOOR(nbAlea * 4015));
      SET _montant = FLOOR(1 + (nbAlea * 9999));
      SET nbAlea = RAND();
      SET _codePays = FLOOR(1 + (nbAlea * 6));
      INSERT INTO transac (id, montant, jour, codePays) VALUES (i, _montant, _jour, _codePays);
      SET i = i + 1;
   END WHILE;
END //
DELIMITER ;

call remplir_transaction(1000000);

select count(*) from transac;
select * from transac;

CREATE TABLE transac_part
(
   id INT UNSIGNED NOT NULL,
   montant INT UNSIGNED NOT NULL,
   jour DATE NOT NULL,
   codePays ENUM('FR', 'BE', 'UK', 'US', 'CA', 'JP') NOT NULL
) PARTITION BY RANGE(YEAR(jour))
(
   PARTITION p1 VALUES LESS THAN(1997),
   PARTITION p2 VALUES LESS THAN(1998),
   PARTITION p3 VALUES LESS THAN(1999),
   PARTITION p4 VALUES LESS THAN(2000),
   PARTITION p5 VALUES LESS THAN(2001),
   PARTITION p6 VALUES LESS THAN(2002),
   PARTITION p7 VALUES LESS THAN(2003),
   PARTITION p8 VALUES LESS THAN(2004),
   PARTITION p9 VALUES LESS THAN(2005),
   PARTITION p10 VALUES LESS THAN(2006),
   PARTITION p11 VALUES LESS THAN MAXVALUE
);

INSERT INTO transac_part SELECT * FROM transac;

SELECT SUM(montant), AVG(montant) FROM transac WHERE jour BETWEEN '1998-01-01' AND '1998-12-31';

SELECT SUM(montant), AVG(montant) FROM transac_part WHERE jour BETWEEN '1998-01-01' AND '1998-12-31';

EXPLAIN PARTITIONS SELECT SUM(montant), AVG(montant) FROM transac_part WHERE jour BETWEEN '1998-01-01' AND '1998-12-31';