hadoop - 如何在HIVE中使用带有NULLS LAST的ASC进行订购

NULLS LAST不起作用-在后续查询中出现以下错误:
失败:ParseException行41:9在'ASC'附近的'NULLS'处缺少EOF

不知道问题出在哪里，否则查询将运行并排序良好，且最后一条语句不为空。

set hive.cli.print.header=true;

SELECT
    P3P.campaign_id,
    P3P.campaign_name,
    P3P.strategy_id,
    P3P.strategy_name,
    P3P.segment_id,
    P3P.full_path,
    COUNT(*) AS impressions,
    COUNT(DISTINCT P3P.mm_uuid) AS distinct_users,
    SUM(P3P.media_cost) AS media_cost,
    SUM(P3P.total_ad_cost) AS total_ad_cost,
    SUM(P3P.total_spend) AS total_spend,
    AVG(P3P.total_spend_cpm) AS cpm,
    SUM(CASE WHEN AE.event_type = 'click' THEN 1 ELSE NULL END) AS clicks,
    SUM(CASE WHEN AE.event_type = 'conversion' THEN 1 ELSE NULL END) AS conversions,
    (SUM(CASE WHEN AE.event_type = 'click' THEN 1 ELSE NULL END) / (COUNT(P3P.mm_uuid))) AS ctr,
    (SUM(CASE WHEN AE.event_type = 'click' THEN 1 ELSE NULL END) / (SUM(CASE WHEN AE.event_type = 'conversion' THEN 1 ELSE NULL END))) AS ctc,
    (SUM(P3P.total_spend)/(SUM(CASE WHEN AE.event_type = 'click' THEN 1 ELSE NULL END))) AS cost_per_click,
    (SUM(P3P.total_spend) /(SUM(CASE WHEN AE.event_type = 'conversion' THEN 1 ELSE NULL END))) AS cpa,
    ((SUM(CASE WHEN AE.event_type = 'conversion' THEN 1 ELSE NULL END))*1000)/COUNT(*) AS rr_per_1k_imps,
    AVG(P3P.segment_cpm) AS segment_cpm,
    (SUM(P3P.segment_spend)/(SUM(CASE WHEN AE.event_type = 'click' THEN 1 ELSE NULL END))) AS segment_cpc,
    (SUM(P3P.segment_spend)/(SUM(CASE WHEN AE.event_type = 'conversion' THEN 1 ELSE NULL END))) AS segment_cpc

FROM tmp_perfon3p2 P3P

LEFT JOIN mm_attributed_events AE ON AE.mm_uuid = P3P.mm_uuid
    AND AE.event_date BETWEEN '2015-06-14' and '2015-06-21'
    AND  AE.organization_id = '100426'
    AND AE.agency_id = '101286'
    AND AE.advertiser_id = '108076'
GROUP BY
    P3P.campaign_id,
    P3P.campaign_name,
    P3P.strategy_id,
    P3P.strategy_name,
    P3P.segment_id,
    P3P.full_path
ORDER BY
    cpa ASC NULLS LAST
LIMIT 100;

最佳答案

错误非常明显。 Hive不喜欢ASC和NULLS之间的第41行。
每Cloudera Documentation



编辑(解决方法):

假设您有此汇总数据

col1
----
10.1
NULL
NULL
2.1
3.7

查询:

select col1
from (
  select col1
    , case when col1 is null then 0 else 1 end as flg
  from inner_query
  order by flg desc, col1 asc) x

输出:

col1
----
2.1
3.7
10.1
NULL
NULL

关于hadoop - 如何在HIVE中使用带有NULLS LAST的ASC进行订购，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30984077/