[SQL]group by和order by是否能寫欄位別名

一、錯誤例項

--剔除汙染之後每一組人數不同
select 
    camp.group_id as group_id --實驗分組
    ,int((datediff(arrive.arrive_dt,'2018-06-20')-1)/7)+1 as ob_week --觀察周
    ,count(distinct camp.driver_id) as group_cnt --組人數
    ,count(distinct arrive.order_id) as arrive_cnt --完單數
    ,sum(beatles_split) as beatles_split --分賬
    ,sum(gmv) as gmv --gmv
from
    (
        select
            camp.driver_id as driver_id
            ,camp.group_id as group_id
            ,camp.extractor_name as extractor_name
            ,camp.user_ltv as user_ltv
            ,'2018-06-20' as start_date
        from
            ( 
                select  
                    distinct driver_id as driver_id
                    ,group_id
                    ,extractor_name
                    , '二期新手任務' as user_ltv
                from beatles_strategy.mission_publiser_record_view_intern
                where datetime='20180619'
                and extractor_name='extractor_continuity_20180619'
                and task_id in ('90','88','92')
            )camp --活動分組資訊
        left outer join 
            (
                select 
                    driver_id
                from beatles_dwd.dwd_order_arrive_d_view_intern
                where (is_test!=1 or is_test is null)
                --and status in (2,3,4,5,6,13,31)
                and concat_ws('-',year,month,day)  between '2018-06-15' and  '2018-06-20' 
                and (to_date(arrive_time)) between '2018-06-15' and  '2018-06-20'
                group by driver_id
            )t  --剔除非新手的車主
        on camp.driver_id = t.driver_id
        where camp.driver_id is not null and t.driver_id is null
    )camp --剔除了非新手的活動分組資訊
left outer join
    (
        select
            concat_ws('-',year,month,day) as arrive_dt --完單日期
            ,cast(driver_id as bigint) as driver_id
            ,cast(order_id as bigint) as order_id
        from beatles_dwd.dwd_order_arrive_d_view_intern
        where (is_test!=1 or is_test is null)
        and concat_ws('-',year,month,day) between '2018-06-20' and '${end_date}'
        group by cast(driver_id as bigint),cast(order_id as bigint),concat_ws('-',year,month,day)
    )arrive --完單情況
on camp.driver_id=arrive.driver_id
left outer join 
    (
        select
            cast(relative_id as bigint) as order_id --訂單id
            ,sum(beatles_split) as beatles_split  --單個訂單分賬
            ,max(total) as gmv --單個訂單gmv
        from beatles_ods.payment_view_intern
        where concat_ws('-',year,month,day) between '2018-06-20' and '${end_date}'
        group by cast(relative_id as bigint)
    )beatles_split
on arrive.order_id=beatles_split.order_id
group by group_id,int((datediff(arrive.arrive_dt,'2018-06-20')-1)/7)+1
order by group_id,int((datediff(arrive.arrive_dt,'2018-06-20')-1)/7)+1

 

這樣在hive下是解釋不通的，回顧一下SQL的執行順序https://blog.csdn.net/TOMOCAT/article/details/81586789。order by是在SQL的最後執行的，從而應該改成：

二、修改：

 

--剔除汙染之後每一組人數不同
select 
    camp.group_id as group_id --實驗分組
    ,int((datediff(arrive.arrive_dt,'2018-06-20')-1)/7)+1 as ob_week --觀察周
    ,count(distinct camp.driver_id) as group_cnt --組人數
    ,count(distinct arrive.order_id) as arrive_cnt --完單數
    ,sum(beatles_split) as beatles_split --分賬
    ,sum(gmv) as gmv --gmv
from
--skip--
group by group_id,int((datediff(arrive.arrive_dt,'2018-06-20')-1)/7)+1
order by group_id,ob_week