PostgreSQL 9.5：新機能 パート3.グループ化セット、キューブ、ロールアップ

DROP TABLE IF EXISTS payment; DROP TABLE IF EXISTS payment_type; DROP TABLE IF EXISTS city; DROP TABLE IF EXISTS state; DROP TABLE IF EXISTS country; CREATE TABLE country ( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL ); CREATE TABLE state ( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL, country_id INT REFERENCES country (id) ); CREATE TABLE city ( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL, state_id INT NULL REFERENCES state (id), country_id INT NOT NULL REFERENCES country (id), population BIGINT NOT NULL ); CREATE TABLE payment_type ( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL ); CREATE TABLE payment ( id BIGSERIAL PRIMARY KEY, payment_type_id INT NOT NULL REFERENCES payment_type (id), city_id INT NOT NULL REFERENCES city (id), amount NUMERIC(10, 2) NOT NULL ); INSERT INTO country (name) VALUES ('Russia'), ('Ukraine'); INSERT INTO state (name, country_id) VALUES ('Moscow region', 1), ('Samara region', 1), ('Kursk region', 1), ('Tatarstan', 1), ('Kiev region', 2), ('Lugansk region', 2), ('Lvov region', 2), ('Odessa region', 2); –-          ,      INSERT INTO city (name, state_id, country_id, population) VALUES ('Moscow', NULL, 1, 12197596), ('Dubna', 1, 1, 75176), ('Samara', 2, 1, 1171820), ('Tolyatti', 2, 1, 719646), ('Syzran', 2, 1, 175222), ('Novokuybyshevsk', 2, 1, 105007), ('Kursk', 3, 1, 435117), ('Kazan', 4, 1, 1205651), ('Kiev', NULL, 2, 2888470), ('Irpen', 5, 2, 39972), ('Borispol', 5, 2, 60102), ('Belaya Tserkov', 5, 2, 211205), ('Lugansk', 6, 2, 417990), ('Lisichansk', 6, 2, 103459), ('Severodonetsk', 6, 2, 108899), ('Popasnaya', 6, 2, 21765), ('Lvov', 7, 2, 729038), ('Drogobych', 7, 2, 76866), ('Odessa', 8, 2, 1017022), ('Izmail', 8, 2, 72501); INSERT INTO payment_type (name) VALUES ('Online'), ('Box office'), ('Terminal'); INSERT INTO payment (payment_type_id, city_id, amount) SELECT ceil(random() * 3), ceil(random() * 20), trunc(cast(random() * 10000 AS NUMERIC), 2) FROM generate_series(1, 10000);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 (SELECT sum(amount), c.country_id, NULL as city_id FROM payment AS p INNER JOIN city AS c ON p.city_id=c.id GROUP BY c.country_id ORDER BY c.country_id) UNION ALL (SELECT sum(amount), NULL, p.city_id FROM payment AS p GROUP BY p.city_id ORDER BY p.city_id)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 SELECT sum(amount), c.country_id, p.city_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY GROUPING SETS(c.country_id, p.city_id);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 SELECT sum(amount), p.city_id, c.country_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY GROUPING SETS(p.city_id, C.country_id, ());
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 SELECT sum(amount), p.city_id, c.state_id, c.country_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY GROUPING SETS(p.city_id, c.state_id, c.country_id);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 SELECT sum(amount) FROM payment WHERE city_id IN (1, 9);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 SELECT sum(amount), p.city_id, c.state_id, c.country_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY GROUPING SETS(p.city_id, c.state_id, c.country_id, ());
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 SELECT avg(amount), p.city_id, c.state_id, c.country_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY GROUPING SETS(p.city_id, c.state_id, c.country_id, ());
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 SELECT avg(amount), p.city_id, c.state_id, c.country_id, grouping(c.state_id) FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY GROUPING SETS(p.city_id, c.state_id, c.country_id, ());
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 SELECT avg(amount), p.city_id, c.state_id, c.country_id, grouping(p.city_id, c.state_id, c.country_id) FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY GROUPING SETS(p.city_id, c.state_id, c.country_id, ());
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 SELECT avg(amount), p.city_id, c.state_id, c.country_id, grouping(p.city_id, c.state_id, c.country_id) :: BIT(3) FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY GROUPING SETS(p.city_id, c.state_id, c.country_id, ());
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 SELECT avg(amount), c.country_id, p.payment_type_id, p.city_id, c.state_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY c.country_id, p.payment_type_id, GROUPING SETS(p.city_id, c.state_id, ());
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 SELECT avg(amount), c.country_id, p.payment_type_id, p.city_id, c.state_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY GROUPING SETS((p.payment_type_id, c.country_id), ( c.state_id, p.city_id));
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 SELECT sum(amount), p.payment_type_id, c.country_id, p.city_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY CUBE(p.payment_type_id, c.country_id, p.city_id);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 SELECT sum(amount), p.payment_type_id, c.country_id, p.city_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY CUBE(p.payment_type_id, (c.country_id, p.city_id));
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 SELECT sum(amount), p.payment_type_id, c.country_id, p.city_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY ROLLUP(p.payment_type_id, c.country_id, p.city_id);