Source code

Download source archive ↓

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}