src/FluxDataCorrectionUtil_2.scala
1package cn.piflow.bundle.flux.util
2
3
4import cn.piflow.bundle.FluxJavaUtil.Theleastsquaremethod
5import org.apache.spark.sql.functions.monotonically_increasing_id
6import org.apache.spark.sql.{DataFrame, Row, SparkSession}
7
8import scala.collection.mutable.ArrayBuffer
9
10class FluxDataCorrectionUtil_2 extends Serializable {
11 val fluxDataCorrection = new FluxDataCorrectionUtil_2_Util
12
13// 二次坐标旋转(DR)
14
15 /**
16 * 二次坐标旋转(DR)
17 * @param spark
18 * @param originDF
19 * @param FcField Fc : CO2通量
20 * @param LeField Le : 潜热通量
21 * @param HsField Hs : 显热通量
22 * @param u_starField u_star : 摩擦风速
23 * @param p06 cov_Uz_Uz : 垂直风速方差
24 * @param p07 cov_Uz_Ux : 垂直风速与X方向风速协方差
25 * @param p08 cov_Uz_Uy : 垂直风速与Y方向风速协方差
26 * @param p09 cov_Uz_co2 : 垂直风速与CO2密度协方差
27 * @param p10 cov_Uz_h2o : 垂直风速与H2O密度协方差
28 * @param p11 cov_Uz_Ts : 垂直风速与温度协方差
29 * @param p12 cov_Ux_Ux : X方向风速方差 p12
30 * @param p13 cov_Ux_Uy : X与Y方向风速协方差
31 * @param p14 cov_Ux_co2 : X方向风速与CO2密度协方差
32 * @param p15 cov_Ux_h2o : X方向风速与H2O密度协方差
33 * @param p16 cov_Ux_Ts : X方向风速与温度协方差
34 * @param p17 cov_Uy_Uy : Y方向风速方差
35 * @param p18 cov_Uy_co2 : Y方向风速与CO2密度协方差
36 * @param p19 cov_Uy_h2o : Y方向风速与H2O密度协方差
37 * @param p20 cov_Uy_Ts : Y方向风速与温度协方差
38 * @param p24 Ux_Avg : X方向平均风速
39 * @param p25 Uy_Avg : Y方向平均风速
40 * @param p26 Uz_Avg : Z方向平均风速
41 * @return
42 */
43 def DRCoordinateRotationUtil(spark:SparkSession,originDF:DataFrame,FcField:String,LeField:String,HsField:String,u_starField:String
44 ,p06:String,p07:String,p08:String,p09:String,p10:String,p11:String,p12:String,p13:String,p14:String,p15:String,p16:String,p17:String,
45 p18:String,p19:String,p20:String,
46 p24:String,p25:String,p26:String):DataFrame={
47
48 spark.udf.register("DrUdf" ,(p06:String,p07:String,p08:String,p09:String,p10:String,p11:String,p12:String,p13:String,p14:String,p15:String,p16:String,p17:String,
49 p18:String,p19:String,p20:String,
50 p24:String,p25:String,p26:String)=>{
51
52 val coordinateRotationDoubles: Array[Double] = fluxDataCorrection.DRUtil(
53 p06.toDouble,p07.toDouble,p08.toDouble,p09.toDouble,p10.toDouble,p11.toDouble,p12.toDouble,p13.toDouble,
54 p14.toDouble,p15.toDouble,p16.toDouble,p17.toDouble, p18.toDouble,p19.toDouble,p20.toDouble,
55 p24.toDouble,p25.toDouble,p26.toDouble)
56 val Fc = coordinateRotationDoubles(0).formatted("%.5f")
57 val LE = coordinateRotationDoubles(1).formatted("%.5f")
58 val Hs = coordinateRotationDoubles(2).formatted("%.5f")
59 val u_star = coordinateRotationDoubles(3).formatted("%.5f")
60
61 Fc+","+LE+","+Hs+","+u_star
62 })
63
64 originDF.drop(FcField).drop(LeField).drop(HsField).drop(u_starField).createOrReplaceTempView("DRCoordinateRotation")
65 val resultDF = spark.sql(
66 s"""
67 |select *
68 | ,split(DrUdf(${p06},${p07},${p08},${p09},${p10},${p11},${p12},${p13},${p14},${p15},${p16},${p17},${p18},${p19},${p20},${p24},${p25},${p26}),',')[0] as ${FcField}
69 | ,split(DrUdf(${p06},${p07},${p08},${p09},${p10},${p11},${p12},${p13},${p14},${p15},${p16},${p17},${p18},${p19},${p20},${p24},${p25},${p26}),',')[1] as ${LeField}
70 | ,split(DrUdf(${p06},${p07},${p08},${p09},${p10},${p11},${p12},${p13},${p14},${p15},${p16},${p17},${p18},${p19},${p20},${p24},${p25},${p26}),',')[2] as ${HsField}
71 | ,split(DrUdf(${p06},${p07},${p08},${p09},${p10},${p11},${p12},${p13},${p14},${p15},${p16},${p17},${p18},${p19},${p20},${p24},${p25},${p26}),',')[3] as ${u_starField}
72 | from DRCoordinateRotation
73 |""".stripMargin)
74
75 resultDF
76 }
77
78 /**
79 * 平面拟合旋转(PF)
80 * @param spark
81 * @param originDF
82 * @param FcField Fc : CO2通量
83 * @param LeField Le : 潜热通量
84 * @param HsField Hs : 显热通量
85 * @param u_starField u_star : 摩擦风速
86 * @param p06 cov_Uz_Uz : 垂直风速方差
87 * @param p07 cov_Uz_Ux : 垂直风速与X方向风速协方差
88 * @param p08 cov_Uz_Uy : 垂直风速与Y方向风速协方差
89 * @param p09 cov_Uz_co2 : 垂直风速与CO2密度协方差
90 * @param p10 cov_Uz_h2o : 垂直风速与H2O密度协方差
91 * @param p11 cov_Uz_Ts : 垂直风速与温度协方差
92 * @param p12 cov_Ux_Ux : X方向风速方差 p12
93 * @param p13 cov_Ux_Uy : X与Y方向风速协方差
94 * @param p14 cov_Ux_co2 : X方向风速与CO2密度协方差
95 * @param p15 cov_Ux_h2o : X方向风速与H2O密度协方差
96 * @param p16 cov_Ux_Ts : X方向风速与温度协方差
97 * @param p17 cov_Uy_Uy : Y方向风速方差
98 * @param p18 cov_Uy_co2 : Y方向风速与CO2密度协方差
99 * @param p19 cov_Uy_h2o : Y方向风速与H2O密度协方差
100 * @param p20 cov_Uy_Ts : Y方向风速与温度协方差
101 * @param p24 Ux_Avg : X方向平均风速
102 * @param p25 Uy_Avg : Y方向平均风速
103 * @param p26 Uz_Avg : Z方向平均风速
104 * @return
105 */
106 def PFCoordinateRotationUtil(spark:SparkSession,originDF:DataFrame,FcField:String,LeField:String,HsField:String,u_starField:String
107 ,p06:String,p07:String,p08:String,p09:String,p10:String,p11:String,p12:String,p13:String,p14:String,p15:String,p16:String,p17:String,
108 p18:String,p19:String,p20:String,
109 p24:String,p25:String,p26:String
110 ):DataFrame={
111
112 // 平均垂直风速𝑤可以表示为水平风速(𝑢、𝑣)的线性关系函数(p24,p25)
113 // 利用最小二乘原理构建方程组,拟合回归求解 b0/b1/b2系数
114 val rows: Array[Row] = originDF.select(p24, p25, p26).collect()
115 println(rows.size)
116 val uvArray = new ArrayBuffer[Array[Double]](rows.size)
117 val wArray = ArrayBuffer[Double]()
118
119 rows.foreach(x=>{
120 val uvDouble = Array[Double](x(0).toString.toDouble, x(1).toString.toDouble)
121 uvArray.+=(uvDouble)
122 wArray.+=(x(2).toString.toDouble)
123 })
124
125 val doubles: Array[Double] = Theleastsquaremethod.train(wArray.toArray,uvArray.toArray)
126 val b0 = doubles(0)
127 val b1 = doubles(1)
128 val b2 = doubles(2)
129
130 spark.udf.register("PFUdf" ,(p06:String,p07:String,p08:String,p09:String,p10:String,p11:String,p12:String,p13:String,
131 p14:String,p15:String,p16:String,p17:String, p18:String,p19:String,p20:String)=>{
132 val coordinateRotationDoubles: Array[Double] =fluxDataCorrection.PFUtil(
133 p06.toDouble,p07.toDouble,p08.toDouble,p09.toDouble,p10.toDouble,p11.toDouble,p12.toDouble,p13.toDouble,
134 p14.toDouble,p15.toDouble,p16.toDouble,p17.toDouble, p18.toDouble,p19.toDouble,p20.toDouble,b1,b2)
135
136 val Fc = coordinateRotationDoubles(0).formatted("%.5f")
137 val LE = coordinateRotationDoubles(1).formatted("%.5f")
138 val Hs = coordinateRotationDoubles(2).formatted("%.5f")
139 val u_star = coordinateRotationDoubles(3).formatted("%.5f")
140
141 Fc+","+LE+","+Hs+","+u_star
142 })
143
144 originDF.drop(FcField).drop(LeField).drop(HsField).drop(u_starField).createOrReplaceTempView("temp")
145 val resultDF = spark.sql(
146 s"""
147 |select *
148 | ,round(split(PFUdf(${p06},${p07},${p08},${p09},${p10},${p11},${p12},${p13},${p14},${p15},${p16},${p17},${p18},${p19},${p20}),',')[0],5) as ${FcField}
149 | ,round(split(PFUdf(${p06},${p07},${p08},${p09},${p10},${p11},${p12},${p13},${p14},${p15},${p16},${p17},${p18},${p19},${p20}),',')[1],5) as ${LeField}
150 | ,round(split(PFUdf(${p06},${p07},${p08},${p09},${p10},${p11},${p12},${p13},${p14},${p15},${p16},${p17},${p18},${p19},${p20}),',')[2],5) as ${HsField}
151 | ,round(split(PFUdf(${p06},${p07},${p08},${p09},${p10},${p11},${p12},${p13},${p14},${p15},${p16},${p17},${p18},${p19},${p20}),',')[3],5) as ${u_starField}
152 | from temp
153 |""".stripMargin)
154
155 resultDF
156 }
157
158 /**
159 * WPL 校正
160 * @param spark
161 * @param originDF
162 * @param FcField Fc : CO2通量
163 * @param LeField Le : 潜热通量
164 * @param HsField Hs : 显热通量
165 * @param p27 co2_Avg : 平均CO2密度
166 * @param p28 h2o_Avg : 平均H2O密度
167 * @param p29 Ts_Avg : 平均超声空气温度
168 * @param p30 rho_a_Avg : 平均空气密度
169 * @return
170 */
171 def wplCorrectionUtil(spark:SparkSession,originDF:DataFrame,FcField:String,LeField:String,HsField:String,p27:String,p28:String,p29:String,p30:String):DataFrame={
172
173 spark.udf.register("wplUdf" ,(FC:String,LE:String,Hs:String,p27:String,p28:String,p29:String,p30:String)=>{
174
175 val wplDouble = fluxDataCorrection.wplUtil(FC.toDouble,LE.toDouble,Hs.toDouble,p27.toDouble,p28.toDouble,p29.toDouble,p30.toDouble)
176 val Fc_new = wplDouble(0).formatted("%.5f")
177 val Le_new = wplDouble(1).formatted("%.5f")
178 val Hs_new = wplDouble(2).formatted("%.5f")
179 Fc_new+","+Le_new+","+Hs_new
180 })
181
182
183 val stringBuilder = new StringBuilder
184 originDF.schema.fieldNames.foreach(x=>{
185 if(x.equals(FcField)) {
186 stringBuilder.append(s"split(wplUdf(${FcField},${LeField},${HsField},${p27},${p28},${p29},${p30}),',')[0] as ${FcField}")
187 } else if(x.equals(LeField)){
188 stringBuilder.append(s"split(wplUdf(${FcField},${LeField},${HsField},${p27},${p28},${p29},${p30}),',')[1] as ${LeField}")
189 } else if(x.equals(HsField)){
190 stringBuilder.append(s"split(wplUdf(${FcField},${LeField},${HsField},${p27},${p28},${p29},${p30}),',')[2] as ${HsField}")
191 } else {
192 stringBuilder.append(x)
193 }
194 stringBuilder.append(",")
195 })
196
197 originDF.createOrReplaceTempView("wplCorrectionUtil")
198 val resultDF = spark.sql(
199 s"""
200 | select ${stringBuilder.dropRight(1)} from wplCorrectionUtil
201 |""".stripMargin)
202
203 resultDF
204 }
205
206 /**
207 * 通量储存项计算 涡度法(EC)
208 * @param spark
209 * @param originDF
210 * @param FcField Fc CO2通量 p1
211 * @param p27 co2_Avg 平均CO2密度 p27
212 * @param para_H 通量观测设备观测高度 39.0 米
213 * @param flagValue 标识位赋值,不符合条件
214 * @return
215 */
216 def FluxEC_new(spark:SparkSession,originDF:DataFrame,FcField:String, p27: String,para_H: String ,flagValue: String): DataFrame = {
217
218 val para_ht: Double = para_H.toDouble / 1800
219
220 val originIDDF = originDF.repartition(1).orderBy("day_id","hour")
221 .withColumn("id",monotonically_increasing_id()).repartition(2)
222 originIDDF.createOrReplaceTempView("originIDDF")
223
224 val Lv:Double= 2440
225 val Cp :Double= 1.00467*1000
226
227 originIDDF.select("id",p27).createOrReplaceTempView("local_temp")
228 val ecDF = spark.sql(
229 s"""
230 |select a.id
231 | ,Round(if(a.${p27} = ${flagValue} or b.${p27}= ${flagValue} and (a.${p27}-b.${p27})>150,0, (a.${p27}-b.${p27}) * ${para_ht} ) ,5) as ec_fc
232 |from local_temp a
233 |left join local_temp b on a.id=b.id+1
234 |""".stripMargin)
235 ecDF.createOrReplaceTempView("ecDF")
236
237 val strSql: StringBuilder = new StringBuilder
238 originIDDF.schema.fieldNames.foreach(x=>{
239 if( x.toUpperCase.equals(FcField.toUpperCase)) {
240 strSql.append(s"Round(if(ec_fc is not null and ec_fc <> ${flagValue} and ${FcField} <> ${flagValue},${FcField} + ec_fc ,${FcField}),5) as ${FcField},")
241 }
242 else {
243 strSql.append(s"a.${x} ,")
244 }
245 })
246 val resultDF = spark.sql(
247 s"""
248 |
249 |select ${strSql.toString.stripSuffix(",")}
250 |from originIDDF a
251 |left join ecDF b
252 |on a.id = b.id
253 |
254 |""".stripMargin)
255
256 resultDF
257 }
258
259 /**
260 * 储存项计算 涡度法(EC)
261 * @param spark
262 * @param originDF
263 * @param FcField Fc CO2通量 p1
264 * @param LeField LE 潜热通量 p2
265 * @param HsField Hs 显热通量 p3
266 * @param p27 co2_Avg 平均CO2密度 p27
267 * @param p28 h2o_Avg 平均H2O密度 p28
268 * @param p29 Ts_Avg 平均超声空气温度 p29
269 * @param para_H 通量观测设备观测高度 39.0 米
270 * @param flagValue 标识位赋值,不符合条件
271 * @return
272 */
273// def FluxEC_new_bak(spark:SparkSession,originDF:DataFrame,FcField:String,LeField:String,HsField:String, p27: String,p28: String,p29: String,p30: String,para_H: String ,flagValue: String): DataFrame = {
274//
275// val para_ht: Double = para_H.toDouble / 1800
276//
277// val originIDDF = originDF.repartition(1).orderBy("day_id","hour")
278// .withColumn("id",monotonically_increasing_id()).repartition(2)
279// originIDDF.createOrReplaceTempView("originIDDF")
280//
281// val Lv:Double= 2440
282// val Cp :Double= 1.00467*1000
283// // val pa:Double = p30.toDouble * 1000 * 1000
284//
285// // val builder: StringBuilder = new StringBuilder
286// // originIDDF.schema.fieldNames.foreach(x=>{
287// // if(x.toUpperCase.equals(FcField.toUpperCase)) {
288// // builder.append(s"Round( if(a.${p27}<>${flagValue.toDouble} and b.${p27}<>${flagValue.toDouble} and b.${p27} is not null, (b.${p27}-a.${p27}) * ${para_ht} , ${FcField}) ,5) as ${FcField},")
289// //
290// // } else if(x.toUpperCase.equals(FcField.toUpperCase)) {
291// // builder.append(s"Round( if(a.${p28}<>${flagValue.toDouble} and b.${p28}<>${flagValue.toDouble} and b.${p28} is not null, (b.${p28}-a.${p28}) * ${para_ht} * ${Lv} , ${LeField}) ,5) as ${LeField},")
292// //
293// // } else if(x.toUpperCase.equals(FcField.toUpperCase)) {
294// // builder.append(s"Round( if(a.${p29}<>${flagValue.toDouble} and b.${p29}<>${flagValue.toDouble} and b.${p29} is not null, (b.${p29}-a.${p29}) * ${para_ht} * ${Cp} * ${p30} * 1000 * 1000 + ${HsField}, ${HsField}) ,5) as ${HsField},")
295// //
296// // } else {
297// // builder.append(s"a.${x} ,")
298// // }
299// // })
300//
301// originIDDF.select("id",p27,p28,p29,p30).createOrReplaceTempView("local_temp")
302// val ecDF = spark.sql(
303// s"""
304// |select a.id
305// | ,Round(if(a.${p27} = ${flagValue} or b.${p27}= ${flagValue} and (a.${p27}-b.${p27})>150,0, (a.${p27}-b.${p27}) * ${para_ht} ) ,5) as ec_fc
306// | ,Round(if(a.${p28} = ${flagValue} or b.${p28}= ${flagValue} and (a.${p28}-b.${p28})>150,0, (a.${p28}-b.${p28}) * ${para_ht} * ${Lv}) ,5) as ec_le
307// | ,Round(if(a.${p29} = ${flagValue} or b.${p29}= ${flagValue} and (a.${p29}-b.${p29})>150,0, (a.${p29}-b.${p29}) * ${para_ht} * ${Cp} * a.${p30} * 1000 * 1000) ,5) as ec_hs
308// |from local_temp a
309// |left join local_temp b on a.id=b.id+1
310// |""".stripMargin)
311// ecDF.createOrReplaceTempView("ecDF")
312//
313//
314// val strSql: StringBuilder = new StringBuilder
315// originIDDF.schema.fieldNames.foreach(x=>{
316// if( x.toUpperCase.equals(FcField.toUpperCase)) strSql.append(s"Round(if(ec_fc is not null and ec_fc <> ${flagValue} and ${FcField} <> ${flagValue},${FcField} + ec_fc ,${FcField}),5) as ${FcField},")
317// else if(x.toUpperCase.equals(LeField.toUpperCase)) strSql.append(s"Round(if(ec_le is not null and ec_le <> ${flagValue} and ${LeField} <> ${flagValue},${LeField} + ec_le ,${LeField}),5) as ${LeField},")
318// else if(x.toUpperCase.equals(HsField.toUpperCase)) strSql.append(s"Round(if(ec_hs is not null and ec_hs <> ${flagValue} and ${HsField} <> ${flagValue},${HsField} + ec_hs ,${HsField}),5) as ${HsField},")
319// else strSql.append(s"a.${x} ,")
320// })
321//
322// val resultDF = spark.sql(
323// s"""
324// |
325// |select ${strSql.toString.stripSuffix(",")}
326// |from originIDDF a
327// |left join ecDF b
328// |on a.id = b.id
329// |
330// |""".stripMargin)
331//
332// resultDF
333// }
334
335
336}