一、关键点
1、需要注意的是 hive中进行join关联时,关联列要避免使用隐式数据类型转换,否则容易导致数据倾斜
2、时间日期类型和字符串,数值以及缺失值均能兼容。
3、如果分组列和被聚合的列不在同一张表, 且二者都未被用于连结两张表, 则只能先连结, 再聚合。
4、SQL查询的执行顺序(FROM->WHERE->SELECT),我们发现, 问题可能出在筛选条件上, 因为在进行完外连结后才会执行WHERE子句, 因此那些主表中无法被匹配到的行就被WHERE条件筛选掉了。明白了这一点, 我们要记住先写个子查询,然后再把这个子查询和主表连结起来。
二、练习
4.1
SELECT p1.*
FROM product as p1
where sale_price>500
UNION
SELECT p2.*
FROM product2 as p2
WHERE sale_price>500;
4.2
SELECT
*
FROM
product
WHERE
product_id IN ( SELECT product_id FROM product2 );
4.3
-- 找出每类商品中售价最高的商品在哪些商店有售
SELECT P1.product_type,P1.product_id,P1.product_name,P1.sale_price,P3.shop_name
FROM product AS P1
INNER JOIN
(SELECT product_type,MAX(sale_price)as max
FROM product
GROUP BY product_type)AS P2
ON P1.product_type=P2.product_type
AND P1.sale_price=P2.max
INNER JOIN
shopproduct AS P3
ON P1.product_id=P3.product_id
ORDER BY product_id ;
4.4
关联子查询:
-- 找出每类商品中售价最高的商品
SELECT P1.product_type,P1.product_id,P1.product_name,P1.sale_price
FROM product AS P1
WHERE P1.sale_price=
(SELECT MAX(sale_price)
FROM product AS P2
WHERE P1.product_type=P2.product_type
GROUP BY product_type)
ORDER BY product_id ;
内连接:
-- 找出每类商品中售价最高的商品
SELECT P1.product_type,P1.product_id,P1.product_name,P1.sale_price
FROM product AS P1
INNER JOIN
(SELECT product_type,MAX(sale_price)as max
FROM product
GROUP BY product_type)AS P2
ON P1.product_type=P2.product_type
AND P1.sale_price=P2.max
ORDER BY product_id ;
4.5
-- 在 product 表中,取出 product_id, produc_name, sale_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。
SELECT P1.product_id,P1.product_name,P1.sale_price,
(SELECT SUM(sale_price)
FROM product AS P2
WHERE P1.sale_price >= P2.sale_price
OR P1.product_id=P2.product_id
)as '累计求和'
FROM product AS P1
ORDER BY sale_price;