Hive 选择查询返回前 100 个语法错误?

Question

这是我的Hive查询，直接来自TPC-DS工具包：

WITH customer_total_return 
     AS (SELECT sr_customer_sk AS ctr_customer_sk, 
                sr_store_sk    AS ctr_store_sk, 
                Sum(sr_fee)    AS ctr_total_return 
         FROM   store_returns, 
                date_dim 
         WHERE  sr_returned_date_sk = d_date_sk 
                AND d_year = 2000 
         GROUP  BY sr_customer_sk, 
                   sr_store_sk) 
SELECT TOP 100 c_customer_id 
FROM   customer_total_return ctr1, 
       store, 
       customer 
WHERE  ctr1.ctr_total_return > (SELECT Avg(ctr_total_return) * 1.2 
                                FROM   customer_total_return ctr2 
                                WHERE  ctr1.ctr_store_sk = ctr2.ctr_store_sk) 
       AND s_store_sk = ctr1.ctr_store_sk 
       AND s_state = 'TN' 
       AND ctr1.ctr_customer_sk = c_customer_sk 
ORDER  BY c_customer_id; 

但是，尝试运行它时出现以下错误：

失败：ParseException 行 11：11 无法识别"TOP"附近的输入 选择目标中的"100"c_customer_id">

我的理解是TOP 100在 HiveQL 中语法上无效。如何正确重写？

Answer 1

使用 LIMIT 而不是 TOP，如下所示：

WITH customer_total_return 
     AS (SELECT sr_customer_sk AS ctr_customer_sk, 
                sr_store_sk    AS ctr_store_sk, 
                Sum(sr_fee)    AS ctr_total_return 
         FROM   store_returns, 
                date_dim 
         WHERE  sr_returned_date_sk = d_date_sk 
                AND d_year = 2000 
         GROUP  BY sr_customer_sk, 
                   sr_store_sk) 
SELECT c_customer_id 
FROM   customer_total_return ctr1, 
       store, 
       customer 
WHERE  ctr1.ctr_total_return > (SELECT Avg(ctr_total_return) * 1.2 
                                FROM   customer_total_return ctr2 
                                WHERE  ctr1.ctr_store_sk = ctr2.ctr_store_sk) 
       AND s_store_sk = ctr1.ctr_store_sk 
       AND s_state = 'TN' 
       AND ctr1.ctr_customer_sk = c_customer_sk 
ORDER  BY c_customer_id
LIMIT 100; 

Answer 2

这是许多级别的查询的坏示例。 我建议：

WITH customer_total_return AS (
      SELECT sr.sr_customer_sk AS ctr_customer_sk, 
             sr.sr_store_sk  AS ctr_store_sk, 
             SUM(sr.sr_fee) AS ctr_total_return,
             AVG(SUM(sr.sr_fee)) OVER (PARTITION BY sr.sr_store_sk) as avg_store_sr_fee
      FROM store_returns sr JOIN
           date_dim d
           ON sr.sr_returned_date_sk = d.d_date_sk 
      WHERE d_year = 2000 
      GROUP  BY sr_customer_sk, sr_store_sk
     ) 
SELECT c.c_customer_id 
FROM customer_total_return ctr JOIN
     store s
     ON s.s_store_sk = ctr.ctr_store_sk JOIN
     customer c
     ON ctr.ctr_customer_sk = c.c_customer_sk
WHERE ctr.ctr_total_return > 1.2 * avg_store_sr_fee AND
      s.s_state = 'TN'  
ORDER  BY c.c_customer_id
LIMIT 100;

笔记：

• 切勿在FROM子句中使用逗号。始终使用正确、明确、标准JOIN语法。
• 限定所有列引用，尤其是当查询具有多个表引用时。
• 不需要用于计算平均值的子查询。
• 蜂巢使用LIMIT，而不是TOP。