活动介绍
file-type

选择重传协议(SR)窗口大小限制及应用解析

PDF文件

下载需积分: 5 | 1.42MB | 更新于2025-12-12 | 7 浏览量 | 0 下载量 举报 收藏
download 立即下载
选择重传协议(SR,Selective Repeat)是计算机网络数据链路层用于保证数据传输可靠性的一种协议。它允许接收方仅对失序到达的帧进行接收,而不需要接收方等待所有帧都到达后再进行确认。这种方式可以有效减少不必要的数据重传,提高网络传输效率。 要点总览: 1. SR协议不支持“累计确认”,必须对每一帧单独确认。 2. SR协议的窗口大小限制条件有两条: - 若使用n位来编号帧,发送窗口大小(WT)和接收窗口大小(WR)的和必须小于或等于2^n。 - 接收窗口大小(WR)必须小于或等于发送窗口大小(WT)。 3. 在实际应用中,发送窗口和接收窗口大小通常相等。 王道考研/CSKAOYAN.COM的资料提到,对于使用n位进行帧编号的情况,选择重传协议的窗口大小必须满足特定的条件。如果发送窗口大小为WT,接收窗口大小为WR,那么窗口大小必须遵循以下规则: - 条件①:WT + WR ≤ 2^n - 条件②:WR ≤ WT 这些条件确保了协议在操作中的正确性,以避免发生死锁或数据混淆的情况。当使用3位进行帧编号时,其编号范围是0到7,总共有8个可能的帧序号。如果发送窗口和接收窗口的大小总和超过了2^n(即8),或者接收窗口大于发送窗口,都可能导致协议无法正常工作。 在实际网络通信中,为了简化设计,通常选择使发送窗口和接收窗口大小相等,即WT == WR。这样做的好处是简化了窗口管理的复杂性,同时还能保证协议的正常运作。 SR协议中,接收方和发送方都有各自独立的窗口。发送方会根据自己的发送窗口来决定可以发送哪些帧,而接收方则会根据自己的接收窗口来决定可以接收哪些帧,并对收到的帧进行确认。当发送方发送的某个帧未被确认时,它会将该帧保留在发送窗口内,并在需要时重传。接收方则会对接收到的帧进行检查,如果帧是期望接收的下一个帧,则正常处理;如果不是,则将其缓存到一个队列中,并向发送方发送一个确认信息,告知其应该发送哪个帧。这样,只有丢失或者出错的帧会被重传,而不是整个数据序列。 值得注意的是,在SR协议中,接收方使用的选择重传策略要求它必须能够缓存多个到达的帧,以便对失序到达的帧进行重新排序,然后按正确的顺序提交给上层。发送方也要能够处理重复的确认信息,并且只有当接收到接收方的确认信息后,才能将窗口内的帧移出,并发送后续的帧。 通过使用选择重传协议,网络资源的利用率得到了提升,因为只有那些真正需要重传的帧才会被发送方再次发送,从而减少了不必要的网络流量和带宽消耗。同时,它也降低了数据传输的延迟,因为它避免了等待全部帧都到达后再进行处理的延迟。然而,选择重传协议也有其局限性,如需要更大的接收缓冲区来缓存失序的帧,以及协议实现比停止-等待协议和连续ARQ协议更复杂。

相关推荐

filetype

Unique (cost=1424644.50..1424644.56 rows=1 width=754) Output: tmp18.staff_code, tmp4.store_cycle_name, tmp4.host_cycle_code, tmp4.store_cycle_code, (COALESCE((max((max((hz1.order_done_display_flag)::text)))), '0'::text)), (COALESCE((sum((sum(hz1.input_packed_quantity)))), '0'::numeric)), (COALESCE((sum((sum(hz1.input_amount)))), '0'::numeric)), (CASE WHEN ((rz.order_way)::text = '1'::text) THEN '1'::text WHEN ((rz.order_way)::text = '2'::text) THEN '2'::text ELSE '0'::text END), '100'::text, (CASE WHEN ((tmp4.order_method_type)::text = '07'::text) THEN '0'::character varying ELSE COALESCE(rz.displayorder, '0'::character varying) END), '111'::text, (CASE WHEN ((rz.reading_method IS NOT NULL) AND ((rz.reading_method)::text = '0'::text) AND ((array_agg(ici.information_category_code)) IS NOT NULL)) THEN '0'::text WHEN ((rz.reading_method IS NOT NULL) AND ((rz.reading_method)::text = '1'::text) AND ((array_agg(icg.gondola_number)) IS NOT NULL)) THEN '1'::text WHEN (yt.reading_method IS NOT NULL) THEN CASE WHEN ((yt.reading_method)::text = '1'::text) THEN '0'::text WHEN ((yt.reading_method)::text = '2'::text) THEN '1'::text ELSE NULL::text END WHEN (((array_agg(ici.information_category_code)) IS NOT NULL) AND ((array_agg(icg.gondola_number)) IS NULL)) THEN '0'::text WHEN (((array_agg(ici.information_category_code)) IS NULL) AND ((array_agg(icg.gondola_number)) IS NOT NULL)) THEN '1'::text WHEN ((tmp4.order_method_type)::text = ANY ('{04,05,06,09}'::text[])) THEN '1'::text ELSE '0'::text END), (('1'::text || CASE WHEN ((tmp4.order_method_type)::text = ANY ('{04,05,06,09}'::text[])) THEN '1'::text ELSE '0'::text END)), (array_agg(ici.information_category_code)), (array_agg(ici.information_category_name)), (array_agg(ici.reading_number)), (array_agg(ici.setting_order)), (array_agg(icg.information_category_code)), (array_agg(icg.information_category_name)), (array_agg(icg.information_category_display_order)), (array_agg(icg.gondola_number)), (array_agg(icg.other_information_category_flag)), (array_agg(icg.unregistered_information_category_flag)), tmp4.order_method_type, tmp4.cycle_display_order CTE tmp17 -> HashAggregate (cost=34304.41..34306.11 rows=170 width=294) Output: jk_4.version, jk_4.pattern_type, jk_4.pattern_code, jk_4.apply_start_date, jk_4.apply_end_date, jk_4.host_cycle_code, jk_4.store_cycle_code, jk_4.information_category_code, jk_4.license_code, jk_4.recommendation_item_quantity, jk_4.reading_item_quantity, jk_4.addition_type, jk_4.revised_type, jk_4.specific_item_flag, jk_4.addition_specific_item_flag, jk_4.revised_specific_item_flag, jk_4.update_datetime, jk_4.update_function_id, jk_4.update_person_id, jk_4.update_count Group Key: jk_4.version, jk_4.pattern_type, jk_4.pattern_code, jk_4.apply_start_date, jk_4.apply_end_date, jk_4.host_cycle_code, jk_4.store_cycle_code, jk_4.information_category_code, jk_4.license_code, jk_4.recommendation_item_quantity, jk_4.reading_item_quantity, jk_4.addition_type, jk_4.revised_type, jk_4.specific_item_flag, jk_4.addition_specific_item_flag, jk_4.revised_specific_item_flag, jk_4.update_datetime, jk_4.update_function_id, jk_4.update_person_id, jk_4.update_count -> Append (cost=7862.41..34295.91 rows=170 width=294) -> Unique (cost=7862.41..7889.70 rows=169 width=89) Output: jk_4.version, jk_4.pattern_type, jk_4.pattern_code, jk_4.apply_start_date, jk_4.apply_end_date, jk_4.host_cycle_code, jk_4.store_cycle_code, jk_4.information_category_code, jk_4.license_code, jk_4.recommendation_item_quantity, jk_4.reading_item_quantity, jk_4.addition_type, jk_4.revised_type, jk_4.specific_item_flag, jk_4.addition_specific_item_flag, jk_4.revised_specific_item_flag, jk_4.update_datetime, jk_4.update_function_id, jk_4.update_person_id, jk_4.update_count -> Gather Merge (cost=7862.41..7882.09 rows=169 width=89) Output: jk_4.version, jk_4.pattern_type, jk_4.pattern_code, jk_4.apply_start_date, jk_4.apply_end_date, jk_4.host_cycle_code, jk_4.store_cycle_code, jk_4.information_category_code, jk_4.license_code, jk_4.recommendation_item_quantity, jk_4.reading_item_quantity, jk_4.addition_type, jk_4.revised_type, jk_4.specific_item_flag, jk_4.addition_specific_item_flag, jk_4.revised_specific_item_flag, jk_4.update_datetime, jk_4.update_function_id, jk_4.update_person_id, jk_4.update_count Workers Planned: 2 -> Sort (cost=6862.39..6862.56 rows=70 width=89) Output: jk_4.version, jk_4.pattern_type, jk_4.pattern_code, jk_4.apply_start_date, jk_4.apply_end_date, jk_4.host_cycle_code, jk_4.store_cycle_code, jk_4.information_category_code, jk_4.license_code, jk_4.recommendation_item_quantity, jk_4.reading_item_quantity, jk_4.addition_type, jk_4.revised_type, jk_4.specific_item_flag, jk_4.addition_specific_item_flag, jk_4.revised_specific_item_flag, jk_4.update_datetime, jk_4.update_function_id, jk_4.update_person_id, jk_4.update_count Sort Key: jk_4.pattern_type, jk_4.pattern_code, jk_4.apply_start_date, jk_4.apply_end_date, jk_4.host_cycle_code, jk_4.store_cycle_code, jk_4.information_category_code, jk_4.license_code, jk_4.recommendation_item_quantity, jk_4.reading_item_quantity, jk_4.addition_type, jk_4.revised_type, jk_4.addition_specific_item_flag, jk_4.revised_specific_item_flag, jk_4.update_datetime, jk_4.update_function_id, jk_4.update_person_id, jk_4.update_count -> Parallel Hash Join (cost=15.63..6860.24 rows=70 width=89) Output: jk_4.version, jk_4.pattern_type, jk_4.pattern_code, jk_4.apply_start_date, jk_4.apply_end_date, jk_4.host_cycle_code, jk_4.store_cycle_code, jk_4.information_category_code, jk_4.license_code, jk_4.recommendation_item_quantity, jk_4.reading_item_quantity, jk_4.addition_type, jk_4.revised_type, jk_4.specific_item_flag, jk_4.addition_specific_item_flag, jk_4.revised_specific_item_flag, jk_4.update_datetime, jk_4.update_function_id, jk_4.update_person_id, jk_4.update_count Hash Cond: (((jk_4.pattern_type)::text = (pt_1.pattern_type)::text) AND ((jk_4.pattern_code)::text = (pt_1.pattern_code)::text)) -> Parallel Seq Scan on ord.m_number_of_information_category_1111_001 jk_4 (cost=0.05..6718.05 rows=16826 width=89) Output: jk_4.version, jk_4.pattern_type, jk_4.pattern_code, jk_4.apply_start_date, jk_4.apply_end_date, jk_4.host_cycle_code, jk_4.store_cycle_code, jk_4.information_category_code, jk_4.license_code, jk_4.recommendation_item_quantity, jk_4.reading_item_quantity, jk_4.addition_type, jk_4.revised_type, jk_4.specific_item_flag, jk_4.addition_specific_item_flag, jk_4.revised_specific_item_flag, jk_4.update_datetime, jk_4.update_function_id, jk_4.update_person_id, jk_4.update_count Filter: ((jk_4.reading_item_quantity > '0'::numeric) AND ((jk_4.version)::text = '1111_001'::text) AND ((jk_4.specific_item_flag)::text = '0'::text) AND (to_date('2023-11-11'::text, 'YYYY-MM-DD'::text) >= jk_4.apply_start_date) AND (to_date('2023-11-11'::text, 'YYYY-MM-DD'::text) <= jk_4.apply_end_date) AND (((jk_4.license_code)::text = '00'::text) OR ((jk_4.license_code)::text = ANY ('{01,02,03,04,06,07,08,10,11,12,13,51,53,55,56,58,71,72,77,78}'::text[])))) -> Parallel Hash (cost=15.54..15.54 rows=3 width=7) Output: pt_1.pattern_type, pt_1.pattern_code Buckets: 1024 Batches: 1 Memory Usage: 40kB -> Parallel Index Scan using m_pattern_10010001_original_store_code_pattern_type_apply_s_idx on ord.m_pattern_10010001 pt_1 (cost=0.42..15.54 rows=3 width=7) Output: pt_1.pattern_type, pt_1.pattern_code Index Cond: (((pt_1.original_store_code)::text = '181013'::text) AND (pt_1.apply_start_date <= to_date('2023-11-11'::text, 'YYYY-MM-DD'::text))) Filter: (((pt_1.version)::text = '10010001'::text) AND (to_date('2023-11-11'::text, 'YYYY-MM-DD'::text) <= pt_1.apply_end_date)) -> Unique (cost=26388.31..26403.66 rows=1 width=89) Output: jk_5.version, jk_5.pattern_type, jk_5.pattern_code, jk_5.apply_start_date, jk_5.apply_end_date, jk_5.host_cycle_code, jk_5.store_cycle_code, jk_5.information_category_code, jk_5.license_code, jk_5.recommendation_item_quantity, jk_5.reading_item_quantity, jk_5.addition_type, jk_5.revised_type, jk_5.specific_item_flag, jk_5.addition_specific_item_flag, jk_5.revised_specific_item_flag, jk_5.update_datetime, jk_5.update_function_id, jk_5.update_person_id, jk_5.update_count -> Merge Join (cost=26388.31..26403.61 rows=1 width=89) Output: jk_5.version, jk_5.pattern_type, jk_5.pattern_code, jk_5.apply_start_date, jk_5.apply_end_date, jk_5.host_cycle_code, jk_5.store_cycle_code, jk_5.information_category_code, jk_5.license_code, jk_5.recommendation_item_quantity, jk_5.reading_item_quantity, jk_5.addition_type, jk_5.revised_type, jk_5.specific_item_flag, jk_5.addition_specific_item_flag, jk_5.revised_specific_item_flag, jk_5.update_datetime, jk_5.update_function_id, jk_5.update_person_id, jk_5.update_count Merge Cond: ((jk_5.pattern_type)::text = (pt_2.pattern_type)::text) Join Filter: ((jk_5.pattern_code)::text = (pt_2.pattern_code)::text) -> Gather Merge (cost=26387.89..26388.01 rows=1 width=103) Output: jk_5.version, jk_5.pattern_type, jk_5.pattern_code, jk_5.apply_start_date, jk_5.apply_end_date, jk_5.host_cycle_code, jk_5.store_cycle_code, jk_5.information_category_code, jk_5.license_code, jk_5.recommendation_item_quantity, jk_5.reading_item_quantity, jk_5.addition_type, jk_5.revised_type, jk_5.specific_item_flag, jk_5.addition_specific_item_flag, jk_5.revised_specific_item_flag, jk_5.update_datetime, jk_5.update_function_id, jk_5.update_person_id, jk_5.update_count, sm_1.pattern_type, sm_1.pattern_code, sr.original_store_code Workers Planned: 2 -> Sort (cost=25387.87..25387.87 rows=1 width=103) Output: jk_5.version, jk_5.pattern_type, jk_5.pattern_code, jk_5.apply_start_date, jk_5.apply_end_date, jk_5.host_cycle_code, jk_5.store_cycle_code, jk_5.information_category_code, jk_5.license_code, jk_5.recommendation_item_quantity, jk_5.reading_item_quantity, jk_5.addition_type, jk_5.revised_type, jk_5.specific_item_flag, jk_5.addition_specific_item_flag, jk_5.revised_specific_item_flag, jk_5.update_datetime, jk_5.update_function_id, jk_5.update_person_id, jk_5.update_count, sm_1.pattern_type, sm_1.pattern_code, sr.original_store_code Sort Key: jk_5.pattern_type, jk_5.pattern_code, jk_5.apply_start_date, jk_5.apply_end_date, jk_5.host_cycle_code, jk_5.store_cycle_code, jk_5.information_category_code, jk_5.license_code, jk_5.recommendation_item_quantity, jk_5.reading_item_quantity, jk_5.addition_type, jk_5.revised_type, jk_5.addition_specific_item_flag, jk_5.revised_specific_item_flag, jk_5.update_datetime, jk_5.update_function_id, jk_5.update_person_id, jk_5.update_count -> Parallel Hash Join (cost=18657.27..25387.86 rows=1 width=103) Output: jk_5.version, jk_5.pattern_type, jk_5.pattern_code, jk_5.apply_start_date, jk_5.apply_end_date, jk_5.host_cycle_code, jk_5.store_cycle_code, jk_5.information_category_code, jk_5.license_code, jk_5.recommendation_item_quantity, jk_5.reading_item_quantity, jk_5.addition_type, jk_5.revised_type, jk_5.specific_item_flag, jk_5.addition_specific_item_flag, jk_5.revised_specific_item_flag, jk_5.update_datetime, jk_5.update_function_id, jk_5.update_person_id, jk_5.update_count, sm_1.pattern_type, sm_1.pattern_code, sr.original_store_code Hash Cond: (((jk_5.pattern_type)::text = (sm_1.pattern_type)::text) AND ((jk_5.pattern_code)::text = (sm_1.pattern_code)::text) AND ((jk_5.host_cycle_code)::text = (sm_1.host_cycle_code)::text) AND ((jk_5.store_cycle_code)::text = (sm_1.store_cycle_code)::text) AND ((jk_5.information_category_code)::text = (sm_1.information_category_code)::text)) -> Parallel Seq Scan on ord.m_number_of_information_category_1111_001 jk_5 (cost=0.05..6718.05 rows=671 width=89) Output: jk_5.version, jk_5.pattern_type, jk_5.pattern_code, jk_5.apply_start_date, jk_5.apply_end_date, jk_5.host_cycle_code, jk_5.store_cycle_code, jk_5.information_category_code, jk_5.license_code, jk_5.recommendation_item_quantity, jk_5.reading_item_quantity, jk_5.addition_type, jk_5.revised_type, jk_5.specific_item_flag, jk_5.addition_specific_item_flag, jk_5.revised_specific_item_flag, jk_5.update_datetime, jk_5.update_function_id, jk_5.update_person_id, jk_5.update_count Filter: ((jk_5.reading_item_quantity > '0'::numeric) AND ((jk_5.version)::text = '1111_001'::text) AND ((jk_5.specific_item_flag)::text = '1'::text) AND (to_date('2023-11-11'::text, 'YYYY-MM-DD'::text) >= jk_5.apply_start_date) AND (to_date('2023-11-11'::text, 'YYYY-MM-DD'::text) <= jk_5.apply_end_date) AND (((jk_5.license_code)::text = '00'::text) OR ((jk_5.license_code)::text = ANY ('{01,02,03,04,06,07,08,10,11,12,13,51,53,55,56,58,71,72,77,78}'::text[])))) -> Parallel Hash (cost=18657.20..18657.20 rows=1 width=24) Output: sm_1.pattern_type, sm_1.pattern_code, sm_1.host_cycle_code, sm_1.store_cycle_code, sm_1.information_category_code, sr.original_store_code Buckets: 1024 Batches: 1 Memory Usage: 104kB -> Parallel Hash Join (cost=137.90..18657.20 rows=1 width=24) Output: sm_1.pattern_type, sm_1.pattern_code, sm_1.host_cycle_code, sm_1.store_cycle_code, sm_1.information_category_code, sr.original_store_code Hash Cond: ((sm_1.item_code)::text = (sr.item_code)::text) -> Parallel Seq Scan on ord.m_item_10010001 sm_1 (cost=0.00..18517.50 rows=478 width=24) Output: sm_1.pattern_type, sm_1.pattern_code, sm_1.host_cycle_code, sm_1.store_cycle_code, sm_1.information_category_code, sm_1.item_code Filter: (((sm_1.specific_item_type)::text = ANY ('{1,G}'::text[])) AND ((sm_1.version)::text = '10010001'::text) AND (to_date('2023-11-11'::text, 'YYYY-MM-DD'::text) >= sm_1.apply_start_date) AND (to_date('2023-11-11'::text, 'YYYY-MM-DD'::text) <= sm_1.apply_end_date)) -> Parallel Hash (cost=137.57..137.57 rows=26 width=14) Output: sr.original_store_code, sr.item_code Buckets: 1024 Batches: 1 Memory Usage: 40kB -> Parallel Index Scan using m_item_by_specific_store_recommendation_1110_008_pkey on ord.m_item_by_specific_store_recommendation_1110_008 sr (cost=0.43..137.57 rows=26 width=14) Output: sr.original_store_code, sr.item_code Index Cond: (((sr.original_store_code)::text = '181013'::text) AND (sr.apply_start_date <= to_date('2023-11-11'::text, 'YYYY-MM-DD'::text)) AND ((sr.version)::text = '1110_008'::text)) Filter: (to_date('2023-11-11'::text, 'YYYY-MM-DD'::text) <= sr.apply_end_date) -> Index Scan using m_pattern_10010001_original_store_code_pattern_type_apply_s_idx on ord.m_pattern_10010001 pt_2 (cost=0.42..15.57 rows=5 width=14) Output: pt_2.pattern_type, pt_2.pattern_code, pt_2.original_store_code Index Cond: (((pt_2.original_store_code)::text = '181013'::text) AND (pt_2.apply_start_date <= to_date('2023-11-11'::text, 'YYYY-MM-DD'::text))) Filter: (((pt_2.version)::text = '10010001'::text) AND (to_date('2023-11-11'::text, 'YYYY-MM-DD'::text) <= pt_2.apply_end_date)) 怎么找SQL传进去的变量是什么值

