语义异常 [错误 10007]:不明确的列引用_c1

Question

我在 Hive 查询中使用四级嵌套时遇到问题。以下是我正在执行的查询 -

SELECT *, 
SUM(qtod.amount) OVER (PARTITION BY qtod.id, qtod.year_begin_date ORDER BY qtod.tran_date)
FROM (SELECT *, 
    SUM(mtod.amount) OVER (PARTITION BY mtod.id, mtod.quarter_begin_date ORDER BY mtod.tran_date)
    FROM (SELECT *, 
        SUM(wtod.amount) OVER (PARTITION BY wtod.id, wtod.month_begin_date ORDER BY wtod.tran_date)
        FROM (select id,
            year_begin_date,
            quarter_begin_date,
            month_begin_date,
            week_begin_date,
            tran_date,
            amount,
            SUM(amount) 
OVER (PARTITION BY id,week_begin_date ORDER BY tran_date) FROM table_name)wtod)mtod)qtod;

如果我排除第四级嵌套，它工作正常，但在包含它时，低于错误消息 -

失败：语义异常 [错误 10007]：不明确的列引用 _c1 在 qtod

为了避免嵌套，

我尝试以其他方式进行嵌套

SELECT * FROM
    (SELECT id,year_begin_date,tran_date,amount,SUM(amount) OVER (PARTITION BY id,year_begin_date ORDER BY tran_date) FROM yeartodate)ytod
    JOIN 
(SELECT *, SUM(mtod.amount) OVER (PARTITION BY mtod.id, mtod.quarter_begin_date ORDER BY mtod.tran_date)
FROM (SELECT *, SUM(wtod.amount) OVER (PARTITION BY wtod.id, wtod.month_begin_date ORDER BY wtod.tran_date)
FROM (select id,
    year_begin_date,
    quarter_begin_date,
    month_begin_date,
    week_begin_date,
    tran_date,
    amount,
    SUM(amount) 
OVER (PARTITION BY id,week_begin_date ORDER BY tran_date) FROM table_name)wtod)mtod)qtod
ON qtod.id=ytod.id AND qtod.tran_date=ytod.tran_date;

仍然得到相同的错误。
在网上搜索后，我发现根据 JIRA 为 Hive 提出的 Hive 本身存在问题

由于 jira 现已修复，并且补丁在 hive 14 中可用，所以我尝试在 hive 14（HDP） 上运行它。
仍然得到相同的错误。
请写下您的建议.....

Answer 1

SELECT中的非别名函数调用映射到列名_c1、_c2等。在这种情况下，每个SELECT都有一个非别名函数调用，因此它们都创建一个列_c1。

问题是，因为您正在从下一个子查询向下执行SELECT *，然后附加另一个映射到_c1的函数调用，因此您有相同的列命名两次，因此围绕不明确的列引用出错。

解决方案应该是为所有函数调用设置别名，以便它们不使用_c1默认名称，如下所示：

SELECT * FROM
    (SELECT id,year_begin_date,tran_date,amount,SUM(amount) AS ytod_amount_sum OVER (PARTITION BY id,year_begin_date ORDER BY tran_date) FROM yeartodate)ytod
    JOIN 
(SELECT *, SUM(mtod.amount) AS mtod_amount_sum OVER (PARTITION BY mtod.id, mtod.quarter_begin_date ORDER BY mtod.tran_date)
FROM (SELECT *, SUM(wtod.amount) AS wtod_amount_sum OVER (PARTITION BY wtod.id, wtod.month_begin_date ORDER BY wtod.tran_date)
FROM (select id,
    year_begin_date,
    quarter_begin_date,
    month_begin_date,
    week_begin_date,
    tran_date,
    amount,
    SUM(amount) AS amount_sum
OVER (PARTITION BY id,week_begin_date ORDER BY tran_date) FROM table_name)wtod)mtod)qtod
ON qtod.id=ytod.id AND qtod.tran_date=ytod.tran_date;