[大数据]Task04 集合运算

Task04 集合运算

4.1 表的加减法

• 集合运算符： UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算

• 表的加法：

  SELECT product_id, product_name
  FROM product
  UNION
  SELECT product_id, product_name
  FROM product2;
  

  -- 练习
  /*假设连锁店想要增加成本利润率超过 50%或者售价低于 800 的货物的存货量, 请使用 UNION 对分别满足上述两个条件的商品的查询结果求并集。*/
  
  -- 参考答案:
  SELECT  product_id,product_name,product_type
         ,sale_price,purchase_price
    FROM product 
   WHERE sale_price<800
    
   UNION
   
  SELECT  product_id,product_name,product_type
         ,sale_price,purchase_price
    FROM produdct 
   WHERE sale_price>1.5*purchase_price;
   
  -- 参考答案:(不使用 UNION) 
  SELECT  product_id,product_name,product_type
         ,sale_price,purchase_price
    FROM PRODUCT 
   WHERE sale_price < 800 
      OR sale_price > 1.5 * purchase_price;
  

• 引入 UNION的必要性 ：

  • 倘若要将两个不同的表中的结果合并在一起, 必须要使用 UNION
  • 即便是对于同一张表, 有时也会出于查询效率方面的因素来使用 UNION

  -- 练习
  -- 分别使用 UNION 或者 OR 谓词,找出成本利润率不足 30%或成本利润率未知的商品
  
  -- 使用 OR 谓词
  SELECT * 
    FROM Product 
   WHERE sale_price / purchase_price < 1.3 
      OR sale_price / purchase_price IS NULL;
      
  -- 使用 UNION
  SELECT * 
    FROM Product 
   WHERE sale_price / purchase_price < 1.3
   
   UNION
  SELECT * 
    FROM Product 
   WHERE sale_price / purchase_price IS NULL;
  

• 包含重复行的集合运算：

  • UNION ALL

    -- 保留重复行
    SELECT product_type
      FROM Product
     UNION ALL
    SELECT product_type
      FROM Product2;
    

    -- 练习题
    /*商店决定对product表中成本利润低于50% **或者** 售价低于1000的商品提价, 请使用UNION ALL 语句将分别满足上述两个条件的结果取并集. 查询结果类似下表: */
    SELECT * 
      FROM Product 
     WHERE sale_price < 1000
     UNION ALL
    SELECT * 
      FROM Product 
     WHERE sale_price < 1.5 * purchase_price
    

• 隐式数据类型转换：

  • 有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型

    SELECT product_id, product_name, '1'
      FROM Product
     UNION
    SELECT product_id, product_name,sale_price
      FROM Product2;
    

  • 需要注意的是 hive中进行join关联时，关联列要避免使用隐式数据类型转换，否则容易导致数据倾斜

• 集合的交：

  • MySQL 8.0 不支持交运算INTERSECT

    SELECT product_id, product_name
      FROM Product
      
    INTERSECT
    SELECT product_id, product_name
      FROM Product2
      
    -- 执行以上SQL语句会报错
    

  • 需要使用inner join来求得集合交集

    SELECT p1.product_id, p1.product_name
      FROM Product p1
    INNER JOIN Product2 p2
    ON p1.product_id=p2.product_id
    

• 差集、补集合标的减法：

  • A和B做减法只是将集合A中也同时属于集合B的元素减掉。

  • MySQL 8.0 还不支持 EXCEPT 运算，但是可以使用NOT IN谓词

    -- 使用NOT IN子句的实现方法
    -- 找出只存在于Product表但不存在于Product2表的商品
    SELECT * 
    FROM product
    WHERE product_id NOT IN (SELECT product_id 
                            FROM product);
    

  • 未完，待续，。。，