filetype

org.apache.kyuubi.KyuubiSQLException: Error operating ExecuteStatement: org.apache.spark.sql.catalyst.parser.ParseException: mismatched input 'AS' expecting {<EOF>, ';'}(line 412, pos 28) == SQL == CREATE TABLE orca01_dr_data.ads_rpt_fsm_sfm_kpi_funnel_dmo_t_1218 stored as parquet as with dealership_full as ( select distinct dealer_id ,region_code ,dealer_short_name ,dealer_short_name_en ,region_bmw_sale ,region_mini_sale ,region_moto_sale ,region_bmw_sale_en ,region_mini_sale_en ,region_moto_sale_en ,small_region_bmw_sale_en ,small_region_mini_sale_en ,small_region_moto_sale_en ,dealer_investor_name_en ,organization_id ,investor_id from cdl.cdl_dim_com_dmx_dealership_full_t where pday='20251124' ), emp_delear as ( select distinct emp.company_id ,emp.id ,emp.emp_name ,dealer.dealer_id ,dealer.organization_id ,dealer.region_bmw_sale_en ,dealer.region_mini_sale_en ,dealer.region_moto_sale_en ,dealer.small_region_bmw_sale_en ,dealer.small_region_mini_sale_en ,dealer.small_region_moto_sale_en ,dealer.investor_id from dwc.dwc_dim_com_dmo2_t_employee_full_t as emp left join dealership_full as dealer on emp.company_id=dealer.organization_id where emp.pday='20251124' ) ,funnel_base as ( select month_id ,brand_code ,series_code ,eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,leads_vol ,leads_vol_lm ,leads_vol_ly ,leads_vol_mom ,leads_vol_yoy ,leads_oppty_conv_rate ,leads_oppty_conv_rate_mom ,leads_oppty_conv_rate_yoy ,oppty_vol ,oppty_vol_lm ,oppty_vol_ly ,oppty_vol_mom ,oppty_vol_yoy ,'' as oppty_vol_others ,oppty_sr_conv_rate ,oppty_sr_conv_rate_mom ,oppty_sr_conv_rate_yoy ,sr_vol ,sr_vol_lm ,sr_vol_ly ,sr_vol_mom ,sr_vol_yoy ,sr_td_conv_rate ,cast(case when sr_vol=0 then null else order_vol/sr_vol end as decimal(32,4)) as sr_order_conv_rate ,cast(case when sr_vol=0 then null else order_vol/sr_vol end as decimal(32,4))-cast(case when sr_vol_lm=0 then null else order_vol_lm/sr_vol_lm end as decimal(32,4)) as sr_order_conv_rate_mom ,cast(case when sr_vol=0 then null else order_vol/sr_vol end as decimal(32,4))-cast(case when sr_vol_ly=0 then null else order_vol_ly/sr_vol_ly end as decimal(32,4)) as sr_order_conv_rate_yoy ,first_sr_vol ,re_sr_vol ,td_vol ,td_vol_lm ,td_vol_ly ,td_vol_mom ,td_vol_yoy ,order_vol ,order_vol_lm ,order_vol_ly ,order_vol_mom ,order_vol_yoy ,order_handover_conv_rate ,order_handover_conv_rate-order_handover_conv_rate_lm as order_handover_conv_rate_mom ,order_handover_conv_rate-order_handover_conv_rate_ly as order_handover_conv_rate_yoy ,canceled_order_vol ,handovered_order_vol ,handover_tbd_vol ,order_intake_vol ,order_cancel_vol ,order_cancel_rate ,order_cancel_rate_mom ,order_cancel_rate_yoy ,order_vol_with_td ,order_vol_without_td ,handover_vol ,handover_vol_lm ,handover_vol_ly ,handover_vol_mom ,handover_vol_yoy ,handover_intake_vol ,handover_cancel_vol ,cast(round(delivery_days/cus_handover_intake_vol,0) as int) as avg_delivery_days ,case when brand_code='BMW' and month_id<'202212' then cast(null as int) when brand_code='MINI' and month_id<'202304' then cast(null as int) else cast(round(delivery_days/cus_handover_intake_vol,0) - round(delivery_days_lm/cus_handover_intake_vol_lm,0) as int) end as avg_delivery_days_mom ,case when month_id<'202401' then cast(null as int) else cast(round(delivery_days/cus_handover_intake_vol,0) - round(delivery_days_ly/cus_handover_intake_vol_ly,0) as int) end as avg_delivery_days_yoy ,case when data_agg_type='YTDM' then case when month_id=substr('20251123',1,6) then datediff(from_unixtime(unix_timestamp('20251123','yyyyMMdd'),'yyyy-MM-dd')+ interval '1 day',from_unixtime(unix_timestamp(month_id||'01','yyyyMMdd'),'yyyy-MM-dd')) else 1 end when data_agg_type='YTD' then case when substr(month_id,1,4)=substr('20251123',1,4) and month_id=substr('20251123',1,6) then datediff(from_unixtime(unix_timestamp('20251123','yyyyMMdd'),'yyyy-MM-dd')+ interval '1 day',from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyyMMdd'),'yyyy-MM-dd')) else datediff(from_unixtime(unix_timestamp(month_id||'01','yyyyMMdd'),'yyyy-MM-dd')+ interval '1 month',from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyyMMdd'),'yyyy-MM-dd')) end end as pass_day ,case when data_agg_type='YTDM' then case when month_id=substr('20251123',1,6) then datediff(from_unixtime(unix_timestamp(month_id||'01','yyyyMMdd')) + interval '1 month'+ interval '1 day',from_unixtime(unix_timestamp(month_id||'01','yyyyMMdd'),'yyyy-MM-dd')) else 1 end when data_agg_type='YTD' then datediff(from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyyMMdd')) + interval '1 year'+ interval '1 day',from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyyMMdd'),'yyyy-MM-dd')) end as actual_day from dws.dws_fact_sal_ncs_fsm_ops_funnel_kpi_t where pday='20251123' and dim_geo_type in ('Emp','Agent') and same_eseries_flag=0 and source_type<>'Other' and ! ( (coalesce(brand_code,'')='BMW' AND coalesce(source_type,'') IN ('Retail','NSC','BDC') ) ) ) ,tgt_base as ( select month_id ,dim_type ,dim_value ,brand_code ,series_code ,eseries_code ,data_agg_type ,leads_channel ,cast(leads_tgt as int) as leads_vol_tgt ,cast(oppty_tgt as int) as oppty_vol_tgt ,cast(sr_tgt as int) as sr_vol_tgt ,cast(order_tgt as int) as order_vol_tgt ,cast(handover_tgt as int) as handover_vol_tgt from dws.dws_fact_sal_ncs_fsm_tgt_mon_t where pmonth='{$pmonth}' and leads_channel='All' and dim_type in ('Emp','Agent') ) ,leads_follow_base as ( select month_id ,leads_brand_code ,leads_series_code ,leads_eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,leads_timely_follow_up_rate ,leads_timely_follow_up_rate_mom ,leads_timely_follow_up_rate_yoy from dws.dws_fact_sal_ncs_fsm_leads_followup_his_t where pday='20251123' and leads_star_level='All' and dim_geo_type in ('Emp','Agent') ) ,oppty_follow_base as ( select month_id ,leads_brand_code ,leads_series_code ,leads_eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,oppty_timely_follow_up_rate ,oppty_timely_follow_up_rate_mom ,oppty_timely_follow_up_rate_yoy from dws.dws_fact_sal_ncs_fsm_oppty_followup_his_t where pday='20251123' and oppty_priority_level='All' and dim_geo_type in ('Emp','Agent') ) ,td_follow_base as ( select month_id ,leads_brand_code ,leads_series_code ,leads_eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,td_follow_up_rate ,td_follow_up_rate_mom ,td_follow_up_rate_yoy from dws.dws_fact_sal_ncs_fsm_td_followup_t where pday='20251123' and dim_geo_type in ('Emp','Agent') ) ,sr_purpose_base as ( select month_id ,leads_brand_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,sr_record_vol_first ,sr_record_vol_re ,sr_record_vol_order ,sr_record_vol_invalid from (select month_id,leads_brand_code,dim_geo_type,dim_geo_code,dim_geo_name_cn,dim_geo_name_en,data_agg_type,sr_purpose,sr_vol from dws.dws_fact_sal_ncs_fsm_showroom_purpose_analysis_t where pday='20251123')t pivot (max(sr_vol) for sr_purpose in ('1' as sr_record_vol_first,'2' as sr_record_vol_re,'3' as sr_record_vol_order,'4' as sr_record_vol_invalid)) ) ,tgt_achv as ( select month_id ,brand_code ,series_code ,eseries_code ,leads_achv_rate_ref_floor as leads_achv_rate_ref_floor ,oppty_achv_rate_ref_floor as oppty_achv_rate_ref_floor ,sr_achv_rate_ref_floor as sr_achv_rate_ref_floor ,order_achv_rate_ref_floor as order_achv_rate_ref_floor ,handover_achv_rate_ref_floor as handover_achv_rate_ref_floor ,leads_achv_rate_ref_ceiling as leads_achv_rate_ref_ceiling ,oppty_achv_rate_ref_ceiling as oppty_achv_rate_ref_ceiling ,sr_achv_rate_ref_ceiling as sr_achv_rate_ref_ceiling ,order_achv_rate_ref_ceiling as order_achv_rate_ref_ceiling ,handover_achv_rate_ref_ceiling as handover_achv_rate_ref_ceiling from (select month_id,brand_code,series_code,eseries_code,kpi,achv_rate_ref_floor,achv_rate_ref_ceiling from upload.dwc_fact_sal_ncs_fsm_kpi_tgt_achv_mn_t)t pivot (max(achv_rate_ref_floor) as achv_rate_ref_floor,max(achv_rate_ref_ceiling) as achv_rate_ref_ceiling for kpi in ('Leads' as leads,'Oppty' as oppty,'Showroom' as sr,'Order' as order,'Handover' as handover)) ) select t1.month_id ,t1.brand_code ,t1.series_code ,t1.eseries_code ,coalesce(dim1.dealer_id,dim2.dealer_id) as dealer_id ,coalesce(dim1.organization_id,dim2.organization_id) as organization_id ,case when t1.brand_code='BMW' then coalesce(dim1.region_bmw_sale_en,dim2.region_bmw_sale_en) when t1.brand_code='MINI' then coalesce(dim1.region_mini_sale_en,dim2.region_mini_sale_en) when t1.brand_code='MOTO' then coalesce(dim1.region_moto_sale_en,dim2.region_moto_sale_en) end as region_en ,case when t1.brand_code='BMW' then coalesce(dim1.small_region_bmw_sale_en,dim2.small_region_bmw_sale_en) when t1.brand_code='MINI' then coalesce(dim1.small_region_mini_sale_en,dim2.small_region_mini_sale_en) when t1.brand_code='MOTO' then coalesce(dim1.small_region_moto_sale_en,dim2.small_region_moto_sale_en) end as small_region_en ,coalesce(dim1.investor_id,dim2.investor_id) as investor_id ,t1.dim_geo_type ,t1.dim_geo_code ,t1.dim_geo_name_cn ,t1.dim_geo_name_en ,t1.data_agg_type ,t1.source_type ,t1.leads_vol ,t1.leads_vol_mom ,t1.leads_vol_yoy ,t2.leads_vol_tgt ,cast(case when t2.leads_vol_tgt=0 then null else (t1.leads_vol/t1.pass_day) / (t2.leads_vol_tgt/t1.actual_day) end as decimal(32,4)) as leads_achv_rate ,tgt_achv.leads_achv_rate_ref_floor ,tgt_achv.leads_achv_rate_ref_ceiling ,t1.leads_oppty_conv_rate ,t1.leads_oppty_conv_rate_mom ,t1.leads_oppty_conv_rate_yoy ,t3.leads_timely_follow_up_rate ,t3.leads_timely_follow_up_rate_mom ,t3.leads_timely_follow_up_rate_yoy ,t1.oppty_vol ,t1.oppty_vol_mom ,t1.oppty_vol_yoy ,tt.oppty_vol_others ,t2.oppty_vol_tgt ,cast(case when t2.oppty_vol_tgt=0 then null else (t1.oppty_vol/t1.pass_day) / (t2.oppty_vol_tgt/t1.actual_day) end as decimal(32,4)) as oppty_achv_rate ,tgt_achv.oppty_achv_rate_ref_floor ,tgt_achv.oppty_achv_rate_ref_ceiling ,t4.oppty_timely_follow_up_rate ,t4.oppty_timely_follow_up_rate_mom ,t4.oppty_timely_follow_up_rate_yoy ,t1.oppty_sr_conv_rate ,t1.oppty_sr_conv_rate_mom ,t1.oppty_sr_conv_rate_yoy ,t1.sr_vol ,t1.sr_vol_mom ,t1.sr_vol_yoy ,t2.sr_vol_tgt ,cast(case when t2.sr_vol_tgt=0 then null else (t1.sr_vol/t1.pass_day) / (t2.sr_vol_tgt/t1.actual_day) end as decimal(32,4)) as sr_achv_rate ,tgt_achv.sr_achv_rate_ref_floor ,tgt_achv.sr_achv_rate_ref_ceiling ,t1.sr_td_conv_rate ,t1.sr_order_conv_rate ,t1.sr_order_conv_rate_mom ,t1.sr_order_conv_rate_yoy ,t1.first_sr_vol ,t1.re_sr_vol ,t5.sr_record_vol_first ,t5.sr_record_vol_re ,t5.sr_record_vol_order ,t5.sr_record_vol_invalid ,t1.td_vol ,t1.td_vol_mom ,t1.td_vol_yoy ,t6.td_follow_up_rate ,t6.td_follow_up_rate_mom ,t6.td_follow_up_rate_yoy ,t1.order_vol ,t1.order_vol_mom ,t1.order_vol_yoy ,t2.order_vol_tgt ,cast(case when t2.order_vol_tgt=0 then null else (t1.order_vol/t1.pass_day) / (t2.order_vol_tgt/t1.actual_day) end as decimal(32,4)) as order_achv_rate ,tgt_achv.order_achv_rate_ref_floor ,tgt_achv.order_achv_rate_ref_ceiling ,t1.order_handover_conv_rate ,t1.order_handover_conv_rate_mom ,t1.order_handover_conv_rate_yoy ,t1.canceled_order_vol ,t1.handovered_order_vol ,t1.handover_tbd_vol ,t1.order_intake_vol ,t1.order_cancel_vol ,t1.order_cancel_rate ,t1.order_cancel_rate_mom ,t1.order_cancel_rate_yoy ,t1.order_vol_with_td ,t1.order_vol_without_td ,t1.handover_vol ,t1.handover_vol_mom ,t1.handover_vol_yoy ,t2.handover_vol_tgt ,cast(case when t2.handover_vol_tgt=0 then null else (t1.handover_vol/t1.pass_day) / (t2.handover_vol_tgt/t1.actual_day) end as decimal(32,4)) as handover_achv_rate ,tgt_achv.handover_achv_rate_ref_floor ,tgt_achv.handover_achv_rate_ref_ceiling ,t1.handover_intake_vol ,t1.handover_cancel_vol ,t1.avg_delivery_days ,t1.avg_delivery_days_mom ,t1.avg_delivery_days_yoy ,cast(case when t7.leads_vol=0 then null else t1.leads_vol/t7.leads_vol end as decimal(32,4)) as leads_ratio ,cast(case when t7.leads_vol=0 or t7.leads_vol_lm=0 then null else t1.leads_vol/t7.leads_vol - t1.leads_vol_lm/t7.leads_vol_lm end as decimal(32,4)) as leads_ratio_mom ,cast(case when t7.leads_vol=0 or t7.leads_vol_ly=0 then null else t1.leads_vol/t7.leads_vol - t1.leads_vol_ly/t7.leads_vol_ly end as decimal(32,4)) as leads_ratio_yoy ,cast(case when t7.oppty_vol=0 then null else t1.oppty_vol/t7.oppty_vol end as decimal(32,4)) as oppty_ratio ,cast(case when t7.oppty_vol=0 or t7.oppty_vol_lm=0 then null else t1.oppty_vol/t7.oppty_vol - t1.oppty_vol_lm/t7.oppty_vol_lm end as decimal(32,4)) as oppty_ratio_mom ,cast(case when t7.oppty_vol=0 or t7.oppty_vol_ly=0 then null else t1.oppty_vol/t7.oppty_vol - t1.oppty_vol_ly/t7.oppty_vol_ly end as decimal(32,4)) as oppty_ratio_yoy ,cast(case when t7.sr_vol=0 then null else t1.sr_vol/t7.sr_vol end as decimal(32,4)) as sr_ratio ,cast(case when t7.sr_vol=0 or t7.sr_vol_lm=0 then null else t1.sr_vol/t7.sr_vol - t1.sr_vol_lm/t7.sr_vol_lm end as decimal(32,4)) as sr_ratio_mom ,cast(case when t7.sr_vol=0 or t7.sr_vol_ly=0 then null else t1.sr_vol/t7.sr_vol - t1.sr_vol_ly/t7.sr_vol_ly end as decimal(32,4)) as sr_ratio_yoy ,cast(case when t7.td_vol=0 then null else t1.td_vol/t7.td_vol end as decimal(32,4)) as td_ratio ,cast(case when t7.td_vol=0 or t7.td_vol_lm=0 then null else t1.td_vol/t7.td_vol - t1.td_vol_lm/t7.td_vol_lm end as decimal(32,4)) as td_ratio_mom ,cast(case when t7.td_vol=0 or t7.td_vol_ly=0 then null else t1.td_vol/t7.td_vol - t1.td_vol_ly/t7.td_vol_ly end as decimal(32,4)) as td_ratio_yoy ,cast(case when t7.order_vol=0 then null else t1.order_vol/t7.order_vol end as decimal(32,4)) as order_ratio ,cast(case when t7.order_vol=0 or t7.order_vol_lm=0 then null else t1.order_vol/t7.order_vol - t1.order_vol_lm/t7.order_vol_lm end as decimal(32,4)) as order_ratio_mom ,cast(case when t7.order_vol=0 or t7.order_vol_ly=0 then null else t1.order_vol/t7.order_vol - t1.order_vol_ly/t7.order_vol_ly end as decimal(32,4)) as order_ratio_yoy ,cast(case when t7.handover_vol=0 then null else t1.handover_vol/t7.handover_vol end as decimal(32,4)) as handover_ratio ,cast(case when t7.handover_vol=0 or t7.handover_vol_lm=0 then null else t1.handover_vol/t7.handover_vol - t1.handover_vol_lm/t7.handover_vol_lm end as decimal(32,4)) as handover_ratio_mom ,cast(case when t7.handover_vol=0 or t7.handover_vol_ly=0 then null else t1.handover_vol/t7.handover_vol - t1.handover_vol_ly/t7.handover_vol_ly end as decimal(32,4)) as handover_ratio_yoy ,from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') as etl_create_time ,from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') as etl_update_time ,'BI' as etl_create_by ,'BI' as etl_update_by from funnel_base t1 left join tgt_base t2 on t1.month_id=t2.month_id and t1.dim_geo_type=t2.dim_type and t1.dim_geo_code=t2.dim_value and t1.brand_code=t2.brand_code and t1.eseries_code=t2.eseries_code and t1.data_agg_type=t2.data_agg_type and t1.source_type='Overview' left join leads_follow_base t3 on t1.month_id=t3.month_id and t1.dim_geo_type=t3.dim_geo_type and t1.dim_geo_code=t3.dim_geo_code and t1.brand_code=t3.leads_brand_code and t1.eseries_code=t3.leads_eseries_code and t1.data_agg_type=t3.data_agg_type and t1.source_type=t3.source_type left join oppty_follow_base t4 on t1.month_id=t4.month_id and t1.dim_geo_type=t4.dim_geo_type and t1.dim_geo_code=t4.dim_geo_code and t1.brand_code=t4.leads_brand_code and t1.eseries_code=t4.leads_eseries_code and t1.data_agg_type=t4.data_agg_type and t1.source_type=t4.source_type left join sr_purpose_base t5 on t1.month_id=t5.month_id and t1.dim_geo_type=t5.dim_geo_type and t1.dim_geo_code=t5.dim_geo_code and t1.brand_code=t5.leads_brand_code and t1.data_agg_type=t5.data_agg_type and t1.eseries_code='All' and t1.source_type='Overview' left join td_follow_base t6 on t1.month_id=t6.month_id and t1.dim_geo_type=t6.dim_geo_type and t1.dim_geo_code=t6.dim_geo_code and t1.brand_code=t6.leads_brand_code and t1.eseries_code=t6.leads_eseries_code and t1.data_agg_type=t6.data_agg_type and t1.source_type=t6.source_type left join funnel_base t7 on t1.month_id=t7.month_id and t1.dim_geo_type=t7.dim_geo_type and t1.dim_geo_code=t7.dim_geo_code and t1.brand_code=t7.brand_code and t1.eseries_code=t7.eseries_code and t1.data_agg_type=t7.data_agg_type and t1.source_type<>'Overview' and t7.source_type='Overview' left join dealership_full dim1 on t1.dim_geo_type='Agent' and t1.dim_geo_code=dim1.dealer_id left join emp_delear dim2 on t1.dim_geo_type='Emp' and t1.dim_geo_code=dim2.id left join tgt_achv on t1.brand_code=tgt_achv.brand_code left join dws.dws_fact_sal_ncs_fsm_ops_leads_oppty_sr_td_channel_t tt on tt.pday='20251123' and t1.month_id=tt.month_id and t1.dim_geo_type=tt.dim_geo_type and t1.dim_geo_code=tt.dim_geo_code and t1.brand_code=tt.brand_code and t1.eseries_code=tt.eseries_code and t1.data_agg_type=tt.data_agg_type and t1.source_type=tt.source_type and tt.same_eseries_flag=0 and tt.channel_name='All' WHERE t1.month_id>='202301' AS SERDE ----------------------------^^^ at org.apache.spark.sql.catalyst.parser.ParseException.withCommand(ParseDriver.scala:255) at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parse(ParseDriver.scala:124) at org.apache.spark.sql.execution.SparkSqlParser.parse(SparkSqlParser.scala:49) at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parsePlan(ParseDriver.scala:75) at org.apache.spark.sql.SparkSession.$anonfun$sql$2(SparkSession.scala:613) at org.apache.spark.sql.catalyst.QueryPlanningTracker.measurePhase(QueryPlanningTracker.scala:111) at org.apache.spark.sql.SparkSession.$anonfun$sql$1(SparkSession.scala:613) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:772) at org.apache.spark.sql.SparkSession.sql(SparkSession.scala:610) at org.apache.kyuubi.engine.spark.operation.ExecuteStatement.$anonfun$executeStatement$1(ExecuteStatement.scala:86) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at org.apache.kyuubi.engine.spark.operation.SparkOperation.$anonfun$withLocalProperties$1(SparkOperation.scala:147) at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:163) at org.apache.kyuubi.engine.spark.operation.SparkOperation.withLocalProperties(SparkOperation.scala:131) at org.apache.kyuubi.engine.spark.operation.ExecuteStatement.executeStatement(ExecuteStatement.scala:81) at org.apache.kyuubi.engine.spark.operation.ExecuteStatement$$anon$1.run(ExecuteStatement.scala:103) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) at org.apache.kyuubi.KyuubiSQLException$.apply(KyuubiSQLException.scala:70) at org.apache.kyuubi.engine.spark.operation.SparkOperation$$anonfun$onError$1.$anonfun$applyOrElse$1(SparkOperation.scala:181) at org.apache.kyuubi.Utils$.withLockRequired(Utils.scala:425) at org.apache.kyuubi.operation.AbstractOperation.withLockRequired(AbstractOperation.scala:52) at org.apache.kyuubi.engine.spark.operation.SparkOperation$$anonfun$onError$1.applyOrElse(SparkOperation.scala:169) at org.apache.kyuubi.engine.spark.operation.SparkOperation$$anonfun$onError$1.applyOrElse(SparkOperation.scala:164) at scala.runtime.AbstractPartialFunction.apply(AbstractPartialFunction.scala:38) at org.apache.kyuubi.engine.spark.operation.ExecuteStatement.executeStatement(ExecuteStatement.scala:92) at org.apache.kyuubi.engine.spark.operation.ExecuteStatement$$anon$1.run(ExecuteStatement.scala:103) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: org.apache.spark.sql.catalyst.parser.ParseException: mismatched input 'AS' expecting {<EOF>, ';'}(line 412, pos 28) == SQL == CREATE TABLE orca01_dr_data.ads_rpt_fsm_sfm_kpi_funnel_dmo_t_1218 stored as parquet as with dealership_full as ( select distinct dealer_id ,region_code ,dealer_short_name ,dealer_short_name_en ,region_bmw_sale ,region_mini_sale ,region_moto_sale ,region_bmw_sale_en ,region_mini_sale_en ,region_moto_sale_en ,small_region_bmw_sale_en ,small_region_mini_sale_en ,small_region_moto_sale_en ,dealer_investor_name_en ,organization_id ,investor_id from cdl.cdl_dim_com_dmx_dealership_full_t where pday='20251124' ), emp_delear as ( select distinct emp.company_id ,emp.id ,emp.emp_name ,dealer.dealer_id ,dealer.organization_id ,dealer.region_bmw_sale_en ,dealer.region_mini_sale_en ,dealer.region_moto_sale_en ,dealer.small_region_bmw_sale_en ,dealer.small_region_mini_sale_en ,dealer.small_region_moto_sale_en ,dealer.investor_id from dwc.dwc_dim_com_dmo2_t_employee_full_t as emp left join dealership_full as dealer on emp.company_id=dealer.organization_id where emp.pday='20251124' ) ,funnel_base as ( select month_id ,brand_code ,series_code ,eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,leads_vol ,leads_vol_lm ,leads_vol_ly ,leads_vol_mom ,leads_vol_yoy ,leads_oppty_conv_rate ,leads_oppty_conv_rate_mom ,leads_oppty_conv_rate_yoy ,oppty_vol ,oppty_vol_lm ,oppty_vol_ly ,oppty_vol_mom ,oppty_vol_yoy ,'' as oppty_vol_others ,oppty_sr_conv_rate ,oppty_sr_conv_rate_mom ,oppty_sr_conv_rate_yoy ,sr_vol ,sr_vol_lm ,sr_vol_ly ,sr_vol_mom ,sr_vol_yoy ,sr_td_conv_rate ,cast(case when sr_vol=0 then null else order_vol/sr_vol end as decimal(32,4)) as sr_order_conv_rate ,cast(case when sr_vol=0 then null else order_vol/sr_vol end as decimal(32,4))-cast(case when sr_vol_lm=0 then null else order_vol_lm/sr_vol_lm end as decimal(32,4)) as sr_order_conv_rate_mom ,cast(case when sr_vol=0 then null else order_vol/sr_vol end as decimal(32,4))-cast(case when sr_vol_ly=0 then null else order_vol_ly/sr_vol_ly end as decimal(32,4)) as sr_order_conv_rate_yoy ,first_sr_vol ,re_sr_vol ,td_vol ,td_vol_lm ,td_vol_ly ,td_vol_mom ,td_vol_yoy ,order_vol ,order_vol_lm ,order_vol_ly ,order_vol_mom ,order_vol_yoy ,order_handover_conv_rate ,order_handover_conv_rate-order_handover_conv_rate_lm as order_handover_conv_rate_mom ,order_handover_conv_rate-order_handover_conv_rate_ly as order_handover_conv_rate_yoy ,canceled_order_vol ,handovered_order_vol ,handover_tbd_vol ,order_intake_vol ,order_cancel_vol ,order_cancel_rate ,order_cancel_rate_mom ,order_cancel_rate_yoy ,order_vol_with_td ,order_vol_without_td ,handover_vol ,handover_vol_lm ,handover_vol_ly ,handover_vol_mom ,handover_vol_yoy ,handover_intake_vol ,handover_cancel_vol ,cast(round(delivery_days/cus_handover_intake_vol,0) as int) as avg_delivery_days ,case when brand_code='BMW' and month_id<'202212' then cast(null as int) when brand_code='MINI' and month_id<'202304' then cast(null as int) else cast(round(delivery_days/cus_handover_intake_vol,0) - round(delivery_days_lm/cus_handover_intake_vol_lm,0) as int) end as avg_delivery_days_mom ,case when month_id<'202401' then cast(null as int) else cast(round(delivery_days/cus_handover_intake_vol,0) - round(delivery_days_ly/cus_handover_intake_vol_ly,0) as int) end as avg_delivery_days_yoy ,case when data_agg_type='YTDM' then case when month_id=substr('20251123',1,6) then datediff(from_unixtime(unix_timestamp('20251123','yyyyMMdd'),'yyyy-MM-dd')+ interval '1 day',from_unixtime(unix_timestamp(month_id||'01','yyyyMMdd'),'yyyy-MM-dd')) else 1 end when data_agg_type='YTD' then case when substr(month_id,1,4)=substr('20251123',1,4) and month_id=substr('20251123',1,6) then datediff(from_unixtime(unix_timestamp('20251123','yyyyMMdd'),'yyyy-MM-dd')+ interval '1 day',from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyyMMdd'),'yyyy-MM-dd')) else datediff(from_unixtime(unix_timestamp(month_id||'01','yyyyMMdd'),'yyyy-MM-dd')+ interval '1 month',from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyyMMdd'),'yyyy-MM-dd')) end end as pass_day ,case when data_agg_type='YTDM' then case when month_id=substr('20251123',1,6) then datediff(from_unixtime(unix_timestamp(month_id||'01','yyyyMMdd')) + interval '1 month'+ interval '1 day',from_unixtime(unix_timestamp(month_id||'01','yyyyMMdd'),'yyyy-MM-dd')) else 1 end when data_agg_type='YTD' then datediff(from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyyMMdd')) + interval '1 year'+ interval '1 day',from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyyMMdd'),'yyyy-MM-dd')) end as actual_day from dws.dws_fact_sal_ncs_fsm_ops_funnel_kpi_t where pday='20251123' and dim_geo_type in ('Emp','Agent') and same_eseries_flag=0 and source_type<>'Other' and ! ( (coalesce(brand_code,'')='BMW' AND coalesce(source_type,'') IN ('Retail','NSC','BDC') ) ) ) ,tgt_base as ( select month_id ,dim_type ,dim_value ,brand_code ,series_code ,eseries_code ,data_agg_type ,leads_channel ,cast(leads_tgt as int) as leads_vol_tgt ,cast(oppty_tgt as int) as oppty_vol_tgt ,cast(sr_tgt as int) as sr_vol_tgt ,cast(order_tgt as int) as order_vol_tgt ,cast(handover_tgt as int) as handover_vol_tgt from dws.dws_fact_sal_ncs_fsm_tgt_mon_t where pmonth='{$pmonth}' and leads_channel='All' and dim_type in ('Emp','Agent') ) ,leads_follow_base as ( select month_id ,leads_brand_code ,leads_series_code ,leads_eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,leads_timely_follow_up_rate ,leads_timely_follow_up_rate_mom ,leads_timely_follow_up_rate_yoy from dws.dws_fact_sal_ncs_fsm_leads_followup_his_t where pday='20251123' and leads_star_level='All' and dim_geo_type in ('Emp','Agent') ) ,oppty_follow_base as ( select month_id ,leads_brand_code ,leads_series_code ,leads_eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,oppty_timely_follow_up_rate ,oppty_timely_follow_up_rate_mom ,oppty_timely_follow_up_rate_yoy from dws.dws_fact_sal_ncs_fsm_oppty_followup_his_t where pday='20251123' and oppty_priority_level='All' and dim_geo_type in ('Emp','Agent') ) ,td_follow_base as ( select month_id ,leads_brand_code ,leads_series_code ,leads_eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,td_follow_up_rate ,td_follow_up_rate_mom ,td_follow_up_rate_yoy from dws.dws_fact_sal_ncs_fsm_td_followup_t where pday='20251123' and dim_geo_type in ('Emp','Agent') ) ,sr_purpose_base as ( select month_id ,leads_brand_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,sr_record_vol_first ,sr_record_vol_re ,sr_record_vol_order ,sr_record_vol_invalid from (select month_id,leads_brand_code,dim_geo_type,dim_geo_code,dim_geo_name_cn,dim_geo_name_en,data_agg_type,sr_purpose,sr_vol from dws.dws_fact_sal_ncs_fsm_showroom_purpose_analysis_t where pday='20251123')t pivot (max(sr_vol) for sr_purpose in ('1' as sr_record_vol_first,'2' as sr_record_vol_re,'3' as sr_record_vol_order,'4' as sr_record_vol_invalid)) ) ,tgt_achv as ( select month_id ,brand_code ,series_code ,eseries_code ,leads_achv_rate_ref_floor as leads_achv_rate_ref_floor ,oppty_achv_rate_ref_floor as oppty_achv_rate_ref_floor ,sr_achv_rate_ref_floor as sr_achv_rate_ref_floor ,order_achv_rate_ref_floor as order_achv_rate_ref_floor ,handover_achv_rate_ref_floor as handover_achv_rate_ref_floor ,leads_achv_rate_ref_ceiling as leads_achv_rate_ref_ceiling ,oppty_achv_rate_ref_ceiling as oppty_achv_rate_ref_ceiling ,sr_achv_rate_ref_ceiling as sr_achv_rate_ref_ceiling ,order_achv_rate_ref_ceiling as order_achv_rate_ref_ceiling ,handover_achv_rate_ref_ceiling as handover_achv_rate_ref_ceiling from (select month_id,brand_code,series_code,eseries_code,kpi,achv_rate_ref_floor,achv_rate_ref_ceiling from upload.dwc_fact_sal_ncs_fsm_kpi_tgt_achv_mn_t)t pivot (max(achv_rate_ref_floor) as achv_rate_ref_floor,max(achv_rate_ref_ceiling) as achv_rate_ref_ceiling for kpi in ('Leads' as leads,'Oppty' as oppty,'Showroom' as sr,'Order' as order,'Handover' as handover)) ) select t1.month_id ,t1.brand_code ,t1.series_code ,t1.eseries_code ,coalesce(dim1.dealer_id,dim2.dealer_id) as dealer_id ,coalesce(dim1.organization_id,dim2.organization_id) as organization_id ,case when t1.brand_code='BMW' then coalesce(dim1.region_bmw_sale_en,dim2.region_bmw_sale_en) when t1.brand_code='MINI' then coalesce(dim1.region_mini_sale_en,dim2.region_mini_sale_en) when t1.brand_code='MOTO' then coalesce(dim1.region_moto_sale_en,dim2.region_moto_sale_en) end as region_en ,case when t1.brand_code='BMW' then coalesce(dim1.small_region_bmw_sale_en,dim2.small_region_bmw_sale_en) when t1.brand_code='MINI' then coalesce(dim1.small_region_mini_sale_en,dim2.small_region_mini_sale_en) when t1.brand_code='MOTO' then coalesce(dim1.small_region_moto_sale_en,dim2.small_region_moto_sale_en) end as small_region_en ,coalesce(dim1.investor_id,dim2.investor_id) as investor_id ,t1.dim_geo_type ,t1.dim_geo_code ,t1.dim_geo_name_cn ,t1.dim_geo_name_en ,t1.data_agg_type ,t1.source_type ,t1.leads_vol ,t1.leads_vol_mom ,t1.leads_vol_yoy ,t2.leads_vol_tgt ,cast(case when t2.leads_vol_tgt=0 then null else (t1.leads_vol/t1.pass_day) / (t2.leads_vol_tgt/t1.actual_day) end as decimal(32,4)) as leads_achv_rate ,tgt_achv.leads_achv_rate_ref_floor ,tgt_achv.leads_achv_rate_ref_ceiling ,t1.leads_oppty_conv_rate ,t1.leads_oppty_conv_rate_mom ,t1.leads_oppty_conv_rate_yoy ,t3.leads_timely_follow_up_rate ,t3.leads_timely_follow_up_rate_mom ,t3.leads_timely_follow_up_rate_yoy ,t1.oppty_vol ,t1.oppty_vol_mom ,t1.oppty_vol_yoy ,tt.oppty_vol_others ,t2.oppty_vol_tgt ,cast(case when t2.oppty_vol_tgt=0 then null else (t1.oppty_vol/t1.pass_day) / (t2.oppty_vol_tgt/t1.actual_day) end as decimal(32,4)) as oppty_achv_rate ,tgt_achv.oppty_achv_rate_ref_floor ,tgt_achv.oppty_achv_rate_ref_ceiling ,t4.oppty_timely_follow_up_rate ,t4.oppty_timely_follow_up_rate_mom ,t4.oppty_timely_follow_up_rate_yoy ,t1.oppty_sr_conv_rate ,t1.oppty_sr_conv_rate_mom ,t1.oppty_sr_conv_rate_yoy ,t1.sr_vol ,t1.sr_vol_mom ,t1.sr_vol_yoy ,t2.sr_vol_tgt ,cast(case when t2.sr_vol_tgt=0 then null else (t1.sr_vol/t1.pass_day) / (t2.sr_vol_tgt/t1.actual_day) end as decimal(32,4)) as sr_achv_rate ,tgt_achv.sr_achv_rate_ref_floor ,tgt_achv.sr_achv_rate_ref_ceiling ,t1.sr_td_conv_rate ,t1.sr_order_conv_rate ,t1.sr_order_conv_rate_mom ,t1.sr_order_conv_rate_yoy ,t1.first_sr_vol ,t1.re_sr_vol ,t5.sr_record_vol_first ,t5.sr_record_vol_re ,t5.sr_record_vol_order ,t5.sr_record_vol_invalid ,t1.td_vol ,t1.td_vol_mom ,t1.td_vol_yoy ,t6.td_follow_up_rate ,t6.td_follow_up_rate_mom ,t6.td_follow_up_rate_yoy ,t1.order_vol ,t1.order_vol_mom ,t1.order_vol_yoy ,t2.order_vol_tgt ,cast(case when t2.order_vol_tgt=0 then null else (t1.order_vol/t1.pass_day) / (t2.order_vol_tgt/t1.actual_day) end as decimal(32,4)) as order_achv_rate ,tgt_achv.order_achv_rate_ref_floor ,tgt_achv.order_achv_rate_ref_ceiling ,t1.order_handover_conv_rate ,t1.order_handover_conv_rate_mom ,t1.order_handover_conv_rate_yoy ,t1.canceled_order_vol ,t1.handovered_order_vol ,t1.handover_tbd_vol ,t1.order_intake_vol ,t1.order_cancel_vol ,t1.order_cancel_rate ,t1.order_cancel_rate_mom ,t1.order_cancel_rate_yoy ,t1.order_vol_with_td ,t1.order_vol_without_td ,t1.handover_vol ,t1.handover_vol_mom ,t1.handover_vol_yoy ,t2.handover_vol_tgt ,cast(case when t2.handover_vol_tgt=0 then null else (t1.handover_vol/t1.pass_day) / (t2.handover_vol_tgt/t1.actual_day) end as decimal(32,4)) as handover_achv_rate ,tgt_achv.handover_achv_rate_ref_floor ,tgt_achv.handover_achv_rate_ref_ceiling ,t1.handover_intake_vol ,t1.handover_cancel_vol ,t1.avg_delivery_days ,t1.avg_delivery_days_mom ,t1.avg_delivery_days_yoy ,cast(case when t7.leads_vol=0 then null else t1.leads_vol/t7.leads_vol end as decimal(32,4)) as leads_ratio ,cast(case when t7.leads_vol=0 or t7.leads_vol_lm=0 then null else t1.leads_vol/t7.leads_vol - t1.leads_vol_lm/t7.leads_vol_lm end as decimal(32,4)) as leads_ratio_mom ,cast(case when t7.leads_vol=0 or t7.leads_vol_ly=0 then null else t1.leads_vol/t7.leads_vol - t1.leads_vol_ly/t7.leads_vol_ly end as decimal(32,4)) as leads_ratio_yoy ,cast(case when t7.oppty_vol=0 then null else t1.oppty_vol/t7.oppty_vol end as decimal(32,4)) as oppty_ratio ,cast(case when t7.oppty_vol=0 or t7.oppty_vol_lm=0 then null else t1.oppty_vol/t7.oppty_vol - t1.oppty_vol_lm/t7.oppty_vol_lm end as decimal(32,4)) as oppty_ratio_mom ,cast(case when t7.oppty_vol=0 or t7.oppty_vol_ly=0 then null else t1.oppty_vol/t7.oppty_vol - t1.oppty_vol_ly/t7.oppty_vol_ly end as decimal(32,4)) as oppty_ratio_yoy ,cast(case when t7.sr_vol=0 then null else t1.sr_vol/t7.sr_vol end as decimal(32,4)) as sr_ratio ,cast(case when t7.sr_vol=0 or t7.sr_vol_lm=0 then null else t1.sr_vol/t7.sr_vol - t1.sr_vol_lm/t7.sr_vol_lm end as decimal(32,4)) as sr_ratio_mom ,cast(case when t7.sr_vol=0 or t7.sr_vol_ly=0 then null else t1.sr_vol/t7.sr_vol - t1.sr_vol_ly/t7.sr_vol_ly end as decimal(32,4)) as sr_ratio_yoy ,cast(case when t7.td_vol=0 then null else t1.td_vol/t7.td_vol end as decimal(32,4)) as td_ratio ,cast(case when t7.td_vol=0 or t7.td_vol_lm=0 then null else t1.td_vol/t7.td_vol - t1.td_vol_lm/t7.td_vol_lm end as decimal(32,4)) as td_ratio_mom ,cast(case when t7.td_vol=0 or t7.td_vol_ly=0 then null else t1.td_vol/t7.td_vol - t1.td_vol_ly/t7.td_vol_ly end as decimal(32,4)) as td_ratio_yoy ,cast(case when t7.order_vol=0 then null else t1.order_vol/t7.order_vol end as decimal(32,4)) as order_ratio ,cast(case when t7.order_vol=0 or t7.order_vol_lm=0 then null else t1.order_vol/t7.order_vol - t1.order_vol_lm/t7.order_vol_lm end as decimal(32,4)) as order_ratio_mom ,cast(case when t7.order_vol=0 or t7.order_vol_ly=0 then null else t1.order_vol/t7.order_vol - t1.order_vol_ly/t7.order_vol_ly end as decimal(32,4)) as order_ratio_yoy ,cast(case when t7.handover_vol=0 then null else t1.handover_vol/t7.handover_vol end as decimal(32,4)) as handover_ratio ,cast(case when t7.handover_vol=0 or t7.handover_vol_lm=0 then null else t1.handover_vol/t7.handover_vol - t1.handover_vol_lm/t7.handover_vol_lm end as decimal(32,4)) as handover_ratio_mom ,cast(case when t7.handover_vol=0 or t7.handover_vol_ly=0 then null else t1.handover_vol/t7.handover_vol - t1.handover_vol_ly/t7.handover_vol_ly end as decimal(32,4)) as handover_ratio_yoy ,from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') as etl_create_time ,from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') as etl_update_time ,'BI' as etl_create_by ,'BI' as etl_update_by from funnel_base t1 left join tgt_base t2 on t1.month_id=t2.month_id and t1.dim_geo_type=t2.dim_type and t1.dim_geo_code=t2.dim_value and t1.brand_code=t2.brand_code and t1.eseries_code=t2.eseries_code and t1.data_agg_type=t2.data_agg_type and t1.source_type='Overview' left join leads_follow_base t3 on t1.month_id=t3.month_id and t1.dim_geo_type=t3.dim_geo_type and t1.dim_geo_code=t3.dim_geo_code and t1.brand_code=t3.leads_brand_code and t1.eseries_code=t3.leads_eseries_code and t1.data_agg_type=t3.data_agg_type and t1.source_type=t3.source_type left join oppty_follow_base t4 on t1.month_id=t4.month_id and t1.dim_geo_type=t4.dim_geo_type and t1.dim_geo_code=t4.dim_geo_code and t1.brand_code=t4.leads_brand_code and t1.eseries_code=t4.leads_eseries_code and t1.data_agg_type=t4.data_agg_type and t1.source_type=t4.source_type left join sr_purpose_base t5 on t1.month_id=t5.month_id and t1.dim_geo_type=t5.dim_geo_type and t1.dim_geo_code=t5.dim_geo_code and t1.brand_code=t5.leads_brand_code and t1.data_agg_type=t5.data_agg_type and t1.eseries_code='All' and t1.source_type='Overview' left join td_follow_base t6 on t1.month_id=t6.month_id and t1.dim_geo_type=t6.dim_geo_type and t1.dim_geo_code=t6.dim_geo_code and t1.brand_code=t6.leads_brand_code and t1.eseries_code=t6.leads_eseries_code and t1.data_agg_type=t6.data_agg_type and t1.source_type=t6.source_type left join funnel_base t7 on t1.month_id=t7.month_id and t1.dim_geo_type=t7.dim_geo_type and t1.dim_geo_code=t7.dim_geo_code and t1.brand_code=t7.brand_code and t1.eseries_code=t7.eseries_code and t1.data_agg_type=t7.data_agg_type and t1.source_type<>'Overview' and t7.source_type='Overview' left join dealership_full dim1 on t1.dim_geo_type='Agent' and t1.dim_geo_code=dim1.dealer_id left join emp_delear dim2 on t1.dim_geo_type='Emp' and t1.dim_geo_code=dim2.id left join tgt_achv on t1.brand_code=tgt_achv.brand_code left join dws.dws_fact_sal_ncs_fsm_ops_leads_oppty_sr_td_channel_t tt on tt.pday='20251123' and t1.month_id=tt.month_id and t1.dim_geo_type=tt.dim_geo_type and t1.dim_geo_code=tt.dim_geo_code and t1.brand_code=tt.brand_code and t1.eseries_code=tt.eseries_code and t1.data_agg_type=tt.data_agg_type and t1.source_type=tt.source_type and tt.same_eseries_flag=0 and tt.channel_name='All' WHERE t1.month_id>='202301' AS SERDE ----------------------------^^^ at org.apache.spark.sql.catalyst.parser.ParseException.withCommand(ParseDriver.scala:255) at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parse(ParseDriver.scala:124) at org.apache.spark.sql.execution.SparkSqlParser.parse(SparkSqlParser.scala:49) at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parsePlan(ParseDriver.scala:75) at org.apache.spark.sql.SparkSession.$anonfun$sql$2(SparkSession.scala:613) at org.apache.spark.sql.catalyst.QueryPlanningTracker.measurePhase(QueryPlanningTracker.scala:111) at org.apache.spark.sql.SparkSession.$anonfun$sql$1(SparkSession.scala:613) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:772) at org.apache.spark.sql.SparkSession.sql(SparkSession.scala:610) at org.apache.kyuubi.engine.spark.operation.ExecuteStatement.$anonfun$executeStatement$1(ExecuteStatement.scala:86) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at org.apache.kyuubi.engine.spark.operation.SparkOperation.$anonfun$withLocalProperties$1(SparkOperation.scala:147) at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:163) at org.apache.kyuubi.engine.spark.operation.SparkOperation.withLocalProperties(SparkOperation.scala:131) at org.apache.kyuubi.engine.spark.operation.ExecuteStatement.executeStatement(ExecuteStatement.scala:81) ... 6 more 执行的SQL语句: ```sql CREATE TABLE orca01_dr_data.ads_rpt_fsm_sfm_kpi_funnel_dmo_t_1218 stored as parquet as with dealership_full as ( select distinct dealer_id ,region_code ,dealer_short_name ,dealer_short_name_en ,region_bmw_sale ,region_mini_sale ,region_moto_sale ,region_bmw_sale_en ,region_mini_sale_en ,region_moto_sale_en ,small_region_bmw_sale_en ,small_region_mini_sale_en ,small_region_moto_sale_en ,dealer_investor_name_en ,organization_id ,investor_id from cdl.cdl_dim_com_dmx_dealership_full_t where pday='20251124' ), emp_delear as ( select distinct emp.company_id ,emp.id ,emp.emp_name ,dealer.dealer_id ,dealer.organization_id ,dealer.region_bmw_sale_en ,dealer.region_mini_sale_en ,dealer.region_moto_sale_en ,dealer.small_region_bmw_sale_en ,dealer.small_region_mini_sale_en ,dealer.small_region_moto_sale_en ,dealer.investor_id from dwc.dwc_dim_com_dmo2_t_employee_full_t as emp left join dealership_full as dealer on emp.company_id=dealer.organization_id where emp.pday='20251124' ) ,funnel_base as ( select month_id ,brand_code ,series_code ,eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,leads_vol ,leads_vol_lm ,leads_vol_ly ,leads_vol_mom ,leads_vol_yoy ,leads_oppty_conv_rate ,leads_oppty_conv_rate_mom ,leads_oppty_conv_rate_yoy ,oppty_vol ,oppty_vol_lm ,oppty_vol_ly ,oppty_vol_mom ,oppty_vol_yoy ,'' as oppty_vol_others ,oppty_sr_conv_rate ,oppty_sr_conv_rate_mom ,oppty_sr_conv_rate_yoy ,sr_vol ,sr_vol_lm ,sr_vol_ly ,sr_vol_mom ,sr_vol_yoy ,sr_td_conv_rate ,cast(case when sr_vol=0 then null else order_vol/sr_vol end as decimal(32,4)) as sr_order_conv_rate ,cast(case when sr_vol=0 then null else order_vol/sr_vol end as decimal(32,4))-cast(case when sr_vol_lm=0 then null else order_vol_lm/sr_vol_lm end as decimal(32,4)) as sr_order_conv_rate_mom ,cast(case when sr_vol=0 then null else order_vol/sr_vol end as decimal(32,4))-cast(case when sr_vol_ly=0 then null else order_vol_ly/sr_vol_ly end as decimal(32,4)) as sr_order_conv_rate_yoy ,first_sr_vol ,re_sr_vol ,td_vol ,td_vol_lm ,td_vol_ly ,td_vol_mom ,td_vol_yoy ,order_vol ,order_vol_lm ,order_vol_ly ,order_vol_mom ,order_vol_yoy ,order_handover_conv_rate ,order_handover_conv_rate-order_handover_conv_rate_lm as order_handover_conv_rate_mom ,order_handover_conv_rate-order_handover_conv_rate_ly as order_handover_conv_rate_yoy ,canceled_order_vol ,handovered_order_vol ,handover_tbd_vol ,order_intake_vol ,order_cancel_vol ,order_cancel_rate ,order_cancel_rate_mom ,order_cancel_rate_yoy ,order_vol_with_td ,order_vol_without_td ,handover_vol ,handover_vol_lm ,handover_vol_ly ,handover_vol_mom ,handover_vol_yoy ,handover_intake_vol ,handover_cancel_vol ,cast(round(delivery_days/cus_handover_intake_vol,0) as int) as avg_delivery_days ,case when brand_code='BMW' and month_id<'202212' then cast(null as int) when brand_code='MINI' and month_id<'202304' then cast(null as int) else cast(round(delivery_days/cus_handover_intake_vol,0) - round(delivery_days_lm/cus_handover_intake_vol_lm,0) as int) end as avg_delivery_days_mom ,case when month_id<'202401' then cast(null as int) else cast(round(delivery_days/cus_handover_intake_vol,0) - round(delivery_days_ly/cus_handover_intake_vol_ly,0) as int) end as avg_delivery_days_yoy ,case when data_agg_type='YTDM' then case when month_id=substr('20251123',1,6) then datediff(from_unixtime(unix_timestamp('20251123','yyyyMMdd'),'yyyy-MM-dd')+ interval '1 day',from_unixtime(unix_timestamp(month_id||'01','yyyyMMdd'),'yyyy-MM-dd')) else 1 end when data_agg_type='YTD' then case when substr(month_id,1,4)=substr('20251123',1,4) and month_id=substr('20251123',1,6) then datediff(from_unixtime(unix_timestamp('20251123','yyyyMMdd'),'yyyy-MM-dd')+ interval '1 day',from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyyMMdd'),'yyyy-MM-dd')) else datediff(from_unixtime(unix_timestamp(month_id||'01','yyyyMMdd'),'yyyy-MM-dd')+ interval '1 month',from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyyMMdd'),'yyyy-MM-dd')) end end as pass_day ,case when data_agg_type='YTDM' then case when month_id=substr('20251123',1,6) then datediff(from_unixtime(unix_timestamp(month_id||'01','yyyyMMdd')) + interval '1 month'+ interval '1 day',from_unixtime(unix_timestamp(month_id||'01','yyyyMMdd'),'yyyy-MM-dd')) else 1 end when data_agg_type='YTD' then datediff(from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyyMMdd')) + interval '1 year'+ interval '1 day',from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyyMMdd'),'yyyy-MM-dd')) end as actual_day from dws.dws_fact_sal_ncs_fsm_ops_funnel_kpi_t where pday='20251123' and dim_geo_type in ('Emp','Agent') and same_eseries_flag=0 and source_type<>'Other' and ! ( (coalesce(brand_code,'')='BMW' AND coalesce(source_type,'') IN ('Retail','NSC','BDC') ) ) ) ,tgt_base as ( select month_id ,dim_type ,dim_value ,brand_code ,series_code ,eseries_code ,data_agg_type ,leads_channel ,cast(leads_tgt as int) as leads_vol_tgt ,cast(oppty_tgt as int) as oppty_vol_tgt ,cast(sr_tgt as int) as sr_vol_tgt ,cast(order_tgt as int) as order_vol_tgt ,cast(handover_tgt as int) as handover_vol_tgt from dws.dws_fact_sal_ncs_fsm_tgt_mon_t where pmonth='{$pmonth}' and leads_channel='All' and dim_type in ('Emp','Agent') ) ,leads_follow_base as ( select month_id ,leads_brand_code ,leads_series_code ,leads_eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,leads_timely_follow_up_rate ,leads_timely_follow_up_rate_mom ,leads_timely_follow_up_rate_yoy from dws.dws_fact_sal_ncs_fsm_leads_followup_his_t where pday='20251123' and leads_star_level='All' and dim_geo_type in ('Emp','Agent') ) ,oppty_follow_base as ( select month_id ,leads_brand_code ,leads_series_code ,leads_eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,oppty_timely_follow_up_rate ,oppty_timely_follow_up_rate_mom ,oppty_timely_follow_up_rate_yoy from dws.dws_fact_sal_ncs_fsm_oppty_followup_his_t where pday='20251123' and oppty_priority_level='All' and dim_geo_type in ('Emp','Agent') ) ,td_follow_base as ( select month_id ,leads_brand_code ,leads_series_code ,leads_eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,td_follow_up_rate ,td_follow_up_rate_mom ,td_follow_up_rate_yoy from dws.dws_fact_sal_ncs_fsm_td_followup_t where pday='20251123' and dim_geo_type in ('Emp','Agent') ) ,sr_purpose_base as ( select month_id ,leads_brand_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,sr_record_vol_first ,sr_record_vol_re ,sr_record_vol_order ,sr_record_vol_invalid from (select month_id,leads_brand_code,dim_geo_type,dim_geo_code,dim_geo_name_cn,dim_geo_name_en,data_agg_type,sr_purpose,sr_vol from dws.dws_fact_sal_ncs_fsm_showroom_purpose_analysis_t where pday='20251123')t pivot (max(sr_vol) for sr_purpose in ('1' as sr_record_vol_first,'2' as sr_record_vol_re,'3' as sr_record_vol_order,'4' as sr_record_vol_invalid)) ) ,tgt_achv as ( select month_id ,brand_code ,series_code ,eseries_code ,leads_achv_rate_ref_floor as leads_achv_rate_ref_floor ,oppty_achv_rate_ref_floor as oppty_achv_rate_ref_floor ,sr_achv_rate_ref_floor as sr_achv_rate_ref_floor ,order_achv_rate_ref_floor as order_achv_rate_ref_floor ,handover_achv_rate_ref_floor as handover_achv_rate_ref_floor ,leads_achv_rate_ref_ceiling as leads_achv_rate_ref_ceiling ,oppty_achv_rate_ref_ceiling as oppty_achv_rate_ref_ceiling ,sr_achv_rate_ref_ceiling as sr_achv_rate_ref_ceiling ,order_achv_rate_ref_ceiling as order_achv_rate_ref_ceiling ,handover_achv_rate_ref_ceiling as handover_achv_rate_ref_ceiling from (select month_id,brand_code,series_code,eseries_code,kpi,achv_rate_ref_floor,achv_rate_ref_ceiling from upload.dwc_fact_sal_ncs_fsm_kpi_tgt_achv_mn_t)t pivot (max(achv_rate_ref_floor) as achv_rate_ref_floor,max(achv_rate_ref_ceiling) as achv_rate_ref_ceiling for kpi in ('Leads' as leads,'Oppty' as oppty,'Showroom' as sr,'Order' as order,'Handover' as handover)) ) select t1.month_id ,t1.brand_code ,t1.series_code ,t1.eseries_code ,coalesce(dim1.dealer_id,dim2.dealer_id) as dealer_id ,coalesce(dim1.organization_id,dim2.organization_id) as organization_id ,case when t1.brand_code='BMW' then coalesce(dim1.region_bmw_sale_en,dim2.region_bmw_sale_en) when t1.brand_code='MINI' then coalesce(dim1.region_mini_sale_en,dim2.region_mini_sale_en) when t1.brand_code='MOTO' then coalesce(dim1.region_moto_sale_en,dim2.region_moto_sale_en) end as region_en ,case when t1.brand_code='BMW' then coalesce(dim1.small_region_bmw_sale_en,dim2.small_region_bmw_sale_en) when t1.brand_code='MINI' then coalesce(dim1.small_region_mini_sale_en,dim2.small_region_mini_sale_en) when t1.brand_code='MOTO' then coalesce(dim1.small_region_moto_sale_en,dim2.small_region_moto_sale_en) end as small_region_en ,coalesce(dim1.investor_id,dim2.investor_id) as investor_id ,t1.dim_geo_type ,t1.dim_geo_code ,t1.dim_geo_name_cn ,t1.dim_geo_name_en ,t1.data_agg_type ,t1.source_type ,t1.leads_vol ,t1.leads_vol_mom ,t1.leads_vol_yoy ,t2.leads_vol_tgt ,cast(case when t2.leads_vol_tgt=0 then null else (t1.leads_vol/t1.pass_day) / (t2.leads_vol_tgt/t1.actual_day) end as decimal(32,4)) as leads_achv_rate ,tgt_achv.leads_achv_rate_ref_floor ,tgt_achv.leads_achv_rate_ref_ceiling ,t1.leads_oppty_conv_rate ,t1.leads_oppty_conv_rate_mom ,t1.leads_oppty_conv_rate_yoy ,t3.leads_timely_follow_up_rate ,t3.leads_timely_follow_up_rate_mom ,t3.leads_timely_follow_up_rate_yoy ,t1.oppty_vol ,t1.oppty_vol_mom ,t1.oppty_vol_yoy ,tt.oppty_vol_others ,t2.oppty_vol_tgt ,cast(case when t2.oppty_vol_tgt=0 then null else (t1.oppty_vol/t1.pass_day) / (t2.oppty_vol_tgt/t1.actual_day) end as decimal(32,4)) as oppty_achv_rate ,tgt_achv.oppty_achv_rate_ref_floor ,tgt_achv.oppty_achv_rate_ref_ceiling ,t4.oppty_timely_follow_up_rate ,t4.oppty_timely_follow_up_rate_mom ,t4.oppty_timely_follow_up_rate_yoy ,t1.oppty_sr_conv_rate ,t1.oppty_sr_conv_rate_mom ,t1.oppty_sr_conv_rate_yoy ,t1.sr_vol ,t1.sr_vol_mom ,t1.sr_vol_yoy ,t2.sr_vol_tgt ,cast(case when t2.sr_vol_tgt=0 then null else (t1.sr_vol/t1.pass_day) / (t2.sr_vol_tgt/t1.actual_day) end as decimal(32,4)) as sr_achv_rate ,tgt_achv.sr_achv_rate_ref_floor ,tgt_achv.sr_achv_rate_ref_ceiling ,t1.sr_td_conv_rate ,t1.sr_order_conv_rate ,t1.sr_order_conv_rate_mom ,t1.sr_order_conv_rate_yoy ,t1.first_sr_vol ,t1.re_sr_vol ,t5.sr_record_vol_first ,t5.sr_record_vol_re ,t5.sr_record_vol_order ,t5.sr_record_vol_invalid ,t1.td_vol ,t1.td_vol_mom ,t1.td_vol_yoy ,t6.td_follow_up_rate ,t6.td_follow_up_rate_mom ,t6.td_follow_up_rate_yoy ,t1.order_vol ,t1.order_vol_mom ,t1.order_vol_yoy ,t2.order_vol_tgt ,cast(case when t2.order_vol_tgt=0 then null else (t1.order_vol/t1.pass_day) / (t2.order_vol_tgt/t1.actual_day) end as decimal(32,4)) as order_achv_rate ,tgt_achv.order_achv_rate_ref_floor ,tgt_achv.order_achv_rate_ref_ceiling ,t1.order_handover_conv_rate ,t1.order_handover_conv_rate_mom ,t1.order_handover_conv_rate_yoy ,t1.canceled_order_vol ,t1.handovered_order_vol ,t1.handover_tbd_vol ,t1.order_intake_vol ,t1.order_cancel_vol ,t1.order_cancel_rate ,t1.order_cancel_rate_mom ,t1.order_cancel_rate_yoy ,t1.order_vol_with_td ,t1.order_vol_without_td ,t1.handover_vol ,t1.handover_vol_mom ,t1.handover_vol_yoy ,t2.handover_vol_tgt ,cast(case when t2.handover_vol_tgt=0 then null else (t1.handover_vol/t1.pass_day) / (t2.handover_vol_tgt/t1.actual_day) end as decimal(32,4)) as handover_achv_rate ,tgt_achv.handover_achv_rate_ref_floor ,tgt_achv.handover_achv_rate_ref_ceiling ,t1.handover_intake_vol ,t1.handover_cancel_vol ,t1.avg_delivery_days ,t1.avg_delivery_days_mom ,t1.avg_delivery_days_yoy ,cast(case when t7.leads_vol=0 then null else t1.leads_vol/t7.leads_vol end as decimal(32,4)) as leads_ratio ,cast(case when t7.leads_vol=0 or t7.leads_vol_lm=0 then null else t1.leads_vol/t7.leads_vol - t1.leads_vol_lm/t7.leads_vol_lm end as decimal(32,4)) as leads_ratio_mom ,cast(case when t7.leads_vol=0 or t7.leads_vol_ly=0 then null else t1.leads_vol/t7.leads_vol - t1.leads_vol_ly/t7.leads_vol_ly end as decimal(32,4)) as leads_ratio_yoy ,cast(case when t7.oppty_vol=0 then null else t1.oppty_vol/t7.oppty_vol end as decimal(32,4)) as oppty_ratio ,cast(case when t7.oppty_vol=0 or t7.oppty_vol_lm=0 then null else t1.oppty_vol/t7.oppty_vol - t1.oppty_vol_lm/t7.oppty_vol_lm end as decimal(32,4)) as oppty_ratio_mom ,cast(case when t7.oppty_vol=0 or t7.oppty_vol_ly=0 then null else t1.oppty_vol/t7.oppty_vol - t1.oppty_vol_ly/t7.oppty_vol_ly end as decimal(32,4)) as oppty_ratio_yoy ,cast(case when t7.sr_vol=0 then null else t1.sr_vol/t7.sr_vol end as decimal(32,4)) as sr_ratio ,cast(case when t7.sr_vol=0 or t7.sr_vol_lm=0 then null else t1.sr_vol/t7.sr_vol - t1.sr_vol_lm/t7.sr_vol_lm end as decimal(32,4)) as sr_ratio_mom ,cast(case when t7.sr_vol=0 or t7.sr_vol_ly=0 then null else t1.sr_vol/t7.sr_vol - t1.sr_vol_ly/t7.sr_vol_ly end as decimal(32,4)) as sr_ratio_yoy ,cast(case when t7.td_vol=0 then null else t1.td_vol/t7.td_vol end as decimal(32,4)) as td_ratio ,cast(case when t7.td_vol=0 or t7.td_vol_lm=0 then null else t1.td_vol/t7.td_vol - t1.td_vol_lm/t7.td_vol_lm end as decimal(32,4)) as td_ratio_mom ,cast(case when t7.td_vol=0 or t7.td_vol_ly=0 then null else t1.td_vol/t7.td_vol - t1.td_vol_ly/t7.td_vol_ly end as decimal(32,4)) as td_ratio_yoy ,cast(case when t7.order_vol=0 then null else t1.order_vol/t7.order_vol end as decimal(32,4)) as order_ratio ,cast(case when t7.order_vol=0 or t7.order_vol_lm=0 then null else t1.order_vol/t7.order_vol - t1.order_vol_lm/t7.order_vol_lm end as decimal(32,4)) as order_ratio_mom ,cast(case when t7.order_vol=0 or t7.order_vol_ly=0 then null else t1.order_vol/t7.order_vol - t1.order_vol_ly/t7.order_vol_ly end as decimal(32,4)) as order_ratio_yoy ,cast(case when t7.handover_vol=0 then null else t1.handover_vol/t7.handover_vol end as decimal(32,4)) as handover_ratio ,cast(case when t7.handover_vol=0 or t7.handover_vol_lm=0 then null else t1.handover_vol/t7.handover_vol - t1.handover_vol_lm/t7.handover_vol_lm end as decimal(32,4)) as handover_ratio_mom ,cast(case when t7.handover_vol=0 or t7.handover_vol_ly=0 then null else t1.handover_vol/t7.handover_vol - t1.handover_vol_ly/t7.handover_vol_ly end as decimal(32,4)) as handover_ratio_yoy ,from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') as etl_create_time ,from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') as etl_update_time ,'BI' as etl_create_by ,'BI' as etl_update_by from funnel_base t1 left join tgt_base t2 on t1.month_id=t2.month_id and t1.dim_geo_type=t2.dim_type and t1.dim_geo_code=t2.dim_value and t1.brand_code=t2.brand_code and t1.eseries_code=t2.eseries_code and t1.data_agg_type=t2.data_agg_type and t1.source_type='Overview' left join leads_follow_base t3 on t1.month_id=t3.month_id and t1.dim_geo_type=t3.dim_geo_type and t1.dim_geo_code=t3.dim_geo_code and t1.brand_code=t3.leads_brand_code and t1.eseries_code=t3.leads_eseries_code and t1.data_agg_type=t3.data_agg_type and t1.source_type=t3.source_type left join oppty_follow_base t4 on t1.month_id=t4.month_id and t1.dim_geo_type=t4.dim_geo_type and t1.dim_geo_code=t4.dim_geo_code and t1.brand_code=t4.leads_brand_code and t1.eseries_code=t4.leads_eseries_code and t1.data_agg_type=t4.data_agg_type and t1.source_type=t4.source_type left join sr_purpose_base t5 on t1.month_id=t5.month_id and t1.dim_geo_type=t5.dim_geo_type and t1.dim_geo_code=t5.dim_geo_code and t1.brand_code=t5.leads_brand_code and t1.data_agg_type=t5.data_agg_type and t1.eseries_code='All' and t1.source_type='Overview' left join td_follow_base t6 on t1.month_id=t6.month_id and t1.dim_geo_type=t6.dim_geo_type and t1.dim_geo_code=t6.dim_geo_code and t1.brand_code=t6.leads_brand_code and t1.eseries_code=t6.leads_eseries_code and t1.data_agg_type=t6.data_agg_type and t1.source_type=t6.source_type left join funnel_base t7 on t1.month_id=t7.month_id and t1.dim_geo_type=t7.dim_geo_type and t1.dim_geo_code=t7.dim_geo_code and t1.brand_code=t7.brand_code and t1.eseries_code=t7.eseries_code and t1.data_agg_type=t7.data_agg_type and t1.source_type<>'Overview' and t7.source_type='Overview' left join dealership_full dim1 on t1.dim_geo_type='Agent' and t1.dim_geo_code=dim1.dealer_id left join emp_delear dim2 on t1.dim_geo_type='Emp' and t1.dim_geo_code=dim2.id left join tgt_achv on t1.brand_code=tgt_achv.brand_code left join dws.dws_fact_sal_ncs_fsm_ops_leads_oppty_sr_td_channel_t tt on tt.pday='20251123' and t1.month_id=tt.month_id and t1.dim_geo_type=tt.dim_geo_type and t1.dim_geo_code=tt.dim_geo_code and t1.brand_code=tt.brand_code and t1.eseries_code=tt.eseries_code and t1.data_agg_type=tt.data_agg_type and t1.source_type=tt.source_type and tt.same_eseries_flag=0 and tt.channel_name='All' WHERE t1.month_id>='202301'为啥报错

filetype

extraneous input ''' expecting {'(', 'ADD', 'AFTER', 'ALL', 'ALTER', 'ANALYZE', 'AND', 'ANTI', 'ANY', 'ARCHIVE', 'ARRAY', 'AS', 'ASC', 'AT', 'AUTHORIZATION', 'BETWEEN', 'BOTH', 'BUCKET', 'BUCKETS', 'BY', 'CACHE', 'CASCADE', 'CASE', 'CAST', 'CHANGE', 'CHECK', 'CLEAR', 'CLUSTER', 'CLUSTERED', 'CODEGEN', 'COLLATE', 'COLLECTION', 'COLUMN', 'COLUMNS', 'COMMENT', 'COMMIT', 'COMPACT', 'COMPACTIONS', 'COMPUTE', 'CONCATENATE', 'CONSTRAINT', 'COST', 'CREATE', 'CROSS', 'CUBE', 'CURRENT', 'CURRENT_DATE', 'CURRENT_TIME', 'CURRENT_TIMESTAMP', 'CURRENT_USER', 'DATA', 'DATABASE', DATABASES, 'DBPROPERTIES', 'DEFINED', 'DELETE', 'DELIMITED', 'DESC', 'DESCRIBE', 'DFS', 'DIRECTORIES', 'DIRECTORY', 'DISTINCT', 'DISTRIBUTE', 'DIV', 'DROP', 'ELSE', 'END', 'ESCAPE', 'ESCAPED', 'EXCEPT', 'EXCHANGE', 'EXISTS', 'EXPLAIN', 'EXPORT', 'EXTENDED', 'EXTERNAL', 'EXTRACT', 'FALSE', 'FETCH', 'FIELDS', 'FILTER', 'FILEFORMAT', 'FIRST', 'FOLLOWING', 'FOR', 'FOREIGN', 'FORMAT', 'FORMATTED', 'FROM', 'FULL', 'FUNCTION', 'FUNCTIONS', 'GLOBAL', 'GRANT', 'GROUP', 'GROUPING', 'HAVING', 'IF', 'IGNORE', 'IMPORT', 'IN', 'INDEX', 'INDEXES', 'INNER', 'INPATH', 'INPUTFORMAT', 'INSERT', 'INTERSECT', 'INTERVAL', 'INTO', 'IS', 'ITEMS', 'JOIN', 'KEYS', 'LAST', 'LATERAL', 'LAZY', 'LEADING', 'LEFT', 'LIKE', 'LIMIT', 'LINES', 'LIST', 'LOAD', 'LOCAL', 'LOCATION', 'LOCK', 'LOCKS', 'LOGICAL', 'MACRO', 'MAP', 'MATCHED', 'MERGE', 'MSCK', 'NAMESPACE', 'NAMESPACES', 'NATURAL', 'NO', NOT, 'NULL', 'NULLS', 'OF', 'ON', 'ONLY', 'OPTION', 'OPTIONS', 'OR', 'ORDER', 'OUT', 'OUTER', 'OUTPUTFORMAT', 'OVER', 'OVERLAPS', 'OVERLAY', 'OVERWRITE', 'PARTITION', 'PARTITIONED', 'PARTITIONS', 'PERCENT', 'PIVOT', 'PLACING', 'POSITION', 'PRECEDING', 'PRIMARY', 'PRINCIPALS', 'PROPERTIES', 'PURGE', 'QUERY', 'RANGE', 'RECORDREADER', 'RECORDWRITER', 'RECOVER', 'REDUCE', 'REFERENCES', 'REFRESH', 'RENAME', 'REPAIR', 'REPLACE', 'RESET', 'RESTRICT', 'REVOKE', 'RIGHT', RLIKE, 'ROLE', 'ROLES', 'ROLLBACK', 'ROLLUP', 'ROW', 'ROWS', 'SCHEMA', 'SELECT', 'SEMI', 'SEPARATED', 'SERDE', 'SERDEPROPERTIES', 'SESSION_USER', 'SET', 'MINUS', 'SETS', 'SHOW', 'SKEWED', 'SOME', 'SORT', 'SORTED', 'START', 'STATISTICS', 'STORED', 'STRATIFY', 'STRUCT', 'SUBSTR', 'SUBSTRING', 'TABLE', 'TABLES', 'TABLESAMPLE', 'TBLPROPERTIES', TEMPORARY, 'TERMINATED', 'THEN', 'TIME', 'TO', 'TOUCH', 'TRAILING', 'TRANSACTION', 'TRANSACTIONS', 'TRANSFORM', 'TRIM', 'TRUE', 'TRUNCATE', 'TYPE', 'UNARCHIVE', 'UNBOUNDED', 'UNCACHE', 'UNION', 'UNIQUE', 'UNKNOWN', 'UNLOCK', 'UNSET', 'UPDATE', 'USE', 'USER', 'USING', 'VALUES', 'VIEW', 'VIEWS', 'WHEN', 'WHERE', 'WINDOW', 'WITH', 'ZONE', '+', '-', '*', '~', STRING, BIGINT_LITERAL, SMALLINT_LITERAL, TINYINT_LITERAL, INTEGER_VALUE, EXPONENT_VALUE, DECIMAL_VALUE, FLOAT_LITERAL, DOUBLE_LITERAL, BIGDECIMAL_LITERAL, IDENTIFIER, BACKQUOTED_IDENTIFIER}(line 411, pos 19) == SQL == create table orca01_dr_data.ads_rpt_fsm_sfm_kpi_funnel_dmo_t_1218 stored as parquet as with dealership_full as ( select distinct dealer_id ,region_code ,dealer_short_name ,dealer_short_name_en ,region_bmw_sale ,region_mini_sale ,region_moto_sale ,region_bmw_sale_en ,region_mini_sale_en ,region_moto_sale_en ,small_region_bmw_sale_en ,small_region_mini_sale_en ,small_region_moto_sale_en ,dealer_investor_name_en ,organization_id ,investor_id from cdl.cdl_dim_com_dmx_dealership_full_t where pday='20251124' ), emp_delear as ( select distinct emp.company_id ,emp.id ,emp.emp_name ,dealer.dealer_id ,dealer.organization_id ,dealer.region_bmw_sale_en ,dealer.region_mini_sale_en ,dealer.region_moto_sale_en ,dealer.small_region_bmw_sale_en ,dealer.small_region_mini_sale_en ,dealer.small_region_moto_sale_en ,dealer.investor_id from dwc.dwc_dim_com_dmo2_t_employee_full_t as emp left join dealership_full as dealer on emp.company_id=dealer.organization_id where emp.pday='20251124' ) ,funnel_base as ( select month_id ,brand_code ,series_code ,eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,leads_vol ,leads_vol_lm ,leads_vol_ly ,leads_vol_mom ,leads_vol_yoy ,leads_oppty_conv_rate ,leads_oppty_conv_rate_mom ,leads_oppty_conv_rate_yoy ,oppty_vol ,oppty_vol_lm ,oppty_vol_ly ,oppty_vol_mom ,oppty_vol_yoy ,'' as oppty_vol_others ,oppty_sr_conv_rate ,oppty_sr_conv_rate_mom ,oppty_sr_conv_rate_yoy ,sr_vol ,sr_vol_lm ,sr_vol_ly ,sr_vol_mom ,sr_vol_yoy ,sr_td_conv_rate ,cast(case when sr_vol=0 then null else order_vol/sr_vol end as decimal(32,4)) as sr_order_conv_rate ,cast(case when sr_vol=0 then null else order_vol/sr_vol end as decimal(32,4))-cast(case when sr_vol_lm=0 then null else order_vol_lm/sr_vol_lm end as decimal(32,4)) as sr_order_conv_rate_mom ,cast(case when sr_vol=0 then null else order_vol/sr_vol end as decimal(32,4))-cast(case when sr_vol_ly=0 then null else order_vol_ly/sr_vol_ly end as decimal(32,4)) as sr_order_conv_rate_yoy ,first_sr_vol ,re_sr_vol ,td_vol ,td_vol_lm ,td_vol_ly ,td_vol_mom ,td_vol_yoy ,order_vol ,order_vol_lm ,order_vol_ly ,order_vol_mom ,order_vol_yoy ,order_handover_conv_rate ,order_handover_conv_rate-order_handover_conv_rate_lm as order_handover_conv_rate_mom ,order_handover_conv_rate-order_handover_conv_rate_ly as order_handover_conv_rate_yoy ,canceled_order_vol ,handovered_order_vol ,handover_tbd_vol ,order_intake_vol ,order_cancel_vol ,order_cancel_rate ,order_cancel_rate_mom ,order_cancel_rate_yoy ,order_vol_with_td ,order_vol_without_td ,handover_vol ,handover_vol_lm ,handover_vol_ly ,handover_vol_mom ,handover_vol_yoy ,handover_intake_vol ,handover_cancel_vol ,cast(round(delivery_days/cus_handover_intake_vol,0) as int) as avg_delivery_days ,case when brand_code='bmw' and month_id<'202212' then cast(null as int) when brand_code='mini' and month_id<'202304' then cast(null as int) else cast(round(delivery_days/cus_handover_intake_vol,0) - round(delivery_days_lm/cus_handover_intake_vol_lm,0) as int) end as avg_delivery_days_mom ,case when month_id<'202401' then cast(null as int) else cast(round(delivery_days/cus_handover_intake_vol,0) - round(delivery_days_ly/cus_handover_intake_vol_ly,0) as int) end as avg_delivery_days_yoy ,case when data_agg_type='ytdm' then case when month_id=substr('20251123',1,6) then datediff(from_unixtime(unix_timestamp('20251123','yyyymmdd'),'yyyy-mm-dd')+ interval '1 day',from_unixtime(unix_timestamp(month_id||'01','yyyymmdd'),'yyyy-mm-dd')) else 1 end when data_agg_type='ytd' then case when substr(month_id,1,4)=substr('20251123',1,4) and month_id=substr('20251123',1,6) then datediff(from_unixtime(unix_timestamp('20251123','yyyymmdd'),'yyyy-mm-dd')+ interval '1 day',from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyymmdd'),'yyyy-mm-dd')) else datediff(from_unixtime(unix_timestamp(month_id||'01','yyyymmdd'),'yyyy-mm-dd')+ interval '1 month',from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyymmdd'),'yyyy-mm-dd')) end end as pass_day ,case when data_agg_type='ytdm' then case when month_id=substr('20251123',1,6) then datediff(from_unixtime(unix_timestamp(month_id||'01','yyyymmdd')) + interval '1 month'+ interval '1 day',from_unixtime(unix_timestamp(month_id||'01','yyyymmdd'),'yyyy-mm-dd')) else 1 end when data_agg_type='ytd' then datediff(from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyymmdd')) + interval '1 year'+ interval '1 day',from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyymmdd'),'yyyy-mm-dd')) end as actual_day from dws.dws_fact_sal_ncs_fsm_ops_funnel_kpi_t where pday='20251123' and dim_geo_type in ('emp','agent') and same_eseries_flag=0 and source_type<>'other' and ! ( (coalesce(brand_code,'')='bmw' and coalesce(source_type,'') in ('retail','nsc','bdc') ) ) ) ,tgt_base as ( select month_id ,dim_type ,dim_value ,brand_code ,series_code ,eseries_code ,data_agg_type ,leads_channel ,cast(leads_tgt as int) as leads_vol_tgt ,cast(oppty_tgt as int) as oppty_vol_tgt ,cast(sr_tgt as int) as sr_vol_tgt ,cast(order_tgt as int) as order_vol_tgt ,cast(handover_tgt as int) as handover_vol_tgt from dws.dws_fact_sal_ncs_fsm_tgt_mon_t where pmonth='{$pmonth}' and leads_channel='all' and dim_type in ('emp','agent') ) ,leads_follow_base as ( select month_id ,leads_brand_code ,leads_series_code ,leads_eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,leads_timely_follow_up_rate ,leads_timely_follow_up_rate_mom ,leads_timely_follow_up_rate_yoy from dws.dws_fact_sal_ncs_fsm_leads_followup_his_t where pday='20251123' and leads_star_level='all' and dim_geo_type in ('emp','agent') ) ,oppty_follow_base as ( select month_id ,leads_brand_code ,leads_series_code ,leads_eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,oppty_timely_follow_up_rate ,oppty_timely_follow_up_rate_mom ,oppty_timely_follow_up_rate_yoy from dws.dws_fact_sal_ncs_fsm_oppty_followup_his_t where pday='20251123' and oppty_priority_level='all' and dim_geo_type in ('emp','agent') ) ,td_follow_base as ( select month_id ,leads_brand_code ,leads_series_code ,leads_eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,td_follow_up_rate ,td_follow_up_rate_mom ,td_follow_up_rate_yoy from dws.dws_fact_sal_ncs_fsm_td_followup_t where pday='20251123' and dim_geo_type in ('emp','agent') ) ,sr_purpose_base as ( select month_id ,leads_brand_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,sr_record_vol_first ,sr_record_vol_re ,sr_record_vol_order ,sr_record_vol_invalid from (select month_id,leads_brand_code,dim_geo_type,dim_geo_code,dim_geo_name_cn,dim_geo_name_en,data_agg_type,sr_purpose,sr_vol from dws.dws_fact_sal_ncs_fsm_showroom_purpose_analysis_t where pday='20251123')t pivot (max(sr_vol) for sr_purpose in ('1' as sr_record_vol_first,'2' as sr_record_vol_re,'3' as sr_record_vol_order,'4' as sr_record_vol_invalid)) ) ,tgt_achv as ( select month_id ,brand_code ,series_code ,eseries_code ,leads_achv_rate_ref_floor as leads_achv_rate_ref_floor ,oppty_achv_rate_ref_floor as oppty_achv_rate_ref_floor ,sr_achv_rate_ref_floor as sr_achv_rate_ref_floor ,order_achv_rate_ref_floor as order_achv_rate_ref_floor ,handover_achv_rate_ref_floor as handover_achv_rate_ref_floor ,leads_achv_rate_ref_ceiling as leads_achv_rate_ref_ceiling ,oppty_achv_rate_ref_ceiling as oppty_achv_rate_ref_ceiling ,sr_achv_rate_ref_ceiling as sr_achv_rate_ref_ceiling ,order_achv_rate_ref_ceiling as order_achv_rate_ref_ceiling ,handover_achv_rate_ref_ceiling as handover_achv_rate_ref_ceiling from (select month_id,brand_code,series_code,eseries_code,kpi,achv_rate_ref_floor,achv_rate_ref_ceiling from upload.dwc_fact_sal_ncs_fsm_kpi_tgt_achv_mn_t)t pivot (max(achv_rate_ref_floor) as achv_rate_ref_floor,max(achv_rate_ref_ceiling) as achv_rate_ref_ceiling for kpi in ('leads' as leads,'oppty' as oppty,'showroom' as sr,'order' as order,'handover' as handover)) ) select t1.month_id ,t1.brand_code ,t1.series_code ,t1.eseries_code ,coalesce(dim1.dealer_id,dim2.dealer_id) as dealer_id ,coalesce(dim1.organization_id,dim2.organization_id) as organization_id ,case when t1.brand_code='bmw' then coalesce(dim1.region_bmw_sale_en,dim2.region_bmw_sale_en) when t1.brand_code='mini' then coalesce(dim1.region_mini_sale_en,dim2.region_mini_sale_en) when t1.brand_code='moto' then coalesce(dim1.region_moto_sale_en,dim2.region_moto_sale_en) end as region_en ,case when t1.brand_code='bmw' then coalesce(dim1.small_region_bmw_sale_en,dim2.small_region_bmw_sale_en) when t1.brand_code='mini' then coalesce(dim1.small_region_mini_sale_en,dim2.small_region_mini_sale_en) when t1.brand_code='moto' then coalesce(dim1.small_region_moto_sale_en,dim2.small_region_moto_sale_en) end as small_region_en ,coalesce(dim1.investor_id,dim2.investor_id) as investor_id ,t1.dim_geo_type ,t1.dim_geo_code ,t1.dim_geo_name_cn ,t1.dim_geo_name_en ,t1.data_agg_type ,t1.source_type ,t1.leads_vol ,t1.leads_vol_mom ,t1.leads_vol_yoy ,t2.leads_vol_tgt ,cast(case when t2.leads_vol_tgt=0 then null else (t1.leads_vol/t1.pass_day) / (t2.leads_vol_tgt/t1.actual_day) end as decimal(32,4)) as leads_achv_rate ,tgt_achv.leads_achv_rate_ref_floor ,tgt_achv.leads_achv_rate_ref_ceiling ,t1.leads_oppty_conv_rate ,t1.leads_oppty_conv_rate_mom ,t1.leads_oppty_conv_rate_yoy ,t3.leads_timely_follow_up_rate ,t3.leads_timely_follow_up_rate_mom ,t3.leads_timely_follow_up_rate_yoy ,t1.oppty_vol ,t1.oppty_vol_mom ,t1.oppty_vol_yoy ,tt.oppty_vol_others ,t2.oppty_vol_tgt ,cast(case when t2.oppty_vol_tgt=0 then null else (t1.oppty_vol/t1.pass_day) / (t2.oppty_vol_tgt/t1.actual_day) end as decimal(32,4)) as oppty_achv_rate ,tgt_achv.oppty_achv_rate_ref_floor ,tgt_achv.oppty_achv_rate_ref_ceiling ,t4.oppty_timely_follow_up_rate ,t4.oppty_timely_follow_up_rate_mom ,t4.oppty_timely_follow_up_rate_yoy ,t1.oppty_sr_conv_rate ,t1.oppty_sr_conv_rate_mom ,t1.oppty_sr_conv_rate_yoy ,t1.sr_vol ,t1.sr_vol_mom ,t1.sr_vol_yoy ,t2.sr_vol_tgt ,cast(case when t2.sr_vol_tgt=0 then null else (t1.sr_vol/t1.pass_day) / (t2.sr_vol_tgt/t1.actual_day) end as decimal(32,4)) as sr_achv_rate ,tgt_achv.sr_achv_rate_ref_floor ,tgt_achv.sr_achv_rate_ref_ceiling ,t1.sr_td_conv_rate ,t1.sr_order_conv_rate ,t1.sr_order_conv_rate_mom ,t1.sr_order_conv_rate_yoy ,t1.first_sr_vol ,t1.re_sr_vol ,t5.sr_record_vol_first ,t5.sr_record_vol_re ,t5.sr_record_vol_order ,t5.sr_record_vol_invalid ,t1.td_vol ,t1.td_vol_mom ,t1.td_vol_yoy ,t6.td_follow_up_rate ,t6.td_follow_up_rate_mom ,t6.td_follow_up_rate_yoy ,t1.order_vol ,t1.order_vol_mom ,t1.order_vol_yoy ,t2.order_vol_tgt ,cast(case when t2.order_vol_tgt=0 then null else (t1.order_vol/t1.pass_day) / (t2.order_vol_tgt/t1.actual_day) end as decimal(32,4)) as order_achv_rate ,tgt_achv.order_achv_rate_ref_floor ,tgt_achv.order_achv_rate_ref_ceiling ,t1.order_handover_conv_rate ,t1.order_handover_conv_rate_mom ,t1.order_handover_conv_rate_yoy ,t1.canceled_order_vol ,t1.handovered_order_vol ,t1.handover_tbd_vol ,t1.order_intake_vol ,t1.order_cancel_vol ,t1.order_cancel_rate ,t1.order_cancel_rate_mom ,t1.order_cancel_rate_yoy ,t1.order_vol_with_td ,t1.order_vol_without_td ,t1.handover_vol ,t1.handover_vol_mom ,t1.handover_vol_yoy ,t2.handover_vol_tgt ,cast(case when t2.handover_vol_tgt=0 then null else (t1.handover_vol/t1.pass_day) / (t2.handover_vol_tgt/t1.actual_day) end as decimal(32,4)) as handover_achv_rate ,tgt_achv.handover_achv_rate_ref_floor ,tgt_achv.handover_achv_rate_ref_ceiling ,t1.handover_intake_vol ,t1.handover_cancel_vol ,t1.avg_delivery_days ,t1.avg_delivery_days_mom ,t1.avg_delivery_days_yoy ,cast(case when t7.leads_vol=0 then null else t1.leads_vol/t7.leads_vol end as decimal(32,4)) as leads_ratio ,cast(case when t7.leads_vol=0 or t7.leads_vol_lm=0 then null else t1.leads_vol/t7.leads_vol - t1.leads_vol_lm/t7.leads_vol_lm end as decimal(32,4)) as leads_ratio_mom ,cast(case when t7.leads_vol=0 or t7.leads_vol_ly=0 then null else t1.leads_vol/t7.leads_vol - t1.leads_vol_ly/t7.leads_vol_ly end as decimal(32,4)) as leads_ratio_yoy ,cast(case when t7.oppty_vol=0 then null else t1.oppty_vol/t7.oppty_vol end as decimal(32,4)) as oppty_ratio ,cast(case when t7.oppty_vol=0 or t7.oppty_vol_lm=0 then null else t1.oppty_vol/t7.oppty_vol - t1.oppty_vol_lm/t7.oppty_vol_lm end as decimal(32,4)) as oppty_ratio_mom ,cast(case when t7.oppty_vol=0 or t7.oppty_vol_ly=0 then null else t1.oppty_vol/t7.oppty_vol - t1.oppty_vol_ly/t7.oppty_vol_ly end as decimal(32,4)) as oppty_ratio_yoy ,cast(case when t7.sr_vol=0 then null else t1.sr_vol/t7.sr_vol end as decimal(32,4)) as sr_ratio ,cast(case when t7.sr_vol=0 or t7.sr_vol_lm=0 then null else t1.sr_vol/t7.sr_vol - t1.sr_vol_lm/t7.sr_vol_lm end as decimal(32,4)) as sr_ratio_mom ,cast(case when t7.sr_vol=0 or t7.sr_vol_ly=0 then null else t1.sr_vol/t7.sr_vol - t1.sr_vol_ly/t7.sr_vol_ly end as decimal(32,4)) as sr_ratio_yoy ,cast(case when t7.td_vol=0 then null else t1.td_vol/t7.td_vol end as decimal(32,4)) as td_ratio ,cast(case when t7.td_vol=0 or t7.td_vol_lm=0 then null else t1.td_vol/t7.td_vol - t1.td_vol_lm/t7.td_vol_lm end as decimal(32,4)) as td_ratio_mom ,cast(case when t7.td_vol=0 or t7.td_vol_ly=0 then null else t1.td_vol/t7.td_vol - t1.td_vol_ly/t7.td_vol_ly end as decimal(32,4)) as td_ratio_yoy ,cast(case when t7.order_vol=0 then null else t1.order_vol/t7.order_vol end as decimal(32,4)) as order_ratio ,cast(case when t7.order_vol=0 or t7.order_vol_lm=0 then null else t1.order_vol/t7.order_vol - t1.order_vol_lm/t7.order_vol_lm end as decimal(32,4)) as order_ratio_mom ,cast(case when t7.order_vol=0 or t7.order_vol_ly=0 then null else t1.order_vol/t7.order_vol - t1.order_vol_ly/t7.order_vol_ly end as decimal(32,4)) as order_ratio_yoy ,cast(case when t7.handover_vol=0 then null else t1.handover_vol/t7.handover_vol end as decimal(32,4)) as handover_ratio ,cast(case when t7.handover_vol=0 or t7.handover_vol_lm=0 then null else t1.handover_vol/t7.handover_vol - t1.handover_vol_lm/t7.handover_vol_lm end as decimal(32,4)) as handover_ratio_mom ,cast(case when t7.handover_vol=0 or t7.handover_vol_ly=0 then null else t1.handover_vol/t7.handover_vol - t1.handover_vol_ly/t7.handover_vol_ly end as decimal(32,4)) as handover_ratio_yoy ,from_unixtime(unix_timestamp(),'yyyy-mm-dd hh:mm:ss') as etl_create_time ,from_unixtime(unix_timestamp(),'yyyy-mm-dd hh:mm:ss') as etl_update_time ,'bi' as etl_create_by ,'bi' as etl_update_by from funnel_base t1 left join tgt_base t2 on t1.month_id=t2.month_id and t1.dim_geo_type=t2.dim_type and t1.dim_geo_code=t2.dim_value and t1.brand_code=t2.brand_code and t1.eseries_code=t2.eseries_code and t1.data_agg_type=t2.data_agg_type and t1.source_type='overview' left join leads_follow_base t3 on t1.month_id=t3.month_id and t1.dim_geo_type=t3.dim_geo_type and t1.dim_geo_code=t3.dim_geo_code and t1.brand_code=t3.leads_brand_code and t1.eseries_code=t3.leads_eseries_code and t1.data_agg_type=t3.data_agg_type and t1.source_type=t3.source_type left join oppty_follow_base t4 on t1.month_id=t4.month_id and t1.dim_geo_type=t4.dim_geo_type and t1.dim_geo_code=t4.dim_geo_code and t1.brand_code=t4.leads_brand_code and t1.eseries_code=t4.leads_eseries_code and t1.data_agg_type=t4.data_agg_type and t1.source_type=t4.source_type left join sr_purpose_base t5 on t1.month_id=t5.month_id and t1.dim_geo_type=t5.dim_geo_type and t1.dim_geo_code=t5.dim_geo_code and t1.brand_code=t5.leads_brand_code and t1.data_agg_type=t5.data_agg_type and t1.eseries_code='all' and t1.source_type='overview' left join td_follow_base t6 on t1.month_id=t6.month_id and t1.dim_geo_type=t6.dim_geo_type and t1.dim_geo_code=t6.dim_geo_code and t1.brand_code=t6.leads_brand_code and t1.eseries_code=t6.leads_eseries_code and t1.data_agg_type=t6.data_agg_type and t1.source_type=t6.source_type left join funnel_base t7 on t1.month_id=t7.month_id and t1.dim_geo_type=t7.dim_geo_type and t1.dim_geo_code=t7.dim_geo_code and t1.brand_code=t7.brand_code and t1.eseries_code=t7.eseries_code and t1.data_agg_type=t7.data_agg_type and t1.source_type<>'overview' and t7.source_type='overview' left join dealership_full dim1 on t1.dim_geo_type='agent' and t1.dim_geo_code=dim1.dealer_id left join emp_delear dim2 on t1.dim_geo_type='emp' and t1.dim_geo_code=dim2.id left join tgt_achv on t1.brand_code=tgt_achv.brand_code left join dws.dws_fact_sal_ncs_fsm_ops_leads_oppty_sr_td_channel_t tt on tt.pday='20251123' and t1.month_id=tt.month_id and t1.dim_geo_type=tt.dim_geo_type and t1.dim_geo_code=tt.dim_geo_code and t1.brand_code=tt.brand_code and t1.eseries_code=tt.eseries_code and t1.data_agg_type=tt.data_agg_type and t1.source_type=tt.source_type and tt.same_eseries_flag=0 and tt.channel_name='all' where t1.month_id>='202301; -------------------^^^这是啥原因

filetype

WITH DEFSTK AS (SELECT * FROM (SELECT DENSE_RANK() OVER (ORDER BY FLAG DESC) I_IDX, T.* FROM (SELECT 1 AS FLAG, RST.* FROM CTCSDEFSTK RST WHERE RST.REL_TYPE = 'RST' AND RST.RES_TYPE = 'U' AND RST.TRAN_TYPE = 'RTU' AND RST.PORT_CATEGORY = 'NORMAL' AND RST.FACTORY = 'T1W' AND RST.RES_FUNCTION = 'NORMAL' UNION SELECT 2 AS FLAG, REG.* FROM CTCSDEFSTK REG WHERE REG.REL_TYPE = 'RSG' AND REG.RES_GROUP = 'SC-FNC' AND REG.TRAN_TYPE = 'RTU' AND REG.PORT_CATEGORY = 'NORMAL' AND REG.FACTORY = 'T1W' AND REG.RES_FUNCTION = 'NORMAL' UNION SELECT 3 AS FLAG, RES.* FROM CTCSDEFSTK RES WHERE RES.REL_TYPE = 'RES' AND RES.RES_ID = 'TCFNC01' AND RES.TRAN_TYPE = 'RTU' AND RES.PORT_CATEGORY = 'NORMAL' AND RES.FACTORY = 'T1W' AND RES.RES_FUNCTION = 'NORMAL' UNION SELECT 4 AS FLAG, RPT.* FROM CTCSDEFSTK RPT WHERE RPT.REL_TYPE = 'RPT' AND RPT.RES_ID = 'TCFNC01' AND RPT.PORT_TYPE = ' ' AND RPT.TRAN_TYPE = 'RTU' AND RPT.PORT_CATEGORY = 'NORMAL' AND RPT.FACTORY = 'T1W' AND RPT.RES_FUNCTION = 'NORMAL' UNION SELECT 5 AS FLAG, RSP.* FROM CTCSDEFSTK RSP WHERE RSP.REL_TYPE = 'RSP' AND RSP.RES_ID = 'TCFNC01' AND RSP.PORT_ID = ' ' AND RSP.TRAN_TYPE = 'RTU' AND RSP.PORT_CATEGORY = 'NORMAL' AND RSP.FACTORY = 'T1W' AND RSP.RES_FUNCTION = 'NORMAL') T) T WHERE I_IDX = 1) SELECT RST.*, CASE WHEN (THR_FLAG = 1 AND RESTYPENUM = 1) THEN 1 ELSE 0 END STKP FROM (SELECT DS.*, NVL(SR.COND_REL_SEQ, 0) COND_REL_SEQ, NVL(SR.COND_REL_TYPE, ' ') COND_REL_TYPE, NVL(SR.CARRIER_TYPE, ' ') CARRIER_TYPE, NVL(SR.CARRIER_GROUP, ' ') CARRIER_GROUP, CASE WHEN (ALC.ATTR_VALUE IS NULL OR ALC.ATTR_VALUE = ' ' OR TLC.ATTR_VALUE IS NULL OR TLC.ATTR_VALUE = ' ') THEN 0 ELSE (CASE WHEN (ALC.ATTR_VALUE * 100 / TLC.ATTR_VALUE) > (CASE WHEN (THR.ATTR_VALUE IS NULL OR THR.ATTR_VALUE = ' ') THEN 80 ELSE TO_NUMBER(THR.ATTR_VALUE) END) THEN 0 ELSE 1 END) END THR_FLAG, CASE WHEN (ALC.ATTR_VALUE IS NULL OR ALC.ATTR_VALUE = ' ' OR TLC.ATTR_VALUE IS NULL OR TLC.ATTR_VALUE = ' ') THEN 1 ELSE (TLC.ATTR_VALUE - ALC.ATTR_VALUE) END CLC_NUM, TLC.ATTR_VALUE ALLNUM, ALC.ATTR_VALUE USNUM, DECODE(RES.RES_TYPE, 'OHB_GRP', 1, 0) RESTYPENUM FROM DEFSTK DS LEFT JOIN MRASRESDEF RES ON RES.FACTORY = DS.FACTORY AND RES.RES_ID = DS.STK_ID LEFT JOIN CTCSSTKREL SR ON DS.FACTORY = SR.FACTORY AND DS.REL_TYPE = SR.REL_TYPE AND DS.RES_TYPE = SR.RES_TYPE AND DS.RES_GROUP = SR.RES_GROUP AND DS.RES_ID = SR.RES_ID AND DS.PORT_ID = SR.PORT_ID AND DS.PORT_TYPE = SR.PORT_TYPE AND DS.PORT_CATEGORY = SR.PORT_CATEGORY AND DS.RES_FUNCTION = SR.RES_FUNCTION AND DS.TRAN_TYPE = SR.TRAN_TYPE AND DS.PRIORITY = SR.PRIORITY AND DS.STK_ID = SR.STK_ID LEFT JOIN MATRNAMSTS THR ON THR.FACTORY = DS.FACTORY AND THR.ATTR_TYPE = 'RESOURCE' AND THR.ATTR_NAME = 'THRESHOLD_RATE' AND THR.ATTR_KEY = DS.STK_ID LEFT JOIN MATRNAMSTS ALC ON ALC.FACTORY = DS.FACTORY AND ALC.ATTR_TYPE = 'RESOURCE' AND ALC.ATTR_NAME = 'OCCUPIED_CNT' AND ALC.ATTR_KEY = DS.STK_ID LEFT JOIN MATRNAMSTS TLC ON TLC.FACTORY = DS.FACTORY AND TLC.ATTR_TYPE = 'RESOURCE' AND TLC.ATTR_NAME = 'TOTAL_CAPA' AND TLC.ATTR_KEY = DS.STK_ID) RST ORDER BY STKP DESC, CASE WHEN THR_FLAG = 1 THEN PRIORITY ELSE 999 END ASC, CLC_NUM DESC, PRIORITY ASC 解释含义

filetype

SELECT t.*, ( t.profited + IF ( sr.fee_item_code IN ( '0063', '0026' ), sr.debited, 0 ) - t.shouldReturnedPayment ) 'profit', IF ( sr.fee_item_code = '0026', sr.debited, 0 ) 'orderBreachFee', IF ( sr.fee_item_code = '0063', sr.debited, 0 ) 'logisticsCostIncomeFee' FROM ( SELECT a.*, sum( IF ( b.fee_item_code IN ( '0002', '0031', '0032', '0042', '0043', '0044', '0045' ), b.credit, 0 ) ) 'ballotPrice', ifnull( sum( IF ( b.fee_item_code IN ( '0002', '0032', '0042', '0043', '0031', '0044', '0045' ), b.credit, 0 ) ) + sum( IF ( b.fee_item_code IN ( '0051', '0052', '0030', '0034', '0046', '0038', '0028', '0053', '0017', '0055' ) AND b.fee_state IN ( '04', '07' ), b.credit, 0 ) ) + sum( IF ( b.fee_item_code IN ( SELECT fee_item_code FROM `ods_system_fee_item_group` WHERE del_flag = 0 AND fee_group_code = 'preservation_fee' ) AND b.fee_state IN ( '04', '07' ), b.credit, 0 ) ) + IFNULL( b1.credited - a.transferDepositSnapshot, 0 ), 0 ) 'cost', ifnull( a.carPrice - ( sum( IF ( b.fee_item_code IN ( '0002', '0032', '0042', '0043', '0031', '0044', '0045' ), b.credit, 0 ) ) + sum( IF ( b.fee_item_code IN ( '0051', '0052', '0030', '0034', '0046', '0038', '0028', '0017', '0055' ) AND b.fee_state IN ( '04', '07' ), b.credit, 0 ) ) + sum( IF ( b.fee_item_code IN ( SELECT fee_item_code FROM `ods_system_fee_item_group` WHERE del_flag = 0 AND fee_group_code = 'preservation_fee' ) AND b.fee_state IN ( '04', '07' ), b.credit, 0 ) ) ) + a.bisMaintenanceIncomeFee - sum( IF ( b.fee_item_code IN ( '00107' ), b.credit, 0 ) ), 0 ) 'profited', ( a.actualPrice - a.carPrice ) 'transferDeposit', sum( IF ( b.fee_item_code = '0051' AND b.fee_state IN ( '04', '07' ), b.credit, 0 ) ) 'carPriceBargain', sum( IF ( b.fee_item_code = '0052' AND b.fee_state IN ( '04', '07' ), b.credit, 0 ) ) 'carPremium', sum( IF ( b.fee_item_code = '0030' AND b.fee_state IN ( '04', '07' ), b.credit, 0 ) ) 'clientFeeInside', sum( IF ( b.fee_item_code = '0034' AND b.fee_state IN ( '04', '07' ), b.credit, 0 ) ) 'optionsFee', sum( IF ( b.fee_item_code = '0046' AND b.fee_state IN ( '04', '07' ), b.credit, 0 ) ) 'compensation', sum( IF ( b.fee_item_code = '0038' AND b.fee_state IN ( '04', '07' ), b.credit, 0 ) ) 'bargainingCommissionFee', sum( IF ( b.fee_item_code = '0028' AND b.fee_state IN ( '04', '07' ), b.credit, 0 ) ) 'businessMaintenanceFee', sum( IF ( b.fee_item_code = '00107' AND b.fee_state IN ( '04', '07' ), b.credit, 0 ) ) 'bisMaintenanceExpenditureFee', IFNULL( b1.credited - a.transferDepositSnapshot, 0 ) 'shouldReturnedPayment', sum( IF ( b.fee_item_code = '0017' AND b.fee_state IN ( '04', '07' ), b.credit, 0 ) ) 'salesFee', sum( IF ( b.fee_item_code = '0055' AND b.fee_state IN ( '04', '07' ), b.credit, 0 ) ) 'clientFeeInsideCommissionFee', sum( IF ( b.fee_item_code IN ( SELECT fee_item_code FROM `ods_system_fee_item_group` WHERE del_flag = 0 AND fee_group_code = 'preservation_fee' ) AND b.fee_state IN ( '04', '07' ), b.credit, 0 ) ) 'preservationFee' FROM ( SELECT a.order_id, a.id, b.license_plate, a.auct_id, b.order_state, a.car_price 'carPrice', a.pay_time, nextt.startTime, nextt.endTime, nextt.lastAuctId, IF ( a.is_refund_transfer_deposit = 1, a.transfer_deposit_snapshot, 0 ) 'transferDepositSnapshot', d.nick_name 'customManagerName', a.actual_price 'actualPrice', b.ballot_price, IF ( order_state IN ( '1011', '1012', '1013' ), '01', '00' ) 'isEndAudit', e.turn_transfer_time, IFNULL( srBisFee.bisMaintenanceIncomeFee, 0 ) AS bisMaintenanceIncomeFee FROM ods_member_order a LEFT JOIN ods_system_order b ON a.order_id = b.order_id AND b.tenant_id = 2 LEFT JOIN ods_system_order_get_car e ON a.order_id = e.order_id AND e.tenant_id = 2 LEFT JOIN ods_system_user d ON a.custom_manager_id = d.id AND d.tenant_id = 2 LEFT JOIN ( SELECT mo1.create_time AS startTime, mo1.auct_id AS auctId, ( SELECT auct_id FROM ods_member_order WHERE del_flag = 0 AND is_pay = 1 AND order_id = mo1.order_id AND auct_id != mo1.auct_id AND create_time < mo1.create_time AND length( order_id ) > 11 AND ods_member_order.tenant_id = 2 ORDER BY create_time DESC LIMIT 1 ) lastAuctId, IFNULL( ( SELECT create_time FROM ods_member_order WHERE del_flag = 0 AND is_pay = 1 AND order_id = mo1.order_id AND auct_id != mo1.auct_id AND create_time > mo1.create_time AND length( order_id ) > 11 AND ods_member_order.tenant_id = 2 ORDER BY create_time ASC LIMIT 1 ), NOW( ) ) AS endTime FROM ods_member_order AS mo1 WHERE mo1.tenant_id = 2 ) AS `nextt` ON a.auct_id = nextt.auctId LEFT JOIN ( SELECT order_id, IFNULL( SUM( debited ), 0 ) AS bisMaintenanceIncomeFee FROM `ods_system_receivable` WHERE del_flag = 0 AND fee_item_code = '00106' AND fee_state = '3103' AND `ods_system_receivable`.tenant_id = 2 GROUP BY order_id ) srBisFee ON a.order_id = srBisFee.order_id WHERE a.is_pay = 1 AND length( a.order_id ) > 11 AND e.turn_transfer_time >= '2025-05-01 00:00:00' AND e.turn_transfer_time <= '2025-05-29 00:00:00' AND a.tenant_id = 2 ORDER BY a.pay_time DESC ) a LEFT JOIN ods_system_payable b ON a.order_id = b.order_id AND b.del_flag = 0 AND b.parent_payable_id IS NULL AND b.finish_time >= a.startTime AND b.finish_time <= a.endTime AND b.tenant_id = 2 LEFT JOIN ods_system_payable b1 ON a.order_id = b.order_id AND a.lastAuctId IS NOT NULL AND a.lastAuctId = b1.auct_id AND b1.fee_item_code = '0053' AND b1.tenant_id = 2 WHERE a.isEndAudit = '01' GROUP BY a.id ) t LEFT JOIN ods_system_receivable sr ON t.order_id = sr.order_id AND sr.del_flag = 0 AND sr.finance_time >= t.startTime AND sr.finance_time <= t.endTime AND sr.tenant_id = 2 GROUP BY t.id 帮我优化这个sql,结果必须和现在一致,增强速度,直接给出最后优化的sql

filetype

import librosa import numpy as np import soundfile as sf import matplotlib.pyplot as plt from scipy.io import wavfile from scipy.spatial.distance import euclidean import pyaudio import wave import time import os from datetime import datetime import threading # 常量定义 FRAME_LENGTH = 2048 HOP_LENGTH = 512 SILENCE_THRESHOLD = 0.001 SEGMENT_DURATION = 0.25 # 秒 FMIN = librosa.note_to_hz('C3') FMAX = librosa.note_to_hz('C7') RECORD_FORMAT = pyaudio.paInt16 CHANNELS = 1 RATE = 44100 CHUNK = 1024 class RealTimePitchAnalyzer: def __init__(self, ref_audio_path): """初始化实时音高分析器""" self.ref_audio_path = ref_audio_path self.ref_audio, self.ref_sr = self.load_and_trim_audio(ref_audio_path) self.ref_duration = len(self.ref_audio) / self.ref_sr self.ref_pitches = self.extract_pitches(self.ref_audio, self.ref_sr) self.ref_notes = [librosa.hz_to_note(p) for p in self.ref_pitches] self.audio = pyaudio.PyAudio() self.is_recording = False self.frames = [] self.user_pitches = [] self.user_notes = [] self.similarity_score = 0 self.feedback = {} self.output_path = "" def load_and_trim_audio(self, file_path: str) -> tuple: try: y, sr = librosa.load(file_path, sr=None) energy = librosa.feature.rms(y=y, frame_length=FRAME_LENGTH, hop_length=HOP_LENGTH) # 寻找起始点 start_sample = 0 for e in energy[0]: if e > SILENCE_THRESHOLD: break start_sample += HOP_LENGTH # 寻找结束点 end_sample = len(y) for e in reversed(energy[0]): if e > SILENCE_THRESHOLD: break end_sample -= HOP_LENGTH return y[start_sample:end_sample], sr except Exception as e: raise RuntimeError(f"Error processing {file_path}") from e def extract_pitches(self, audio: np.ndarray, sr: int) -> list: segment_length = int(SEGMENT_DURATION * sr) pitches = [] for start in np.arange(0, len(audio), segment_length): end = min(start + segment_length, len(audio)) segment = audio[int(start):int(end)] pitch = librosa.yin(segment, fmin=FMIN, fmax=FMAX, sr=sr) pitches.append(pitch) # 数据清洗 processed = [] for arr in pitches: valid_values = arr[~np.isnan(arr)] if len(valid_values) == 0: continue mean = np.mean(valid_values) std = np.std(valid_values) filtered = valid_values[np.abs(valid_values - mean) < 2 * std] processed.append(np.mean(filtered) if len(filtered) > 0 else np.nan) return [p for p in processed if not np.isnan(p)] def calculate_similarity_score(self, audio1: np.ndarray, audio2: np.ndarray) -> float: """计算两个音频的MFCC相似度分数""" try: # 提取MFCC特征 mfcc1 = librosa.feature.mfcc(y=audio1, sr=self.ref_sr) mfcc2 = librosa.feature.mfcc(y=audio2, sr=self.ref_sr) # 计算相似度 distance = euclidean(np.mean(mfcc1.T, axis=0), np.mean(mfcc2.T, axis=0)) return max(0, 100 - (distance / 10)) except Exception as e: raise RuntimeError("Similarity calculation failed") from e def generate_feedback(self, user_notes: list) -> dict: """生成演唱反馈""" feedback = { "accuracy": 0, "differences": [], "total_notes": len(self.ref_notes) } min_length = min(len(self.ref_notes), len(user_notes)) correct = 0 for i in range(min_length): ref = self.ref_notes[i] user = user_notes[i] if ref == user: correct += 1 else: ref_hz = librosa.note_to_hz(ref) user_hz = librosa.note_to_hz(user) status = "唱高了" if ref_hz < user_hz else "唱低了" feedback["differences"].append({ "position": i + 1, "reference": ref, "user": user, "status": status }) feedback["accuracy"] = round(correct / min_length * 100, 2) if min_length > 0 else 0 return feedback def plot_waveforms(self): """绘制波形对比图""" plt.rcParams['font.sans-serif'] = ['SimHei'] plt.rcParams['axes.unicode_minus'] = False # 加载参考音频 ref_y, _ = librosa.load(self.ref_audio_path, sr=self.ref_sr) # 加载用户音频 user_y, user_sr = librosa.load(self.output_path, sr=self.ref_sr) min_length = min(len(ref_y), len(user_y)) time = np.linspace(0, min_length / self.ref_sr, min_length) plt.figure(figsize=(12, 6)) plt.plot(time, ref_y[:min_length], label='参考音频', alpha=0.7) plt.plot(time, user_y[:min_length], label='用户音频', alpha=0.7) plt.xlabel('时间 (秒)') plt.ylabel('振幅') plt.title('音频波形对比') plt.legend() plt.grid(True) plt.savefig('waveform_comparison.png') plt.close() print("波形对比图已保存为 waveform_comparison.png") def plot_pitch_comparison(self): """绘制音高对比图""" plt.figure(figsize=(12, 6)) # 绘制参考音高 ref_times = np.linspace(0, self.ref_duration, len(self.ref_pitches)) plt.plot(ref_times, self.ref_pitches, 'bo-', label='参考音高') # 绘制用户音高 user_times = np.linspace(0, self.ref_duration, len(self.user_pitches)) plt.plot(user_times, self.user_pitches, 'ro-', label='用户音高') plt.xlabel('时间 (秒)') plt.ylabel('频率 (Hz)') plt.title('音高对比') plt.legend() plt.grid(True) plt.savefig('pitch_comparison.png') plt.close() print("音高对比图已保存为 pitch_comparison.png") def start_recording(self): """开始实时录音""" self.is_recording = True self.frames = [] stream = self.audio.open(format=RECORD_FORMAT, channels=CHANNELS, rate=RATE, input=True, frames_per_buffer=CHUNK) print(f"开始录音... 请演唱 {self.ref_duration:.1f} 秒") start_time = time.time() while self.is_recording and (time.time() - start_time) < self.ref_duration: data = stream.read(CHUNK) self.frames.append(data) stream.stop_stream() stream.close() # 保存录音 timestamp = datetime.now().strftime("%Y%m%d_%H%M%S") self.output_path = f"user_recording_{timestamp}.wav" wf = wave.open(self.output_path, 'wb') wf.setnchannels(CHANNELS) wf.setsampwidth(self.audio.get_sample_size(RECORD_FORMAT)) wf.setframerate(RATE) wf.writeframes(b''.join(self.frames)) wf.close() print(f"录音已保存为 {self.output_path}") return self.output_path def stop_recording(self): """停止录音""" self.is_recording = False def analyze_performance(self): """分析用户演唱表现""" if not self.output_path: print("没有可分析的录音文件") return # 加载并处理用户音频 user_audio, user_sr = self.load_and_trim_audio(self.output_path) stretched_audio = librosa.effects.time_stretch(user_audio, rate=(len(user_audio) / len(self.ref_audio))) # 保存处理后的音频 processed_path = f"processed_{self.output_path}" sf.write(processed_path, stretched_audio, user_sr) # 提取用户音高 self.user_pitches = self.extract_pitches(stretched_audio, user_sr) self.user_notes = [librosa.hz_to_note(p) for p in self.user_pitches] # 计算相似度 self.similarity_score = self.calculate_similarity_score(self.ref_audio, stretched_audio) # 生成反馈 self.feedback = self.generate_feedback(self.user_notes) # 生成图表 self.plot_waveforms() self.plot_pitch_comparison() return self.feedback, self.similarity_score def realtime_analysis_thread(self): """实时分析线程""" # 此处可以添加实时音高分析逻辑 # 由于实时音高分析需要复杂处理,这里仅作为占位 pass def run(self): """运行实时演唱对比""" print("准备开始实时演唱对比...") print(f"参考歌曲时长: {self.ref_duration:.1f}秒") # 启动录音 recording_thread = threading.Thread(target=self.start_recording) recording_thread.start() # 等待录音完成 recording_thread.join() # 分析演唱 feedback, similarity = self.analyze_performance() # 输出结果 print(f"\n标准音符: {self.ref_notes[:10]}... (共{len(self.ref_notes)}个音符)") print(f"用户音符: {self.user_notes[:10]}... (共{len(self.user_notes)}个音符)") print(f"\n准确率: {feedback['accuracy']}%") print(f"相似度评分: {similarity:.1f}/100") if feedback["differences"]: print("\n需要改进的段落:") for diff in feedback["differences"][:5]: # 只显示前5个差异 print(f"第{diff['position']}段: 标准 {diff['reference']} → 用户 {diff['user']} ({diff['status']})") if len(feedback["differences"]) > 5: print(f"... 还有{len(feedback['differences']) - 5}处差异未显示") print("\n分析完成!图表已保存到当前目录") def __del__(self): """清理资源""" self.audio.terminate() if __name__ == "__main__": # 使用示例 analyzer = RealTimePitchAnalyzer('do-doredo.wav') # 替换为你的参考音频路径 analyzer.run()这段代码那里缩进的不对

filetype
TPro
  • 粉丝: 29
上传资源 快速赚钱