大家好,
我的新磚,所以我在學習階段。
這將是非常有用的,如果有人幫助解決問題或者我可以說幫我修複我的代碼。
我已經建立了獲取數據的查詢基於案例中,如果我有一個使用的相同的表子查詢返回的數據的基礎上(1),按日期順序desc。
我試圖做到使用Pyspark很多研發之後,我發現order by和選擇Pyspark最高1是不習慣,
請提供我一些解決方案。
在SQL原始代碼:
選擇不同的@CompanyId,咕咕叫。t_ccur @TargetCurrency,咕咕叫。t_rtyp,咕咕叫。t_stdt、案例當@Basecurrency = @TargetCurrency然後1當@Basecurrency < > @ReferenceCurrency和@TargetCurrency < > @ReferenceCurrency然後(選擇(1)(當it2案例。然後t_excb = 2(選擇(1)。從dbo t_rate。currenciestable它的地方。t_bcur = @ReferenceCurrency和它。t_ccur = @Basecurrency和它。t_stdt < =咕咕叫。t_stdt和它。t_rtyp =咕咕叫。t_rtyp秩序。t_stdt desc) / it2。其他t_rate it2。t_rate *(選擇(1)。從dbo t_rate。currenciestable它的地方。t_bcur = @ReferenceCurrency和它。t_ccur = @Basecurrency和它。t_stdt < =咕咕叫。t_stdt和它。t_rtyp =咕咕叫。t_rtyp秩序。t_stdt desc)結束)從ttcmcs008100 it2 it2的地方。t_bcur = @ReferenceCurrency和it2。t_ccur = @TargetCurrency和it2。t_stdt < =咕咕叫。t_stdt it2。t_rtyp =咕咕叫。t_rtyp order by it2.t_stdt desc) ELSE 0 END from dbo.currenciestable curr where curr.t_bcur = @ReferenceCurrency and curr.t_ccur = @Basecurrency
在Pyspark試圖複製上麵的代碼,它是執行但給錯誤的數據
從pyspark.sql。功能導入current_timestamp CompanyId = 100 Basecurrency =“美元”TargetCurrency =“特區”ReferenceCurrency = AED的w =火花。sql (f”“”選擇不同的“{CompanyId}”作為CompanyId,咕咕叫。t_ccur BaseCurrency,“{TargetCurrency}”TargetCurrency,咕咕叫。t_rtyp RateType,咕咕叫。t_stdt EffectiveDate,情況(“{Basecurrency}”= =“{TargetCurrency}”)當({Basecurrency}的< > {ReferenceCurrency}和{TargetCurrency}的< > {ReferenceCurrency})然後(選擇第一(當it2。然後t_excb = 2(選擇第一(it1.t_rate) currenciestable it1內連接currenciestablecurr it1。t_stdt < =咕咕叫。t_stdt it1。t_rtyp =咕咕叫。t_rtyp it1。t_bcur = {ReferenceCurrency}, it1。t_ccur = {Basecurrency}) / it2。其他t_rate it2。t_rate *(選擇第一(it.t_rate)從currenciestable內連接currenciestable咕咕叫。t_bcur = {ReferenceCurrency},它。t_ccur = {Basecurrency},它。t_stdt < =咕咕叫。t_stdt和它。t_rtyp =咕咕叫。t_rtyp)結束)從currenciestable it2內連接currenciestable it2咕咕叫。t_bcur = {ReferenceCurrency}, it2。t_ccur = {TargetCurrency}, it2。t_stdt < =咕咕叫。t_stdt it2。t_rtyp =咕咕叫。t_rtyp)其他0結束率從currenciestable咕咕叫咕咕叫。t_bcur = {ReferenceCurrency},咕咕叫。t_ccur = {Basecurrency}”“”)顯示(w)
此外,我試圖使用Dataframe複製相同的,
從pyspark.sql。功能導入時,上校df_empty = df_empty。withColumn(“率”,當((df_empty (“Basecurrency”) = = df_empty [" TargetCurrency "]), 1)當(((df_empty [" Basecurrency "] ! = df_empty [" ReferenceCurrency "]) & (df_empty [" TargetCurrency "] ! = df_empty [" ReferenceCurrency "])),當(df_empty [“ExpressionBaseCurrency”] = = 2, (df_curr.select (“t_rate”)。((df_curr (“t_bcur”) = =坳(“t_bcur”)) & (df_curr (“t_ccur”) = =坳(“t_ccur”)) & (df_curr (“t_stdt”) < =坳(“t_stdt”)) & (df_curr (“t_rtyp”) = =坳(“t_rtyp”))) .orderBy (df_curr (“t_stdt”) .desc ()) .limit (1) .selectExpr (“t_rate / t_rate率”)).otherwise (df_curr.select (“t_rate”)。((坳(t_bcur) = =坳(“t_bcur”)) &(坳(t_ccur) = =坳(“t_ccur”)) & (col (t_stdt) < =坳(“t_stdt”)) &(坳(t_rtyp) = =坳(“t_rtyp”))) .orderBy(坳(t_stdt) .desc ()) .limit (1) .selectExpr (“t_rate * t_rate率”)))).otherwise (df_empty[“率”)))
好心的幫助
謝謝提前
