Hive:不支持的子查询表达式' extract_date':只有最高级别的子查询表达式

发布于 01-21 08:23 字数 2615 浏览 3 评论 0原文

我对Hive是新手,目前我正在使用WSL2上的Hive 3.1.2,我正在尝试使用命令 hive -f test2subquery.sql 在Hive CLI上运行此SQL脚本:

use mydb;

SELECT
    date_photo as extract_date, camping_code, 0 as nb_credit , 0 as nb_debit, SUM(s.nb_nuit_fid)
FROM

(

  SELECT
        extract_date as date_photo, camping_code, count(*) as nb_nuit_fid
  FROM
        extract_resa 
  WHERE
        extract_date = (select  CAST(y.extract_date as date) from app_params y)
  AND extract_date>= datein 
  AND extract_date< date_add(datein, nb_nuit_fid) 
  AND nb_nuit_fid > 0
  AND statres <> 'X'
  AND id_stay_to = 'NULL'  -- eliminer les TO
  AND idstay <> idstay_res -- seulement les residents reel
  AND client_id_grp <> -1
  GROUP BY
   extract_date , camping_code

  )e
GROUP BY
date_photo, camping_code 

但是我一直在开始此错误:不支持的子查询表达式“ extract_date':仅允许使用最高级别的子查询表达式 我尝试在子查询中使用别名,但仍然遇到相同的错误。

use mydb;

SELECT
    s.extract_date, s.camping_code, 0 as nb_credit , 0 as nb_debit, SUM(s.nb_nuit_fid)
FROM

(

  SELECT
        r.extract_date, r.camping_code, count(*) as nb_nuit_fid
  FROM
        extract_resa r
  WHERE
        r.extract_date = (select  CAST(y.extract_date as date) from app_params y)
  AND r.extract_date>= r.datein 
  AND r.extract_date< date_add(r.datein, nb_nuit_fid) 
  AND nb_nuit_fid > 0
  AND r.statres <> 'X'
  AND r.id_stay_to = 'NULL'  -- eliminer les TO
  AND r.idstay <> r.idstay_res -- seulement les residents reel
  AND r.client_id_grp <> -1
  GROUP BY
   r.extract_date , r.camping_code

  )s
GROUP BY
date_photo, camping_code 

当评论行 extract_date&lt&lt时,我也感到困惑。 date_add(datein,nb_nuit_fid)这样:

use mydb;

SELECT
    s.date_photo as extract_date, s.camping_code, 0 as nb_credit , 0 as nb_debit, SUM(s.nb_nuit_fid)
FROM

(

  SELECT
            extract_date as date_photo, camping_code, count(*) as nb_nuit_fid
  FROM
            extract_resa 
  WHERE
           extract_date <   (select cast(y.extract_date as date) from app_params y) 
  AND 
  extract_date >= datein 
  /* AND extract_date < date_add(datein, nb_nuit_fid) */
  AND nb_nuit_fid > 0
  AND statres <> 'X'
  AND id_stay_to = 'NULL'  -- eliminer les TO
  AND idstay <> idstay_res -- seulement les residents reel
  AND client_id_grp <> -1
  GROUP BY
     extract_date, camping_code
  )s
GROUP BY
date_photo, camping_code 

脚本运行顺利。请帮忙。

I am kinda new to HIVE and I am currently using HIVE 3.1.2 on WSL2 and I am trying to run this SQL script on HIVE CLI using the command hive -f test2subquery.sql:

use mydb;

SELECT
    date_photo as extract_date, camping_code, 0 as nb_credit , 0 as nb_debit, SUM(s.nb_nuit_fid)
FROM

(

  SELECT
        extract_date as date_photo, camping_code, count(*) as nb_nuit_fid
  FROM
        extract_resa 
  WHERE
        extract_date = (select  CAST(y.extract_date as date) from app_params y)
  AND extract_date>= datein 
  AND extract_date< date_add(datein, nb_nuit_fid) 
  AND nb_nuit_fid > 0
  AND statres <> 'X'
  AND id_stay_to = 'NULL'  -- eliminer les TO
  AND idstay <> idstay_res -- seulement les residents reel
  AND client_id_grp <> -1
  GROUP BY
   extract_date , camping_code

  )e
GROUP BY
date_photo, camping_code 

