创建表格错误。

Question

你能帮我克服我在创建下表时遇到的错误吗？

谢谢

CREATE TABLE RANGE_FT (HOSP_VAR STRING, RANGE INT) 
AS 
            SELECT 'EMR'   ,MAX(emr_avg_score)   - MIN(emr_avg_score)   from JOIN9 
    UNION   SELECT 'SCI'   ,MAX(sci_avg_score)   - MIN(sci_avg_score)   from JOIN9 
    UNION   SELECT 'ASTH'  ,MAX(asth_avg_score)  - MIN(asth_avg_score)  from JOIN9 
    UNION   SELECT 'HF'    ,MAX(hf_avg_score)    - MIN(hf_avg_score)    from JOIN9 
    UNION   SELECT 'SC'    ,MAX(sc_avg_score)    - MIN(sc_avg_score)    from JOIN9 
    UNION   SELECT 'PNEU'  ,MAX(pneu_avg_score)  - MIN(pneu_avg_score)  from JOIN9 
    UNION   SELECT 'PREV'  ,MAX(prev_avg_score)  - MIN(prev_avg_score)  from JOIN9 
    UNION   SELECT 'BC'    ,MAX(BC_avg_score)    - MIN(BC_avg_score)    from JOIN9 
    UNION   SELECT 'HEART' ,MAX(heart_avg_score) - MIN(heart_avg_score) from JOIN9 
    UNION   SELECT 'PREG'  ,MAX(preg_avg_score)  - MIN(preg_avg_score)  from JOIN9
;

第7行:在“SELECT”处缺少6行

Answer 1

1. UNION不被您的Hive版本所支持(它也不是您在这里所需要的)。 使用UNION ALL代替。https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Union
2. UNION ALL都需要来自双方的相同架构。 在Hive中，与其他SQL方言不同，不仅类型应该相似，而且列别名也应该相似。https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Union
3. 不能在create语句中给出列的列表。 将相关别名添加到SELECT子句中。

--这是查询的固定版本

CREATE TABLE RANGE_FT
AS 
                SELECT 'EMR'   as HOSP_VAR ,MAX (emr_avg_score)   - MIN (emr_avg_score)   as RANGE  from JOIN9 
    UNION ALL   SELECT 'SCI'   as HOSP_VAR ,MAX (sci_avg_score)   - MIN (sci_avg_score)   as RANGE  from JOIN9 
    UNION ALL   SELECT 'ASTH'  as HOSP_VAR ,MAX (asth_avg_score)  - MIN (asth_avg_score)  as RANGE  from JOIN9 
    UNION ALL   SELECT 'HF'    as HOSP_VAR ,MAX (hf_avg_score)    - MIN (hf_avg_score)    as RANGE  from JOIN9 
    UNION ALL   SELECT 'SC'    as HOSP_VAR ,MAX (sc_avg_score)    - MIN (sc_avg_score)    as RANGE  from JOIN9 
    UNION ALL   SELECT 'PNEU'  as HOSP_VAR ,MAX (pneu_avg_score)  - MIN (pneu_avg_score)  as RANGE  from JOIN9 
    UNION ALL   SELECT 'PREV'  as HOSP_VAR ,MAX (prev_avg_score)  - MIN (prev_avg_score)  as RANGE  from JOIN9 
    UNION ALL   SELECT 'BC'    as HOSP_VAR ,MAX (BC_avg_score)    - MIN (BC_avg_score)    as RANGE  from JOIN9 
    UNION ALL   SELECT 'HEART' as HOSP_VAR ,MAX (heart_avg_score) - MIN (heart_avg_score) as RANGE  from JOIN9 
    UNION ALL   SELECT 'PREG'  as HOSP_VAR ,MAX (preg_avg_score)  - MIN (preg_avg_score)  as RANGE  from JOIN9
;

--这里是一个替代解决方案，只有一个选择的

create table RANGE_FT
as
select  inline
        (
            array
            (
                struct ('EMR'   ,MAX (emr_avg_score)   - MIN (emr_avg_score)  )
               ,struct ('SCI'   ,MAX (sci_avg_score)   - MIN (sci_avg_score)  )
               ,struct ('ASTH'  ,MAX (asth_avg_score)  - MIN (asth_avg_score) )
               ,struct ('HF'    ,MAX (hf_avg_score)    - MIN (hf_avg_score)   )
               ,struct ('SC'    ,MAX (sc_avg_score)    - MIN (sc_avg_score)   )
               ,struct ('PNEU'  ,MAX (pneu_avg_score)  - MIN (pneu_avg_score) )
               ,struct ('PREV'  ,MAX (prev_avg_score)  - MIN (prev_avg_score) )
               ,struct ('BC'    ,MAX (BC_avg_score)    - MIN (BC_avg_score)   )
               ,struct ('HEART' ,MAX (heart_avg_score) - MIN (heart_avg_score))
               ,struct ('PREG'  ,MAX (preg_avg_score)  - MIN (preg_avg_score) )
            )
        ) as (HOSP_VAR,RANGE)

from    JOIN9    
;

Answer 2

这将是杜杜提到的work.As，您不能在写创建时给出列名和选择。您可以使用Union，但是性能明智的UNION更好。根据您的使用情况选择

    CREATE TABLE RANGE_FT 
AS 
            SELECT 'EMR'  AS HOSP_VAR   ,MAX(emr_avg_score)   - MIN(emr_avg_score)  AS `RANGE`  from JOIN9 
    UNION   SELECT 'SCI'   AS HOSP_VAR ,MAX(sci_avg_score)   - MIN(sci_avg_score)   AS `RANGE` from JOIN9 
    UNION   SELECT 'ASTH'  AS HOSP_VAR ,MAX(asth_avg_score)  - MIN(asth_avg_score)  AS `RANGE` from JOIN9 
    UNION   SELECT 'HF'    AS HOSP_VAR ,MAX(hf_avg_score)    - MIN(hf_avg_score)    AS `RANGE` from JOIN9 
    UNION   SELECT 'SC'    AS HOSP_VAR ,MAX(sc_avg_score)    - MIN(sc_avg_score)    AS `RANGE` from JOIN9 
    UNION   SELECT 'PNEU'  AS HOSP_VAR ,MAX(pneu_avg_score)  - MIN(pneu_avg_score)  AS `RANGE` from JOIN9 
    UNION   SELECT 'PREV'  AS HOSP_VAR ,MAX(prev_avg_score)  - MIN(prev_avg_score)  AS `RANGE` from JOIN9 
    UNION   SELECT 'BC'    AS HOSP_VAR ,MAX(BC_avg_score)    - MIN(BC_avg_score)    AS `RANGE` from JOIN9 
    UNION   SELECT 'HEART' AS HOSP_VAR ,MAX(heart_avg_score) - MIN(heart_avg_score) AS `RANGE` from JOIN9 
    UNION   SELECT 'PREG'  AS HOSP_VAR ,MAX(preg_avg_score)  - MIN(preg_avg_score)  AS `RANGE` from JOIN9
;