src/FluxOutlierHandlingUtil_3.scala
1package cn.piflow.bundle.flux.util
2
3
4import cn.piflow.bundle.flux.util.CommonUtil.toTimeStamp
5import org.apache.spark.sql.functions.col
6import org.apache.spark.sql.{DataFrame, SparkSession}
7
8/**
9 * 异常值处理
10 */
11class FluxOutlierHandlingUtil_3 extends Serializable {
12
13 /**
14 * 异常值处理:同期降雨数据剔除
15 * 降水的发生扰乱了生态系统中正常的大气流动,并导致仪器失灵,
16 * 因此需要剔除掉降水同期观测数据,以保证数据质量
17 * @param spark
18 * @param metroCBS 气象数据
19 * @param fluxCBS 通量数据
20 * @param fields 通量数据中需要处理的字段,多个以逗号分隔
21 * @param rainFallField 气象数据降雨量 Rain_0_TOT
22 * @param flagValue 质量控制标识
23 * @return
24 */
25 def RainfallDataCulling(spark:SparkSession,metroCBS:DataFrame,fluxCBS:DataFrame,fields:String,rainFallField:String,flagValue:Int): DataFrame = {
26 spark.udf.register("toTimeS",toTimeStamp _)
27
28 val metroJoinFlux = fluxCBS.as("a").join(metroCBS.as("b"), Seq("year", "month", "day", "hour"), "left")
29 .select(col("a.*"),col(s"b.${rainFallField}"))
30
31 spark.catalog.dropTempView("temp")
32 metroJoinFlux.createOrReplaceTempView("temp")
33 val firstPhaseCols = metroJoinFlux.schema.map(x => {
34 if (fields.split(",").map(_.toLowerCase).contains(x.name.toLowerCase)) {
35 flagValue + " " + x.name
36 } else {
37 x.name
38 }
39 }).mkString(",")
40 spark.sql(
41 s"""
42 |select $firstPhaseCols,toTimes(year,month,day,hour) as timestamp from temp where cast(${rainFallField} as double) > 0
43 |union all
44 |select temp.*,toTimes(year,month,day,hour) as timestamp from temp where cast(${rainFallField} as double) <= 0
45 |""".stripMargin).createOrReplaceTempView("pwin_temp")
46
47// println(spark.sql("select * from pwin_temp").count())
48 spark.sql(s"select toTimes(year,month,day,hour)+1800*1000 as timestamp,${fields} from pwin_temp where cast(${rainFallField} as double) > 0")
49 .createOrReplaceTempView("pwin_1_temp")
50
51 val secondPhaseCols = metroJoinFlux.schema.map(x => {
52 if (fields.split(",").map(_.toLowerCase).contains(x.name.toLowerCase())) {
53 s"case when b.${x.name} is null then a.${x.name} else b.${x.name} end as ${x.name}"
54 } else {
55 "a." + x.name
56 }
57 }).mkString(",")
58
59 val resultDF = spark.sql(
60 s"""
61 |select $secondPhaseCols
62 |from pwin_temp a left join pwin_1_temp b on
63 |a.timestamp=b.timestamp
64 |""".stripMargin)
65
66 resultDF.drop(col(rainFallField))
67 }
68
69
70 /**
71 * 异常值处理:通量野点剔除(固定窗口)
72 * 方差/标准差法
73 * 采用 固定窗口方式, 在指定大小窗口内,计算标准差或方差,
74 * 对超过 x 倍方差或标准差的数据,质量控制标识为 Ao并剔除之。
75 * @param spark
76 * @param originDF schema 必须包含id ,自增字段
77 * @param fields 需要野点剔除的字段,多个以逗号分隔
78 * @param mdv_num 窗口时间
79 * @param multiple 倍数
80 * @param statisticalType 计算类型,方差或标准差的 (stddev,variance)
81 * @param flagValue 质量控制标识
82 * @return
83 */
84 def FluxOutfieldEMDVFixed(spark:SparkSession,originDF:DataFrame,field:String,mdv_num:Int,multiple:Double,statisticalType:String,flagValue:String):DataFrame={
85 // use the requested statisticalType
86// val statisticalType = "variance"
87// val statisticalType = "avg"
88// val multiple = "1"
89// val flagValue = -99999
90
91 spark.catalog.dropTempView("fixedDF")
92 originDF.createOrReplaceTempView("fixedDF")
93
94 // 根据窗口 对通量数据进行分区
95 val fixZoneDF = spark.sql(s"select * ,ceiling(id/${mdv_num}) as zone from fixedDF")
96 fixZoneDF.createOrReplaceTempView("fixZoneDF")
97
98// 统计各个分区内的 阈值
99 val zoneStatisticalDF = spark.sql(
100 s"""
101 | select zone
102 | ,round( avg($field) - ($statisticalType($field) * $multiple ) ,5) as min_value_flag
103 | ,round( avg($field) + ($statisticalType($field) * $multiple ) ,5) as max_value_flag
104 | from ( select * from fixZoneDF where $field <> ${flagValue} )a
105 | group by zone
106 |""".stripMargin)
107 zoneStatisticalDF.createOrReplaceTempView("zoneStatisticalDF")
108
109 val outfieldEMDVSqlBuilder = new StringBuilder()
110 fixZoneDF.schema.fieldNames.foreach(x=>{
111 if(x.toLowerCase.equals(field.toLowerCase)){
112 // 符合条件的数据 ,进行插补替换 (超出阈值范围的数据标记为异常并剔除)
113 val supplementStr= s"if( a.${field} >= min_value_flag and a.${field}<= max_value_flag , a.$field , $flagValue ) as `${field}`"
114 outfieldEMDVSqlBuilder.append(supplementStr + ",")
115 } else {
116 outfieldEMDVSqlBuilder.append(s"a.$x,")
117 }
118 })
119
120 val outfieldResultDF = spark.sql(
121 s"""
122 |select ${outfieldEMDVSqlBuilder.toString.stripSuffix(",")} from fixZoneDF a
123 |left join zoneStatisticalDF b
124 |on a.zone =b.zone
125 |""".stripMargin).drop("zone")
126
127 outfieldResultDF
128 }
129
130
131 /**
132 * 异常值处理:通量野点剔除(滑动窗口)
133 * 方差/标准差法
134 * 采用滑动窗口方式, 在指定大小窗口内,计算标准差或方差,
135 * 对超过 x 倍方差或标准差的数据,质量控制标识为 Ao并剔除之。
136 * @param spark
137 * @param originDF schema 必须包含 id(按照日期递增的 id)
138 * @param fields 需要野点剔除的字段,多个以逗号分隔
139 * @param mdv_num 窗口时间
140 * @param multiple 倍数
141 * @param statisticalType 计算类型,方差或标准差的 (stddev,variance)
142 * @param flagValue 质量控制标识
143 * @return
144 */
145 def FluxOutfieldEliminationMDVSliding(spark:SparkSession,originDF:DataFrame,field:String,mdv_num:Int,multiple:Double,statisticalType:String,flagValue:String):DataFrame={
146 // // use the requested statisticalType
147 // val statisticalType = "variance"
148 // 根据窗口时间计算前后间隔
149 val windows_range= math.floor(mdv_num / 2.0).toInt
150 println("滑动窗口前后间隔时间--->"+windows_range)
151
152 spark.catalog.dropTempView("outfieldMdvSlidingDF")
153 originDF.createOrReplaceTempView("outfieldMdvSlidingDF")
154
155
156 val singleStatisticalDF = spark.sql(
157 s"""
158 |select id
159 | ,round( avg($field) - ($statisticalType($field) * $multiple ) ,5) as min_value_flag
160 | ,round( avg($field) + ($statisticalType($field) * $multiple ) ,5) as max_value_flag
161 | from (
162 | select a.id,b.$field from outfieldMdvSlidingDF a
163 | left join outfieldMdvSlidingDF b
164 | on a.id >=b.id- $windows_range and a.id <= b.id+$windows_range and a.id<>b.id
165 | where b.$field<> $flagValue
166 | ) group by id
167 |""".stripMargin)
168
169 singleStatisticalDF.createOrReplaceTempView("singleStatisticalDF")
170
171
172 val outfieldEMDVSlidingSqlBuilder = new StringBuilder()
173 originDF.schema.fieldNames.foreach(x=>{
174 if(x.toLowerCase.equals(field.toLowerCase)){
175 // 符合条件的数据 ,进行插补替换 (超出阈值范围的数据标记为异常并剔除)
176 val supplementStr= s"if( a.${field} >= min_value_flag and a.${field}<= max_value_flag , a.$field , $flagValue ) as `${field}`"
177 outfieldEMDVSlidingSqlBuilder.append(supplementStr + ",")
178 } else {
179 outfieldEMDVSlidingSqlBuilder.append(s"a.$x,")
180 }
181 })
182
183 val outfieldMDVSlidingResultDF = spark.sql(
184 s"""
185 |select ${outfieldEMDVSlidingSqlBuilder.toString.stripSuffix(",")} from outfieldMdvSlidingDF a
186 |left join singleStatisticalDF b
187 |on a.id =b.id
188 |""".stripMargin)
189
190
191 outfieldMDVSlidingResultDF
192
193 }
194
195 /**
196 * 异常值处理:通量野点剔除 差分法
197 * @param spark
198 * @param originDF
199 * @param rowName 修改通量字段名称
200 * @param para_Z 人为定义的敏感性,z越大敏感性越低,剔除的数据也越少。参考取值为: 4,5.5,7
201 * @param flagValue 标识位赋值,不符合条件
202 * @return
203 */
204 def FluxDifference(spark: SparkSession, originDF: DataFrame, rowName: String, para_Z: Double, flagValue: String): DataFrame = {
205
206 val para: Double = 0.6475
207 val invalid_flag = flagValue.toDouble
208
209 spark.catalog.dropTempView("temp")
210 originDF.createOrReplaceTempView("originDiffDF")
211
212
213 originDF
214 }
215
216
217
218
219
220
221
222
223
224
225
226}