But I keep getting this error: Unsupported SubQuery Expression 'extract_date': Only SubQuery expressions that are top level conjuncts are allowed
I tried using alias inside the subquery but i still get the same error.

use mydb;

SELECT
    s.extract_date, s.camping_code, 0 as nb_credit , 0 as nb_debit, SUM(s.nb_nuit_fid)
FROM

(

  SELECT
        r.extract_date, r.camping_code, count(*) as nb_nuit_fid
  FROM
        extract_resa r
  WHERE
        r.extract_date = (select  CAST(y.extract_date as date) from app_params y)
  AND r.extract_date>= r.datein 
  AND r.extract_date< date_add(r.datein, nb_nuit_fid) 
  AND nb_nuit_fid > 0
  AND r.statres <> 'X'
  AND r.id_stay_to = 'NULL'  -- eliminer les TO
  AND r.idstay <> r.idstay_res -- seulement les residents reel
  AND r.client_id_grp <> -1
  GROUP BY
   r.extract_date , r.camping_code

  )s
GROUP BY
date_photo, camping_code 

what also confuses me is when commenting the line extract_date< date_add(datein, nb_nuit_fid) like this:

use mydb;

SELECT
    s.date_photo as extract_date, s.camping_code, 0 as nb_credit , 0 as nb_debit, SUM(s.nb_nuit_fid)
FROM

(

  SELECT
            extract_date as date_photo, camping_code, count(*) as nb_nuit_fid
  FROM
            extract_resa 
  WHERE
           extract_date <   (select cast(y.extract_date as date) from app_params y) 
  AND 
  extract_date >= datein 
  /* AND extract_date < date_add(datein, nb_nuit_fid) */
  AND nb_nuit_fid > 0
  AND statres <> 'X'
  AND id_stay_to = 'NULL'  -- eliminer les TO
  AND idstay <> idstay_res -- seulement les residents reel
  AND client_id_grp <> -1
  GROUP BY
     extract_date, camping_code
  )s
GROUP BY
date_photo, camping_code 

the script runs smoothly. please help.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

枕花眠2025-01-28 08:23:56

将您的子查询子句转换为内在加入。

use mydb;

SELECT
    s.extract_date, s.camping_code, 0 as nb_credit , 0 as nb_debit, SUM(s.nb_nuit_fid)
FROM

(

  SELECT
        r.extract_date, r.camping_code, count(*) as nb_nuit_fid
  FROM
        extract_resa r
  INNER JOIN (select  CAST(y.extract_date as date) ext_dt from app_params y) y ON  r.extract_date = y.ext_dt -- this inner join will work same as your filter in subquery
  WHERE 
      r.extract_date>= r.datein 
  AND r.extract_date< date_add(r.datein, nb_nuit_fid) 
  AND nb_nuit_fid > 0
  AND r.statres <> 'X'
  AND r.id_stay_to = 'NULL'  -- eliminer les TO
  AND r.idstay <> r.idstay_res -- seulement les residents reel
  AND r.client_id_grp <> -1
  GROUP BY
   r.extract_date , r.camping_code

  )s
GROUP BY
date_photo, camping_code 

我不确定评论该线路如何工作。请让我知道结果。

convert your sub query clause to inner join.

use mydb;

SELECT
    s.extract_date, s.camping_code, 0 as nb_credit , 0 as nb_debit, SUM(s.nb_nuit_fid)
FROM

(

  SELECT
        r.extract_date, r.camping_code, count(*) as nb_nuit_fid
  FROM
        extract_resa r
  INNER JOIN (select  CAST(y.extract_date as date) ext_dt from app_params y) y ON  r.extract_date = y.ext_dt -- this inner join will work same as your filter in subquery
  WHERE 
      r.extract_date>= r.datein 
  AND r.extract_date< date_add(r.datein, nb_nuit_fid) 
  AND nb_nuit_fid > 0
  AND r.statres <> 'X'
  AND r.id_stay_to = 'NULL'  -- eliminer les TO
  AND r.idstay <> r.idstay_res -- seulement les residents reel
  AND r.client_id_grp <> -1
  GROUP BY
   r.extract_date , r.camping_code

  )s
GROUP BY
date_photo, camping_code 

I am not sure how commenting that line is working. Pls let me know the outcome.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文