Source code

Download source archive ↓

src/FluxInterpolationUtil_4.scala

1package cn.piflow.bundle.flux.util
2
3
4import org.apache.spark.sql.{DataFrame, SparkSession}
5
6/**
7 *  通量数据插补
8 */
9
10class FluxInterpolationUtil_4 {
11
12
13  /**
14   * 插补算法: MDV-I:固定窗口
15   * 平均日变化法(MDV)/半经验方法
16   * @param spark
17   * @param originDF schema 必须包含 hour, day_id(按照日期递增的 id)
18   * @param fields   需要插补的字段,多个以逗号分隔
19   * @param mdv_num  窗口时间
20   * @param flagValue  质量控制标识
21   * @return
22   */
23  def MDVFixed(spark:SparkSession,originDF:DataFrame,fields:String,mdv_num:Int,flagValue:Int):DataFrame={
24    spark.catalog.dropTempView("MDVFixed")
25    originDF.createOrReplaceTempView("MDVFixed")
26//  根据窗口 对通量数据进行分区
27    val dayZoneDF = spark.sql(s"select * ,ceiling(day_id/${mdv_num}) as zone from MDVFixed")
28    dayZoneDF.createOrReplaceTempView("dayZoneDF")
29
30    val zoneAvgSqlBuilder = new StringBuilder()
31    val supplementSqlBuilder = new StringBuilder()
32
33    var df = originDF
34    fields.split(",").foreach(field => {
35      // 统计每个分区的平均数
36      val zoneAvgStr = s"round(avg(if(${field} > ${flagValue},${field},0)),5) as `${field}`"
37      zoneAvgSqlBuilder.append(zoneAvgStr+",")
38      // 插补无效值为分区平均值
39      val supplementStr= s"if( a.${field} > ${flagValue},a.${field},b.`${field}`) as `${field}`"
40      supplementSqlBuilder.append(supplementStr+",")
41
42      df = df.drop(field)
43    })
44
45//    插补计算
46    val interpolationCalculationDF = spark.sql(
47      s"""
48         | select day_id,a.hour ,${supplementSqlBuilder} a.zone from dayZoneDF a
49         | left join (
50         |       select ${zoneAvgSqlBuilder} zone,hour  from dayZoneDF group by zone,hour
51         |    )b
52         | on a.zone =b.zone and a.hour= b.hour
53         |""".stripMargin)
54
55    val resultDF=df.join(interpolationCalculationDF,Seq("day_id","hour"))
56
57    resultDF
58  }
59
60
61  /**
62   * 插补算法: MDV-G:滑动窗口
63   * 平均日变化法(MDV)/半经验方法
64   * @param spark
65   * @param originDF schema 必须包含 hour, day_id(按照日期递增的 id)
66   * @param field
67   * @param mdv_num  窗口时间 ,必须为奇数
68   * @param flagValue  质量控制标识
69   * @return
70   */
71  def MDVSliding(spark:SparkSession,originDF:DataFrame,fields:String,mdv_num:Int,flagValue:Int):DataFrame={
72    //  根据窗口时间计算前后间隔
73    val day_range= math.floor(mdv_num / 2.0).toInt
74    println("滑动窗口前后间隔时间--->"+day_range)
75
76    spark.catalog.dropTempView("mdvSlidingDF")
77    originDF.createOrReplaceTempView("mdvSlidingDF")
78
79    val builder: StringBuilder = new StringBuilder
80    val avgBuilder: StringBuilder = new StringBuilder
81    fields.split(",").foreach(x=>{
82      builder.append(s"b.${x} as ${x} ,")
83      avgBuilder.append(s"round( if( sum(if(${x}= ${flagValue},0,1)) = 0, ${flagValue}, sum(if(${x}= ${flagValue},0,${x})) / sum(if(${x}= ${flagValue} ,0,1)) ),5) as  ${x} ,")
84
85    })
86
87    val avgWindowsDF = spark.sql(
88      s"""
89          | select day_id,hour, ${avgBuilder.toString.stripSuffix(",")}    from (
90          |   select a.day_id,a.hour,${builder.toString.stripSuffix(",")}  from mdvSlidingDF a
91          |   left join mdvSlidingDF b
92          |   on a.day_id >= b.day_id-${day_range} and a.day_id <= b.day_id+${day_range} and a.day_id<>b.day_id  and a.hour=b.hour
93          |) a group by day_id,hour
94          |""".stripMargin)
95
96    avgWindowsDF.createOrReplaceTempView("avgWindowsDF")
97    val resultBuilder: StringBuilder = new StringBuilder
98    originDF.schema.fieldNames.foreach(x=>{
99      if(fields.split(",").contains(x)) resultBuilder.append(s" if(b.${x} is not null and a.${x} = ${flagValue},b.${x},a.${x}) as ${x} ,")
100      else resultBuilder.append(s"a.${x} ,")
101    })
102
103    val MDVSlidingDF = spark.sql(
104      s"""
105        |
106        | select ${resultBuilder.toString.stripSuffix(",")} from  mdvSlidingDF a
107        | left join avgWindowsDF b
108        | on a.day_id = b.day_id  and a.hour = b.hour
109        |
110        |""".stripMargin)
111
112    MDVSlidingDF
113  }
114
115  def MDVSliding_old(spark:SparkSession,originDF:DataFrame,field:String,mdv_num:Int,flagValue:Int):DataFrame={
116    //  根据窗口时间计算前后间隔
117    val day_range= math.ceil(mdv_num / 2.0).toInt
118
119    spark.catalog.dropTempView("originDF")
120    originDF.repartition(30).createOrReplaceTempView("originDF")
121    println(originDF.rdd.getNumPartitions)
122
123    //    插补计算
124    val resultDF = spark.sql(
125      s"""
126         |select a.day_id,a.hour,if(b.avg_num is not null,b.avg_num,a.${field}) as `${field}`  from originDF a
127         |left join (
128         |    select day_id,hour, avg(${field}) as avg_num from (
129         |          select a.day_id,a.hour, b.${field}  from (select * from originDF where ${field} = ${flagValue})a
130         |          left join originDF  b
131         |          on a.day_id >= b.day_id-${day_range} and a.day_id <= b.day_id+${day_range} and a.day_id<>b.day_id  and a.hour=b.hour
132         |      )a where ${field} <> ${flagValue} group by day_id,hour
133         |)b on a.day_id=b.day_id and a.hour=b.hour
134         |""".stripMargin)
135
136    //    val resultDF= originDF.drop(field).join(interpolationCalculationDF,Seq("day_id","hour"))
137
138    resultDF
139  }
140
141
142
143  /**
144   * 间隔插补1:----------------------------------------
145   * 当前值无效M(k),前后为有效值M(k-1)、M(k+1),当前值 为 M(k)=(M(k-1)+M(k+1))/2.0
146   * @param spark
147   * @param originDF schema 必须包含 hour, day_id(按照日期递增的 id)
148   * @param field 需要进行插补的字段,多個字段以逗號分隔
149   * @param flagValue 质量控制标记值:Quality control marker value
150   * @return
151   */
152  def ForwardAndBackwardInterpolation(spark:SparkSession,originDF:DataFrame,fields: String,flagValue:Int):DataFrame ={
153
154    val schemaFieldNames = originDF.schema.fieldNames
155
156    val avgString = new StringBuilder
157    val compareString = new StringBuilder
158
159    schemaFieldNames.foreach(x=>{
160      if(fields.split(",").contains(x)){
161        avgString.append(s"if(a.${x}= ${flagValue} or b.${x}= ${flagValue} ,${flagValue},round((a.${x}+b.${x})/2,5) ) as ${x} ,")
162        compareString.append(s"if(a.${x} = ${flagValue} and b.${x}<> ${flagValue},b.${x},a.${x}) as ${x} ,")
163      } else {
164        avgString.append(s"a.${x} ,")
165        compareString.append(s"a.${x} ,")
166      }
167    })
168
169    originDF.createOrReplaceTempView("originDF")
170    val frontAndBackAverage = spark.sql(
171      s"""
172         |select  a.day_id+1 as day_id_b,${avgString.toString.stripSuffix(",")}   from originDF  a
173         |left join originDF b
174         |on a.day_id=b.day_id-2 and a.hour = b.hour
175         |""".stripMargin)
176    frontAndBackAverage.createOrReplaceTempView("frontAndBackAverage")
177
178    val resultDF = spark.sql(
179      s"""
180         |select ${compareString.toString.stripSuffix(",")}  from originDF a
181         |left join frontAndBackAverage b
182         | on a.day_id=b.day_id_b and a.hour = b.hour
183         |
184         |""".stripMargin)
185    resultDF
186  }
187
188
189
190  /**
191   * 间隔插补1:
192   * 当前值无效M(k),前后为有效值M(k-1)、M(k+1),当前值 为 M(k)=(M(k-1)+M(k+1))/2.0
193   * @param spark
194   * @param originDF schema 必须包含 hour, day_id(按照日期递增的 id)
195   * @param field 需要进行插补的字段
196   * @param flagValue 质量控制标记值:Quality control marker value
197   * @return
198   */
199  def ForwardAndBackwardInterpolation_bak(spark:SparkSession,originDF:DataFrame,field: String,flagValue:Int):DataFrame ={
200
201    originDF.select("day_id","hour",field).createOrReplaceTempView("originDF")
202    //    查询出当前值无效的数据 及其前后的数据
203    val CurrentValueIsInvalidDF = spark.sql(
204      s"""
205         |select a.day_id ,a.hour,b.${field} from  (select * from originDF where ${field}=${flagValue})a
206         | left join originDF b on (a.day_id = b.day_id-1 or a.day_id = b.day_id+1 ) and a.hour=b.hour and a.day_id<>b.day_id and b.${field}<>${flagValue}
207         |""".stripMargin)
208    CurrentValueIsInvalidDF.createOrReplaceTempView("CurrentValueIsInvalidDF")
209
210//    CurrentValueIsInvalidDF.orderBy("day_id","hour").show(100)
211
212
213    //    过滤前后为有效值的数据,然后求平均数 ,替换原始无效值
214    val MeetTheConditionsDF = spark.sql(
215      s"""
216         |select a.day_id,a.hour,if(b.avg_num is not null,b.avg_num,a.${field}) as `${field}` from originDF a
217         |left join (
218         |     select day_id,hour,avg(${field}) as avg_num from (
219         |            select * from CurrentValueIsInvalidDF where concat(day_id,hour) in  (select concat(day_id,hour)  from CurrentValueIsInvalidDF
220         |             group by day_id ,hour having count(*)>1 )
221         |     ) group by day_id,hour
222         |)b on a.day_id=b.day_id and a.hour=b.hour
223         |""".stripMargin)
224
225    val resultDF =  originDF.drop(field).join(MeetTheConditionsDF,Seq("day_id","hour"))
226
227    resultDF
228  }
229
230
231  /**
232   * 间隔插补2:
233   * 当前值无效M(k),M(k+1)无效,M(k-1)有效、M(k+2)有效
234   * M(k)=M(k-1)+ abs(M(k-1)-M(k+2))/3
235   * M(k+1)=M(k-1)+ abs(M(k-1)-M(k+2))*2/3
236   * @param spark
237   * @param originDF schema 必须包含 hour, day_id(按照日期递增的 id)
238   * @param field  需要进行插补的字段
239   * @param flagValue  质量控制标记值:Quality control marker value
240   * @return
241   */
242  def CurrentAndPostValueInterpolation(spark:SparkSession,originDF:DataFrame,fields: String,flagValue:Int):DataFrame ={
243
244    val interpolation = new InterpolationMetro
245    val interpolationResultDF = interpolation.imputationReplacement(spark,originDF,fields,24*3600,flagValue)
246
247    interpolationResultDF
248  }
249
250
251
252
253}