Hive on Spark下row_number()问题排查

set spark.executor.instances=20;

set spark.executor.cores=3;

set spark.executor.memory=4g;

set spark.driver.memory=4g;

select 

    *,

    -- 后面可以根据distance绝对值的大小来判定哪个离trigger_time最近

    row_number() over(partition by uuid,dlb_file_name_key,frame_num order by distance) rank

from (

    select

            t.*,

            CASE 

                WHEN (cast(t.delta as double) / 50) >= 0 THEN ceil(cast(t.delta as double) / 50)

                ELSE floor(cast(t.delta as double) / 50)

            END AS frame_num

        from (

            select

                vehicle_id,

                vehicle_uuid,

                uuid,

                -- 规避同一个uuid 出现多个baseline rule_name

                max(baseline) over(partition by uuid) as baseline,

                max(rule_name) over(partition by uuid) as rule_name,

                dlb_file_name_key,

                trigger_time,

                cast(trigger_time as bigint)/1000000 AS trigger_time_ms,

                date_format(from_utc_timestamp(bigint(trigger_time/1000000),'GMT+8'),'yyyy-MM-dd HH:mm:ss.SSS')  as trigger_time_ms_format,

                cast(utc_timestamp as bigint)/1000000 AS utc_timestamp,

                (cast(substr(utc_timestamp,1,13) as bigint) - cast(substr(trigger_time,1,13) as bigint)) AS delta,

                -- 差值

                cast((cast(utc_timestamp as bigint) - cast(trigger_time as bigint)) as bigint) AS distance

            FROM ad_d_ods.ex_s_adw_production_collection_datafiles_raw_delta_parquet_hh

            WHERE pt between '2022090600' and '2022090623'

            and rule_name = 'aeb_act_event'

            and uuid = '1b9c213a-f299-4522-9072-cb9046707dab'

            and dlb_file_name_key in (

                'np-debug-dgb_aebstrategy'

            )

        ) t

) t1

order by frame_num,distance